SQL server: Can concurrent threads update same row?
sql, sql-server
Solution
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.
Problem
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?