Sql-server – How to place an UpdateLock on a row with an index

lockingsql serversql server 2014

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:

  1. Select the whole row (SELECT *) instead of only the id on the first query → This results in too much overhead in my application.

  2. 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,

select id 
from Accounts WITH (UPDLOCK) 
where id = (select a.id from Accounts a where a.OwnerId=1)