FIX: Error 1203 Using Dynamic Cursor Within Transaction (158288)
The information in this article applies to:
This article was previously published under Q158288
BUG #: 16082 (6.50)
SYMPTOMS
Using Dynamic Cursor within a user-defined transaction can cause hundreds
of 1203 errors in the SQL Server errorlog and Windows NT Event Viewer. The
SQL Server shuts down afterwards, with the following error message:
Error: 1203, Severity: 20, State: 2
Caller of lock manager is incorrectly trying to unlock an unlocked object.
spid=%d locktype=%d dbid=%d lockid=%Id
CAUSE
Dynamic Cursor scans through all data pages in the table, starting from the
first page. When it accumulates enough SH_PAGE locks on the table, lock
promotion occurs, which frees all SH_PAGE locks, and it acquires a table
lock instead. Cursor fetch has no knowledge of the lock promotion, so it
finishes its scan and tries to free SH_PAGE lock. However, SH_PAGE lock is
no longer there, which causes the problem.
WORKAROUND
To work around this problem, do one of the following:
- Change the cursor definition to use either SCROLL or INSENSITIVE.
-or-
- Increase "LE Threshold Maximum" setting in sp_configure.
-or-
- Do not use transaction around Dynamic Cursor.
STATUS
Microsoft has confirmed this to be a problem in Microsoft SQL Server
version 6.5. This problem has been corrected in U.S. Service Pack 2 for
Microsoft SQL Server version 6.5. For more information, contact your
primary support provider.
MORE INFORMATION
Trace flag 1200 can be used to monitor the lock promotion in this case.
| Modification Type: | Major | Last Reviewed: | 10/3/2003 |
|---|
| Keywords: | kbBug kbfix kbnetwork KB158288 |
|---|
|