This article was previously published under Q293232
This article has been archived. It is offered "as is" and will no longer be updated.
BUG #: 352575, 352695 (SHILOH)
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:
Perform a "SELECT *" query from the sysprocesses system table, and then perform a "SELECT *" query from the syslockinfo system table.
Locate a blocked EC that is waiting on a lock.
Search the output to see who owns the lock.
Determine if the owning EC is waiting on a CXPACKET.
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 theMicrosoft Knowledge Base:
290211 INF: How to Obtain the Latest SQL Server 2000 Service Pack
The English version of this fix should have the following file attributes or later:
File name Platform -------------------- s80263i.exe INTEL
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.
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.