6 AnswersNew Answer
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)
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 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.
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.
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