MySQL Transaction Isolation Levels

Describe and test the transaction isolation levels in MySQL InnoDB.

Three Read Phenomena

• Dirty Read
• Non-Repeatable Read
• Phantom Read

Four Isolation Levels

• Read Uncommitted
• Read Committed
• Repeatable Read
• Serializable

Isolation Levels vs Read Phenomena

	Dirty reads	Non-repeatable reads	Phantoms
Read Uncommitted	may occur	may occur	may occur
Read Committed	don't occur	may occur	may occur
Repeatable Read	don't occur	don't occur	may occur
Serializable	don't occur	don't occur	don't occur

Try it yourself

create table users (
    id int primary key,
    name varchar(100)
);

insert into users(id, name) values(1, 'alice');
insert into users(id, name) values(2, 'billy');
insert into users(id, name) values(3, 'chris');

1. Dirty Reads occur in Read Uncommitted level.

2. Dirty Reads don't occur in Read Committed level.

3. Non-Repeatable Reads occur in Read Committed level.

4. Non-Repeatable Reads don't occur in Repeatable Read level.

5. Phantom Reads don't occur in Repeatable Read level if SELECT only.

It is more restrictive in Repeatable Read level of MySQL than SQL Standard, but it doesn't mean MySQL prevents Phantom Reads entirely in Repeatable Read level. See the following Part 6.

6. Phantom Reads occur in Repeatable Read level after UPDATE.

7. Phantom Reads don't occur in Serializable level.

7.1 A write ops waits another transaction to be committed.

7.2 A read ops waits another write transaction to be committed.

References

• Isolation - Wikipedia