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.
Best Answer
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.
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 aU
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 aU
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 exclusiveX
lock is taken on the clustered index immediately - there is no priorU
lock.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-heldU
lock toX
.For example, if the row to update was located using the base table, a
U
will be held and converted toX
at the base table update operator. If the row was located using a secondary index,U
is held on that index and a newX
lock is taken on the base table. BothU
andX
are held at the same time, on different resources.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:
U
lock on the base table row while reading.U
on the same row in the base table.U
lock and finds nothing to do, since Value != 0 now.Outcome: Value is set to 10.
Scenario 2:
U
lock on the base table row while reading.U
on the same row in the base table.U
lock and finds nothing to do, since Value != 0 now.Outcome: Value is set to 20.
Scenario 3:
U
lock on a secondary index (locating a row to update).U
lock on the base table (locating a row to update).X
on the base table row to perform the update, but is blocked by theU
lock held by session 2.U
lock on the base table toX
and sets Value to 20.X
on the secondary index row to update it, but is blocked by theU
lock held by session 1.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: