lock analysis with TSQL

All Replies

• Saturday, January 26, 2013 8:43 PM

   

   
  

  1

  RID: 9:3:206695:32
  
  According to MSDN docs the RID lock is expressed as db_id:file_id:page_no:row_no.
  
  What is the TSQL to look up each element in this string?  How do you lookup the table the RID lock belongs to?
  

  To look up db_id, use the db_name() function: SELECT db_name(9).

  To get information about the object, use DBCC PAGE. This is not a officially documented command, but googling it will give you information. I don't recall the syntax by heart myself, and I am not going to look it up.

  KEY: 9:626345259433984 (a8016a427524)
  
  According to MSDN docs the KEY lock is expressed as KEY: db_id:hobt_id(index key hash value)
  
  What is the TSQL to look up each element in this string?

  
  SELECT object_name(object_id) FROM sys.partitions
  WHERE hobt_id = 626345259433984  should give you the table name.

  But if you use trace flag 1222, you get more readable information in the deadlock graph. And if you are on SQL 2008, the deadlocks are in the System Health session.

  ---

  Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

  • Marked As Answer by scott_m Sunday, January 27, 2013 5:39 PM
  •  
  • Reply
  • Quote

   
• Saturday, January 26, 2013 8:59 PM

  Moderator

   

   
  

  1

  You should see the queries in the deadlock graph.

  Just, post your query(ies) & DDL for quick assistance.  Thanks.

  Deadlock article:

  http://www.sqlusa.com/bestpractices/deadlock/

  Optimization article:

  http://www.sqlusa.com/articles/query-optimization/

  ---

  Kalman Toth SQL 2008 GRAND SLAM
  Paperback: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

  
  
  

  • Edited by Kalman TothMicrosoft Community Contributor, Moderator Saturday, January 26, 2013 9:03 PM
  •  
  • Reply
  • Quote

   
• Sunday, January 27, 2013 4:29 PM

   

   
  

  0

  RID: 9:3:206695:32
  
  According to MSDN docs the RID lock is expressed as db_id:file_id:page_no:row_no.
  
  What is the TSQL to look up each element in this string?  How do you lookup the table the RID lock belongs to?
  

  To look up db_id, use the db_name() function: SELECT db_name(9).

  To get information about the object, use DBCC PAGE. This is not a officially documented command, but googling it will give you information. I don't recall the syntax by heart myself, and I am not going to look it up.

  KEY: 9:626345259433984 (a8016a427524)
  
  According to MSDN docs the KEY lock is expressed as KEY: db_id:hobt_id(index key hash value)
  
  What is the TSQL to look up each element in this string?

  
  SELECT object_name(object_id) FROM sys.partitions
  WHERE hobt_id = 626345259433984  should give you the table name.

  But if you use trace flag 1222, you get more readable information in the deadlock graph. And if you are on SQL 2008, the deadlocks are in the System Health session.

  ---

  Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

  Thanks!

  I was querying the xml_deadlock_report extended event for the deadlock graph to avoid potential overhead of trace flag 1222.

  That's good to know about the undocumented dbcc page function.   To resolve the key lookup hash, it appears there is yet another undocumented feature called %%lockres%%.   Here is a link on it.

  One thing of interest... I ran a query for the key hash against the table that I thought was the deadlock source but the hash format is different that what was reported in the deadlock graph:

  
  

  select %%lockres%% as LockHash, *
  from dbo.BatchTrans(nolock) 
  where %%lockres%% ='(a8016a427524)''

  --0 rows

  So I decided to look at the hash of the first record in BatchTrans table.

  select top 1 %%lockres%% as LockHash, *
  from dbo.BatchTrans(nolock) 

  The returned lockhash value was 1:35592:0.  This format is not the same format as was reported by the key lock in the deadlock graph.

  Now if I tweak the query like so I can match a record:

  Select %%lockres%% as LockHash, * from dbo.BatchTrans with (index(PK_BatchTrans))  where %%lockres%% = '(a8016a427524)'
  

  So apparently lockres works differently depending on the context.
  
  

  
  

  
  

  
  
  

  
  

  • Edited by scott_m Sunday, January 27, 2013 7:05 PM
  •  
  • Reply
  • Quote

   
• Sunday, January 27, 2013 8:00 PM

   

   
  

  0

  I was querying the xml_deadlock_report extended event for the deadlock graph to avoid potential overhead of trace flag 1222.

  They should have the same data, I believe.

  I will have to admit that I mainly get to analyse deadlocks when people post deadlock traces/reports in this forum. The system I mainly work with produces deadlocks all the time, but since we use SET DEADLOCK_PRIORITY so that only background processes bites the dust, we don't worry too much. So it is possible that my recollection of what you see in the output is inaccurate.

  Interesting stuff about %%lockres%%. I will have admit that I have not worked that deep with deadlock analysis. Often, the deadlock is possible to understand without going down on this level.

  ---

  Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
  • Reply
  • Quote