How to identify Locking and Blocking sessions ?

Open a New Query: (Session: 57)

Create a new Table : Tb_SQLBuddy

Use SQLServerBuddy
Go
If OBJECT_ID('Tb_SQLBuddy') Is Not Null
Drop Table Tb_SQLBuddy
Go
Create Table Tb_SQLBuddy
(
Column1 Int
)
Go


Run the Following block at Session: 57

BEGIN TRAN
SELECT Column1
FROM Tb_SQLBuddy
WITH(holdlock, rowlock);

The table will have Shard Lock on Tb_SQLBuddy

Open a New Query: (Session: 58)

BEGIN TRAN
UPDATE Tb_SQLBuddy SET Column1 = 10

The table will have Intent Exclusive Lock on Tb_SQLBuddy.

Open a New Query: (Session: 59)

Execute the query to identity which session/Object have Lock and which session/Object have been Blocked.

SELECT
DB_NAME(t1.resource_database_id) 'Database',
OBJECT_NAME(t3.[object_id]) 'Table Name',
t1.resource_type,
t1.request_mode,
t1.request_session_id 'Request/Blocked Session',
t2.blocking_session_id 'Locking Session'
FROM sys.dm_tran_locks as t1
JOIN sys.dm_os_waiting_tasks as t2
ON t1.lock_owner_address = t2.resource_address
JOIN sys.partitions AS T3 on (T3.[object_id] = t1.resource_associated_entity_id)

Result:



Session: 57 holds Lock on Tb_SQLBuddy table and Session: 58 Blocked by the Session : 57

Identifying Queries applied at each session

Select c.session_id 'Session ID',
s.[text] 'Query Applied'
from sys.dm_exec_connections c Cross Apply Sys.dm_exec_sql_text(c.most_recent_sql_handle) s
Where c.session_id in(57,58)

Result:

SELECT query applied at Session: 57, UPDATE query applied at Session: 58