FIX: Lock Escalation With Parallel Query May Cause 1203 Error And Server Shutdown

ID: Q240853

The information in this article applies to:

Microsoft SQL Server version 7.0

BUG #: 56266 (SQLBUG_70)

SYMPTOMS

If a lock escalation occurs while running a parallel query, it is possible to encounter error 1203 as follows:

spid7 Process 7 unlocking unowned resource: KEY: 13:117575457:2 (35010560ebcd)
spid7 Process 7 unlocking unowned resource: KEY: 13:117575457:2 (35010560ebcd)
spid7 Error: 1203, Severity: 20, State: 1
spid7 Process ID 7 attempting to unlock unowned resource KEY: 13:117575457:2 (35010560ebcd).
spid7 Error: 1203, Severity: 20, State: 1
spid7 Process ID 7 attempting to unlock unowned resource KEY: 13:117575457:2 (35010560ebcd).

The error message included in the error log probably mentions the same lock resource in several of the error messages.

Once the error is printed, an assertion message similar to the following is also printed:

1999-08-09 13:15:26.79 kernel SQL Server Assertion: File: <proc.c>, line=1866 Failed Assertion = 'm_activeSdesList.Head () == NULL'.

After a dump of the stack for all threads, the server initiates a shutdown of the SQL Server process.

CAUSE

A race condition occurs where multiple threads involved in the parallel query may try to unlock the same resource. After the first thread successfully frees the lock, other threads encounter error 1203, but are unprepared to handle this. A default error handler within the server causes the process to exit.

RESOLUTION

A supported fix that corrects this problem is now available from Microsoft, but it has not been fully regression tested and should be applied only to systems experiencing this specific problem. If you are not severely affected by this specific problem, Microsoft recommends that you wait for the next SQL Server service pack that contains this fix.

To resolve this problem immediately, contact Microsoft Product Support Services to obtain the fix. For a complete list of Microsoft Product Support Services phone numbers and information on support costs, please go to the following address on the World Wide Web:

http://www.microsoft.com/support/supportnet/overview/overview.asp

The English version of this fix should have the following file attributes or later:


   Version      File name   Platform
   ---------------------------------

   7.00.725     s70725i.exe x86
   7.00.725     s70725a.exe Alpha

NOTE: Due to file dependencies, the most recent hotfix or feature that contains the above files may also contain additional files.

NOTE: If this product was already installed on your computer when you purchased it from the Original Equipment Manufacturer (OEM) and you need this fix, please call the Pay Per Incident number listed on the above Web site. If you contact Microsoft to obtain this fix, and if it is determined that you only require the fix you requested, no fee will be charged. However, if you request additional technical support, and if your no-charge technical support period has expired, or if you are not eligible for standard no-charge technical support, you may be charged a non-refundable fee.

For more information about eligibility for no-charge technical support, see the following article in the Microsoft Knowledge Base:

Q154871 Determining If You Are Eligible for No-Charge Technical Support

WORKAROUND

You can prevent a parallel plan by setting the 'max degree of parallelism' option in sp_configure to a value of one. Even if there are available CPU resources, the query is processed serially.

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 7.0.

MORE INFORMATION

Parallel queries are only considered when there are one or more idle processors available to SQL Server. On a heavily used system, the optimizer should not choose a parallel plan for any queries.

Setting the "max degree of parallelism" to force a serial plan may cause some performance degradation, but only during those periods of time when the number of simultaneous connections running queries is less than the number of available processors, and those queries would otherwise be eligible for a parallel plan.

SQL Profiler can be used to determine what queries are using a parallel plan. The "Event Sub Class" column for the DELETE, INSERT, SELECT and UPDATE operators shows the degree of parallelism used. To monitor lock escalation, add the Lock Escalation event to your trace. Note that this event is only listed when you go to the Tools menu, choose Options, and then select "All event classes."

Additional query words: locking DOP

Keywords : SSrvErr_Log SSrvLock kbSQLServ700bug
Version : winnt:7.0
Platform : winnt
Issue type : kbbug