Understanding MySQL Transaction Isolation Levels with Real‑World Examples

Four Transaction Isolation Levels

Read Uncommitted : Allows dirty reads, meaning a transaction can see uncommitted changes from other sessions.

Read Committed : Only sees data that has been committed; this is the default for many databases such as Oracle.

Repeatable Read : Guarantees that repeated reads within the same transaction return the same rows; InnoDB uses this as its default level, but phantom reads may still occur.

Serializable : Provides full serializability by acquiring table‑level shared locks, preventing all concurrency anomalies at the cost of performance.

Read Uncommitted Example

First set the session isolation level to read uncommitted and create a simple user table:

CREATE TABLE user (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE `uniq_name` USING BTREE (name)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

Two terminals simulate two concurrent transactions.

Transaction 1 :

mysql> start transaction;
mysql> insert into user(name) values('ziwenxie');

Transaction 2 :

mysql> start transaction;
mysql> select * from user;

Transaction 2 reads the row inserted by Transaction 1 even though Transaction 1 has not committed, demonstrating a dirty read under the Read Uncommitted level.

Read Committed Example

Switch to read committed to eliminate dirty reads:

set session transaction isolation level read committed;

Transaction 1 reads the initial row, then Transaction 2 updates the same row and commits.

-- Transaction 2
start transaction;
update user set name='lisi' where id=10;
commit;

When Transaction 1 reads the row again after Transaction 2's commit, it sees the new value, showing that the non‑repeatable‑read anomaly is still possible under Read Committed.

Repeatable Read (RR) Example

InnoDB’s default level is Repeatable Read, which eliminates non‑repeatable reads but may still allow phantom reads. To illustrate, we extend the table with a salary column and perform inserts and updates.

alter table user add salary int(11);
delete from user;
insert into user(name, salary) values('ziwenxie', 88888888);
-- Transaction 1
start transaction;
select * from user;  -- sees one row
update user set salary='4444';  -- affects two rows (phantom read)
select * from user;  -- both rows show salary 4444
commit;

Transaction 2 inserts a new row and commits. After the commit, Transaction 1 still sees the original snapshot, confirming that Repeatable Read prevents the new row from appearing during the transaction, yet the earlier update affected both rows, indicating that InnoDB does not fully prevent phantom reads in this scenario.

Serializable Example

Serializable enforces full serial execution by acquiring table‑level shared locks, guaranteeing the absence of dirty, non‑repeatable, and phantom reads, but it incurs a heavy performance penalty and is rarely used in production.

Conclusion

The article demonstrates how each isolation level behaves in MySQL using concrete command‑line sessions. Read Uncommitted permits dirty reads, Read Committed eliminates them but allows non‑repeatable reads, Repeatable Read (the InnoDB default) blocks non‑repeatable reads yet may still exhibit phantom reads, and Serializable provides the strongest guarantees at the cost of concurrency.