FIX: Optimizer Hint UPDLOCK Results in Two Pages Being Locked

ID: Q142450

The information in this article applies to:
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 <column_list> from <table_name> where <key_field> = <value>
...


Replace this with: 

begin transaction
update <table_name> set <col1>=<col1> where <key_field> = <value> --
dummy update
select <column_list> from <table_name> where <key_field> = <value> --
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          : kbprg SSrvLock kbbug6.00 kbfix6.00.sp3 
Version           : 6.0
Platform          : WINDOWS 
Issue type        :

Last Reviewed: March 24, 1999