Describe and test the transaction isolation levels in MySQL InnoDB.
- Dirty Read
- Non-Repeatable Read
- Phantom Read
- Read Uncommitted
- Read Committed
- Repeatable Read
- Serializable
| 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 |
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');It is more restrictive in
Repeatable Readlevel of MySQL than SQL Standard, but it doesn't mean MySQL preventsPhantom Readsentirely inRepeatable Readlevel. See the following Part 6.