FIX: Optimizer Hint UPDLOCK Results in Two Pages Being Locked

• Microsoft SQL Server, version 6.0

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.