Microsoft KB Archive/142450

{|
 * width="100%"|

FIX: Optimizer Hint UPDLOCK Results in Two Pages Being Locked

 * }

Q142450

-

The information in this article applies to:


 * Microsoft SQL Server version 6.0

-

BUG# NT: 12291 (6.00)

SYMPTOMS
Selecting a row inside a user-defined transaction using the optimizer hint UPDLOCK results in two pages being locked. The update statement locks only one page.

WORKAROUND
You can use a dummy update instead of the optimizer hint UPDLOCK. This would ensure that the page is locked and guarantee read consistency.

Suppose the select statement is:

begin transaction select  from  where  = ...

Replace this with:

begin transaction update  set = where  = -- dummy update select  from  where  = -- no UPDLOCK ...

STATUS
Microsoft has confirmed this to be a problem in Microsoft SQL Server Version 6.0. This problem has been corrected in U.S. Service Pack 3 for Microsoft SQL Server version 6.0. For more information, contact your primary support provider.

MORE INFORMATION
When you perform a select on a table with the row size padded out to one row per page, SQL Server obtains an update_page lock on two different pages when the optimizer hint UPDLOCK is used - one on the page actually being updated and the other on the next page in the page chain.

This is a problem because this scenario is commonly used to simulate row level locking. The second lock, on the next page, is redundant and reduces concurrency.

Keywords : kbprogramming

Issue type : kbbug

Technology : kbSQLServSearch kbAudDeveloper kbSQLServ600