Understanding Transaction Isolation Levels in MySQL

 Understanding Transaction Isolation Levels 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.

Mysql default setting given below:-

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 – T1 and T2.

For session T1:

At E1, I globally modified the transaction_isolation to read-uncommitted and started the transaction. I executed the UPDATE statement ( name = ram ) at E1 but did not commit the transaction yet. Then I created the E2 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. 

Terminal T1:-

Terminal T2

After that we are going to terminal t1 and write a commit command

Agin we are going to terminal t2 and checking 

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 – T1 and T2.

Teminal T1:-

Terminal T2:-

Terminal  T1:-

SERIALIZABLE:

• No dirty reads
• No non-repeatable reads
• No phantom reads

Serializable provides the highest level of isolation between concurrent transactions. It behaves a lot like “Repeatable Read” if the “autocommit” flag is enabled; otherwise, all the reads are performed in a locking fashion.

Let’s look at the example below to understand this practically. We will set the isolation level as “SERIALIZABLE” and start sessions S1, S2, and transactions T1, T2 respectively. We will first fetch the records with some criteria within T1 and then try to update the same record within T2.

T1;-