Understanding Transaction Isolation Levels in MySQL

Ever found yourself wondering about concurrent transactions? Transactions that operate on the same database record? What happens when a user tries to use their debit card on the merchant POS while parallelly doing a payout via UPI from the same account. And, both the transactions land on your system at the same time? Sure you’re using appropriate locking mechanisms, however, did you know there’s another property that plays a vital role in isolating these kinds of transactions?

Let’s cover that property of transaction isolation and understand how it generally affects the application.

What is Transaction Isolation?

Transaction isolation is one of the foundational concepts of database management. It is the `I` of the ACID properties. Isolation level dictates how concurrent transactions are processed. 

All production-grade applications spawn multiple threads to cater to concurrent requests. These threads, in turn, create multiple database sessions and transactions to fulfill the processing requirements. The data on which these transactions are operating may be the same or different, and we want our application to process requests reliably with reasonable efficiency. This is where the property of Isolation becomes vital to be understood.

This property can be set at both global and session levels.

Isolation Levels and Anomalies

The MySQL InnoDB engine provides four levels of Isolation using different locking mechanisms. Each level provides a different degree of Isolation amongst transactions and has its own benefits and anomalies.

Before we delve deeper into isolation levels, let’s understand what different types of anomalies are:

What are the Types of Anomalies 

Dirty Reads

A dirty read happens when one transaction can read data that another transaction has updated but not committed.

Non-repeatable Reads

When the same query results in different results within a single transaction, non-repeatable reads occur.

Phantom Reads

The scenario of phantom read occurs when a query can read/update the rows that were not available within the transaction earlier.

Now let’s take a look at each and every isolation level and anomaly along with some examples:

Isolation Levels & Anomalies With Examples

Read Uncommitted

Read Uncommitted provides the lowest or weakest isolation level amongst concurrent transactions. All the reads happen in a non-locking fashion. With this isolation level, the transactions can read uncommitted data from other transactions, leading to dirty reads.

In the following example, we will set the transaction isolation level to “READ UNCOMMITTED” and start two separate MySQL sessions, S1 and S2, and respective transactions T1 and T2.

Now, let’s update the balance in transaction T1 but not commit it. Parallelly we will try to fetch the balance in T2.

T2 reads the balance as 1100 instead of 1000. Now, at this stage, if T1 decides to rollback because of any of the reasons and T2 has already consumed the value 1100 in its own application thread, the scenario of dirty read takes place.

Read Committed

Read Committed offers a higher transaction isolation level than Read Uncommitted and a little lower than Repeatable Read. With this isolation level, the transactions will still be able to access updated data from other transactions; however, only the committed data. This helps in avoiding the scenario of dirty reads.

For the example below, we will set the isolation level as “READ-COMMITTED” and start sessions S1, S2, and transactions T1, T2, respectively.

Next, we will update the balance in T1 without committing it and try to fetch the balance for the same record in T2.

Next, we will update the balance in T1 without committing it and try to fetch the balance for the same record in T2.

T2 reads the balance as 1000, which avoids dirty reads. However, non-repeatable reads still take place with this isolation level. Let’s take a look at this practically below.

First, we will fetch the records within both the transactions T1 and T2 and update the balance in T1. Next, we commit the update in T1 and again fetch the records in T2.

Thus, within the same transaction T2, the queries result in different values leading to non-repeatable reads. This happens because with “Read Committed” isolation level, innodb creates and reads from the fresh snapshot after the last DML operation.

Repeatable Read

Repeatable Read is the default isolation level of MySQL InnoDB engine. This level solves non-repeatable reads by establishing and using the snapshot created at the beginning of the transaction. Thus, queries within the same transaction result in the same values.

In the following example, we will not update the isolation level but instead, use the default one and start sessions S1, S2, and transactions T1 and T2, respectively.

Next, we will update and commit the record in T1 and try to fetch the same record in T2.

As seen from the snippet above, non-repeatable reads are avoided. However, phantom row updates aren’t.

Let’s look at the following example to understand this better. Again, we’ll start sessions S1, S2, and transactions T1, T2 respectively. Also, we will fetch and display records in both transactions.

Now, we will insert a new record within T1 and commit it. Next, we fetch records within T2. Since with this isolation level snapshots established at the start of the transactions are used, T2 will still not have any view of the new record.

Next, we will try and update the newly inserted record in T2.

Thus, as seen from the snippet above, while the snapshot established did not have any view of the newly inserted record, T2 was still able to update and read the same record.

While Repeatable Read is the default MySQL setting for Isolation, commercial applications set the isolation level according to their need for performance and reliability.

Serializable

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.

As seen from the snippet above, since all the reads are performed in a locking fashion, the update query within T2 waits for T1 to complete and times out.

This isolation level being the strictest, avoids all the anomalies listed above.

On a Concluding Note

While Repeatable Read is the default MySQL setting for Isolation, commercial applications set the isolation level according to their need for performance and reliability. This depends a lot on the kind of workflow the application requires and the balance required between performance and reliability.

In this post, we’ve covered different transaction isolation levels and anomalies and their effects on the application. 

Feel free to shoot a topic you want us to cover next, and we will try our best to simplify and explain it. Whether it is about handling your traffic using an API Gateway or handling bulk payouts, our engineering team loves to chalk about important system components from time to time.

Until next time!