How to View Deadlock Transactions In SQL Server?

Question

In our production server, the deadlock happening frequently. Is anyone can help on how to view those transactions from which the deadlock occur?

See: http://stackoverflow.com/questions/720508/help-with-deadlock-in-sql-server-2008 — S.K, Jul 23 '15 at 06:52

score 4 · Accepted Answer · answered Jul 23 '15 at 06:54

Use this query

SELECT db.name                  DBName,
       tl.request_session_id,
       wt.blocking_session_id,
       Object_name(p.OBJECT_ID) BlockedObjectName,
       tl.resource_type,
       h1.TEXT                  AS RequestingText,
       h2.TEXT                  AS BlockingTest,
       tl.request_mode
FROM   sys.dm_tran_locks AS tl
       INNER JOIN sys.databases db
               ON db.database_id = tl.resource_database_id
       INNER JOIN sys.dm_os_waiting_tasks AS wt
               ON tl.lock_owner_address = wt.resource_address
       INNER JOIN sys.partitions AS p
               ON p.hobt_id = tl.resource_associated_entity_id
       INNER JOIN sys.dm_exec_connections ec1
               ON ec1.session_id = tl.request_session_id
       INNER JOIN sys.dm_exec_connections ec2
               ON ec2.session_id = wt.blocking_session_id
       CROSS APPLY sys.Dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
       CROSS APPLY sys.Dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2

Source : http://blog.sqlauthority.com/2010/10/06/sql-server-quickest-way-to-identify-blocking-query-and-resolution-dirty-solution/

Is there any manual solution to prevent deadlock transactions? — , Jul 23 '15 at 07:06
I'm afraid I have to disagree with this answer (and thus the downvote - sorry!). The query that you have deals with blocking, whereas the OP asked about deadlocks. You can't query about deadlocks out of a DMV because, by definition, one of the processes gets killed and is thus no longer available in the system. — Ben Thul, Jul 23 '15 at 16:53

score 1 · Answer 2 · edited Aug 14 '20 at 08:09

I get using these three methods:

Get active deadlocks:

SELECT
    SESSION_ID
    ,BLOCKING_SESSION_ID
FROM SYS.DM_EXEC_REQUESTS
WHERE BLOCKING_SESSION_ID != 0

Get text of the query (And check if it is really important):

exec sp_whoisactive |session_id|

Kill the deadlock (Use wisely):

kill |session_id|

Also, the previous answer didn't work fine (I don't know why), I've executed this:

Query 1:

begin tran
create table ##a (i int)
insert ##a values (1)

Query 2:

delete ##a

Query 3: The answer

How to View Deadlock Transactions In SQL Server?

2 Answers2