Find a previous statement or held locks in a blocking situation

Question

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

<blocked-process-report monitorLoop="3963">
    <blocked-process>
        <process
            id="process22254469468"
            taskpriority="0"
            logused="0"
            waitresource="KEY: 11:72057594047299584 (8194443284a0)"
            waittime="21867"
            ownerId="715194"
            transactionname="user_transaction"
            lasttranstarted="2022-09-22T11:36:27.260"
            XDES="0x222681a0470" lockMode="U"
            schedulerid="15"
            kpid="13744"
            status="suspended"
            spid="125"
            sbid="0"
            ecid="0"
            priority="0"
            trancount="3"
            lastbatchstarted="2022-09-22T11:38:37.947"
            lastbatchcompleted="2022-09-22T11:38:37.947"
            lastattention="2022-09-22T11:38:08.630"
            clientapp="Microsoft SQL Server Management Studio - Query"
            hostname="MyHost"
            hostpid="27992"
            loginname="MyHost\MyLogin"
            isolationlevel="read committed (2)"
            xactid="715194"
            currentdb="11"
            currentdbname="Rubbish"
            lockTimeout="4294967295"
            clientoption1="671090784"
            clientoption2="390200"
        >
            <executionStack>
                <frame line="3" stmtstart="50" stmtend="100" sqlhandle="0x020000007620ac3a2dda99a1c6f6f97305d90262cac2e4080000000000000000000000000000000000000000" />
            </executionStack>
            <inputbuf>
   
   BEGIN TRANSACTION
       DELETE FROM dbo.FirstQuery   </inputbuf>
        </process>
    </blocked-process>
    <blocking-process>
        <process
            status="sleeping"
            spid="121"
            sbid="0"
            ecid="0"
            priority="0"
            trancount="1"
            lastbatchstarted="2022-09-22T11:38:35.193"
            lastbatchcompleted="2022-09-22T11:38:35.193"
            lastattention="1900-01-01T00:00:00.193"
            clientapp="Microsoft SQL Server Management Studio - Query"
            hostname="MyHost"
            hostpid="27992"
            loginname="MyHost\MyLogin"
            isolationlevel="read committed (2)"
            xactid="717574"
            currentdb="11"
            currentdbname="Rubbish"
            lockTimeout="4294967295"
            clientoption1="671090784"
            clientoption2="390200"
        >
            <executionStack />
            <inputbuf>
       DELETE FROM dbo.SecondQuery
   -- ROLLBACK   </inputbuf>
        </process>
    </blocking-process>
</blocked-process-report>

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.