How to check which locks are held on a table

Question

How can we check which database locks are applied on which rows against a query batch    Any tool that highlights table row level locking in real time    DB  SQL Server 2005

User · Answer

You can find current locks on your table by following query   USE yourdatabase  GO  SELECT   FROM sys dm tran locks   WHERE resource database id   DB ID     AND resource associated entity id   OBJECT ID N dbo yourtablename      See sys dm tran locks  If multiple instances of the same request owner type exist  the request owner id column is used to distinguish each instance  For distributed transactions  the request owner type and the request owner guid columns will show the different entity information   For example  Session S1 owns a shared lock on Table1  and transaction T1  which is running under session S1  also owns a shared lock on Table1  In this case  the resource description column that is returned by sys dm tran locks will show two instances of the same resource  The request owner type column will show one instance as a session and the other as a transaction  Also  the resource owner id column will have different values

User · Answer

You can find details via the below script   -- List all Locks of the Current Database  SELECT TL resource type AS ResType         TL resource description AS ResDescr         TL request mode AS ReqMode         TL request type AS ReqType         TL request status AS ReqStatus         TL request owner type AS ReqOwnerType         TAT  name  AS TransName         TAT transaction begin time AS TransBegin         DATEDIFF ss  TAT transaction begin time  GETDATE    AS TransDura         ES session id AS S Id         ES login name AS LoginName         COALESCE OBJ name  PAROBJ name  AS ObjectName         PARIDX name AS IndexName         ES host name AS HostName         ES program name AS ProgramName  FROM sys dm tran locks AS TL       INNER JOIN sys dm exec sessions AS ES           ON TL request session id   ES session id       LEFT JOIN sys dm tran active transactions AS TAT           ON TL request owner id   TAT transaction id              AND TL request owner type    TRANSACTION        LEFT JOIN sys objects AS OBJ           ON TL resource associated entity id   OBJ object id              AND TL resource type    OBJECT        LEFT JOIN sys partitions AS PAR           ON TL resource associated entity id   PAR hobt id              AND TL resource type IN   PAGE    KEY    RID    HOBT         LEFT JOIN sys objects AS PAROBJ           ON PAR object id   PAROBJ object id       LEFT JOIN sys indexes AS PARIDX           ON PAR object id   PARIDX object id              AND PAR index id   PARIDX index id  WHERE TL resource database id    DB ID          AND ES session id  lt  gt    Spid -- Exclude  my  session        -- optional filter         AND TL request mode  lt  gt   S  -- Exclude simple shared locks  ORDER BY TL resource type           TL request mode           TL request type           TL request status           ObjectName           ES login name     --TSQL commands 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 where 1 1 and db name rsc dbid    db name      --Lock on a specific object SELECT    FROM sys dm tran locks WHERE resource database id   DB ID   AND resource associated entity id   object id  Specific Table

User · Answer

You can find blocking sql and wait sql by running this  SELECT     t1 resource type       DB NAME  resource database id  AS dat name       t1 resource associated entity id      t1 request mode      t1 request session id      t2 wait duration ms        SELECT TEXT FROM sys dm exec requests r CROSS apply sys dm exec sql text   r sql handle   WHERE r session id   t1 request session id   AS wait sql      t2 blocking session id        SELECT TEXT FROM sys sysprocesses p CROSS apply sys dm exec sql text   p sql handle   WHERE p spid   t2 blocking session id   AS blocking sql  FROM     sys dm tran locks t1      sys dm os waiting tasks t2  WHERE     t1 lock owner address   t2 resource address

User · Answer

I use a Dynamic Management View  DMV  to capture locks as well as the object id or partition id of the item that is locked    MUST switch to the Database you want to observe to get object id   SELECT       TL resource type       TL resource database id       TL resource associated entity id       TL request mode       TL request session id       WT blocking session id       O name AS  object name        O type desc AS  object descr        P partition id AS  partition id        P rows AS  partition page rows        AU type desc AS  index descr        AU container id AS  index page container id  FROM sys dm tran locks AS TL INNER JOIN sys dm os waiting tasks AS WT   ON TL lock owner address   WT resource address LEFT OUTER JOIN sys objects AS O   ON O object id   TL resource associated entity id LEFT OUTER JOIN sys partitions AS P   ON P hobt id   TL resource associated entity id LEFT OUTER JOIN sys allocation units AS AU   ON AU allocation unit id   TL resource associated entity id

User · Answer

This is not exactly showing you which rows are locked  but this may helpful to you   You can check which statements are blocked by running this   select cmd   from sys sysprocesses where blocked  gt  0   It will also tell you what each block is waiting on   So you can trace that all the way up to see which statement caused the first block that caused the other blocks   Edit to add comment from  MikeBlandford      The blocked column indicates the spid of the blocking process  You can run kill  spid  to fix it

User · Answer

To add to the other responses  sp lock can also be used to dump full lock information on all running processes   The output can be overwhelming  but if you want to know exactly what is locked  it s a valuable one to run   I usually use it along with sp who2 to quickly zero in on locking problems   There are multiple different versions of  friendlier  sp lock procedures available online  depending on the version of SQL Server in question   In your case  for SQL Server 2005  sp lock is still available  but deprecated  so it s now recommended to use the sys dm tran locks view for this kind of thing   You can find an example of how to  roll your own  sp lock function here

User · Answer

You can also use the built-in sp who2 stored procedure to get current blocked and blocking processes on a SQL Server instance   Typically you d run this alongside a SQL Profiler instance to find a blocking process and look at the most recent command that spid issued in profiler

[sql] How to check which locks are held on a table

Examples related to sql

Examples related to sql-server

Examples related to database

Examples related to performance

Examples related to sql-server-2005