Transaction Isolation Levels in DBMS

We know that the database follows the ACID properties to maintain consistency in the database. The database has four properties, and we called these four properties as ACID properties. The ACID properties are Atomicity, Consistency, Isolation, and Durability. Among these four properties, with the help of isolation, the database can determine how the transaction integrity is visible to the system and the user. It means when the only transaction is accessing the resource of the database at that moment, the transaction should take place in the system.

The level of isolation defines the degree to which the transaction is isolated during the modification of the data, and the other transaction in the database does these modifications. We can define transaction isolation by the following phenomena.

1. Dirty Read: A dirty read is a type of situation that occurs when a transaction reads the data and that data has not been committed yet. Let us understand this with an example. Let's say transaction-1 updates a row, and that transaction 1 is not committed yet. Then transaction 2 reads the updated row. If transaction 1 rolls back the changes, then transaction-2 will read all the data, and these data will be considered as never existing.
2. Non-Repeatable Read: The non-repeatable Read occurs when the transaction reads a single row multiple times and gets a different value on each Read. Let's take an example. Suppose transaction1 reads the row data. But due to a concurrency issue, transaction 2 updates the same data and is committed. Now transaction 2 reads the same row again and gets a different value this time.
3. Phantom Read: When the two queries are executed, but they retrieve the two rows differently, at that time, phantom Read occurs. Let's take an example. Suppose transaction 1 retrieves a set of rows, and these rows are satisfied by some conditions. Now transaction 2 generates some new rows that match the search criteria of transaction 1. If transaction 1 executes the statement that reads the row, it gets a different row at different times.

Isolation Levels

Based on the above different phenomena, SQL defines into four isolation levels.

1. Read Uncommitted: It is the lowest in the isolation level. At this level, one transaction can not read the changes made by the other transactions, so it allows dirty reads. At this level, the transaction is not isolated from each other.
2. Read Committed: It provides a guarantee to each data that these data gets committed when these are read by any transaction. So that it does not allow dirty Read. The transaction holds the Read or writes action so that it prevents the data from reading or written by any other transaction.
3. Repeatable Read: This is the most restrictive isolation level. The transaction holds read locks on all rows it references and writes locks on referenced rows for updates and deletes actions. Since other transactions cannot read, update or delete these rows, consequently, it avoids non-repeatable Read.
4. Serializable: This is the highest isolation level. A serializable execution is guaranteed to be serializable. Serializable execution is defined to be an execution of operations in which concurrently executing transactions appears to be serially executing.

The Table given below clearly depicts the relationship between isolation levels, read phenomena, and locks:

Example

Consider an example of isolation.

What is the isolation level of transaction E?

SET GLOBAL TRANSACTION

ISOLATION LEVEL SERIALIZABLE;

session ends session begins

SET SESSION TRANSACTION

ISOLATION LEVEL REPEATABLE READ;

SET TRANSACTION

ISOLATION LEVEL READ UNCOMMITTED;

SET TRANSACTION

ISOLATION LEVEL READ COMMITTED;

Check which option