Microsoft KB Archive/175887

= FIX: Msg 1203 and SQL Server May Be Shut Down with Large Cursor Row =

Article ID: 175887

Article Last Modified on 10/3/2003

-

APPLIES TO


 * Microsoft SQL Server 6.5 Standard Edition

-



This article was previously published under Q175887



BUG #: 17175 (NT: 6.5)



SYMPTOMS
SQL Server may recursively print Msg 1203 and be shut down after a stack overflow exception error. This problem can occur if all of the following conditions are true:


 * The "LE threshold maximum" configuration option is set so that an individual cursor rowset fetch may cause a lock escalation to occur.
 * A keyset cursor fetch becomes blocked by another user and subsequently escalates to a table lock after the blocking is resolved.
 * The table has a unique index or primary key that can be used to build the keyset table for the cursor. Note that if this condition is false, the cursor reverts to an INSENSITIVE cursor, and the problem does not occur.

The following is the text of message 1203:

  Caller of lock manager is incorrectly trying to unlock an unlocked object. spid=10 locktype=6 dbid=6 lockid=645.



CAUSE
The cursor code improperly handles the lock escalation and attempts to free page locks at the end of the fetch operation. This causes a recursive error 1203 leading to a stack overflow, at which time SQL Server stops.



WORKAROUND
To work around the problem, do either of the following:


 * Use a smaller rowset size for your cursor.

-or-
 * Increase the "LE threshold maximum" configuration option to a higher value so that a single cursor fetch rowset will not need to traverse enough pages to escalate to a table lock. For example, if the maximum row size within your tables allows for 1 row per page, and you have the cursor rowset configured to pull back 500 pages at a time, then the "LE threshold maximum" should be larger than 500. If you use the default escalation value of 200 and only have 1 row per page, use a cursor rowset size smaller than 200.



STATUS
Microsoft has confirmed this to be a problem in SQL Server version 6.5. This problem was corrected in the latest Microsoft SQL Server 6.5 U.S. Service Pack. For information on obtaining the service pack, query on the following word in the Microsoft Knowledge Base (without the spaces):

  S E R V P A C K

The following is the sequence of events that causes this problem to occur:


 * 1) Process A opens a keyset cursor on the table. After the cursor is opened, the user may perform cursor fetches.
 * 2) Process B performs a modification operation within a transaction, which causes it to hold an exclusive lock on a page.
 * 3) Process A performs a cursor fetch using a large rowset size. When it reaches the exclusively locked page, the process blocks, waiting on the lock to be released.
 * 4) Process B releases the exclusive page lock by either committing or rolling back the transaction.
 * 5) Process A continues scanning to retrieve the remainder of the cursor rowset and escalates to a table lock when the operation has scanned more pages than the configured "LE threshold maximum" value. When the full rowset has been filled, the process attempts to free previously obtained locks and encounters error 1203.

Because normal ANSI cursors, as exposed through Transact-SQL, only allow a cursor rowset size of 1, this problem will never occur if you use ANSI cursors. The client application may set a larger rowset size if it uses cursors from DB-Library, ODBC, or OLE-DB-based interfaces.

Additional query words: shutdown terminate terminated

Keywords: kberrmsg kbbug kbfix kbsqlserv650bug KB175887

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.