Microsoft KB Archive/293232

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

Article ID: 293232

Article Last Modified on 10/9/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q293232



BUG #: 352575, 352695 (SHILOH)



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 &quot;SELECT *&quot; query from the sysprocesses system table, and then perform a &quot;SELECT *&quot; 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.



RESOLUTION
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:

290211 INF: How to Obtain the Latest SQL Server 2000 Service Pack

Hotfix
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.



WORKAROUND
To resolve the deadlock situation use either:
 * A query timeout.

-or-


 * A Transact-SQL KILL command.



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

Keywords: kbbug kbfix kbqfe kbsqlserv2000sp1fix KB293232

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.