Transaction Isolation Levels in DBMS

The levels of transaction isolation in DBMS determine how the concurrently running transactions behave and, therefore, ensure data consistency with performance being even. There are four basic levels, it depends upon choosing the right one based on whether the need is speed or data integrity.

What is the Transaction Isolation Level?

In a database management system, transaction isolation levels define the degree to which the operations in one transaction are isolated from the operations of other concurrent transactions.

As we know, to maintain consistency in a database, it follows ACID (Atomicity, Consistency, Isolation, and Durability) properties. Isolation levels define the degree to which a transaction must be isolated from the data modifications made by any other transaction in the database system.

A transaction isolation level is defined by the following phenomena:

• Dirty Read: is a situation when a transaction reads data that has not yet been committed. For example, Let’s say transaction 1 updates a row and leaves it uncommitted, meanwhile, Transaction 2 reads the updated row. If transaction 1 rolls back the change, transaction 2 will have read data that is considered never to have existed.
• Non Repeatable read: occurs when a transaction reads the same row twice and gets a different value each time. For example, suppose transaction T1 reads data. Due to concurrency, another transaction T2 updates the same data and commit, Now if transaction T1 rereads the same data, it will retrieve a different value.
• Phantom Read: occurs when two same queries are executed, but the rows retrieved by the two, are different. For example, suppose transaction T1 retrieves a set of rows that satisfy some search criteria. Now, Transaction T2 generates some new rows that match the search criteria for Transaction T1. If transaction T1 re-executes the statement that reads the rows, it gets a different set of rows this time.

Based on these phenomena, The SQL standard defines Four Isolation Levels

1. Read Uncommitted

Read Uncommitted is the lowest isolation level. In this level, one transaction may read not yet committed changes made by other transactions, thereby allowing dirty reads. At this level, transactions are not isolated from each other.

-- Create sample table
CREATE TABLE Accounts (
    AccountID INT PRIMARY KEY,
    Balance DECIMAL(10,2)
);

INSERT INTO Accounts (AccountID, Balance) VALUES (1, 100.00);

-- Run T1, but not commit
-- Set New Balance = 50.00
START TRANSACTION;
UPDATE Accounts SET Balance = Balance - 50.00 WHERE AccountID = 1;

-- Run T2
-- Balance = 50.00
START TRANSACTION;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT Balance FROM Accounts WHERE AccountID = 1;

2. Read Committed

This isolation level guarantees that any data read is committed at the moment it is read. Thus it does not allow dirty read. The transaction holds a read or write lock on the current row, and thus prevents other transactions from reading, updating, or deleting it.

-- Create sample table
CREATE TABLE Accounts (
    AccountID INT PRIMARY KEY,
    Balance DECIMAL(10,2)
);

INSERT INTO Accounts (AccountID, Balance) VALUES (1, 100.00);

-- Run T1, but not commit
-- Set New Balance = 50.00
START TRANSACTION;
UPDATE Accounts SET Balance = Balance - 50.00 WHERE AccountID = 1;

-- Run T2
-- Balance = 100.00, not 50.00
START TRANSACTION;
SELECT Balance FROM Accounts WHERE AccountID = 1;
COMMIT;

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 update and delete actions. Since other transactions cannot read, update or delete these rows, consequently it avoids non-repeatable read.

-- Create sample table
CREATE TABLE Accounts (
    AccountID INT PRIMARY KEY,
    Balance DECIMAL(10,2)
);

INSERT INTO Accounts (AccountID, Balance) VALUES (1, 100.00);

-- Run T1
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;

-- 1st reading query
-- Balance = 100.00
SELECT Balance FROM Accounts WHERE AccountID = 1;

-- 2nd reading query 
-- Balance = 100.00, not 50.00
SELECT Balance FROM Accounts WHERE AccountID = 1;
COMMIT;

-- Run T2 after 1st reading query in T1
-- Balance = 50.00
START TRANSACTION;
UPDATE Accounts SET Balance = Balance - 50.00 WHERE AccountID = 1;
COMMIT;

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.

Case 1: Run T1 before T2

-- Case 1: Run T1 before T2
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;

-- Reading query
-- Balance = 100.00
SELECT Balance FROM Accounts WHERE AccountID = 1;

-- Writing query
-- New Balance = 200.00
UPDATE Accounts SET Balance = Balance + 100.00 WHERE AccountID = 1;

COMMIT;

-- Run T2
-- Blocking until T1 commit
-- New balance = 150.00
START TRANSACTION;
UPDATE Accounts SET Balance = Balance - 50.00 WHERE AccountID = 1;

COMMIT;

Case 2: Run T1 after T2

-- Case 1: Run T1 after T2
-- Blocking until T2 commit
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;

-- Reading query
-- Balance = 50.00
SELECT Balance FROM Accounts WHERE AccountID = 1;

-- Writing query
-- New Balance = 150.00
UPDATE Accounts SET Balance = Balance + 100.00 WHERE AccountID = 1;

COMMIT;

-- Run T2
-- New balance = 50.00
START TRANSACTION;
UPDATE Accounts SET Balance = Balance - 50.00 WHERE AccountID = 1;

COMMIT;

Conclusion

The choice of isolation level depends on the specific requirements of the application. Higher isolation levels offer stronger data consistency but can also result in longer lock times and increased contention, leading to decreased concurrency and performance. Lower isolation levels provide more concurrency but can result in data inconsistencies.