Does a SQL UPDATE operation read data to "local memory"?

Question

This answer quotes this Technet article which explains the two interpretations of lost updates:

A lost update can be interpreted in one of two ways. In the first scenario, a lost update is considered to have taken place when data that has been updated by one transaction is overwritten by another transaction, before the first transaction is either committed or rolled back. This type of lost update cannot occur in SQL Server 2005 because it is not allowed under any transaction isolation level.

The other interpretation of a lost update is when one transaction (Transaction #1) reads data into its local memory, and then another transaction (Transaction #2) changes this data and commits its change. After this, Transaction #1 updates the same data based on what it read into memory before Transaction #2 was executed. In this case, the update performed by Transaction #2 can be considered a lost update.

So it looks like the difference is that in the first scenario the whole update happens out of "local memory" while in the second one there's "local memory" used and this makes a difference.

Suppose I have the following code:

UPDATE MagicTable SET MagicColumn = MagicColumn + 10 WHERE SomeCondition

Does this involve "local memory"? Is it prone to the first or to the second interpretation of lost updates?

Answer 1

I suppose it would come under the second interpretation.

However the way this type of UPDATE is implemented in SQL Server a lost update is still not possible. Rows read for the update are protected with a U lock (converted to X lock when the row is actually updated).

U locks are not compatible with other U locks (or X locks)

So at all isolation levels if two concurrent transactions were to run this statement then one of them would end up blocked behind the other transaction's U lock or X lock and would not be able to proceed until that transaction completes.

Therefore it is not possible for lost updates to occur with this pattern in SQL Server at any isolation level.

To achieve a lost update you would need to do something like

BEGIN TRAN

DECLARE @MagicColumn INT;

/*Two concurrent transactions can both read the same pre-update value*/
SELECT @MagicColumn = MagicColumn FROM MagicTable WHERE SomeCondition

UPDATE MagicTable SET MagicColumn = @MagicColumn + 10 WHERE SomeCondition

COMMIT