How do I acquire write locks in SQL Server?

Question

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.)

Jonathan Allen · Accepted Answer · 2014-08-17 06:56:22Z

1

Try applying UPDLOCK

BEGIN TRAN SELECT * FROM table1 WITH (UPDLOCK, ROWLOCK) WHERE col1 = 'value1' UPDATE table1 set col1 = 'value2' where col1 = 'value1' COMMIT TRAN

edited Aug 17, 2014 at 6:56

Jonathan Allen

70.6k77 gold badges276 silver badges459 bronze badges

answered Aug 17, 2014 at 5:41

ydoow

2,9964 gold badges27 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jonathan Allen Over a year ago

Close, but HOLDLOCK only takes a read-lock in this scenario. You did help me find the real answer though.

Collectives™ on Stack Overflow

How do I acquire write locks in SQL Server?

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related