ISOLATION LEVEL READ COMMITTED

It is the default isolation level for SQL Server. Read committed isolation reads only committed data in a database. It gives a guarantee of sensible committed data exists in Table. It is not a part of Dirty read. When read committed isolation level in on then, shared locks are held while the data is being read to avoid dirty reads.

Shared locks held for the duration of statement to prevent a concurrent transaction from concurrently modifying the data, because modification would require an exclusive lock, which is not compatible with the shared lock.

Now will go for an practical example.

I have created a database (Test_db) and inside it created a table named “Pritam_table”.

Now, we have to open 2 instance by opening 2 new window in sql server and get the instance number.

Then, write following codes in instance number 79 window

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

BEGIN TRAN

SELECT * FROM PRITAM_TABLE

UPDATE PRITAM_TABLE SET LEAVE_TYPE_DESCR='ASHGFSGDFGHJSD' WHERE DIM_LEAVE_TYPE_ID=1

ROLLBACK

And, write following codes in instance number 81 window

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT * FROM PRITAM_TABLE

Now we have to open one more new window and write the codes given below.

select request_session_id,

case resource_type

when 'DATABASE' then DB_NAME(resource_database_id)

when 'PAGE' then OBJECT_NAME(OBJECT_ID)

when 'RID' then OBJECT_NAME(OBJECT_ID)

when 'OBJECT' then OBJECT_NAME(resource_associated_entity_id)

ELSE 'UNKNOWN'end AS name

,resource_type,request_mode,resource_description,request_status from sys.dm_tran_locks

LEFT JOIN SYS.partitions ON sys.dm_tran_locks.resource_associated_entity_id=SYS.partitions.hobt_id

where request_session_id in (79,81)

If we look on this code the last line we can see the request_session_id in (79,81)

This is nothing but a new window number.

Now will go for 79 window and execute the yellow shared code only

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

BEGIN TRAN

Will see the table now

SELECT * FROM PRITAM_TABLE

Next, will run update command to this table. Remember in first step we started BEGIN TRAN.

UPDATE PRITAM_TABLE SET LEAVE_TYPE_DESCR='Lisa Das' WHERE DIM_LEAVE_TYPE_ID=1

Next will see the locking type exists in for above update query.

Now will go to 81 window and execute the yellow shared code only

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

If we run SELECT * FROM PRITAM_TABLE we then it will wait for the updating of the row in 81 window. This situation is called Deadlock. 

Will look on which locking type exists now.

Now will go for 79 window and execute the yellow shared code only

ROLLBACK

If we run SELECT * FROM PRITAM_TABLE. Will see the result come immediately.