Couple of years back I was not exactly sure of difference between Isolation levels in SQL Server. I learnt it from
Roji &
Balmukund. Posting my notes and examples I had to understand it better.
“Pessimistic locking” (where data collisions are assumed and locks taken to prevent conflicting updates) and the four standard isolation levels:
1. Read Uncommitted (also known as “Dirty Read”)
2. Read Committed (the default SQL Server transaction isolation level)
3. Repeatable Read
4. Serializable
STEP 1
USE TestDB
CREATE TABLE Employees(EID int primary key identity,EmpName nvarchar(50),Salary money);
GO
INSERT INTO Employees(EmpName, Salary) VALUES ('Raja', 5500);
INSERT INTO Employees(EmpName, Salary) VALUES ('Raman', 44500);
INSERT INTO Employees(EmpName, Salary) VALUES ('Kanna', 12000);
STEP 2
--Isolation Level - READ UNCOMMITTED
--Dirty reads - Uncommitted/Transaction inprogress would be considered part of transaction
--SESSION 1
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
BEGIN TRAN
SELECT * FROM Employees
WAITFOR DELAY '00:00:10'
SELECT * FROM Employees
ROLLBACK
STEP 3
--SESSION 2
USE TestDB
GO
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
GO
BEGIN TRAN
UPDATE Employees SET Salary = Salary * 1.2
WAITFOR DELAY '00:00:10'
ROLLBACK
STEP 4
--Execute SESSION 2 and then SESSION 1. Below is the output of SESSION 1
Isolation Level - Lost Updates
Nonrepeatable Read: If somebody performed UPDATE or DELETE of any of the rows you read earlier.
Read Committed - When SQL Server executes a statement at the read committed isolation level, it acquires short lived share locks on a row by row basis.
STEP 1
--SESSION 1
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRAN
SELECT * FROM Employees
WAITFOR DELAY '00:00:10'
SELECT * FROM Employees
ROLLBACK
STEP 2
--SESSION 2
BEGIN TRAN
UPDATE Employees
SET Salary = Salary * 1.2
COMMIT
STEP 3
Execute SESSION 1 and then SESSION 2. Output of SESSION 1
Phantom Reads
Repeatable read - a repeatable read scan retains locks on every row it touches until the end of the transaction.
Phantom: If anybody INSERTed a row within the range you had for an earlier query (i.e., you see new rows).
STEP 1
--SESSION 1
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN
SELECT * FROM Employees
WAITFOR DELAY '00:00:10'
SELECT * FROM Employees
ROLLBACK
STEP 2
--SESSION 2
BEGIN TRAN
INSERT INTO Employees(EmpName, Salary)
VALUES ('New Employee', 500);
COMMIT
STEP 3
Execute SESSION 1 and then SESSION 2. Output of SESSION 1
SERIALIZABLE Isolation Level
--Locks all data in the Range, They remain locked until transaction is Complete
SERIALIZABLE - instead of the simple shared lock that was there with the REPEATABLE READ level, you now have RANGE shared locks (looks like 'RangeS-S, which means a range shared lock for a range scan). You will only see these type of locks when you are using a serialized transaction...these differ from the
REPEATABLE READ level locks in that not only do they protect the exact key values returned from the statement, but they also protect the entire range of values the query scans and won't allow the phantom inserts within the queried range like the REPEATABLE READ level did.
STEP 1
--SESSION 1
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN
SELECT * FROM Employees
WAITFOR DELAY '00:00:10'
SELECT * FROM Employees
ROLLBACK
STEP 2
--SESSION 2
BEGIN TRAN
UPDATE Employees
SET Salary = Salary * 1.2
INSERT INTO Employees(EmpName, Salary)
VALUES ('New Employee Again', 800);
COMMIT
STEP 3
Execute SESSION 1 and then SESSION 2. Output of SESSION 1
Snapshot Isolation and Read Committed with Snapshot Isolation (RCSI).
These impacts are:
- Increased I/O to manage row versions
- Increased tempdb usage
- Changes to default behavior affecting applications that rely on pessimistic locking to implement ordered queues.
STEP 1
ALTER DATABASE TestDB SET ALLOW_SNAPSHOT_ISOLATION ON;
GO
STEP 2
--SESSION 1
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
BEGIN TRAN
SELECT * FROM Employees
WAITFOR DELAY '00:00:10'
SELECT * FROM Employees
COMMIT TRAN
--Results #1 and #2 will be same, Before starting transaction the latest available data copy would be used as Reference
STEP 3
--SESSION 2
BEGIN TRAN
UPDATE Employees
SET Salary = Salary * 1.2
INSERT INTO Employees(EmpName, Salary)
VALUES ('New Employee Again', 800);
COMMIT
STEP 4
--Execute SESSION 1 and then SESSION 2. Output of SESSION 1
Read Committed Snapshot Isolation
- Statement Level Row Versioning Results for #1 and #2 will differ
- Before executing each statement it takes a copy
STEP 1
ALTER DATABASE TestDB SET READ_COMMITTED_SNAPSHOT ON
GO
STEP 2
--SESSION 1
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
GO
BEGIN TRAN
SELECT * FROM Employees
WAITFOR DELAY '00:00:10'
SELECT * FROM Employees
COMMIT TRAN
STEP 3
--SESSION 2
BEGIN TRAN
UPDATE Employees
SET Salary = Salary * 1.2
INSERT INTO Employees(EmpName, Salary)
VALUES ('New Employee Again', 800);
COMMIT
STEP 4
Execute SESSION 1 and SESSION 2. Output of SESSION 1
STEP 5
Query to check for Database Status
SELECT snapshot_isolation_state,name
FROM sys.databases
SELECT is_read_committed_snapshot_on ,name
FROM sys.databases
More Reads
Concurrency Series: Basics of Transaction Isolation Levels
Visualising Isolation Levels
Happy Reading!!
