DetailPage-MSS-KB

Microsoft small business knowledge base

Article ID: 293232 - Last Review: October 9, 2003 - Revision: 3.1

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 "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  (http://support.microsoft.com/kb/290211/EN-US/ ) 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.

APPLIES TO
  • Microsoft SQL Server 2000 Standard Edition
Keywords: 
kbbug kbfix kbqfe kbsqlserv2000sp1fix KB293232
Share
Additional support options
Ask The Microsoft Small Business Support Community
Contact Microsoft Small Business Support
Find Microsoft Small Business Support Certified Partner
Find a Microsoft Store For In-Person Small Business Support