The third part of this series shows how transactions work when the REPEATABLE READ and SERIALIZABLE transaction levels are used. I show these two together, because they work very similar and can easily be shown in one example.
REPEATABLE READ is the default transaction level in MySQL and the one that’s most commonly used. We start with the same data as in the last two examples – a simple InnoDB table called tt with one integer field called d, which holds three rows with the values 1, 2 and 3. Again, we need two MySQL monitor client windows to perform following steps:
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.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 CLIENT 1 ======== mysql> select * from tt; +------+ | d | +------+ | 1 | | 2 | | 3 | +------+ 3 rows in set (0.00 sec)
After client 2 has modified a row, client 1, who’s in transaction mode, still sees the unchanged data. This makes it possible for client 1 to always receive consistent (the C of ACID) data, while performing a transaction.
This example showed how REPEATABLE READ works. So what’s the difference to SERIALIZABLE? Let’s do a rollback on client 1 and change the transaction isolation level to SERIALIZABLE and let client 2 try to change another row:
CLIENT 1 ======== mysql> rollback; Query OK, 0 rows affected (0.00 sec) mysql> set session transaction isolation level serializable; Query OK, 0 rows affected (0.00 sec) mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> select * from tt; +------+ | d | +------+ | 1 | | 4 | | 3 | +------+ 3 rows in set (0.02 sec) CLIENT 2 ======== mysql> update tt set d=2 where d=4; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
This time, client 2 cannot update the row. After a few seconds of waiting, the attempt to update a row ends with the error message “Lock wait timeout exceeded”.
To sum this up: both REPEATABLE READ and SERIALIZABLE don’t allow dirty reads, nonrepeatable reads and phantom reads to happen. While REPEATABLE READ still allows another client to modify data, while he performs a transaction, SERIALIZABLE strictly disallows it. Because of this, REPEATABLE READ is in most cases the best compromise between fulfilling the ACID (atomicy, consistency, isolation, durability) principles and still giving the best possible performence.