FIX: Msg 1203 & SQL Server May Be Shut Down w/ Large Cursor Row

Last reviewed: December 19, 1997
Article ID: Q175887
The information in this article applies to:
  • Microsoft SQL Server, version 6.5
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

MORE INFORMATION

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 : kbbug6.50 SSrvAdmin SSrvLock SSrvProg kbfix6.50.sp4 kberrmsg
Version : Windows:6.5
Platform : WINDOWS
Issue type : kbbug
Solution Type : kbfix


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: December 19, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.