SQL Server Quickie #33 – Deadlocks

2 thoughts on “SQL Server Quickie #33 – Deadlocks”

1. 

   Paul

   03/12/2016 at 3:36 PM

   Hello KLaus,

   Thanks for this informative video.

   In terms of deadlocks I’ve got a couple of questions that I haven’t found definitive answers for:

   We seem to have recurring intra-query parallelism deadlocks. To what extent are these deadlocks critical, how does SQL server remedy them and do they also trigger a Msg 1205 and a deadlock graph in profiler? They seem not to do that.

   Another point: If I query historical deadlock info from the DB, I get a resultset of about 120 entries. However, if I query the number of deadlocks that have occured since last server-restart, I get back ‘7’.
   (I use the standard queries of the SQL server community for this)

   It seems that these seven deadlocks are of the type you’ve been talking about and the intra-query deadlocks don’t count.

   I would appreciate your feedback.

   Kind regards,

   Paul

   Reply
   1. 

      Klaus Aschenbrenner

      03/13/2016 at 7:22 PM

      Hello Paul,

      Thanks for your comment.
      Intra-parallelism deadlocks are bugs in SQL Server, which are not fixed by Microsoft 😉
      In that case you have to make sure that your query runs single-threaded.
      When you get such a deadlock, you still have the deadlock graph available, and yes the transaction is rolled back with error number 1205.

      Thanks,

      -Klaus

      Reply