Transaction Isolation Levels In Innodb

First, let's break the heading, a Transaction is a sequential group of statements, queries, or operations that performs as a single work unit that can be committed or rolled back. Next, Isolation is the I of ACID properties, isolation refers to the ability to concurrently process multiple transactions in a way that one does not affect another. Lastly, Innodb is the default storage-engine for Mysql. In this article, we will understand the different isolation levels available in MySQL-InnoDB.

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.02 sec)

Isolation level can be set for all sessions, i.e. set globally or set per session. The default transaction isolation level is REPEATABLE-READ.

There are 4 levels of transaction isolation available in MySQL.

READ-UNCOMMITTED: As the name suggests, transactions can read data that are not even committed to the database leading to dirty reads.

In the above example, we started 2 sessions, set the isolation level to READ-UNCOMMITTED in both session, and we can see, that in session 1 we can read data which is not committed by session2.

READ-COMMITTED: In this level, dirty-reads are not possible, as only committed data can be read in select, however, each subsequent read can be non-repeatable. Let's see examples to understand.

In the above example, we can see, that only committed writes were selected, which fixes the dirty-read problem, however, each read would result in different results if other transactions are committed, this leads to non-repeatable reads.

REPEATABLE-READ: This is the default transaction isolation level of Innodb. In this isolation level, select yields the same value as a snapshot of the first execution of SELECT is taken and that is used until the transaction ends. With this both dirty-read and non-repeatable reads are handled, however, we may get phantom rows with repeatable-read isolation levels.

In the above example, we started transactions in both the sessions, we can see, that even if the write has been committed to the database, all reads in transaction 1 are still yielding the same result.

In the above example, we see that transaction 1, now has 1 new row which is also a phantom-row.

SERIALIZABLE: This isolation level is the strongest possible isolation level, but this also increases the chances of locking conditions. This is done by use of shared read locks and exclusive write locks. SERIALIZABLE is similar to REPEATABLE READ with the additional restriction that the row selected by one transaction cannot be changed by another until the first transaction finishes. Let's see an example.

In the above example, we can see that when we tried to insert a new row in different transactions, Lock wait timeout exceeded error. This guarantees the fix from phantom-rows.

And there you have it, all 4 transaction isolation levels explained with examples. We have also seen that the isolation level is a balance between consistency and throughput. High consistency (SERIALIZABLE) means more locking conditions/less throughput and vice-versa.

To read more, please visit: https://buildatscale.tech/transaction-isolation-level-in-innodb/