SQL Server Cursors — Concurrency Option with Update Locks Which cursor concurrency option places update locks on rows as they are read to prevent concurrent changes?

Introduction:
SQL Server cursors can run under different concurrency models that trade off blocking, correctness, and the probability of update conflicts. Understanding these options helps you predict locking behavior when iterating through result sets.

Given Data / Assumptions:

• Options include READ_ONLY, SCROLL_LOCK(S), OPTIMISTIC, and variations.
• Goal: find which option acquires update locks while reading.

Concept / Approach:
SCROLL_LOCKS cursors acquire update locks on fetched rows to prevent other sessions from updating them until the cursor operation is finished, thereby avoiding lost updates in pessimistic fashion. OPTIMISTIC variants do not lock at read time; they detect conflicts at update time using timestamps or checksums. READ_ONLY cannot update and therefore does not lock for updates. READCOMMITTED is an isolation level, not a cursor concurrency option.

Step-by-Step Solution:
1) Map each option to its locking semantics.2) Identify the pessimistic model that locks rows on read.3) Choose SCROLL_LOCK (SCROLL_LOCKS) as the correct answer.

Verification / Alternative check:
Consult cursor declaration syntax: DECLARE cur CURSOR FOR … FOR UPDATE OF … WITH SCROLL_LOCKS acquires update locks during fetch.

Why Other Options Are Wrong:

• READ_ONLY: No updates and no update locks.
• OPTIMISTIC: Uses versioning or values checks, not locks at read time.
• READCOMMITTED: Isolation level, not a cursor concurrency option.
• OPTIMISTIC with checksum: Still optimistic, checks at update time.

Common Pitfalls:
Confusing isolation levels with cursor concurrency options, and overlooking that optimistic models defer conflict detection.

Final Answer:
SCROLL_LOCK