This series of articles will show you how the different transaction isolation levels work in practice. Most people who work with databases on a regular basis might have heared that there is a distiction how transactions can deal with concurrent reads and writes. However, their names and exact (but simular) functionality differ between the database systems. This shows, how the isolation levels work in MySQL.
To execute the examples, you need one simple InnoDB table and two opened MySQL monitor clients. With them, we can simulate slowly what in real world applications with multiple concurrent reads and writes can happen in less than a blink of an eye.
Here’s the definition of the table – very simple, indeed. Insert three rows with values from 1 to 3:
create table tt (d int) engine=innodb; insert into tt values (1), (2), (3);
We start with the least restrictive isolation level, the READ UNCOMMITTED.
To output the current isolation level, execute the command
select @@tx_isolation;
To change the isolation level, use the command
set session transaction isolation level read uncommitted;
Now open 2 MySQL monitor clients and execute following commands:
CLIENT 1 ======== mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> select * from tt; +------+ | d | +------+ | 1 | | 2 | | 3 | +------+ 3 rows in set (0.00 sec) CLIENT 2 ======== mysql> update tt set d=4 where d=2; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 CLIENT 1 ======== mysql> select * from tt; +------+ | d | +------+ | 1 | | 4 | | 3 | +------+ 3 rows in set (0.00 sec)
What we see here is a non repeatable read. After client 1 started the transaction and selected the data, client 2 changed one row. When client 1, still in transaction mode, performed a second SELECT statement, he got a different result.
We continue this example:
CLIENT 2 ======== mysql> insert into tt values (5); Query OK, 1 row affected (0.00 sec) CLIENT 1 ======== mysql> select * from tt; +------+ | d | +------+ | 1 | | 4 | | 3 | | 5 | +------+ 4 rows in set (0.02 sec)
After client 2 inserts a row, client 1 can immediately see this new row, while he’s still inside the transaction. This is called a phantom read.
As this example continues, client 2 also starts a transaction:
CLIENT 2 ======== mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> insert into tt values (6), (7); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> update tt set d=8 where d=1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 CLIENT 1 ======== mysql> select * from tt; +------+ | d | +------+ | 8 | | 4 | | 3 | | 5 | | 6 | | 7 | +------+ 6 rows in set (0.00 sec)
As client 2 starts the transaction, inserts 2 rows and updates another one, client 1 can immediately see the changes. Now, client 2 performs a rollback on his transaction:
CLIENT 2 ======== mysql> rollback; Query OK, 0 rows affected (0.00 sec) CLIENT 1 ======== mysql> select * from tt; +------+ | d | +------+ | 1 | | 4 | | 3 | | 5 | +------+ 4 rows in set (0.00 sec) mysql> rollback; Query OK, 0 rows affected (0.00 sec)
As client 2 rolls back his transaction, client 1 sees the same result as before client 2 started his transaction. This way, client 2 sees records that have never really existed in the table. This is called a dirty read.