How SQL engine implement the isolation levels in MySQL

Isolation is one of the ACID properties of DBMS. Isolation ensures that each transaction is isolated from other transactions and that transactions don’t affect each other. There are multiple ways a transaction can be affected by other transactions. It is known as a read phenomenon. Different types of read phenomena are:

1. Dirty read: It happens when a transaction reads data written by another transaction that has not been committed yet. This is bad because we are not sure if that other transaction will eventually be committed or rolled back. So we might end up using incorrect data in case rollback occurs.

2. Non-repeatable read: It happens when a transaction read the same row multiple times and the value is different each time because the row is modified by another transaction that is committed after the previous read. This is unexpected.

3. Phantom read: If the same query is executed multiple times but returns a different number of rows, due to some changes made by other recently-committed transactions, such as inserting new rows or deleting existing rows, it is known as a phantom read. For example, we want to query students that have heights between 5’’ to 6’’. It might possible that a new student is added, while the previous read transaction is still in progress, whose height is between 5 to 6 feet, in that case, the query will return this newly added user too.

4. Lost Update: It happens when two different transactions are trying to update the same column on the same row of a table and one transaction overrides the update of another transaction.

   For example, there are 4 students with id id1, id2, id3 and id4. id1, id2 belongs to team A and id3, id4 belongs to team B.
   

   mysql> select * from students_team;
   +----+-------+
   | id | team  |
   +----+-------+
   |id1 | A     |
   |id2 | A     |
   |id3 | B     |
   |id4 | B     |
   

The first transaction updated team A to B but before it
commit, the second transaction already started that updated team B to A. Once the first transaction commits team A converted to team B.

After the update in first transaction doing the read query in same transaction
mysql> select * from students_team;
+----+-------+
| id | team  |
+----+-------+
|id1 | B     |
|id2 | B     |
|id3 | B     |
|id4 | B     |

So all the students belong to team B now. The second transaction converts team B to A. Because all students belong to team B now, the second transaction converts all of them to team A.

mysql> select * from students_team;
+----+-------+
| id | team  |
+----+-------+
|id1 | A     |
|id2 | A     |
|id3 | A     |
|id4 | A     |

SQL engine offers four isolation levels to handle the read phenomenon. In this blog post, we are going to discuss what are those four isolation levels and how SQL engines ensure these isolation levels.

SQL use locking to protect a transaction from seeing or changing data that is being queried or changed by other transactions. SQL use different locking strategy to for different isolation levels

1. Read uncommitted: In read uncommitted isolation level one transaction can read the changes of another transaction even if the other transaction update is not committed. SQL engines don’t lock any row in this isolation level. So one transaction can affect the other transactions. All the read phenomenon is possible at this isolation level.

2. Read committed: If a transaction tries to read a row which is already locked by another transaction then until that transaction is completed it will wait and once the transaction is committed new changed value will be used. Here exclusive lock is used by the SQL engine. So if any update transaction comes, it acquires the exclusive lock and changes the value and releases the lock. Other transactions read the value after that. Read committed resolve the dirty read issue but another read phenomenon is still. Because it doesn’t prevent the new insertion or update in data even if any other transaction is reading the same row.

3. Repeatable Read: Repeatable Read isolation is achieved by placing a shared lock on the row read by each transaction and the lock is held until the transaction is committed. Until the lock is removed other transactions can’t change the data of row held by a different transaction. But it doesn’t prevent the new row insertion. So it prevents dirty reads and non-repeatable read but phantom read is still possible.

4. Serializable: SQL engine holds the lock on the row until is committed. It is similar to a Repeatable read but if required locks can be acquired on the key range, preventing other transactions from updating or inserting rows into the data set until the transaction is complete. It helps to prevent phantom reads. This is the most strict isolation level and it prevents all the read phenomena.

MySQL innodb engine default isolation level is Repeatable Read.