SQL Server Cursors — Optimistic Concurrency Which cursor concurrency model acquires no locks when reading and waits until update time to check for conflicts?

Introduction:
Optimistic concurrency aims to maximize concurrency by avoiding locks during reads and validating at write time that no conflicting change occurred. This model is common in high-read, lower-conflict workloads.

Given Data / Assumptions:

• We are choosing among cursor concurrency options.
• Goal: identify the model that avoids locking until the user updates data.

Concept / Approach:
In SQL Server cursors, OPTIMISTIC uses either a rowversion value or a checksum snapshot of columns to detect if a row changed since it was fetched. If it changed, the update is rejected at update time; otherwise, it proceeds. No update locks are taken during fetch, so readers do not block writers during enumeration.

Step-by-Step Solution:
1) Map options to semantics: OPTIMISTIC defers conflict detection, SCROLL_LOCKS locks early, READ_ONLY cannot modify, PESSIMISTIC is not a T-SQL cursor keyword.2) Select OPTIMISTIC as the model with no read-time locks.3) Note that OPTIMISTIC with checksum is a stricter variant but still optimistic.

Verification / Alternative check:
Check DECLARE CURSOR WITH OPTIMISTIC or OPTIMISTIC TYPE—behavior is to validate at UPDATE/DELETE time, not FETCH time.

Why Other Options Are Wrong:

• READ_ONLY: No updates allowed at all.
• SCROLL_LOCK: Takes update locks on fetch.
• PESSIMISTIC: Not a SQL Server cursor keyword; describes locking philosophy only.
• READ WRITE with HOLDLOCK: A statement hint pattern, not a cursor concurrency option.

Common Pitfalls:
Believing READ COMMITTED isolation equals optimistic cursor behavior; they are distinct concerns.

Final Answer:
OPTIMISTIC