Troubleshooting deadlocks - they dont happen for very long

Category: sql server dbengine


shiftbit on Mon, 08 Aug 2016 19:21:42

There is a stored procedure that is being used by process running on another server.  Occasionally there are SQL errors generated and logged on the system where the process is running.  The error message is related to deadlocks

something like this: "...was deadlocked on lock communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction"

I need a way to see what was being blocked and what was blocking it.  The procedure isnt that complicated, a select then an update.  The blocking doesnt seem to occur for very long but just long enough to cause errors.  Nothing shows up with sp_who2 as being blocked.

I tried running the blocked process report with sql profiler, but I didnt capture anything (yes I ran sp_configure), but I didnt see anything come across and I used 2 as the "frequency" parameter.  See this example.  Could the deadlocked be getting released too soon?


Erland Sommarskog on Mon, 08 Aug 2016 21:32:33

You would not see a deadlock in the blocked process report, because that report concerns process that are blocked and remains blocked.

The same background process that produces the blocked process report, also checks for deadlocks. A deadlock is a special blocking situation: two process are blocking each other so that none of them can continue. Since that is quite a useless situation, SQL Server detects this situation and injects an error in one of them. Since deadlocks are resolved in at most five seconds, you rarely see them by random monitoring.

There are multiple ways to get information about deadlocks. You can enable trace flags 1222 and 3604 at startup, which causes deadlock informtion to be printed to the SQL Server errorlog.

You can also get the most recent deadlocks from the SQL Server Health Session. Here is a query for this: (Requires SQL 2008 or later).

   xed.value('@timestamp', 'datetime2(3)') as CreationDate,
   xed.query('.') AS XEvent
   SELECT CAST([target_data] AS XML) AS TargetData
   FROM sys.dm_xe_session_targets AS st
      INNER JOIN sys.dm_xe_sessions AS s
         ON s.address = st.event_session_address
      WHERE = N'system_health'
         AND st.target_name = N'ring_buffer'
) AS Data
CROSS APPLY TargetData.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData (xed)
ORDER BY CreationDate DESC

shiftbit on Mon, 08 Aug 2016 23:14:20

Erland, thank you!  I ran that and see a node for "victim list" but what about the process or code that caused or initiated the deadlock?

fyi - im running this against an instance that is 2008 SP3 (not R2)

Erland Sommarskog on Tue, 09 Aug 2016 07:15:26

If you enable the trace flag that I mentioned, the output in the errorlog
will include the very statements.

This output only has the call stack. There is the procedure name (if any)
and the line number. There is also the sqlhandle and offsets, which you can
use in this way:

SELECT substring(text, stmtstart/2, CASE WHEN stmtstart > 0
                                         THEN (stmtend - stmtart + 2) / 2
                                         ELSE len(text)
FROM   sys.dm_exec_sql_text(sqlhandle)

Note that this query will not return anything if the plan is no longer in
the cache.

Obviously, this can be integrated in the query I posted, but I don't have
anything canned for this.

shiftbit on Tue, 09 Aug 2016 12:24:10

Thanks again Erland.

fyi - I have a subscription to Pluralsight and found a what looks like a good series on deadlock analysis.  Ill be going through that shortly.  I especially want to get familiar with deadlock graphs and reading them.