What are Different Transaction Isolation Levels in DBMS

Transaction

Transactions are some set of logical operations performed on the database. There can be any operation like reading the value, writing into the database, deleting the values, updating the current value, etc. For example, adding 1000 Rs. money into somebody's account is a transaction where we update its account balance.

To make the database consistent, it follows the ACID properties, which can be defined as Atomicity, Consistency, Isolation, and Durability. If a database follows these four properties, it is considered in a consistent and stable state. Isolation property defines the integrity behavior of the transaction to the other users and systems. It means the transaction should happen in such a way that it is the single transaction occurring or the single user accessing the resources of the database. It should not be affected by other concurrent transactions.

For a particular transaction, the Isolation Level defines the degree to which the transaction is isolated toward the modification or changes in the data made by any other transactions in the database system.

There are some phenomena that define the Isolation Level of a transaction is defined below:

• Dirty Read

In this phenomenon, one transaction reads the value which is not committed yet. For example, there are two transactions, and the first transaction updated any data in the database but has not been committed yet. If another transaction reads the updated data and if the first transaction rolls back its modification, then there will be ambiguity in the database. Because there is no modification in the database, but according to the second transaction, there is a modification in the data.

• Non-Repeatable Read

Non Repeatable Read is a situation when any transaction reads some data more than one time, and each time it gets different values. For example, two transactions occur in the database if the first transaction reads the value of any row. At the same time, another transaction commits changes in the same data. After a few times, if the first transaction reads the same data, then it will find a different value.

• Phantom Read

Phantom Read is the situation when the same queries give different data. For example, two transactions are occurring, where the first transaction is running a query and getting some set of rows. If the other transaction is running its query and getting the set of tuples and the set of tuples matches with the first transaction. Now, if we run the first query again, then we will get a different set of rows this time.

Depending on the above phenomenon, there are four isolation levels in the database:

1. Read Uncommitted:

Read Uncommitted is at the lowest level of all the levels. In this level, one transaction reads the modified data which are still not committed by other transactions. It means in this level, transactions are in a Dirty Read situation. At this level, all the transactions are not isolated from each other.

2. Read Committed:

This is the isolation level where data is read-only when it is committed, and changes are made in the database by the other transactions. It means it does not allow the Dirty Reading phenomena. The transaction puts a read or write lock on some tuples so that other transactions can not read, write, update or delete it.

3. Repeatable Read:

This level puts the read lock on those tuples which are going to be read by this transaction. It also puts the write locks on those tuples which have been referenced by this transaction to save it from updation and deletion from other transactions. So we can say that it is the level where a lot of restrictions are present. Therefore, it stops repeatable read situations.

4. Serializable:

This is the top level of isolation. This level gives the assurance of serializable execution. It means all the transactions which are occurring concurrently will be serialized in the database.

There is a table that defines the relationship between isolation level and the phenomenon of isolation level: