FIX: SH_PAGE Lock Not Released After Fetch Operation on Keyset Cursor

ID: Q245552

The information in this article applies to:

Microsoft SQL Server version 6.5

BUG #: 18895 (SQLBUG_65)

SYMPTOMS

When fetching a row from a keyset-driven server side cursor, this operation may leave a SH_PAGE lock on a data page of the base table of the cursor, which does not get released until this client session terminates.

CAUSE

A keyset-driven cursor maintains a worktable containing the row ID (RID) of all qualifying rows. Whenever a row from the keyset is fetched, a SH_PAGE lock is being acquired on the data page identified by the pertaining RID and the page is read from disk. If the row has moved after populating the keyset upon cursor open (for example, by a page split), the row will not be found on this page and therefore an index-based scan must be performed on the table to find the page holding the row. Again, a SH_PAGE lock has to be acquired on this new data page. After the fetch operation has returned the row to the client, the SH_PAGE lock on the new page is being released, but not the one on the page scanned in the first step.

For additional information on how a keyset-driven cursor works, click the article number below to view the article in the Microsoft Knowledge Base:

Q168678 INF: Understanding Worktables Used by Server Side Cursors

RESOLUTION

A supported fix that corrects this problem is now available from Microsoft, but it has not been fully regression tested and should be applied only to systems experiencing this specific problem. If you are not severely affected by this specific problem, Microsoft recommends that you wait for the next SQL Server service pack that contains this fix.

To resolve this problem immediately, contact Microsoft Product Support Services to obtain the fix. For a complete list of Microsoft Product Support Services phone numbers and information on support costs, please go to the following address on the World Wide Web:

http://www.microsoft.com/support/supportnet/overview/overview.asp

The English version of this fix should have the following file attributes or later:


   Version     File name     Platform
   -----------------------------------------

   6.50.463   Sqlservr.exe   Intel and Alpha

NOTE: Due to file dependencies, the most recent hotfix or feature that contains the above files may also contain additional files.

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 6.5.

Additional query words: lock locks locked locking block blocked

Keywords : SSrvLock kbbug6.50 kbSQLServ650bug
Version : winnt:6.5
Platform : winnt
Issue type : kbbug