Sample Header Ad - 728x90

Find a previous statement or held locks in a blocking situation

7 votes
1 answer
278 views
I'm using XE blocked_process_report to detect and analyse the blocking. But since it's a point-in-time situation, I only see the blocking leader's statement currently being run and the incompatible locks the blocked session is trying to get. So if the blocking leader had several batches/statements in a transaction, I cannot find out which of the previous statements causes the blocking. Repro script
/* Set up tables */
CREATE TABLE dbo.FirstQuery (Id int PRIMARY KEY)
CREATE TABLE dbo.SecondQuery (Id int PRIMARY KEY)

INSERT INTO dbo.FirstQuery (Id)
OUTPUT Inserted.Id INTO dbo.SecondQuery ( Id )
VALUES (1), (2), (3)

/* set up the blocked process event */

EXEC sys.sp_configure
        @configname = 'blocked process threshold (s)' -- varchar(35)
      , @configvalue = 10 -- int
    
RECONFIGURE 

CREATE EVENT SESSION [blocked_process_report] ON SERVER 
ADD EVENT sqlserver.blocked_process_report
(
    ACTION(sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_stack)
)
GO
ALTER EVENT SESSION [blocked_process_report] ON SERVER STATE = START

/* Session 1 - run first and don't commit or rollback */
BEGIN TRANSACTION
    DELETE FROM dbo.FirstQuery
GO
    DELETE FROM dbo.SecondQuery
-- ROLLBACK

/* Session 2 - run second */
BEGIN TRANSACTION
    DELETE FROM dbo.FirstQuery
The blocked process report
BEGIN TRANSACTION
       DELETE FROM dbo.FirstQuery   
        
    
    
        
            
            
       DELETE FROM dbo.SecondQuery
   -- ROLLBACK
I can only see the currently running batch in the input buffer. I could find the held lock info in the sys.dm_tran_locks, but if the blocking is short, I won't capture it in time.
; -- Previous statement must be properly terminated 
WITH LockInfo
AS
(
    SELECT
        dtl.request_session_id
	  , dtl.resource_type
      , dtl.resource_description
      , dtl.resource_associated_entity_id
      , dtl.request_mode
      , dtl.request_status
    FROM 
        sys.dm_tran_locks AS dtl
)
SELECT 
	*
FROM LockInfo AS blcked
JOIN LockInfo AS blcker
    ON blcker.request_session_id = 121 /* blocker session ID */
    AND blcker.resource_type = blcked.resource_type
    AND blcker.resource_description = blcked.resource_description
    AND blcker.resource_associated_entity_id = blcked.resource_associated_entity_id
WHERE 
    blcked.request_session_id = 125 /* blocked session ID */ 
    AND blcked.request_status = N'WAIT'
Is there any automatic and efficient way to find which statement is causing blocking and which locks are being held? I hope to find anything that helps me track the earlier blocker from session 1 (in this case, DELETE FROM dbo.FirstQuery in the code base or to set up additional monitoring. - TSQL Stack - Query Hash - ObjectId (if part of a procedure) - sql_text I can probably infer the held locks from the statement.
Asked by Zikato (5724 rep)
Sep 22, 2022, 10:18 AM
Last activity: Jun 3, 2025, 06:56 PM