SQL Server Concurrency in update

Question

I have a TABLE:

id  status  mod runat
1   0   null    null
2   0   null    null
3   0   null    null
4   0   null    null

And, I call this query two times, at same time.

UPDATE TABLE
SET
    status = 1,
    mod = GETDATE()
OUTPUT INSERTED.id
WHERE id = (
        SELECT TOP (1) id
        FROM TABLE 
        WHERE STATUS = 0
            AND NOT EXISTS(SELECT * FROM TABLE WHERE STATUS = 1)
            AND COALESCE(runat, GETDATE()) <= GETDATE()
        ORDER BY ID ASC)

And... some times I have:

1
1

Instead

1
NULL

why? Update query isn't transactional?

Answer 1

Short answer

Add WITH (UPDLOCK, HOLDLOCK) to select

UPDATE TABLE
SET
    status = 1,
    mod = GETDATE()
OUTPUT INSERTED.id
WHERE id = (
        SELECT TOP (1) id
        FROM TABLE WITH (UPDLOCK, HOLDLOCK)
        WHERE STATUS = 0
            AND NOT EXISTS(SELECT * FROM TABLE WHERE STATUS = 1)
        AND COALESCE(runat, GETDATE()) <= GETDATE()
    ORDER BY ID ASC)

Explanation

Because you are using a subquery to get the ID there are basically two statements being run here - a select and an update. When 1 is returned twice it just means both select statements ran before either update was completed. If you add an UPDLOCK, then when the first one runs it holds the UPDLOCK. The second SELECT has to wait for the UPDLOCK to be released by the first select before it can execute.

More information

Exactly what is happening will depending on the locking scheme of your database, and the locks issued by other statements. This kind of update can even lead to deadlocks under certain circumstances.

Because the statements runs so fast it's hard to see what locks they are holding. To effectively slow things down a good trick is to

1. Open a session and run the first statement with a BEGIN TRANS statement at the start of it (don't include a COMMIT or ROLLBACK)
2. Run a query on sys.dm_tran_locks to see what locks are being held
3. Open a second session and run the second statement and see what happens. If your locking scheme is setup correctly it should wait for the first one to finish before it does anything.
4. Switch back to the first session and COMMIT to simulate it finished

This link has a lot of information but locking and data contention are complex areas with lots of possible solutions. This link should give you everything you need to know to decide how to approach this issue. https://learn.microsoft.com/en-us/sql/relational-databases/sql-server- transaction-locking-and-row-versioning-guide?view=sql-server-2017