Transaction Isolation Levels in MSSQL Server

Q. What is default isolation level of SQL Server?

A. READ COMMITTED is the default isolation level for the Microsoft SQL Server Database Engine.

CREATE TABLE ##Employee

(

       EmpId int identity,

       EmpName varchar(50),

       EmpDept varchar(50)

)

insert into ##Employee(EmpName,EmpDept)values('Ram','HR')

insert into ##Employee(EmpName,EmpDept)values('Shyam','Accountant')

insert into ##Employee(EmpName,EmpDept)values('Ghanshyam','Sales')

insert into ##Employee(EmpName,EmpDept)values('Siva','Marketing')

insert into ##Employee(EmpName,EmpDept)values('Siv','Marketing')

select * from ##Employee

EmpId	EmpName	EmpDept
1	Ram	HR
2	Shyam	Accountant
3	Ghanshyam	Sales
4	Siva	Marketing
5	Siv	Marketing

READ UNCOMMITTED

1. Data can be modified and read during the transaction.
2. This isolation level allows dirty reads

Step 1:- In Session 1, Update the record and set EmpDept “Operation” instead of “Marketing” whose Employee ID is 5. After running Session 1 query, immediately run the Step 2 query in Session 2

BEGIN TRANSACTION;

UPDATE ##Employee

SET EmpDept = 'Operation'

WHERE EmpId = 5;

WAITFOR DELAY '00:00:10'

ROLLBACK TRANSACTION;

Step 2:- Select Record from ##Employee table

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SELECT * FROM ##Employee

WHERE EmpId = 5;

You Noticed…..

1. You will get immediately updated record.
2. Now, after completing the Session 1 query, again select the record in Session 2.
3. You will get the old record.

The point 1 result is an example of a dirty read

Note:-

When Transaction Isolation Level is read uncommitted and you are selecting the record from ##Employee table. But same table in another transaction updating the same record.

You will get immediately updated record. But, if that transaction was rollback.

In this case you will get the wrong record.

READ COMMITTED

1. Data can be modified, but cannot be read during the transaction.
2. This isolation level not allows dirty reads

Step 1:- In Session 1, Update the record and set EmpDept “Operation” instead of “Marketing” whose Employee ID is 5. After running Session 1 query, immediately run the Step 2 query in Session 2

BEGIN TRANSACTION;

UPDATE ##Employee

SET EmpDept = 'Operation'

WHERE EmpId = 5;

WAITFOR DELAY '00:00:10'

COMMIT TRANSACTION;

Step 2:- Select Record from ##Employee table

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

SELECT * FROM ##Employee

WHERE EmpId = 5;

You Noticed…..

1. Session 2 select statement, waiting for the Session 1 transaction completion.
2. You can see, no dirty read.

Note:-

You will wait till the Session 1 transaction completes.

REPEATABLE READ

1. Data can be read but not modified during the transaction. New data can be added during the transaction.

Step 1:- Select Record from ##Employee table and In Session 2, Update the record and set EmpDept “Operation” instead of “Marketing” whose Employee ID is 5.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

BEGIN TRANSACTION;

SELECT * FROM ##Employee

WAITFOR DELAY '00:00:10'

COMMIT TRANSACTION;

Step 2:- Update the record of ##Employee table

BEGIN TRANSACTION;

UPDATE ##Employee

SET EmpDept = 'Operation'

WHERE EmpId = 5;

insert into ##Employee(EmpName,EmpDept)values('Darshan','Finance')

COMMIT TRANSACTION;

SERIALIZABLE

1. Data can be read but not modified, and no new data can be added during the transaction.

Step 1:- Select Record from ##Employee table and In Session 2, Update the record and set EmpDept “Operation” instead of “Marketing” whose Employee ID is 5.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

BEGIN TRANSACTION;

SELECT * FROM ##Employee

WAITFOR DELAY '00:00:10'

COMMIT TRANSACTION;

Step 2:- Update the record of ##Employee table

BEGIN TRANSACTION;

UPDATE ##Employee

SET EmpDept = 'Operation'

WHERE EmpId = 5;

insert into ##Employee(EmpName,EmpDept)values('Darshan','Finance')

COMMIT TRANSACTION;

For more info visit following link.

Link 1

Link 2