In Microsoft SQL Server, which cursor concurrency option indicates that no lock is obtained when rows are read and that a lock is taken only if the user actually updates the data, implementing optimistic concurrency control?

Introduction / Context:
This question examines your knowledge of cursor concurrency options in Microsoft SQL Server. Concurrency control is essential when multiple users access and modify the same data. SQL Server offers different concurrency options for cursors, and understanding the optimistic approach helps in designing responsive applications that reduce locking overhead when conflicts are rare.

Given Data / Assumptions:

- The question refers to SQL Server cursor concurrency.- It specifies that no lock is obtained when reading, and a lock is taken only on update.- Several named options are given, including OPTIMISTIC and PESSIMISTIC.

Concept / Approach:
Optimistic concurrency assumes that conflicts between users are relatively rare. Under this model, readers do not lock rows aggressively when they read them. Instead, the system checks for conflicts when an update is attempted. If someone else has changed the data in the meantime, the update may fail or must be retried. Pessimistic concurrency, by contrast, locks data when it is read to prevent others from modifying it until the operation is complete. The description in the question aligns clearly with optimistic behavior.

Step-by-Step Solution:
Step 1: Identify that no lock is obtained at read time, which is the first clue.Step 2: Recognize that a lock is taken only when an update is attempted, indicating that conflict checks occur at update time.Step 3: Recall that this delayed locking behavior is characteristic of optimistic concurrency.Step 4: Review the options and find OPTIMISTIC as one of the concurrency choices.Step 5: READ_ONLY describes cursors that cannot update data, which does not match the condition that a lock is taken on update.Step 6: PESSIMISTIC implies early locking, the opposite of the behavior described.Step 7: Therefore, the correct option is OPTIMISTIC.

Verification / Alternative check:
SQL Server documentation explains that optimistic concurrency uses row versioning or comparison of original values to detect conflicts at update time. In this model, readers do not block writers, and writers do not block readers until the moment of change. Pessimistic concurrency uses exclusive locks during data access, which is not what the question describes. This technical confirmation supports choosing the OPTIMISTIC option.

Why Other Options Are Wrong:
READ_ONLY cursors cannot perform updates, so the idea of taking a lock on update does not apply. SCROLL_LOCKS is related to how scrolling cursors lock rows during navigation and does not match the specific description. PESSIMISTIC implies that locks are taken early to prevent conflicts, which conflicts with the statement that no lock is obtained at read time. SERIALIZABLE is an isolation level, not specifically a cursor concurrency type here, and it enforces strict ordering rather than optimistic behavior.

Common Pitfalls:
Many learners confuse optimistic and pessimistic concurrency, sometimes remembering only that one of them reduces locking but not recalling which. A good way to remember is that optimistic concurrency is optimistic about conflicts and delays locking until necessary, while pessimistic concurrency is pessimistic and locks early. Carefully matching the description in the question to these behaviors avoids mistakes.

Final Answer:
The described behavior corresponds to the OPTIMISTIC cursor concurrency option.