0

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

1
  • For memory optimized tables SQL Server is not doing any locks, but marks what the query is dependent on. So in this scenario both updates will be successful with v=v+1 and the second commit will fail. – Piotr Jan 6 '20 at 18:22
0

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.

2
  • Correct me if I am wrong. If I do not explicitly start a transaction then it will run in auto commit transaction mode. And the isolation level would be server default (read committed). in such case the Write lock is still placed before the first read operation right? – Steve Jan 6 '20 at 20:02
  • @steve, if you don't explicitly set a transaction, every statement commits its work. You still set the isolation level, either explicitly or by keeping the default. – Daniel Hutmacher Jan 6 '20 at 20:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.