SERIALIZABLE isolation level in SQL server

SERIALIZABLE is the highest isolation level in SQL Server and guarantees that transactions behave as if they were executed one at a time. It prevents Dirty Reads, Non-Repeatable Reads, and Phantom Reads. SQL Server accomplishes this by holding Shared Locks for the entire transaction and acquiring Key-Range Locks on indexed ranges. These range locks prevent other transactions from inserting, updating, or deleting rows that would affect the result set. While SERIALIZABLE provides the strongest consistency and is useful for financial calculations, inventory management, and business-rule enforcement, it also introduces significant blocking, higher deadlock risk, and lower concurrency. Compared to SNAPSHOT isolation, SERIALIZABLE relies on locking rather than row versioning and therefore trades performance for maximum data integrity.

SERIALIZABLE is the highest isolation level in SQL Server.

It prevents:

Ø  Dirty Reads

Ø  Non-Repeatable Reads

Ø  Phantom Reads

To achieve this, SQL Server uses:

Ø  Shared Locks (S)

Ø  Exclusive Locks (X)

Ø  Key-Range Locks (RangeS-S, RangeS-U, RangeI-N, etc.)

and holds locks until the transaction completes.

Enabling SERIALIZABLE

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; 

Preventing Non-Repeatable Read & Dirty Read

Session 1:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; BEGIN TRAN; SELECT * FROM Employee WHERE EmployeeID = 1; --commit

Transaction is still not committed.

Session 2

Updating the record

UPDATE Employee SET Salary = 60000 WHERE EmployeeID = 1;

It blocks the update.

We are not able to update this record.

Now run the session 1 again

Getting the same value. Because we are not able to update this record. In this way we can prevents the Non-Repeatable Read & Dirty Read.

Preventing Phantom Reads

Session 1

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; BEGIN TRAN; SELECT count(*) FROM Employee --commit

Now in session 2 inserting few records into this table.

insert into Employee(EmployeeID,EmployeeName,Salary) values (100,'Ramesh',900000), (101,'Rahul',26000);

Insert blocked. Run session 1 still we will get the same count.

In this way we can prevent the phantom read.

See the lock details

SELECT     request_session_id,     resource_type,     request_mode,     request_status FROM sys.dm_tran_locks

Session id 52 hold all lock.

In the session id 52 we have opened the transaction but not yet committed.

Now we are committing this open transaction. Now see the lock details.

Types of Problems Prevented

Problem	Prevented?
Dirty Read	Yes
Non-Repeatable Read	Yes
Phantom Read	Yes
Lost Update	Yes

 

Advantages:

Ø  Highest data consistency

Ø  Prevents Dirty Reads

Ø  Prevents Non-Repeatable Reads

Ø  Prevents Phantom Reads

Ø  Ideal for banking, inventory, and financial systems

Disadvantages:

Ø  Lowest concurrency

Ø  Increased blocking

Ø  Higher deadlock risk

Ø  Longer lock duration

Ø  Performance overhead

Ø  Can cause lock escalation