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.