InnoDB Transaction Isolation Levels
To maintain transactional consistency in the database systems, It follows ACID properties. ACID stands for Atomicity, Consistency, Isolation and Durability, The isolation levels determines how transaction must be isolated from the data modifications made by any other transactions with in the database system, Below I have explained transaction isolation level in detail :
Dirty Read – The “Dirty Read” happens when a transaction reads data which is not yet committed so technically there is no isolation level in place. For example, Let’s consider transaction T1 updates a row and has not yet committed. meanwhile the transaction T2 reads the uncommitted updated row . If ever transaction T1 rollbacks, transaction T2 would have read the data that is considered never to have never existed . To conclude, we can’t consider such systems as transactional systems !
Non Repeatable Read – “Non Repeatable Read” happens when a transaction reads same row twice and reports different data each time . For example, Consider transaction T1 reads a data and another transaction T2 updates the same data and commit, If again transaction T1 rereads the same data, It will report a different data .
Phantom Read – “Phantom Read” occurs when two same queries are executed, but rows retrieved by the two are different . Let me explain with a example, Transaction T1 is reading data on a specific SELECT criteria by continuously executing the same query and Transaction T2 is continuously inserting data matching to the SELECT criteria of Transaction T1 . When Transaction T1 re-executes the same query, It will retrieve different set of rows this time
To address these challenges InnoDB have come-up with different isolation levels – Repeatable Read, Read Committed, Read Uncommitted and Serializable
Repeatable Read
We can’t see ANY DATA from other transaction until we are actually done with our transaction, Theoretically (and in many other database systems) this kind of an isolation level may cause phantom reads, but in InnoDB it’s not possible .
Read Committed
In Read Committed isolation level, You cannot see the data in Transaction T1 from Transaction T2 until it is committed in the Transaction T1, Note that Transaction T1 won’t see the data until it has committed it’s own data. So dirty reads are not possible in Read Committed isolation level but Non-Repeatable Reads and Phantom Reads are still possible .
Read Uncommitted
There is no isolation in Read Uncommitted transaction isolation level, Transaction T1 will see all the uncommitted transactions from Transaction T2, This means when Transaction T2 rollback, all data will be rolled back to what it was just like before start in Transaction T1 , This is called dirty read .
Secondly, when transaction T1 changes data and commits that data, transaction T2 still sees that data as changed during it transaction. Basically: it gets different results with the same query, which is called a non-repeatable read and allthough this is similar as a dirty read, we categorize it differently.
Finally, it is possible that transaction T1 inserts new rows in its transaction. Those records are called phantom rows and of course can dissappear when the transaction does a rollback.
Serializable
Serializable locks all records that a transaction select for all the other transactions. It means that when a transaction updates a record, it becomes impossible for others to select that record. It’s a great way to make absolutely sure that no transaction overwrites the actions of other transactions, but it comes with the costs of lots of record locking.
Examples
I have explained below the MySQL isolation levels :
create database isolate; use isolate; create table tab1 ( col1 int, col2 int); insert into tab1 values (2,4); insert into tab1 values (3,5); insert into tab1 values (6,8);
Read Uncommitted
Transaction T1
select @@tx_isolation; +-----------------+ | @@tx_isolation | +-----------------+ | REPEATABLE-READ | +-----------------+
Transaction T2
select @@tx_isolation; +-----------------+ | @@tx_isolation | +-----------------+ | REPEATABLE-READ | +-----------------+
Transaction T1
start transaction; Query OK, 0 rows affected (0.00 sec)
Transaction T2
set session transaction isolation level read uncommitted; Query OK, 0 rows affected (0.00 sec)
start transaction; Query OK, 0 rows affected (0.00 sec)
Transaction T1
select * from tab1; +------+------+ | col1 | col2 | +------+------+ | 2 | 4 | | 3 | 5 | | 6 | 8 | +------+------+ 3 rows in set (0.00 sec)
Transaction T2
select * from tab1; +------+------+ | col1 | col2 | +------+------+ | 2 | 4 | | 3 | 5 | | 6 | 8 | +------+------+ 3 rows in set (0.00 sec)
Transaction T1
update tab1 set col2 = col2 + 1; Query OK, 3 rows affected (0.00 sec) Rows matched: 3 Changed: 3 Warnings: 0
Transaction T2 (dirty read)
select * from tab1; +------+------+ | col1 | col2 | +------+------+ | 2 | 5 | | 3 | 6 | | 6 | 9 | +------+------+ 3 rows in set (0.00 sec)
Transaction T1
rollback; Query OK, 0 rows affected (0.01 sec)
Transaction T2
select * from tab1; +------+------+ | col1 | col2 | +------+------+ | 2 | 4 | | 3 | 5 | | 6 | 8 | +------+------+ 3 rows in set (0.00 sec) commit; Query OK, 0 rows affected (0.00 sec)
As you can see it’s possible for Transaction T2 to see the data that was modified by Transaction T1. However, after the rollback of Transaction T1, the data is reverted back.
Read Committed
Transaction T1
start transaction; Query OK, 0 rows affected (0.00 sec)
Transaction T2
set session transaction isolation level read committed; Query OK, 0 rows affected (0.00 sec) start transaction; Query OK, 0 rows affected (0.00 sec)
Transaction T1
select * from tab1; +------+------+ | col1 | col2 | +------+------+ | 2 | 4 | | 3 | 5 | | 6 | 8 | +------+------+ 3 rows in set (0.00 sec)
Transaction T2
select * from tab1; +------+------+ | col1 | col2 | +------+------+ | 2 | 4 | | 3 | 5 | | 6 | 8 | +------+------+ 3 rows in set (0.00 sec)
Transaction T1
update tab1 set col2 = col1 + 3; Query OK, 3 rows affected (0.00 sec) Rows matched: 3 Changed: 3 Warnings: 0
Transaction T2 (no dirty reads)
select * from tab1; +------+------+ | col1 | col2 | +------+------+ | 2 | 4 | | 3 | 5 | | 6 | 8 | +------+------+ 3 rows in set (0.00 sec)
Transaction T1
commit; Query OK, 0 rows affected (0.00 sec)
Transaction T2
select * from tab1; +------+------+ | col1 | col2 | +------+------+ | 2 | 5 | | 3 | 6 | | 6 | 9 | +------+------+ 3 rows in set (0.00 sec)
This level shows the dirty read is not possible, but after the commit of Transaction T1, the data is available to Transaction T2 .
Repeatable Read
Transaction T1
start transaction; Query OK, 0 rows affected (0.00 sec)
Transaction T2
set session transaction isolation level repeatable read; Query OK, 0 rows affected (0.00 sec)
start transaction; Query OK, 0 rows affected (0.00 sec)
Transaction T1
select * from tab1; +------+------+ | col1 | col2 | +------+------+ | 2 | 5 | | 3 | 6 | | 6 | 9 | +------+------+ 3 rows in set (0.00 sec)
Transaction T2
select * from tab1; +------+------+ | col1 | col2 | +------+------+ | 2 | 5 | | 3 | 6 | | 6 | 9 | +------+------+ 3 rows in set (0.00 sec)
Transaction T1
update tab1 set col2 = col1 + 10; Query OK, 3 rows affected (0.01 sec) Rows matched: 3 Changed: 3 Warnings: 0
select * from tab1; +------+------+ | col1 | col2 | +------+------+ | 2 | 12 | | 3 | 13 | | 6 | 16 | +------+------+ 3 rows in set (0.00 sec)
Transaction T2
select * from tab1; +------+------+ | col1 | col2 | +------+------+ | 2 | 5 | | 3 | 6 | | 6 | 9 | +------+------+
Transaction T1
commit; Query OK, 0 rows affected (0.01 sec)
Transaction T2
select * from tab1; +------+------+ | col1 | col2 | +------+------+ | 2 | 5 | | 3 | 6 | | 6 | 9 | +------+------+
commit; Query OK, 0 rows affected (0.00 sec)
select * from tab1; +------+------+ | col1 | col2 | +------+------+ | 2 | 12 | | 3 | 13 | | 6 | 16 | +------+------+ 3 rows in set (0.00 sec)
After the commit of Transaction T1 we still see the unchanged data. Only after a commit (or rollback) from Transaction T2 we see that the data has changed.
Serializable
Transaction T1
start transaction; Query OK, 0 rows affected (0.00 sec)
Transaction T2
set session transaction isolation level serializable; Query OK, 0 rows affected (0.00 sec)
start transaction; Query OK, 0 rows affected (0.00 sec)
Transaction T1
select * from tab1; +------+------+ | col1 | col2 | +------+------+ | 2 | 17 | | 3 | 18 | | 6 | 21 | +------+------+ 3 rows in set (0.00 sec)
update tab1 set col2 = col2 + 5; Query OK, 3 rows affected (0.00 sec) Rows matched: 3 changed: 3 Warnings: 0
Transaction T2 (locked)
select * from tab1; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
Transaction T1
commit; Query OK, 0 rows affected (0.01 sec)
Transaction T2
select * from tab1; +------+------+ | col1 | col2 | +------+------+ | 2 | 22 | | 3 | 23 | | 6 | 26 | +------+------+ 3 rows in set (0.00 sec)
You see that after we have done an update we cannot select the data in the other transaction anymore. We have to wait until Transaction T1 commits or rollbacks the transaction.
