Sql-server – How to find the query that is still holding a lock

blockinglockingsql server

Querying the sys.dm_tran_locks DMV shows us which sessions (SPIDs) are holding locks on resources like table, page and row.

For each lock acquired, is there any way to determine which SQL statement (delete, insert, update or select) caused that lock?

I know that the most_recent_query_handle column of the sys.dm_exec_connections DMV gives us the text of the last query executed, but several times other queries ran before under the same session (SPID) and are still holding locks.

I already use the sp_whoisactive procedure (from Adam Machanic) and it only shows the query that is on the input buffer at the moment (think DBCC INPUTBUFFER @spid), which not always (and in my case usually never) is the query that acquired the lock.

For example:

  1. open transaction/session
  2. exec a statement (that holds a lock on a resource)
  3. exec another statement on the same session
  4. open another transaction/session and try to modify the resource locked at step 2.

The sp_whoisactive procedure will point out the statement at step 3, which is not the responsible for the lock, and thus not useful.

This question came from doing an analysis using the Blocked Process Reports feature, to find the root cause of blocking scenarios in production. Each transaction runs several queries, and most of time the last one (that is shown on input buffer at BPR) is rarely the one holding the lock.

I have a follow-up question: Framework to effectively identify blocking queries

Best Answer

SQL Server doesn't keep a history of the commands that have been executed1,2. You can determine what objects have locks, but you cannot necessarily see what statement caused those locks.

For example, if you execute this statement:

BEGIN TRANSACTION
INSERT INTO dbo.TestLock DEFAULT VALUES

And look at the SQL Text via the most recent sql handle, you'll see that statement does show up. However, if the session did this:

BEGIN TRANSACTION
INSERT INTO dbo.TestLock DEFAULT VALUES
GO
SELECT *
FROM dbo.TestLock;
GO

You'd only see the SELECT * FROM dbo.TestLock; statement, even though the transaction hasn't been committed, and the INSERT statement is blocking readers against the dbo.TestLock table.

I use this for looking for uncommitted transactions that are blocking other sessions:

/*
    This query shows sessions that are blocking other sessions, including sessions that are 
    not currently processing requests (for instance, they have an open, uncommitted transaction).

    By:  Max Vernon, 2017-03-20
*/
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; --reduce possible blocking by this query.

USE tempdb;

IF OBJECT_ID('tempdb..#dm_tran_session_transactions') IS NOT NULL
DROP TABLE #dm_tran_session_transactions;
SELECT *
INTO #dm_tran_session_transactions
FROM sys.dm_tran_session_transactions;

IF OBJECT_ID('tempdb..#dm_exec_connections') IS NOT NULL
DROP TABLE #dm_exec_connections;
SELECT *
INTO #dm_exec_connections
FROM sys.dm_exec_connections;

IF OBJECT_ID('tempdb..#dm_os_waiting_tasks') IS NOT NULL
DROP TABLE #dm_os_waiting_tasks;
SELECT *
INTO #dm_os_waiting_tasks
FROM sys.dm_os_waiting_tasks;

IF OBJECT_ID('tempdb..#dm_exec_sessions') IS NOT NULL
DROP TABLE #dm_exec_sessions;
SELECT *
INTO #dm_exec_sessions
FROM sys.dm_exec_sessions;

IF OBJECT_ID('tempdb..#dm_exec_requests') IS NOT NULL
DROP TABLE #dm_exec_requests;
SELECT *
INTO #dm_exec_requests
FROM sys.dm_exec_requests;

;WITH IsolationLevels AS 
(
    SELECT v.*
    FROM (VALUES 
              (0, 'Unspecified')
            , (1, 'Read Uncomitted')
            , (2, 'Read Committed')
            , (3, 'Repeatable')
            , (4, 'Serializable')
            , (5, 'Snapshot')
        ) v(Level, Description)
)
, trans AS 
(
    SELECT dtst.session_id
        , blocking_sesion_id = 0
        , Type = 'Transaction'
        , QueryText = dest.text
    FROM #dm_tran_session_transactions dtst 
        LEFT JOIN #dm_exec_connections dec ON dtst.session_id = dec.session_id
    OUTER APPLY sys.dm_exec_sql_text(dec.most_recent_sql_handle) dest
)
, tasks AS 
(
    SELECT dowt.session_id
        , dowt.blocking_session_id
        , Type = 'Waiting Task'
        , QueryText = dest.text
    FROM #dm_os_waiting_tasks dowt
        LEFT JOIN #dm_exec_connections dec ON dowt.session_id = dec.session_id
    OUTER APPLY sys.dm_exec_sql_text(dec.most_recent_sql_handle) dest
    WHERE dowt.blocking_session_id IS NOT NULL
)
, requests AS 
(
SELECT des.session_id
    , der.blocking_session_id
    , Type = 'Session Request'
    , QueryText = dest.text
FROM #dm_exec_sessions des
    INNER JOIN #dm_exec_requests der ON des.session_id = der.session_id
OUTER APPLY sys.dm_exec_sql_text(der.sql_handle) dest
WHERE der.blocking_session_id IS NOT NULL
    AND der.blocking_session_id > 0 
)
, Agg AS (
    SELECT SessionID = tr.session_id
        , ItemType = tr.Type
        , CountOfBlockedSessions = (SELECT COUNT(*) FROM requests r WHERE r.blocking_session_id = tr.session_id)
        , BlockedBySessionID = tr.blocking_sesion_id
        , QueryText = tr.QueryText
    FROM trans tr
    WHERE EXISTS (
        SELECT 1
        FROM requests r
        WHERE r.blocking_session_id = tr.session_id
        )
    UNION ALL
    SELECT ta.session_id
        , ta.Type
        , CountOfBlockedSessions = (SELECT COUNT(*) FROM requests r WHERE r.blocking_session_id = ta.session_id)
        , BlockedBySessionID = ta.blocking_session_id
        , ta.QueryText
    FROM tasks ta
    UNION ALL
    SELECT rq.session_id
        , rq.Type
        , CountOfBlockedSessions =  (SELECT COUNT(*) FROM requests r WHERE r.blocking_session_id = rq.session_id)
        , BlockedBySessionID = rq.blocking_session_id
        , rq.QueryText
    FROM requests rq
)
SELECT agg.SessionID
    , ItemType = STUFF((SELECT ', ' + COALESCE(a.ItemType, '') FROM agg a WHERE a.SessionID = agg.SessionID ORDER BY a.ItemType FOR XML PATH ('')), 1, 2, '')
    , agg.BlockedBySessionID
    , agg.QueryText
    , agg.CountOfBlockedSessions
    , des.host_name
    , des.login_name
    , des.is_user_process
    , des.program_name
    , des.status
    , TransactionIsolationLevel = il.Description
