Below two-sessions-trying-to-update-the-same-row scenario was provided by Paul White.
- Session 1 obtains a
Ulock on the base table row while reading.
- Session 2 blocks, waiting to acquire
Uon the same row in the base table.
- Session 1 sets Value to 10 and commits.
- Session 2 acquires its
Ulock 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?