FIX: Fetching from an Asynchronous Cursor May Stop SQL Server

• Microsoft SQL Server version 6.5

SYMPTOMS

When SQL Server executes a transaction that contains a cursor, the transaction may stop responding in the COMMIT TRANSACTION. Or, if there is a ROLLBACK TRANSACTION, you may receive the following error:

3307 Process %d was expected to hold logical lock on page %Id.

• There is a cursor in the transaction that goes into asynchronous cursor mode. (Asynchronous cursor mode only occurs if the sp_configure entry for cursor threshold is changed from its default of -1 to some other value.)
  
  -and-



• The computer has more than one processor.

CAUSE

This problem occurs when SQL Server has finished using the asynchronous cursor involved in the transaction. Under a certain condition where the data distribution page's statistics are wrong on the underlying table that the cursor is accessing, SQL Server closes the child thread in the asynchronous cursor before the parent thread is ready, and SQL Server then tries to close the child thread again. This causes SQL Server to stop as it tries to deallocate a non-existing child thread.

WORKAROUND

To work around this problem and prevent it from occurring, you can do either of the following:

• Update the statistics on the underlying table the cursor is accessing.
  
  -or-



• Change the cursor threshold sp_configure parameter back to its default value of 1.

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 6.5. This problem has been corrected in U.S. Service Pack 5a for Microsoft SQL Server version 6.5. For information about downloading and installing the latest SQL Server Service Pack, see http://support.microsoft.com/support/sql/.

For more information, contact your primary support provider.

Additional query words: prodsqlsp sp5hang hangs hung tran terminated

Keywords : kbbug6.50 kbfix6.50.SP5
Version : winnt:6.5
Platform : winnt
Issue type : kbbug