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

• Microsoft SQL Server, version 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.

   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.