FROM agg 
    LEFT JOIN #dm_exec_sessions des ON agg.SessionID = des.session_id
    LEFT JOIN IsolationLevels il ON des.transaction_isolation_level = il.Level
GROUP BY agg.SessionID
    , agg.BlockedBySessionID
    , agg.CountOfBlockedSessions
    , agg.QueryText
    , des.host_name
    , des.login_name
    , des.is_user_process
    , des.program_name
    , des.status
    , il.Description
ORDER BY 
    agg.BlockedBySessionID
    , agg.CountOfBlockedSessions
    , agg.SessionID;

If we setup a simple test-bed in SSMS with a couple of query windows, we can see that we can only see the most recently active statement.

In the first query window, run this:

CREATE TABLE dbo.TestLock
(
    id int NOT NULL IDENTITY(1,1)
);
BEGIN TRANSACTION
INSERT INTO dbo.TestLock DEFAULT VALUES

In the second window, run this:

SELECT *
FROM  dbo.TestLock

Now, if we run the uncommitted blocking transactions query from above, we see the following output:

╔═══════════╦═══════════════════════════════╦════════════════════╦═════════════════════════════════════════╗
║ SessionID ║           ItemType            ║ BlockedBySessionID ║                QueryText                ║
╠═══════════╬═══════════════════════════════╬════════════════════╬═════════════════════════════════════════╣
║        67 ║ Transaction                   ║                  0 ║ BEGIN TRANSACTION                       ║
║           ║                               ║                    ║ INSERT INTO dbo.TestLock DEFAULT VALUES ║
║        68 ║ Session Request, Waiting Task ║                 67 ║ SELECT *                                ║
║           ║                               ║                    ║ FROM  dbo.TestLock                      ║
╚═══════════╩═══════════════════════════════╩════════════════════╩═════════════════════════════════════════╝

(I've removed some irrelevant columns from the end of the results).

Now, if we change the first query window to this:

BEGIN TRANSACTION
INSERT INTO dbo.TestLock DEFAULT VALUES
GO
SELECT *
FROM dbo.TestLock;
GO

and re-run the 2nd query window:

SELECT *
FROM  dbo.TestLock

We'll see this output from the blocking transactions query:

╔═══════════╦═══════════════════════════════╦════════════════════╦════════════════════╗
║ SessionID ║           ItemType            ║ BlockedBySessionID ║     QueryText      ║
╠═══════════╬═══════════════════════════════╬════════════════════╬════════════════════╣
║        67 ║ Transaction                   ║                  0 ║ SELECT *           ║
║           ║                               ║                    ║ FROM dbo.TestLock; ║
║        68 ║ Session Request, Waiting Task ║                 67 ║ SELECT *           ║
║           ║                               ║                    ║ FROM  dbo.TestLock ║
╚═══════════╩═══════════════════════════════╩════════════════════╩════════════════════╝

1 - not entirely true. There is the procedure cache, which may contain the statement responsible for the lock. However, it may not be easy to determine which statement is the actual cause of the lock since there may be many queries in the cache that touch the resource in question.

The query below shows the query plan for the test queries above since my procedure cache is not very busy.

SELECT TOP(30) t.text
    , p.query_plan
    , deqs.execution_count
    , deqs.total_elapsed_time
    , deqs.total_logical_reads
    , deqs.total_logical_writes
    , deqs.total_logical_writes
    , deqs.total_rows
    , deqs.total_worker_time
    , deqs.*
FROM sys.dm_exec_query_stats deqs
OUTER APPLY sys.dm_exec_sql_text(deqs.sql_handle) t 
OUTER APPLY sys.dm_exec_query_plan(deqs.plan_handle) p
WHERE t.text LIKE '%dbo.TestLock%'  --change this to suit your needs
    AND t.text NOT LIKE '/\/\/\/\/EXCLUDE ME/\/\/\/\/\'
ORDER BY 
    deqs.total_worker_time DESC;

The results of this query may allow you to find the culprit, but be aware, inspecting the procedure cache like this can be quite demanding on a busy system.

2SQL Server 2016 and above offer the Query Store, which does retain complete history of queries executed.

Related Question