How to Detect sql server lock table or row?

1.4K 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

Your Answer

Answer (1)

Ranjana

To detect locks on tables or rows in SQL Server, you can use several methods. Here are some common techniques:

1. Using Dynamic Management Views (DMVs)

SQL Server provides several dynamic management views that can help you identify locks. The most commonly used views for this purpose are:

sys.dm_tran_locks: Provides information about currently active lock requests.
sys.dm_exec_sessions: Provides information about the sessions.
sys.dm_exec_requests: Provides information about requests currently executing in SQL Server.
Here's an example query that combines these DMVs to provide a detailed view of the locks:

  SELECT     tl.resource_type,    tl.resource_database_id,    DB_NAME(tl.resource_database_id) AS database_name,    tl.resource_associated_entity_id AS object_id,    CASE         WHEN tl.resource_type = 'OBJECT' THEN OBJECT_NAME(tl.resource_associated_entity_id)        ELSE 'N/A'    END AS object_name,    tl.request_mode,    tl.request_status,    tl.request_session_id,    es.host_name,    es.program_name,    es.login_name,    er.blocking_session_id,    er.wait_type,    er.wait_time,    er.wait_resourceFROM     sys.dm_tran_locks AS tlJOIN     sys.dm_exec_sessions AS es ON tl.request_session_id = es.session_idJOIN     sys.dm_exec_requests AS er ON tl.request_session_id = er.session_idORDER BY     tl.resource_database_id,    tl.resource_associated_entity_id;

2. Using SQL Server Profiler

SQL Server Profiler can capture and analyze events on the SQL Server, including lock acquisition and release events. To do this:
Open SQL Server Profiler.
Create a new trace.
In the "Events Selection" tab, check the events related to locks under the "Locks" category (e.g., "Lock

", "Lock

", etc.).

3. Using Activity Monitor

SQL Server Management Studio (SSMS) provides an Activity Monitor tool which you can use to view locking information:

Open SSMS and connect to your SQL Server instance.

Right-click the server name in Object Explorer.

  Select "Activity Monitor".

In the Activity Monitor, go to the "Processes" tab to see currently active sessions and any associated locks.

You can also use the "Locks" tab to view lock details.

4. Using System Stored Procedures

You can use system stored procedures like sp_who2 and sp_lock to get information about locks.

sp_who2 provides information about current users, sessions, and processes.

sp_lock provides information about locks.

  EXEC sp_who2;GOEXEC sp_lock;GO

Example of Interpreting Lock Information

When analyzing the output from these tools, pay attention to the following details:

Resource Type: Indicates what type of resource is locked (e.g., DATABASE, OBJECT, PAGE, KEY).
Request Mode: The mode of the lock (e.g., Shared (S), Update (U), Exclusive (X)).
Request Status: The status of the lock request (e.g., GRANT, WAIT).
Session ID: The ID of the session that holds the lock.
Blocking Session ID: The ID of the session that is causing a block.

6 Months