How exactly does SQL Server execute an UPDATE statement?

Question

Below two-sessions-trying-to-update-the-same-row scenario was provided by Paul White.

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.

Based on Paul, session 2 has already located the row that it needs to update, but it got blocked. When the blocking is gone and session 2 got its U lock, it needs to check the condition again? Am I understanding it right? To me it's kind of weird. The session has located this row, it's because it evaluated the filter which leads to the identification of this row. Why it needs to check the condition again before actual update?

Answer 1

The answer to that is implied in the text you posted.

The Value = 0 condition was met initially but by the time it gets the U lock the Value = 0 condition is no longer met.

SQL Server only reads value(s) from the row after the lock is acquired.

The SQL Statement

UPDATE dbo.Table1
SET    Value = 10
WHERE  ID = 1 AND Value = 0; 

Is supposed to update rows where ID = 1 AND Value = 0 at the time of the update. Not in a previous version.

This is why these approaches to test for optimistic concurrency violations work.