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.
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 the
Microsoft Knowledge Base:
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
: 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.