UPDATE statement behavior

Question

I have a question regarding inner workings of UPDATE statements with regards to SQL Server. I am trying to understand what will happen if the following 2 update statements are received or serviced at the same time:

 |    Session 1 - Statement 1 | Session 2 - Statement 2
 |   -------------------------+-------------------------
 |    update dbo.Table1       |  update dbo.Table1 
 |    set Value = 10          |  set Value = 20 
 |    where ID = 1            |  where ID = 1 
 ▼    and Value = 0;          |  and Value = 0;
(t)

From what I understand, update will first select the row which needs to be updated with a shared lock which means that both update statements can select specific row. It then asks for an exclusive lock for updating the column values. So, it seems that the result would the Value being set to 20.

Am I missing something or is my understanding correct?

The isolation level at the connection is set to READ UNCOMMITTED.

Answer 1

I have a question regarding inner workings of UPDATE statements with regards to SQL Server.

The statement describes a desired logical change to the database. What happens physically at runtime depends on the execution plan, the state of the database, and what other concurrently running statements/queries are doing.

I mention this because your question relates very much to implementation details.

From what I understand, update will first select the row which needs to be updated with a shared lock which means that both update statements can select specific row.

This is generally not what happens.

Under most isolation levels (including READ UNCOMMITTED as specified in the question) SQL Server will take update (U) locks when locating rows to update. This is an implementation detail added to offer some protection against a common form of conversion deadlock (it is not always sufficient, but it does help).

Note: SQL Server may choose any convenient access method to locate rows to update. For example, it might choose to use a nonclustered index. The update will still ultimately update the base table and any applicable secondary indexes, but the order of operations may differ.

This means U locks do not necessarily serialize as one might expect. For example, one update statement in the question might choose to locate records via a secondary index, in which case an entry in that index has a U lock applied. There is nothing to stop the second update statement choosing a plan that locates records using a scan of the base table (heap or clustered).

In that case, the second update could acquire a U lock on a row in the base table while the first update holds a U lock on a row in a secondary index that links to the same base table row.

In yet another scenario, SQL Server might be able to choose a single operator update plan (for example if a clustered index exists on (ID, Value)). In this case, an exclusive X lock is taken on the clustered index immediately - there is no prior U lock.

It then asks for an exclusive lock for updating the column values.

Data-changing operations always take an X lock before the change is made. This lock is held to the end of the transaction. It may or may not involve converting a currently-held U lock to X.

For example, if the row to update was located using the base table, a U will be held and converted to X at the base table update operator. If the row was located using a secondary index, U is held on that index and a new X lock is taken on the base table. Both U and X are held at the same time, on different resources.

So, it seems that the result would the Value being set to 20.

There are a number of possible outcomes depending on timing and the execution plans chosen by each of the two update statements. Choosing just a few of the more interesting ones:

Scenario 1:

1. Session 1 obtains a U lock on the base table row while reading.
2. Session 2 blocks waiting to acquire U on the same row in the base table.
3. Session 1 sets Value to 10 and commits.
4. Session 2 acquires its U lock and finds nothing to do, since Value != 0 now.

Outcome: Value is set to 10.

Scenario 2:

1. Session 2 obtains a U lock on the base table row while reading.
2. Session 1 blocks waiting to acquire U on the same row in the base table.
3. Session 2 sets Value to 20 and commits.
4. Session 1 acquires its U lock and finds nothing to do, since Value != 0 now.

Outcome: Value is set to 20.

Scenario 3:

1. Session 1 obtains a U lock on a secondary index (locating a row to update).
2. Session 2 obtains a U lock on the base table (locating a row to update).
3. Session 1 needs to acquire X on the base table row to perform the update, but is blocked by the U lock held by session 2.
4. Session 2 converts its U lock on the base table to X and sets Value to 20.
5. Session 2 now needs to maintain the secondary index (assuming that index contains the Value column).
6. Session 2 needs to acquire X on the secondary index row to update it, but is blocked by the U lock held by session 1.
7. Deadlock. One of the sessions rolls back with an error; the other completes successfully.

Outcome: Indeterminate. Value may end up as either 10 or 20.

---

These are just some of the possibilities. It is generally a mistake (in my opinion) to try to predict detailed engine locking behaviours. Focus instead on writing a correct logical specification of the change you want to make, and use the isolation level that provides the guarantees you need.

Something about the way the question is written makes me think you might really be asking about "lost updates". If that is case, please review existing Q & A like the following before asking a more specific new question:

• Lost Update Understanding
• Are lost updates completely handled in SQL Server or is manual intervention still required?
• Why are U locks required when using Read Committed Snapshot Isolation
• Where is Read Committed insufficient?

Answer 2

When it comes to writing, there is no such thing as "same time." One session will "win" the race for the lock, and update the value first; the "loser" will update the value next, and that's whose change will be reflected for the next reader.

As an aside, isolation level doesn't have any effect here (unless there are explicit transactions that include other statements), and READ UNCOMMITTED does not make sense for DML anyway. Why are you setting this at the connection level? Are there not enough horror stories for you? Do you need more? And more?