How to Detect sql server lock table or row?

993 Asked by ankur_3579 in Salesforce , Asked on Jul 12, 2021

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 foo table. sp_who2 shows the following (I removed some columns to only show the important information):

-----+--------------+-------+----------+------------------+------+----------

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. Querying sys.dm_os_waiting_tasks also 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; returns: 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 sys.dm_tran_locks and sys.dm_os_waiting_tasks like this: 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_locks doesn'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 foo". 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 select statement 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. Please help how to sql server lock table or row, any help would be appreciated.

Answered by Chris Dyer

You can try this to detect sql server lock table :

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

 Or

You can try a second approach to detect sql server lock table

  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

How to Detect sql server lock table or row?

Your Answer