Understanding Isolation in the Database Transactions

In this we are elaborate description of the isolation levels in database transactions, discussing each of them with their examples, areas of conflict and practical considerations. We are going to discuss four ANSI SQL standard isolation levels Read Uncommitted, Read Committed, Repeatable Read, and Serializable seeing how they work with sample data and SQL scripts. It is aimed at giving a complete picture of the role that these levels play in data consistency and concurrency in a multi-user database system.

Isolation Levels Introduction

Isolation levels determine how much isolation of concurrent transactions occurs. They determine how much one could see the change that has been made by other transactions that have not been committed. The higher the level of isolation, the greater the consistency in data and the lesser the transaction concurrency and the lower the level of isolation, the higher the concurrent transactions and the more they might expose transactions to a number of anomalies.

ACID Properties

Before discussing each of the isolation levels in detail, it is important to get an idea on the ACID conceits of the database transaction:

Atomicity: A transaction is viewed as a unit of work. All or none of the changes would be made.

Consistency: A transaction should not violate the integrity of the database. It has to transition the database states between two valid states.

Isolation: One transaction should be isolated with respect to another. Parallel transactions ought not to interact with the operations of one another.

Durability: A committed transaction is permanent and will not be lost even through failure of the system.

Isolation levels center mainly on the property of ACID known as Isolation.

Sample Data & Environment

For the sake of this example let's imagine we have a simple Accounts table with the following layout:

CREATE TABLE Accounts (  
    AccountID INT PRIMARY KEY,  
    Balance DECIMAL(10, 2)  
);  
  
INSERT INTO Accounts (AccountID, Balance) VALUES  
(1, 100.00),  
(2, 200.00);  

--get data  
  
select * from Accounts  
--output  
AccountID   Balance  
----------- ------------------------------  
1           100.00  
2           200.00  
  
(2 rows affected)  

We shall employ two concurrent transactions, Transaction A and Transaction B, to explain how each isolation level behaves.

Levels of Isolation Explained

1. Read Uncommitted

It is the weakest form of isolation. Transactions are able to access modifications done by other transactions even though those modifications are not committed.

Anomalies:

Dirty Reads A dirty read occurs when a transaction reads a data that has been changed by another transaction but not yet committed. In case the modifying transaction rolls back, then the reading transaction will have read erroneous data.

Concurrency: Max concurrency.

Example:

/**Transaction A:**/  
-- Set isolation level to Read Uncommitted (implementation varies by database)  
  
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;  
  
BEGIN TRANSACTION  
UPDATE Accounts SET Balance = 150.00 WHERE AccountID = 1;  
-- Before commit  
  
/**Transaction B:**/  
  
-- Set isolation level to Read Uncommitted  
  
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;  
  
BEGIN TRANSACTION;  
  
SELECT Balance FROM Accounts WHERE AccountID = 1; -- Reads 150.00 (dirty read)  
  
COMMIT;  
  
--If Transaction A rolls back, Transaction B has read an incorrect value.  

2. Read Committed

Transactions may only read data which has been committed by other transactions. This inhibits dirty reads

Anomalies:
Non-Repeatable Reads: A transaction reads a row and then the row is modified and committed by another transaction before the same row is read again.
Concurrency: great concurrency.

Example:

/Transaction A:/

-- Set isolation level to Read Committed

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

START TRANSACTION;

SELECT Balance FROM Accounts WHERE AccountID = 1; -- Reads 100.00

UPDATE Accounts SET Balance = 150.00 WHERE AccountID = 1;

COMMIT;

/Transaction B:/

-- Set isolation level to Read Committed

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

START TRANSACTION;

SELECT Balance FROM Accounts WHERE AccountID = 1; -- Reads 100.00

-- After Transaction A commits

SELECT Balance FROM Accounts WHERE AccountID = 1; -- Reads 150.00 (non-repeatable read)
COMMIT;

--Transaction B reads different values for the same row within the same transaction.

3. Repeatable Read

The same row can be read by different transactions and return the same value even when some other transaction updates and commits to the row. This prevents non repeatable reads.

Anomalies:
Phantom Reads: In a transaction, a query is executed returning a set of rows. In yet another transaction, new rows are inserted which fulfil the query condition and the transaction is committed. The first transaction will re-run that query and it will also see the phantoms.
Concurrency: Moderate concurrency.

Example:

/Transaction A:/

-- Set isolation level to Repeatable Read

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

START TRANSACTION;

SELECT Balance FROM Accounts WHERE AccountID = 1; -- Reads 100.00

-- Before Transaction B commits

SELECT Balance FROM Accounts WHERE AccountID = 1; -- Still reads 100.00

COMMIT;

/Transaction B:/

-- Set isolation level to Repeatable Read

START TRANSACTION;

INSERT INTO Accounts (AccountID, Balance) VALUES (3, 50.00);
COMMIT;

/Transaction C:/

-- Set isolation level to Repeatable Read

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

START TRANSACTION;

SELECT * FROM Accounts; -- Initially sees AccountID 1 and 2

-- After Transaction B commits

SELECT * FROM Accounts; -- May or may not see AccountID 3 (phantom read) depending on the database implementation

COMMIT;

--Transaction C might see a "phantom" row (AccountID 3) if it re-executes the query after Transaction B commits.

4. Serializable

This is the most isolation level. Transactions are totally independent of one another. It eliminates all anomalies, such as dirty reads, non-repeatable reads and phantom reads. It is, in effect, serializing transactions just as though they were performed sequentially.

Anomalies: None.
Concurrency: Lowest concurrency.

Example:

/Transaction A:/
-- Set isolation level to Serializable

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

START TRANSACTION;

SELECT Balance FROM Accounts WHERE AccountID = 1; -- Reads 100.00

-- Before Transaction B commits

SELECT Balance FROM Accounts WHERE AccountID = 1; -- Still reads 100.00

COMMIT;

/Transaction B:/

-- Set isolation level to Serializable

START TRANSACTION;

UPDATE Accounts SET Balance = 250.00 WHERE AccountID = 2;

COMMIT;
In Serializable isolation, Transaction A will obtain a consistent view of data all the time regardless of what Transaction B does. The implementation of the database may cause a blocking of transaction B until transaction A completes or rollback to ensure an absence of serialization anomalies.

Choosing the Right Isolation Level

The isolation level will be based on the specificity of the application. Greater levels of isolation will give a higher level of data consistency, but decreased concurrency The lower isolation levels will permit more concurrency at the expense of potential exposure to a number of anomalies.

Read Uncommitted: This is hardly ever used in production so as to avoid the risk of dirty reads.
Read Committed: Another default isolation level that has an ideal balance between consistency and concurrency.
Repeatable Read: It is appropriate to applications which demand a consistent read within the transaction.
Serializable: Data integrity is the key here and concurrency is less of a concern.

Database-Specific Implementations

It should be mentioned that isolation level deployment may differ across databases systems. Certain databases might support more or different levels of isolation than those standard. Each level of isolation has different behavior and it is always recommended that you consult the documentation of your particular database system to explain exactly what will happen at each of the isolation levels.

Conclusion

It is essential to comprehensively understand the levels of isolation in order to come up with solid and dependable database programs. Developers can tune the right level of isolation to the concurrency they want as well as the data consistency they require. Review the possible anomalies and trade-offs of each level to make suitable choices to address the particular needs of your application.

T-SQL Queries

Understanding Isolation in the Database Transactions