SQL Server Update Query Race Condition

concurrencysql server

Consider this simple query

UPDATE [SomeTable] SET Value = Value + 1 WHERE Id = 1

For almost any programming language this has a potential risk of race condition if it is executed by more than one thread at the same time.

The question is: Does it apply to Sql?

I believe UPDATE will grab a write lock to that row that prevents other write operations to it

So if two threads try to execute this query the order should be like

T1: write lock
T2: attempt to grab write lock (blocked)
T1: read value
T1: write value = value +1
T1: release write lock
T2: write lock
T2: read value
T2: write value = value + 1
T2: release write lock

Result: Value = Value + 2

Or could it look like this

T1: read value
T1: write lock
T2: read value
T2: attempt to grab write lock (blocked)
T1: write value = value +1
T1: release write lock
T2: write lock
T2: write value = value + 1
T2: release write lock

Result: Value = Value + 1

Mostly interested in Sql server behavior but would be nice to know if the behavior is consistent across all ANSI compliant DBMS

Best Answer

You will typically get result #1 (Value=2).

Update locks make the queries run sequentially

The UPDATE query will place an update lock from the start, because it knows that it will update the row. This prevents another query from placing an update lock of its own, meaning that they will run sequentially.

There are different so-called isolation levels that may be implemented slightly differently in different database platforms, but to the best of my knowledge, the above reflects how it works in SQL Server.

The second scenario

Your second scenario (read-only lock, which escalates to an exclusive read-write lock) can only happen if you separate the UPDATE into two statements, and provided you have a less restrictive isolation level (i.e. not for instance SERIALIZABLE).

DECLARE @val int;

BEGIN TRANSACTION;

    SELECT @val=Value FROM [Some table] WHERE Id=1;
    UPDATE [Some table] SET Value=@val+1 WHERE Id=1;

COMMIT TRANSACTION;

Again, if you have a restrictive isolation like SERIALIZABLE, the initial SELECT statement will place an exclusive lock on the row immediately, preventing other processes from viewing or changing it. If you use, for instance, READ COMMITTED (the default level), the SELECT statement places a shared lock, so other processes can read the row as well.

In-memory OLTP (Hekaton)

If you're using in-memory tables, like @Piotr commented, the second query will fail because it doesn't wait for the first query to complete. Because of this, the row would have changed by the time the second query tries to commit its own changes, so the result will be Value=1.

For this reason, it is best practice to trap and retry data modifications in the client app when using in-memory tables.

Related Question