CODEWITHSUNDEEP
sqlt-sqlatomic
krimog
krimog

Reputation: 1276

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 ?

Upvotes: 0

Views: 130

Answers (3)

Ruslan K.
Ruslan K.

Reputation: 1981

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

Upvotes: 2

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48177

Calculate the max ID first then use it in a cross join for the the update.

SQL DEMO

WITH cte as (
    SELECT TOP 1 ID
    FROM [MyTable]
    WHERE MYFIELD IS NULL
    ORDER BY ID
)
UPDATE t
SET [ID] = cte.[ID]
FROM [MyTable] t
CROSS JOIN  cte;

OUTPUT

enter image description here

Upvotes: 0

Ryan Gadsdon
Ryan Gadsdon

Reputation: 2378

This would result the top 1 value but if its the same you have the potential to return more than one value.

Try using DISTINCT

I understand that top(1) should only return 1 row but the question states its returning more than one row. So i thought it might be because the value is the same. So you could possibly use something like this

    SELECT DISTINCT TOP 1 name FROM [Class];

Its either that or where clause to narrow results

Upvotes: 0

Related Questions