Difficulty: Easy
Correct Answer: Incorrect
Explanation:
Introduction / Context:
Optimistic concurrency assumes that most transactions do not conflict. Instead of locking rows on read, the system detects conflicts at write time. SQL Server implements optimistic models via row versioning (e.g., READ COMMITTED SNAPSHOT and SNAPSHOT isolation) and application-side concurrency tokens (rowversion/timestamp or original value checks).
Given Data / Assumptions:
Concept / Approach:
In optimistic concurrency, readers do not take update locks. They either read versioned rows (row-versioning isolation) or read current data and carry an etag/rowversion. At update time, the engine or application verifies that data has not changed since read; if it has, the update is rejected or retried. Placing an update lock on read describes a pessimistic approach (UPDLOCK or SELECT FOR UPDATE semantics), not optimistic.
Step-by-Step Solution:
Verification / Alternative check:
Use sys.dm_tran_locks and Extended Events to observe locks; compare behavior with and without UPDLOCK under different isolation levels.
Why Other Options Are Wrong:
Common Pitfalls:
Mixing optimistic and pessimistic assumptions; forgetting to include concurrency checks on update; assuming SNAPSHOT prevents all conflicts (it detects, not prevents).
Final Answer:
Incorrect
Discussion & Comments