CODEWITHSUNDEEP
sql-server
Jonathan Allen
Jonathan Allen

Reputation: 70307

How do I acquire write locks in SQL Server?

I need to run a query that selects ten records. Then, based on their values and some outside information, update said records.

Unfortunately I am running into deadlocks when I do this in a multi-threaded fashion. Both threads A and B run their selects at the same time, acquiring read locks on the ten records. So when one of them tries to do an update, the other transaction is aborted.

So what I need to be able to say is "select and write-lock these ten records".

(Yea, I know serial transactions should be avoided, but this is a special case for me.)

Upvotes: 1

Views: 336

Answers (1)

ydoow
ydoow

Reputation: 3006

Try applying UPDLOCK

BEGIN TRAN

SELECT * FROM table1
WITH (UPDLOCK, ROWLOCK)
WHERE col1 = 'value1'

UPDATE table1 
set col1 = 'value2'
where col1 = 'value1'

COMMIT TRAN

Upvotes: 1

Related Questions