Article ID: 240853
Article Last Modified on 3/14/2006
APPLIES TO
- Microsoft SQL Server 7.0 Standard Edition
This article was previously published under Q240853
BUG #: 56266 (SQLBUG_70)
SYMPTOMS
If a lock escalation occurs while running a parallel query, it is possible to encounter error message 1203 as follows:
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:
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.
Additional query words: locking DOP
Keywords: kbbug kbfix kbqfe KB240853
