8

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.

13

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:

2
  • Wow, what a great explanation. My experience with SQL has been limited to the surface only. My main reason for asking this question was to confirm my superficial understanding how locking mechanism will work during selection and then updating of a row. Saying the final result will be 20 was a mistake on my part and actually meant whether only 1 operation will update the row or both. My understanding was that both statements will select the row once without any U locks and then request an U lock after selection. Once acquired it will be converted to X and update made. Forgot indexes. – Vivek Kumar Batra Aug 14 '20 at 18:36
  • Seems a good time to read about locks and their mechanisms. I asked this question as I was looking into how SQL can support concurrency controls by using it in the manner I described - 2 requests made at the same time but only 1 will succeed because of the specific inherent criteria where the column being updated is also part of the where/selection criteria. Thanks for the useful links regarding lost updates. – Vivek Kumar Batra Aug 14 '20 at 18:39
8

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?

2
  • Haha. I have seen enough horror stories in the projects to stay away from them. But the application that I inherited has this defined on the connection level to SQL server and thus all queries run with this option :(. From what I know this option does have some effect on how locks are acquired, or I should say required, but not related to the context. I am aware of the winner part but trying to confirm if setting a higher isolation level will lock the row when the row is identified for the first update statement. – Vivek Kumar Batra Aug 14 '20 at 18:20
  • I got some good understanding of locks and @Paul White's answer explains a lot of what I needed but couldn't express them in a better way in my question. I asked this question to understand if I can use this inherent behavior of SQL where only 1 statement will win if the column being updated is part of the where/selection criteria. From Paul's answer it seems that only 1 will be commit as the other's selection criteria would not fulfill or one will be rolled back due to a deadlock. And yes these statements are part of bigger transactions having other statements. – Vivek Kumar Batra Aug 14 '20 at 18:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.