FIX: Error 1203 Using Dynamic Cursor Within Transaction

• Microsoft SQL Server version 6.5

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.

Additional query words: unhandled exception

Keywords          : kbnetwork SSrvErr_Log SSrvLock kbbug6.50 kbfix6.50.sp2 
Version           : 6.5
Platform          : WINDOWS 
Issue type        : 

Last Reviewed: April 3, 1999