The goal of this blog post is to explain the various types of transaction isolation levels available in MySQL. After reading the blog, you will be able to explain dirty reads, non-repeatable reads, and the concept of phantom rows as well.
What is the Isolation Level in MySQL?
Isolation (I) is one of the properties from ACID. It defines how each transaction is isolated from other transactions and is a critical component of application design. As per the SQL:1992 standard, InnoDB has four types of Isolation levels. Below, I have listed the types in order, and each transaction isolation level provides better consistency compared to the previous one.
- READ-UNCOMMITTED
- READ-COMMITTED
- REPEATABLE-READ – ( MySQL’s DEFAULT )
- SERIALIZABLE
You can change the isolation level using the variable “transaction_isolation” at runtime. As transaction isolation changes can impact the result sets of your queries, you most certainly want to test this in a non-production environment in order to evaluate the impact on your application.”
1 2 | mysql> set global transaction_isolation='read-committed'; Query OK, 0 rows affected (0.00 sec) |
READ-UNCOMMITTED:
- No locks
- Dirty reads, non-repeatable reads, phantom reads are possible
The below example will help to understand the “read-uncommitted” and how the dirty reads are happening. I am using two sessions – S1 and S2.
For session S1:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | mysql> set global transaction_isolation='read-uncommitted'; Query OK, 0 rows affected (0.00 sec) mysql> r Connection id: 16 Current database: percona mysql> select * from ReadUncommit; +----+------+ | id | name | +----+------+ | 1 | jc | | 2 | herc | | 3 | sri | +----+------+ 3 rows in set (0.00 sec) mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> update ReadUncommit set name='ram' where id=3; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 |
For session S2:
1 2 3 4 5 6 7 8 9 | mysql> select * from percona.ReadUncommit; +----+------+ | id | name | +----+------+ | 1 | jc | | 2 | herc | | 3 | ram | +----+------+ 3 rows in set (0.00 sec) |
At S1, I globally modified the transaction_isolation to read-uncommitted and started the transaction. I executed the UPDATE statement ( name = ram ) at S1 but did not commit the transaction yet. Then I created the S2 and executed the SELECT for the table, and I was able to see the uncommitted modified data. This is called dirty reads.
So, with “read-uncommitted”, the transactions from different sessions can view the modification from the different transactions before it commits.
READ-COMMITTED:
- Dirty reads are not possible
- Non-repeatable reads and phantom reads are possible
The below example will help to understand the “read-committed” and how the non-repeatable reads are happening. I am using two sessions – S1 and S2.
For session S1:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | mysql> set global transaction_isolation='read-committed'; Query OK, 0 rows affected (0.00 sec) mysql> r Connection id: 18 Current database: percona mysql> select * from ReadCommit; +----+------+ | id | name | +----+------+ | 1 | jc | | 2 | herc | | 3 | sri | +----+------+ 3 rows in set (0.00 sec) mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> update ReadCommit set name='ram' where id=3; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 |
For session S2:
1 2 3 4 5 6 7 8 9 | mysql> select * from percona.ReadCommit; +----+------+ | id | name | +----+------+ | 1 | jc | | 2 | herc | | 3 | sri | +----+------+ 3 rows in set (0.00 sec) |
At S1, I globally modified the transaction_isolation to “read-committed” and started the transaction. I executed the UPDATE statement ( name = ram ) at S1 but did not commit the transaction. Then I created S2 and executed the SELECT for the table, and I was not able to see the uncommitted modified data.
So, with “read-committed”, the transactions from different sessions can’t view the modification from the different transactions until it commits. Only committed modifications can be viewed.
Then, what is the drawback with “read-committed”?
Non-repeatable read is possible with the “read-committed”. Below, I explain how the non-repeatable read occurred.
For session S1:
1 2 3 4 5 6 7 8 9 10 11 12 | mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from ReadCommit; +----+------+ | id | name | +----+------+ | 1 | jc | | 2 | herc | | 3 | sri | +----+------+ 3 rows in set (0.00 sec) |
For session S2:
1 2 3 4 5 6 7 8 9 | mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> update percona.ReadCommit set name='ram' where id=3; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> commit; Query OK, 0 rows affected (0.00 sec) |
For session S1:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from ReadCommit; +----+------+ | id | name | +----+------+ | 1 | jc | | 2 | herc | | 3 | sri | +----+------+ 3 rows in set (0.00 sec) mysql> select * from ReadCommit; +----+------+ | id | name | +----+------+ | 1 | jc | | 2 | herc | | 3 | ram | +----+------+ 3 rows in set (0.00 sec) |
At S1, I started the transaction and executed the SELECT to view the data. Then at S2, I executed the UPDATE statement ( name = ram where id = 3) to modify the data. I committed the transaction on S2. Again, at S1 I executed the same SELECT to view the data inside the same transaction. But, this time I have a different result. This is called a non-repeatable read.
Having a different result for the same query inside the transaction is not fair, and it may lead your transaction to be inconsistent. “REPEATABLE-READ” will help to overcome this.
REPEATABLE-READ:
- Dirty reads and non-repeatable reads are not possible
- Phantom reads are possible
The below example will help to understand the “repeatable-read” and how the phantom reads are happening. I am using two sessions – S1 and S2.
For session S1:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | mysql> set global transaction_isolation='repeatable-read'; Query OK, 0 rows affected (0.00 sec) mysql> r Connection id: 20 Current database: percona mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from RepeatRead; +----+------+ | id | name | +----+------+ | 1 | jc | | 2 | herc | | 3 | sri | +----+------+ 3 rows in set (0.00 sec) |
For session S2:
1 2 3 4 5 6 7 8 9 | mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> update percona.RepeatRead set name='ram' where id=3; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> commit; Query OK, 0 rows affected (0.00 sec) |
For session S1:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from RepeatRead; +----+------+ | id | name | +----+------+ | 1 | jc | | 2 | herc | | 3 | sri | +----+------+ 3 rows in set (0.00 sec) mysql> select * from RepeatRead; +----+------+ | id | name | +----+------+ | 1 | jc | | 2 | herc | | 3 | sri | +----+------+ 3 rows in set (0.00 sec) |
At S1, I globally modified the transaction_isolation to “repeatable-read” and started the transaction. I executed the SELECT to view the data. Then I created S2 and executed the UPDATE statement ( name = ram where id = 3) to modify the data. I committed the transaction on S2. Again at S1, I executed the same SELECT to view the data inside the same transaction. There are no changes. So, here we overcome the problem of being read-committed.
At repeatable-read, the snapshot of the SELECT will be taken during the first execution of SELECT, and it will be until the transaction ends. Still, we may get the phantom rows with repeatable-read.
How Do Phantom Rows Occur?
The below example will help to understand how the phantom rows are occurring inside the transaction.
For session S1:
1 2 3 4 5 6 7 8 9 10 11 12 | mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from RepeatRead; +----+------+ | id | name | +----+------+ | 1 | jc | | 2 | herc | | 3 | sri | +----+------+ 3 rows in set (0.00 sec) |
For session S2:
1 2 3 4 5 6 7 8 | mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> insert into percona.RepeatRead values (4,'ram'); Query OK, 1 row affected (0.00 sec) mysql> commit; Query OK, 0 rows affected (0.00 sec) |
For session S1:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 | mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from RepeatRead; +----+------+ | id | name | +----+------+ | 1 | jc | | 2 | herc | | 3 | sri | +----+------+ 3 rows in set (0.00 sec) mysql> select * from RepeatRead; +----+------+ | id | name | +----+------+ | 1 | jc | | 2 | herc | | 3 | sri | +----+------+ 3 rows in set (0.00 sec) mysql> update percona.RepeatRead set name='sriram' where id=4; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from RepeatRead; +----+--------+ | id | name | +----+--------+ | 1 | jc | | 2 | herc | | 3 | sri | | 4 | sriram | +----+--------+ 4 rows in set (0.00 sec) |
From the above example, at S1, I have executed the SELECT to read the data. Then, I have inserted the row ( id=4 ) on S2. Again, I executed the SELECT at S1, and there are no changes because we are using repeatable-read isolation. Again at S1, I executed the UPDATE to modify the data which was inserted by S2 ( id = 4 ), then executed the same SELECT at S1. But, this time I have different results, which is called the phantom reads. This can be avoided with “SERIALIZABLE”.
SERIALIZABLE:
- No dirty reads
- No non-repeatable reads
- No phantom reads
The below example will help to understand the “serializable” isolation. I am using two sessions – S1 and S2.
For session S1:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | mysql> set global transaction_isolation='serializable'; Query OK, 0 rows affected (0.00 sec) mysql> r Connection id: 22 Current database: percona mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from Serialize; +----+------+ | id | name | +----+------+ | 1 | jc | | 2 | herc | | 3 | sri | +----+------+ 3 rows in set (0.00 sec) |
For session S2:
1 2 3 4 5 6 7 8 | mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> insert into percona.Serialize values (4,'ram'); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> update percona.Serialize set name='aaa' where id=3; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction |
From the above example, I globally modified the isolation level to “serializable” and started the transaction at S1. Then I created session 2 and tried to do an INSERT/UPDATE at S2… however, these statements timeout due to pre-existing locks being held. At the isolation level serializable, InnoDB implicitly converts all SELECT statements to SELECT FOR SHARE if autocommit is disabled. The SELECT statement from S1 sets shared locks (LOCK IN SHARE MODE) that permit other transactions to read the examined rows but not to update or delete them. So, the INSERT/UPDATE is failing.
Serializable ensures more consistency for the transactions, but you will have to address increased locking conditions in MySQL.
The call for papers for Percona Live is open! We’d love to receive submissions on topics related to open source databases such as MySQL, MongoDB, MariaDB, and PostgreSQL. To find out more visit percona.com/live
Shakti why insert’s get’s block here, anywhich ways it is locking the existing rows in share mode,new rows should be allowed or is it apply table level lock.