Optimistic concurrency in SQL Server:\nJudge the claim:\n\n"With optimistic locking, an update lock is placed on the row when the row is read."

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:

  • No explicit locking hints like UPDLOCK, XLOCK are used on the SELECT.
  • We refer to optimistic patterns, not pessimistic locking.
  • Isolation may be READ COMMITTED SNAPSHOT or SNAPSHOT, or application-enforced checks.


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:

Reader performs SELECT without UPDLOCK under snapshot-style isolation → no update lock taken.On UPDATE, engine checks rowversion or detects write-write conflict; conflicting transaction rolls back.Therefore, the claim that an update lock is placed on read contradicts optimistic design.


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:

  • Correct choices rely on pessimistic locking or explicit hints.
  • RCSI-only: Even in RCSI, readers do not take update locks.
  • UPDLOCK: That is a pessimistic override, not optimistic default.


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

No comments yet. Be the first to comment!
Join Discussion