What's the lowest isolation required to ensure only one SQL UPDATE makes a change?

Question

Here's a SQL UPDATE command with parameters...

UPDATE MyTable 
SET MyField = @newvalue, 
    @success = 1
WHERE 
    Id = @id 
    AND MyField = @oldvalue

Many clients will be running this command with the same @id and @oldvalue parameter values at the same time. I want only one of them to be the "winner" and set MyField to it's own @newvalue, and for it to know it won because the @success output parameter will be set to 1.

What's the lowest isolation level needed for this to work and guarantee that only one client will ever "win"? Do I need a transaction for this one statement?

I'm using SQL Server 2008, but I hope there's a standard SQL answer.

[When an `update` command is executing, an exclusive lock is placed on the table](http://msdn.microsoft.com/en-us/library/ms175519.aspx). — , Mar 02 '12 at 19:05
@JackManey Not to be pedantic, but technically an "update lock" is placed on the table. — Michael Fredrickson, Mar 02 '12 at 19:08
@MichaelFredrickson - That's what I thought at a glance, but the description of an exclusive lock is ["Used for data-modification operations, such as INSERT, UPDATE, or DELETE. Ensures that multiple updates cannot be made to the same resource at the same time."](http://msdn.microsoft.com/en-us/library/ms175519.aspx) — , Mar 02 '12 at 19:09
@MichaelFredrickson: do be **totally** pedantic: **first** an update lock (U) is placed on the table whîle the data to be updated is being read, and then this (U) lock is escalated to a (X) exclusive lock for the duration of the **writing** part of the update — marc_s, Mar 02 '12 at 19:57

score 2 · Answer 1 · answered Mar 02 '12 at 19:06

Provided the new value is always different from the old value, the default isolation level, READ COMMITTED, is enough.

However, if the value may end up being same as the old value, all of the clients will succeed. One after one. And each will have @success = 1.

marc_s · Accepted Answer · 2012-03-02T19:58:07.073

The SQL Server UPDATE statement will always put an (U) (update lock) during the reading phase of the update, which is then escalated into an (X) (exclusive lock) for the actual writing process of the new values onto the row (or page/table) that you're updating - regardless of which transaction isolation level you're using.

Since one (U) lock is incompatible with another (U) lock on the same level, if one process already has an (U) lock, no other connection can start an UPDATE at the same time and will have to wait.

Again: this is standard behavior for SQL Server - regardless of which transaction isolation level you're using - it's the same for all those isolation levels.

I don't know if this statement is valid for other RDBMS systems - most likely not. These kinds of things aren't handled by any ANSI SQL standard - those are always very much vendor-specific.

score 0 · Answer 3 · answered Apr 02 '12 at 22:46

SQL Server behavior has been covered, but I'll expand on the part of the question asking about "a standard SQL answer".

I'm not aware of any RDBMS which would require a stricter isolation level than READ COMMITTED to get the behavior you're asking for, provided (as already mentioned) that the new value is distinct from the old value. I believe that the requirements of the various transaction isolation levels in the SQL standard should guarantee this for any conforming database product. This is not always enforced by the types of locks described in other answers, but most systems use some kind of blocking lock to provide this guarantee.

What's the lowest isolation required to ensure only one SQL UPDATE makes a change?

3 Answers3