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:
- Process A opens a keyset cursor on the table. After the cursor is
opened, the user may perform cursor fetches.
- Process B performs a modification operation within a transaction,
which causes it to hold an exclusive lock on a page.
- 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.
- Process B releases the exclusive page lock by either committing or
rolling back the transaction.
- 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.
|