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

SYMPTOMS

If a lock escalation occurs while running a parallel query, it is possible to encounter error message 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.

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 7.0. This problem has been corrected in U.S. Service Pack 3 for Microsoft SQL Server 7.0. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

274799 INF: How to Obtain Service Pack 3 for Microsoft SQL Server 7.0 and Microsoft Data Engine (MSDE) 1.0

For more information, contact your primary support provider.

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.