FIX: Fetching from an Asynchronous Cursor May Stop SQL Server (193454)

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.

If this process is killed or if the same transaction is executed again, SQL Server stops responding and must be restarted.

NOTE: This problem only occurs if both of the following conditions are true:

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 6.5. This problem has been corrected in Service Pack 5a for Microsoft SQL Server 6.5.For more information, click the following article number to view the article in the Microsoft Knowledge Base:

197177 INF: How to Obtain SQL Server 6.5 Service Pack 5a

For more information, contact your primary support provider.