What are different transaction isolation levels in DBMS?

In case of transaction the term ACID has been used significantly to state some of important properties that a transaction must follow. We all know ACID stands for Atomicity, Consistency, Isolation and Durability and these properties collectively called as ACID Properties.

Properties of transaction

Database system ensures ACID property −

• Atomicity − Either all or none of the transaction operation is done.

• Consistency − A transaction transfer from one consistent (correct) state to another consistent state.

• Isolation − A transaction is isolated from other transactions. i.e. A transaction is not affected by another transaction. Although multiple transactions execute concurrently it must appear as if the transaction are running serially (one after the other).

• Durability − The results of transactions are permanent i.e. the result will never be lost with subsequent failure, durability refers to long lasting i.e. permanency.

Isolation

It determines the visibility of transactions of other systems. A lower level allows every user to access the same data. Therefore, it involves high risk of data privacy and security of the system. However, a higher isolation level reduces the type of concurrency over the data but requires more resources and is slower than lower isolation levels.

The isolation protocols help safeguards the data from unwanted transactions. They maintain the integrity of every data by defining how and when the changes made by one operation are visible to others.

Levels of isolation

There are four levels of isolations which are explained below −

• Read Uncommitted − It is the lowest level of isolation. At this level; the dirty reads are allowed, which means one can read the uncommitted changes made by another.

• Read committed − It allows no dirty reads, and clearly states that any uncommitted data is committed now it is read.

• Repeatable Read − This is the most restricted level of isolation. The transaction holds read locks on all the rows it references and write locks over all the rows it updates/inserts/deletes. So, there is no chance of non-repeatable reads.

• Serializable − The highest level of civilization. It determines that all concurrent transactions be executed serially.

Example

Consider an example of isolation.

What is the isolation level of transaction E?

session begins

SET GLOBAL TRANSACTION

ISOLATION LEVEL SERIALIZABLE;

session ends
session begins

SET SESSION TRANSACTION

ISOLATION LEVEL REPEATABLE READ;

transaction A
transaction B

SET TRANSACTION

ISOLATION LEVEL READ UNCOMMITTED;

transaction C

SET TRANSACTION

ISOLATION LEVEL READ COMMITTED;

transaction D
transaction E
session ends

Check which option −

A- Serializable
B- Repeatable read
C- Read uncommitted

Solution

Repeatable Read is the right answer.

Reason & Explanation

• Step 1 − In the above program, the first session starts and ends without doing any transaction.

• Step 2 − The second session begins at session-level with isolation level "Repeatable Read". Transaction A & B gets executed with these settings.

• Step 3 − Once again a new transaction begins with isolation level "Read uncommitted". This setting is used only for "Transaction C" since "Set transaction" alone is mentioned. If the "SET transaction" is used without global or session keywords, then these particular settings will work only for a single transaction.

• Step 4 − Once again "Set Transaction" with isolation level Read committed works only for Transaction D. (Refer step 3 for reason)

• Step 5 − "Transaction E" gets continued at the "Repeatable Read" since the transaction started at step 2 has not ended still. Transaction isolation level set at Step 3 and Step 4 vanishes once a single transaction is executed. So, automatically "Transaction E" will refer to the prior transaction settings.