SQL Server Isolation Levels with examples

Question

Following are the different types of isolations available in SQL Server.

READ COMMITTED
READ UNCOMMITTED
REPEATABLE READ
SERIALIZABLE
SNAPSHOT

Answer 1

Let us discuss about each isolation level in details.Before this, execute following script to create table and insert some data that we are going to use in examples for each isolation

IF OBJECT_ID('Emp') is not null
begin
DROP TABLE Emp
end

create table Emp(ID int,Name Varchar(50),Salary Int)

insert into Emp(ID,Name,Salary)
values( 1,'David',1000)

insert into Emp(ID,Name,Salary)
values( 2,'Steve',2000)

insert into Emp(ID,Name,Salary)
values( 3,'Chris',3000)

Answer 2

Read Committed
In select query it will take only commited values of table. If any transaction is opened and incompleted on table in others sessions then select query will wait till no transactions are pending on same table.

Read Committed is the default transaction isolation level.

Read committed example 1:
Session 1

begin tran
update emp set Salary=999 where ID=1
waitfor delay '00:00:15'
commit

Session 2

set transaction isolation level read committed
select Salary from Emp where ID=1
Run both sessions side by side.

Output

999

In second session, it returns the result only after execution of complete transaction in first session because of the lock on Emp table. We have used wait command to delay 15 seconds after updating the Emp table in transaction.

Answer 3

Read Uncommitted
If any table is updated(insert or update or delete) under a transaction and same transaction is not completed that is not committed or roll backed then uncommitted values will displaly(Dirty Read) in select query of "Read Uncommitted" isolation transaction sessions. There won't be any delay in select query execution because this transaction level does not wait for committed values on table.

Read uncommitted example 1
Session 1

begin tran
update emp set Salary=999 where ID=1
waitfor delay '00:00:15'
rollback

Session 2

set transaction isolation level read uncommitted
select Salary from Emp where ID=1
Run both sessions at a time one by one.

Output

999

Select query in Session2 executes after update Emp table in transaction and before transaction rolled back. Hence 999 is returned instead of 1000.

If you want to maintain Isolation level "Read Committed" but you want dirty read values for specific tables then use with(nolock) in select query for same tables as shown below.

set transaction isolation level read committed
select * from Emp with(nolock)

Answer 4

Repeatable Read
select query data of table that is used under transaction of isolation level "Repeatable Read" can not be modified from any other sessions till transcation is completed.

Repeatable Read Example 1
Session 1

set transaction isolation level repeatable read
begin tran
select * from emp where ID in(1,2)
waitfor delay '00:00:15'
select * from Emp where ID in (1,2)
rollback
Session 2

update emp set Salary=999 where ID=1
Run both sessions side by side.

Output

Update command in session 2 will wait till session 1 transaction is completed because emp table row with ID=1 has locked in session1 transaction.

Answer 5

Serializable
Serializable Isolation is similar to Repeatable Read Isolation but the difference is it prevents Phantom Read. This works based on range lock. If table has index then it locks records based on index range used in WHERE clause(like where ID between 1 and 3). If table doesn't have index then it locks complete table.

Serializable Example 1
Assume table does not have index column.
Session 1

set transaction isolation level serializable
begin tran
select * from emp
waitfor delay '00:00:15'
select * from Emp
rollback
Session 2

insert into Emp(ID,Name,Salary)
values( 11,'Stewart',11000)

Run both sessions side by side.

Output

Result in Session 1.

Serializable Isolation example table with no index
Complete Emp table will be locked during the transaction in Session 1. Unlike "Repeatable Read", insert query in Session 2 will wait till session 1 execution is completed. Hence Phantom read is prevented and both queries in session 1 will display same number of rows.

To compare same scenario with "Repeatable Read" read Repeatable Read Example 2.

Answer 6

Snapshot
Snapshot isolation is similar to Serializable isolation. The difference is Snapshot does not hold lock on table during the transaction so table can be modified in other sessions. Snapshot isolation maintains versioning in Tempdb for old data in case of any data modification occurs in other sessions then existing transaction displays the old data from Tempdb.

Snapshot Example 1
Session 1

set transaction isolation level snapshot
begin tran
select * from Emp
waitfor delay '00:00:15'
select * from Emp
rollback
Session 2

insert into Emp(ID,Name,Salary) values( 11,'Stewart',11000)
update Emp set Salary=4444 where ID=4
select * from Emp