Today's Annoyingly-Unwieldy Term: "Intra-Query Parallel Thread Deadlocks"

I’ve received a couple of questions in email and in comments about deadlocks involving mysterious-sounding non-lock resources like “exchangeEvent” and “threadpool”.  There are a couple of examples in the comments for post http://blogs.msdn.com/bartd/archive/2006/09/25/deadlock-troubleshooting-part-3.aspx, and here’s a forum post on the topic: http://forums.microsoft.com/Forums/ShowPost.aspx?PostID=3913233&SiteID=1.     Here’s one example (note that I’ve omitted the “inputbuf” and “executionStack” nodes…

9

Deadlock Troubleshooting, Part 3

Here’s an example of the classic scenario that is usually used to introduce the concept of a deadlock in a database:     Process A Process B     1. Begin Transaction 1. Begin Transaction     2. Update Part table 2. Update Supplier table   à 3. Update Supplier table 3. Update Part table…

45

Deadlock Troubleshooting, Part 2

In this post I’ll look at an actual deadlock, then troubleshoot it using the steps I described in Deadlock Troubleshooting, Part 1 so you can see them in action.  This is a simplified version of a deadlock scenario that an internal customer here at Microsoft called us for help with.  To set up the scenario,…

5

Deadlock Troubleshooting, Part 1

A deadlock is a circular blocking chain, where two or more threads are each blocked by the other so that no one can proceed.  When the deadlock monitor thread in SQL Server detects a circular blocking chain, it selects one of the participants as a victim, cancels that spid’s current batch, and rolls backs his…

81