Discovering long running queries/dead lock situation in SQL Server

Submitted by Jochus on Mon, 26/11/2012 - 22:54 | Posted in: Java

I recently came across this situation on a M$ SQL Server 2008 database:

I was doing an update operation on a certain table. While doing this update, a clustered index was being read. To be able to read from that index, a lock was obtained on a non-clustered index. The update operation went perfectly.
Now, in the same transaction, I was doing a select query which needed a lock on a non-clustered index. Unfortunately for me, this lock was already taken by the update statement and it would only be released when the transaction completed.

More details on this issue can be found on this blogpost.

At first, I knew there was a deadlock, but I didn't know on which resources/queries. This query helped me a lot while debugging:

SELECT DTL.resource_type,  
   CASE   
       WHEN DTL.resource_type IN ('DATABASE', 'FILE', 'METADATA') THEN DTL.resource_type  
       WHEN DTL.resource_type = 'OBJECT' THEN OBJECT_NAME(DTL.resource_associated_entity_id, SP.[dbid])  
       WHEN DTL.resource_type IN ('KEY', 'PAGE', 'RID') THEN   
           (  
           SELECT OBJECT_NAME([object_id])  
           FROM sys.partitions  
           WHERE sys.partitions.hobt_id =   
             DTL.resource_associated_entity_id  
           )  
       ELSE 'Unidentified'  
   END AS requested_object_name, DTL.request_mode, DTL.request_status,  
   DEST.TEXT, SP.spid, SP.blocked, SP.status, SP.loginame 
FROM sys.dm_tran_locks DTL  
   INNER JOIN sys.sysprocesses SP  
       ON DTL.request_session_id = SP.spid   
   --INNER JOIN sys.[dm_exec_requests] AS SDER ON SP.[spid] = [SDER].[session_id] 
   CROSS APPLY sys.dm_exec_sql_text(SP.sql_handle) AS DEST  
WHERE SP.dbid = DB_ID()  
   AND DTL.[resource_type] <> 'DATABASE' 
ORDER BY DTL.[request_session_id];

It lists the queries which are being blocked and it also tells you on which queries they are blocked (see columns spid and blocked).

Eventually, I solved the issue by handling the transaction at bean level (my bean was container managed and update / select queries where in the same bean). Before the update, I manually started a transaction. After the update was successful, I committed the transaction. Finally, I got a resultdata (select query) - without any transaction, as I didn't need a transaction for this query.

Add new comment

The content of this field is kept private and will not be shown publicly.

Full HTML

  • Lines and paragraphs break automatically.
  • You can caption images (data-caption="Text"), but also videos, blockquotes, and so on.
  • Web page addresses and email addresses turn into links automatically.
  • You can enable syntax highlighting of source code with the following tags: <code>, <blockcode>, <bash>, <cpp>, <css>, <html5>, <java>, <javascript>, <php>, <sql>, <xml>. The supported tag styles are: <foo>, [foo].
CAPTCHA
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.