Various Types of InnoDB Transaction Isolation Levels Explained Using Terminal

InnoDB Transaction Isolation LevelsThe goal of this blog post is to explain the various types of transaction isolation levels available in MySQL. After reading the blog, you will be able to explain dirty reads, non-repeatable reads, and the concept of phantom rows as well.

What is the Isolation Level 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, you most certainly want to test this in a non-production environment in order to evaluate the impact on your application.”

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 – S1 and S2.

For session S1:

For session S2:

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

For session S1:

For session  S2:

At S1, I globally modified the transaction_isolation to “read-committed” and started the transaction. I executed the UPDATE statement ( name = ram ) at S1 but did not commit the transaction. Then I created S2 and executed the SELECT for the table, and I was not able to see the uncommitted modified data.

So, with “read-committed”, the transactions from different sessions can’t view the modification from the different transactions until it commits. Only committed modifications can be viewed.

Then, what is the drawback with “read-committed”?

Non-repeatable read is possible with the “read-committed”. Below, I explain how the non-repeatable read occurred.

For session  S1:

For session  S2:

For session  S1: