Q142450 - FIX: Optimizer Hint UPDLOCK Results in Two Pages Being Locked

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 <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 : kbprogramming
Issue type : kbbug
Technology : kbSQLServSearch kbAudDeveloper kbSQLServ600