Search code examples
sqlt-sqlatomic

Update where select, guarantee atomicity

I have a T-SQL query like this:

UPDATE 
    [MyTable]
SET
    [MyField] = @myValue
WHERE
    [Id] = 
    (
        SELECT TOP(1) 
            [Id]
        FROM
            [MyTable]
        WHERE
            [MyField] IS NULL
            -- AND other conditions on [MyTable]
        ORDER BY
            [Id] ASC
    )

It seems that this query is not atomic (the select of 2 concurrent executions can return the same Id twice).

Edit: If I execute this query, the Id returned by the SELECT will not be available for the next execution (because [MyField] will not be NULL anymore). However, if I execute this query twice at the same time, both executions could return the same Id (and the second UPDATE would overwrite the first one).

I've read that one solution to avoid that is to use a SERIALIZABLE isolation level. Is that the best / fastest / most simple way ?

Solution

  • As I can see, UPDLOCK would be enough (test code confirms that)