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

Article translations Article translations
Article ID: 293232 - View products that this article applies to.
This article was previously published under Q293232
BUG #: 352575, 352695 (SHILOH)
Expand all | Collapse all

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.

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.

Properties

Article ID: 293232 - Last Review: October 9, 2003 - Revision: 3.1
APPLIES TO
  • Microsoft SQL Server 2000 Standard Edition
Keywords: 
kbbug kbfix kbqfe kbsqlserv2000sp1fix KB293232

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com