Introduction to Database Transactions: Isolation Levels

A basic knowledge of transactions is required before proceeding with the below article.

I would recommend to go through Introduction to Database Transactions first.

What are isolation levels?

If you have gone through my previous article, you would know the various problems that comes with transactions, i.e., dirty read, non-repeatable read, overwriting uncommitted Data and phantom read. Isolation levels aims to solve these problems. They determine the strategy to be used while a data is accessed by multiple transactions simultaneously. For example, if a data is read by a transaction, should other transaction running in parallel be allowed to read that data. Similarly, what should happen when one transaction has modified some data but not committed as of yet and other transaction has to use that data. All these different scenarios are handled by specifying the correct isolation level.

Types of Isolation Levels

There are four types of isolation levels:

• Read uncommitted
• Read committed
• Repeatable read
• Serializable

Below table describes the salient features of each of them:

Isolation Level	Description
Read uncommitted	Transactions can read rows that have been modified by other transactions but not yet committed. Least restrictive isolation level. Read Uncommitted level are usually read-only.
Read committed	Transactions cannot read data that has been modified but not committed by other transactions. This prevents "dirty read". Transactions can modify data that has been read by another transactions which have not committed yet, resulting in “non-repeatable reads” or “phantom data”.  Generally uses row-lock.
Repeatable read	Transactions cannot read data that has been modified but not committed by other transactions. This prevents "dirty read".   Transactions cannot modify data that has been read by another transactions which have not committed yet. This prevents “non-repeatable reads”.  Other transactions can insert new rows that match the search conditions of statements issued by the current transaction resulting in “phantom data”.  Generally uses row-lock.  Concurrency is lower than READ COMMITTED isolation level since locks are held till the end of a transaction instead of being released at the end of each statement.
Serializable	Transactions cannot read data that has been modified but not committed by other transactions. This prevents "dirty read". Transactions cannot modify data that has been read by another transactions which have not committed yet. This prevents “non-repeatable reads”. Other transactions cannot insert new rows that match the search conditions of statements issued by the current transaction until the current transaction completes. This prevents “phantom data”. Range locks are used that match the search conditions executed in a transaction. This blocks other transactions from updating or inserting any rows that would match the search conditions issued by the current transaction. Most restrictive isolation level. Concurrency is lower.

Following tables summarizes the problems solved by each isolation level:

Transaction isolation level	Dirty reads	Non Repeatable reads	Phantom Data
Read uncommitted	Not Solved	Not Solved	Not Solved
Read committed	Solved	Not Solved	Not Solved
Repeatable read	Solved	Solved	Not Solved
Serializable	Solved	Solved	Solved

Things to note:

• Only one of the isolation level options can be set at a time, and it remains set for that connection until it is explicitly changed.
• In case of change of isolation level, resources that are read after the change are protected according to the rules of the new level. Resources that are read before the change continue to be protected according to the rules of the previous level.
• Transaction isolation level does not affect a transaction's ability to see its own changes; transactions can always see any changes they make.

That is it! Thanks for reading. Hope you liked the article. Please do post in the comment section for any query.

Do not forget to follow us at +Java Territory to stay updated.