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.
- open transaction/session
- exec a statement (that holds a lock on a resource)
- exec another statement on the same session
- open another transaction/session and try to modify the resource locked at step 2.
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