Transaction isolation levels

To maintain consistency in a database, a transaction should have the ACID properties. DBMS ensures this by implementing read-and-write locks on the data, depending on the transaction isolation level.

In this topic, we will try to understand the transaction isolation levels and what concurrency bugs may appear as a result.

Transaction isolation levels

Transaction isolation determines what data is visible to the statements within a transaction. The visibility of data changes across concurrent transactions can affect the application logic or database operations.

There are four levels of transaction isolation:

Read uncommitted

Read uncommitted allows a transaction to read any data, regardless of whether that data has been committed. As long as the data exist in memory, a transaction can read it and take actions based on the values read. This can result in an anomaly called dirty read, as depicted in the image below.

From the image above, transaction B would have manufactured a car from nowhere when transaction A rolls back the insert.

Read committed

A transaction running on read committed isolation level sees only data committed before the transaction began. Dirty read is avoided as uncommitted data is not read.

However, since a transaction can read changes committed by concurrent transactions during its execution, you are not guaranteed to see the same data if you rerun the same query within the same transaction. This anomaly is called non-repeatable read anomaly.

We can see that transaction B may update a row, and delete and insert new cars to the table. Transaction A will always see the committed changes made.

Repeatable read

Repeatable read isolation level guarantees that any data read cannot change, in addition to the guarantees of the read committed level. In other words, if the same query is issued twice within a transaction, changes to data values made in another concurrent transaction won't be read.

However, a repeatable read isolation level protects only the data that you have read from being deleted or updated. New rows might appear when the query is reissued that meets the selection criteria.

As we can see, the new row added by a concurrent transaction is visible to transaction A while the update modification is not read. These new records are called phantoms.

Serializable

Serializable is the highest isolation level. Serializability refers to the ability of a system to run transactions in parallel, but in such a way that is equivalent to running them serially.

You are guaranteed to read the same data throughout the duration of the transaction, hence, the anomalies described on the previous isolation levels do not occur in serializable isolation. No new data, no row updated nor deleted by a concurrent transaction.

Conclusion