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
Last activity: Jun 3, 2025, 06:56 PM