FIX: Undetected Deadlock Occurs When Lock Owner Is Waiting on CXPACKET (293232)

SYMPTOMS

Parallel queries may experience an undetected deadlock when the lock owner Execution Context (EC) is waiting on a CXPACKET.

For the deadlock to occur you must have two spids and each spid must be running a parallel query.

The sysprocesses output shows multiple ECs (working on the behalf of a single spid) with some waiting on locks and others waiting on CXPACKET.

The pattern is such that no direct EC correlation from either spid results in a standard lock waiter deadlock. Each lock waiter is blocked by an owner waiting on a CXPACKET resource. You can use information gathered from a query of the syslockinfo system table to verify this pattern.

To determine if the SQL Server is encountering the issue, use these steps:

1. Perform a "SELECT *" query from the sysprocesses system table, and then perform a "SELECT *" query from the syslockinfo system table.
2. Locate a blocked EC that is waiting on a lock.
3. Search the output to see who owns the lock.
4. Determine if the owning EC is waiting on a CXPACKET.
5. Repeat steps 1 through 4 for all of the ECs of the two suspect spids.

The pattern must be all blocking lock owners that are waiting on a CXPACKET.

   File name   Platform
   --------------------
   s80263i.exe INTEL