Isolation Levels in SQL Server – SQL Server Tutorial

Isolation Levels in SQL Server :-

Locks are controlled by isolation levels,
Types of isolation levels:

1. Read uncommitted
2. Read committed
3. Serializable

(I) Read uncommitted in SQL Server  :-

If the isolation level is set to read uncommitted then SQL Server doesn’t place any locks.

(II) Read committed in SQL Server  :-

If the isolation level is set to read  committed then shared locks are released immediately after execution of select statement but the exclusively locks are released after the end of transaction.

Transaction —-→

Select com → Released

Update sal

End Transaction  → Released

(III) Serializable in SQL Server :-

If the isolation level is set to serializable then both locks are released after end of transaction.

Tx starts

Select com

Update sal

set transaction isolation level read committed.

sp_lock → this will show which objects are locked.

Select statement for update  :-

when select statement is submitted with for update instead of placing shared lock sql server places exclusive lock.

Select sal,com from emp where empno=7369 for update (or)

         Declare c1 cursor for

            Select sal,comm from emp for update.