How to find the query that is still holding a lock?
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.
To complement Max's answer, I have found below utilities extremely useful:
- beta_lockinfo - by Erland Sommarskog
I use the beta_lockinfo when I want to deep dive into blocking and analyze what and how blocking arose - which is extremely useful.
beta_lockinfo is a stored procedure that provides information about processes and the locks they hold as well their active transactions. beta_lockinfo is designed to gather as much information about a blocking situation as possible, so that you can instantly find the culprit and kill the blocking process if the situation is desperate. Then you can sit back and analyse the output from beta_lockinfo to understand how the blocking situation arose and figure out what actions to take to prevent the situation from reoccurring. The output from beta_lockinfo shows all active process as well as passive processes with locks, which objects they lock, what command they last submitted and which statement they are executing. You also get the query plans for the current statements.
- Running-Blocked-Processes - by MS Tiger Team