2

We've been having some blocking problems on SQL Server 2014.

First, let me test my knowledge:

A deadlock, as I understand it, is when process A blocks process B which then also blocks process A, creating a cycle in the graph of blocked processes. It was my understanding that, when this occurs, SQL Server can recognize it and kill one of the processes, the deadlock victim. I assume SQL Server can also recognize a cycle of three or more processes, too, but maybe not?

What's not a deadlock is when a process locks a resource, then some other processes need it and waits for it to become unlocked. If the first process never completes, then the other processes will just wait (until they timeout). SQL Server will not kill any processes in this case because blocking is normal.

Now, are there any kind of deadlocks that SQL Server either can't detect or will choose not to kill?

We have some users running heavy queries, a job that runs every 5 minutes to update the tables, and a second job that runs a small query to check the health of the first job. (It checks the most recent date and sends an email if the data is stale.)

My DBA tells me that some combination of the above three things is creating a deadlock, but that SQL Server won't kill it. Not a normal block, but an actual deadlock. Is that possible?

2
  • What does your DBA claim the deadlock cycle is? Commented Jun 21, 2017 at 21:35
  • It was sort of a hypothetical discussion of previous issues (that will certainly happen again), and what to do about it. He didn't make any specific claims.
    – Glazed
    Commented Jun 22, 2017 at 15:09

1 Answer 1

2

As an anecdote that may or may not shed some light on your particular issue, we've had a similar problem to this where an external application would execute a stored procedure and improperly roll back when an error was encountered.

This procedure was vendor provided and unable to be modified. Adding to this, XACT_ABORT was set to the default value of OFF and could not be modified.

The application would reach a CommandTimeout event (due to SqlException being thrown and the application not handling this appropriately), but the session would remain open.

Since XACT_ABORT is OFF and explicit transaction statements were used in the SP without appropriate error handling, the session would maintain any locks held by the aborted procedure. The session itself would be in a "SLEEPING" status with "AWAITING COMMAND".

This was a somewhat insidious issue for us, because the failed session would not be immediately obvious. However, contention would rapidly grow in the way that a vehicle stopping in the middle of a busy intersection will cause a multi-car incident. Unrelated applications attempting to access the locked resources held by the now-orphaned session would simply WAIT, entering a SUSPENDED status while potentially holding locks of their own, creating a scenario of branching contention and lock escalation.

While there are certainly other methods, this was a very busy application database which limited the usefulness of more direct methods which would have been optimal in a more controlled or quieter environment. In order to quickly and easily identify the ROOT session responsible, we cobbled together the following query, which returns the contention "tree" and allowed us to KILL the offending session (once appropriately identified).

USE master
GO;

WITH CTE_Blks
AS (
 SELECT Blocked [SPID]
 FROM sysprocesses
 WHERE Blocked <> 0
 )
 , CTE_Tree
AS (
 SELECT DB_NAME(sp.dbid) AS [Database]
  , spid AS [Session]
  , Blocked [BlockedBy]
  , sp.STATUS AS [Status]
  , cmd [Command]
  , loginame AS [Login]
  , hostname AS [Host]
  , program_name AS [Program]
  , ec.client_Net_Address [IPv4]
  , ec.Client_TCP_Port AS [Port]
  , Last_Batch
  , ST.Query [Transaction]
  , ST2.Query AS [MostRecentStmnt]
 FROM sysprocesses SP
 LEFT JOIN sys.dm_exec_connections ec ON ec.session_id = sp.spid
 CROSS APPLY (
  SELECT TEXT AS [processing-instruction(x)]
  FROM sys.dm_exec_sql_text(SP.sql_handle)
  FOR XML PATH('')
   , TYPE
  ) AS ST(Query)
 CROSS APPLY (
  SELECT TEXT AS [processing-instruction(x)]
  FROM sys.dm_exec_sql_text(ec.most_recent_sql_handle)
  FOR XML PATH('')
   , TYPE
  ) AS ST2(Query)
 WHERE blocked <> 0
  OR spid IN (
   SELECT SPID
   FROM CTE_Blks
   )
 )
SELECT CASE 
  WHEN BlockedBy = 0
   THEN 'ROOT'
  WHEN BlockedBy <> 0
   AND EXISTS (
    SELECT 1
    FROM CTE_Tree s1
    WHERE s1.BlockedBy = t1.Session
    )
   THEN 'BRANCH'
  ELSE 'LEAF'
  END [NodeType]
 , t1.*
FROM CTE_Tree t1
ORDER BY [NodeType]

To add some explanation to this:

  1. ROOT is obviously the orphaned session and should be KILLed.
  2. A LEAF is a blocked session which is not, itself, blocking other transactions.
  3. A BRANCH is a blocked session which, due to its own locks, is blocking another session--either another BRANCH in the chain, or a LEAF (terminal).

The use of returning our sql_handle text as an XML processing-instruction may seem odd, but was deliberate to circumvent truncation, preserve formatting, and bypass the need to explicitly escape characters.

2
  • Thanks, that looks useful. So you just had a tree of blocks and no deadlocks, right? That's what I think is happening in our case, and not a deadlock.
    – Glazed
    Commented Jun 22, 2017 at 15:10
  • @Glazed, that's correct. In effect, an open transaction was holding resources needed by 1+ other transactions, which may or may not themselves be holding resources required by other transactions. This was in a fairly busy application database with many user connections. This particular SP was such a problem because it prevented reads as well as manipulations (exclusive lock), so depending on a particular query plan and the state of contention, even unrelated rows in the same page/table would require a wait.
    – coyote
    Commented Jun 23, 2017 at 19:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.