CODEWITHSUNDEEP
sqlsql-server
Henley Wing Chiu
Henley Wing Chiu

Reputation: 22515

SQL server: Can concurrent threads update same row?

I have a stored proc that has this UPDATE query:

UPDATE TOP(1) Batch_tbl
SET locked = 1
OUTPUT inserted.batchId INTO #batchId
FROM Batch_tbl 
WHERE locked = 0;

It updates the first row where locked = 0, and sets locked = 1.

If I have multiple threads, could there be a chance 2+ threads update the same row? This is assuming locked is never set back to 0 once it's 1.

If so, how can I make it such that 2+ threads always update distinct rows?

Upvotes: 3

Views: 6643

Answers (1)

marc_s
marc_s

Reputation: 754488

No. When SQL Server wants to update a row, an UPDATE lock is acquired. This is compatible with other locks, like a shared lock (to read), but it's NOT compatible with another update lock.

So if two concurrent users attempt to update the same row, one of them will "win" and get the UPDATE Lock, while the other user / transaction will have to wait until the first update is done.

Upvotes: 16

Related Questions