Let's assume I have a table like this on a SQL Server 2014:
CREATE TABLE Accounts (
Id int IDENTITY(1,1) NOT NULL,
OwnerId int NOT NULL,
Balance bigint NOT NULL
CONSTRAINT PK_Account PRIMARY KEY CLUSTERED (Id ASC),
CONSTRAINT UQ_OwnerId UNIQUE NONCLUSTERED (OwnerId)
)
Let's assume that there is a row with Id=1
, OwnerId=1
and Balance=100
.
I need to select the Id for a specific Account (only the Id) but at the same time would like to lock the row against updates.
In the first session I execute the following statement (in a transaction):
SELECT Id FROM Accounts WITH (UPDLOCK, ROWLOCK) WHERE OwnerId = 1;
In the second session I execute the following statement (in another transaction):
UPDATE Accounts SET Balance = 500 WHERE Id = 1;
Both statements complete without being blocked, because the first statement locks on the unique index. What can I do to force the second statement to wait?
Solutions I've already come up with, but don't like:
-
Select the whole row (
SELECT *
) instead of only the id on the first query → This results in too much overhead in my application. -
Add an
INDEX=PK_Account
hint to the first query to force locking on the PK → I lose performance because this results in a clustered index scan.
Is there any other way to retrieve only the Id (while using the UQ_OwnerId) and prevent any update to the row?
Best Answer
Two seeks, and lock on PK,