Transaction Isolation Levels in MySQL

Isolation level in any database system refers to the degree to which the transactions are isolated from each other when accessing data. There are four isolation levels in MySQL: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE. Each level provides a different degree of data consistency and concurrency control, with different trade-offs in terms of performance and data consistency.

Before diving in to the isolation levels, we need to know the different read anomalies that are present in a transactional database system.

Dirty read: A dirty read occurs when a transaction reads data that has been modified by another transaction that has not yet been committed. This can lead to inconsistent or incorrect data being read.

Let’s say T1 and T2 are independent transactions, reading and updating a field count in a table. initially count = 0, both T1 and T2 read the count as 0, now T1 modifies the count to 1, T2 re-reads the count as 1. Now if T1 rollback due to any issue T2 has read a non existent value! This is called a dirty read and leads to data inconsistency.

1
2
3
4
5
6
7

T1 reads count => 0
T2 reads count => 0

T1 update count=1
T2 reads count => 1

T1 rollback

Now T2 is left with a dirty read!

Non repeatable read: A non-repeatable read occurs when a transaction reads the same data twice, but the data has been modified by another transaction in between the two reads.

1
2
3
4
5
6
7
8
9

T1 read count => 0
T2 read count => 0

T1 update count=1
T1 commit

T1 read count => 1
T2 read count => 1

T2 reads the table twice before and after T1 updated and see two different states.

Phantom read: A phantom read occurs when a transaction reads a set of rows that satisfy a certain condition, but another transaction inserts or deletes rows that also satisfy that condition in between the two reads. This can lead to inconsistent or incorrect data being read.

1
2
3
4
5
6
7

T1 read count(*) => 1
T2 read count(*) => 1

T1 insert ROW

T2 read count(*) => 2

If transaction T1 inserts a new record, T2 may have a phantom read issue as its reads are inconsistent.

Serialization anomaly: This anomaly can occur when transactions are executed in a non-serializable order, which means that the result of the transactions depends on the order in which they are executed. This can lead to inconsistent or incorrect data being written to the database.

There are 4 Standard Isolation levels in MySQL.

READ UNCOMMITTED

This level allows transactions to read uncommitted changes made by other transactions, which can lead to dirty reads, non-repeatable reads, and phantom reads. It offers the best performance but the lowest data consistency.

READ COMMITTED

This level allows transactions to read only committed changes made by other transactions, which avoids dirty reads but can still lead to non-repeatable reads and phantom reads. It offers a better data consistency than READ UNCOMMITTED but lower performance.

REPEATABLE READ

This level guarantees that a transaction sees a consistent snapshot of data throughout its execution, even if other transactions modify the same data. It offers a better data consistency than READ COMMITTED but lower performance. It prevents dirty reads and non-repeatable reads but can still lead to phantom reads. However InnoDB solves the phantom read problem with multi-version concurrency control.

In the case of Repeatable Read, each transaction is reading its own snapshot of the data, we can see T2 is not aware of the update T1 made even if T1 is committed. Thus reads are consistent throughout the transaction.

It gets interesting when we have write operation on both transactions. If T2 try to update after T1 is committed, The first Read doesn’t show the updates made by T1. But post the T2 update, the snapshot is refreshed and shows the updated data that also include updates from T1!

SERIALIZABLE

This level guarantees that transactions are executed in a serializable order, which means that they behave as if they were executed one after the other, without any concurrency control. It prevents all types of read anomalies but can result in more frequent deadlocks and lower performance.

Conclusion

To sum it up,

References

• MySQL Docs
• Repeatable Reads - Pythian Blog