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

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.

To resolve this problem, obtain the latest service pack for SQL Server 2000. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

Hotfix

The English version of this fix should have the following file attributes or later: NOTE: Due to file dependencies, the most recent hotfix or feature that contains the preceding files may also contain additional files.

To resolve the deadlock situation use either:

• A query timeout.
  
  -or-
  
  
• A Transact-SQL KILL command.

Microsoft has confirmed that this is a problem in SQL Server 2000. This problem was first corrected in SQL Server 2000 Service Pack 1.