SQL Server Isolation level
SQL Server isolation levels are used to define the degree to which one transaction must be isolated from resource or data modifications made by other concurrent transactions,
Following are the different types of isolations available in SQL Server.
- READ COMMITTED
- READ UNCOMMITTED
- REPEATABLE READ
- SERIALIZABLE
- SNAPSHOT
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)
Note: Before executing each example in this article, reset the Emp table values by executing the above script.
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.
Read committed example 2
Session1
begin tran
select * from Emp
waitfor delay '00:00:15'
commit
Session2
set transaction isolation level read committed
select * from Emp
Run both sessions side by side.
Output
1000
In session2, there won't be any delay in execution because in session1 Emp table is used under transaction but it is not used update or delete command hence Emp table is not locked.
Read committed example 3
Session 1
begin tran
select * from emp
waitfor delay '00:00:15'
update emp set Salary=999 where ID=1
commit
Session 2
set transaction isolation level read committed
select Salary from Emp where ID=1
Run both sessions side by side.
Output
1000
In session2, there won't be any delay in execution because when session2 is executed Emp table in session1 is not locked(used only select command, locking on Emp table occurs after wait delay command).
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)
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.
Repeatable Read Example 2
Session 1
set transaction isolation level repeatable read
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.
session 2 will execute without any delay because it has insert query for new entry. This isolation level allows to insert new data but does not allow to modify data that is used in select query executed in transaction.
You can notice two results displayed in Session 1 have different number of row count(1 row extra in sectond result set).
Repeatable Read Example 3
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=3
Run both sessions at a time one by one.
Output
session 2 will execute without any delay because row with ID=3 is not locked, that is only 2 records whose IDs are 1,2 are locked in Session 1.
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.
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.
Serializable Example 2
Assume table has primary key on column "ID". In our example script, primary key is not added. Add primary key on column Emp.ID before executing below examples.
Session 1
set transaction isolation level serializable
begin tran
select * from emp where ID between 1 and 3
waitfor delay '00:00:15'
select * from Emp where ID between 1 and 3
rollback
Session 2
insert into Emp(ID,Name,Salary)
values( 11,'Stewart',11000)
Run both sessions side by side.
Output
Since Session 1 is filtering IDs between 1 and 3, only those records whose IDs range between 1 and 3 will be locked and these records can not be modified and no new records with ID range between 1 to 3 will be inserted. In this example, new record with ID=11 will be inserted in Session 2 without any delay.
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
Run both sessions side by side.
Output
Result in Session 1.
Result in Session 2.
Session 2 queries will be executed in parallel as transaction in session 1 won't lock the table Emp.
