Detecting the locked table or row in SQL Server
I'm trying to understand/learn how to track down the details of a blocked session.
So I created the following setup:
create table foo (id integer not null primary key, some_data varchar(20)); insert into foo values (1, 'foo'); commit;
Now I connect to the database twice from two different clients.
The first session issues:
begin transaction update foo set some_data = 'update' where id = 1;
I explicitly do not commit there in order to keep the locks.
In the second session I issue the same statement and of course that one waits due to locking. Now I'm trying to use the different queries floating around in order to see that session 2 is waiting for the
sp_who2shows the following (I removed some columns to only show the important information):
SPID | Status | BlkBy | DBName | Command | SPID | REQUESTID -----+--------------+-------+----------+------------------+------+---------- 52 | sleeping | . | foodb | AWAITING COMMAND | 52 | 0 53 | sleeping | . | foodb | AWAITING COMMAND | 53 | 0 54 | SUSPENDED | 52 | foodb | UPDATE | 54 | 0 56 | RUNNABLE | . | foodb | SELECT INTO | 56 | 0
This is expected, session 54 is blocked by the un-committed changes from session 52.
sys.dm_os_waiting_tasksalso shows this. The statement:
select session_id, wait_type, resource_address, resource_description from sys.dm_os_waiting_tasks where blocking_session_id is not null;
session_id | wait_type | resource_address | resource_description -----------+-----------+--------------------+--------------------------------------------------------------------------------- 54 | LCK_M_X | 0x000000002a35cd40 | keylock hobtid=72057594046054400 dbid=6 id=lock4ed1dd780 mode=X associatedObjectId=72057594046054400
Again this is expected.
My problem is, that I can't figure out how to find the actual object name that session 54 is waiting for.
I have found several queries that are joining
SELECT .... FROM sys.dm_tran_locks AS l JOIN sys.dm_os_waiting_tasks AS wt ON wt.resource_address = l.lock_owner_address
But in my above test scenario this join does not return anything. So either that join is wrong or
dm_tran_locksdoesn't actually contain the information I'm looking for.
So what I am looking for is a query that returns something like:
"session 54 is waiting for a lock in table
Some background info:
The real life problem I'm trying to solve is a bit more complicated, but boils down to the question "on which table is session 54 waiting for". The problem in question involves a largish stored procedure that updates several tables and a select from a view that accesses some of those tables. The
selectstatement is blocked even though we have snapshot isolation and read committed snapshot enabled. Figuring out why the select is blocked (which I thought would not be possible if snapshot isolation is enabled) will be the next step.
As a first step I'd like to find out on what that session is waiting.
http://msdn.microsoft.com/en-us/library/ms190345.aspx says your join is correct.
@MaxVernon: thanks for confirming that. But then I'm even more confused. Why does it not return anything although I _know_ there is a lock and a blocked session?
I'm unable to recreate the problem you are seeing in SQL Server 2012. I created a test database, enabled RCSI, created your tables, and ran both update statements, and I see a row returned by your last query.
If you want a visual aid in detecting your locks there is an open source tool available called SQL lock finder. You can find the source on: https://github.com/LucBos/SqlLockFinder Or download the executable on: http://sqllockfinder.com We also love any contributions you could make to the code so we can make it better.
I think this does what you need.
USE 'yourDB' GO SELECT OBJECT_NAME(p.[object_id]) BlockedObject FROM sys.dm_exec_connections AS blocking INNER JOIN sys.dm_exec_requests blocked ON blocking.session_id = blocked.blocking_session_id INNER JOIN sys.dm_os_waiting_tasks waitstats ON waitstats.session_id = blocked.session_id INNER JOIN sys.partitions p ON SUBSTRING(resource_description, PATINDEX('%associatedObjectId%', resource_description) + 19, LEN(resource_description)) = p.partition_id
You can try it :
SELECT db_name(rsc_dbid) AS 'DATABASE_NAME', case rsc_type when 1 then 'null' when 2 then 'DATABASE' WHEN 3 THEN 'FILE' WHEN 4 THEN 'INDEX' WHEN 5 THEN 'TABLE' WHEN 6 THEN 'PAGE' WHEN 7 THEN 'KEY' WHEN 8 THEN 'EXTEND' WHEN 9 THEN 'RID ( ROW ID)' WHEN 10 THEN 'APPLICATION' end AS 'REQUEST_TYPE', CASE req_ownertype WHEN 1 THEN 'TRANSACTION' WHEN 2 THEN 'CURSOR' WHEN 3 THEN 'SESSION' WHEN 4 THEN 'ExSESSION' END AS 'REQUEST_OWNERTYPE', OBJECT_NAME(rsc_objid ,rsc_dbid) AS 'OBJECT_NAME', PROCESS.HOSTNAME , PROCESS.program_name , PROCESS.nt_domain , PROCESS.nt_username , PROCESS.program_name , SQLTEXT.text FROM sys.syslockinfo LOCK JOIN sys.sysprocesses PROCESS ON LOCK.req_spid = PROCESS.spid CROSS APPLY sys.dm_exec_sql_text(PROCESS.SQL_HANDLE) SQLTEXT