Is an operation in SQL guaranteed to be atomic if performed in a single update statement?

Question

I'm trying to remove the need to explicitly lock a table with the following code but from reports I've been getting it may be possible that the parameter and value in the row are suffering from a race condition.

    Declare @NextNumber Int;

Update
    MyParameterTable
Set
    NextNumber = NextNumber + 1,
    @NextNumber = NextNumber + 1

Select @NextNumber As NextNumber;

Hopefully you can see it is just incrementing an integer and returning the result. I was under the impression that an update statement formatted in this manner would handle the necessary locks but as I say, it might be the case that the parameter is beign assigned outside of the update lock.

Can anyone throw some light on this and suggest an alternative?

Regards

Ryan

Answer 1

That will lock fine, but you can do it in a three-part equality statement:

Declare @NextNumber Int;

Update
    MyParameterTable
Set
    @NextNumber = NextNumber = NextNumber + 1

Select @NextNumber As NextNumber;

Answer 2

It is my understanding, too, that your query will not have race condition or concurrency problems.

I also think @Variable = Column = /Expression/ is just syntactic sugar on top of the exact same query as you originally posted. Running a trace and examining the locks acquired and released will prove this definitively.

I don't like to use that syntax, though, because it can have problems as in the following case:

Update MyParameterTable
Set
   @NextNumber = NextNumber = NextNumber + 1,
   @NextNumber = @NextNumber + 1

While I realize you may not ever want to do this, that it breaks in strange ways (the first row is handled differently from other rows) gives me the willies about trusting it. At one point in a particular SP of SQL 2000, there were real bugs with this syntax, too.

If you want to ensure that the row is being locked properly and no race condition can occur, then here is a great way to test it. Open several query windows in SSMS/QA and in each one do the following:

WAITFOR TIME '11:00:00' -- use a time in the near future
EXEC dbo.YourProcedureName

This will make all the batches you submitted in different sessions run at essentially the exact same moment, something very difficult to simulate any other way. You can soon get a clear indicator of whether a race condition can occur.

I recommend some articles for you to do further reading on the subject. They're not about this exact issue but are definitely related (dealing with race conditions in inserts and updates):

• Conditional Insert/Update Race Condition
• “UPSERT” Race Condition With MERGE