Under pessimistic locking in SQL Server, are locks typically acquired when data is read (and held as needed) rather than waiting until the user issues an update before any locks are taken?

Introduction / Context:
Pessimistic concurrency assumes conflicts are likely, so it protects data by locking. Understanding when locks are acquired helps explain blocking, deadlocks, and query behavior under different isolation levels.

Given Data / Assumptions:

• We consider classic locking isolation levels (READ COMMITTED, REPEATABLE READ, SERIALIZABLE).
• We exclude versioning levels (READ_COMMITTED_SNAPSHOT, SNAPSHOT) which reduce reader-writer blocking.
• Default SQL Server behavior applies.

Concept / Approach:
Under pessimistic locking, the engine acquires shared locks on reads and update/intent/exclusive locks on modifications. In READ COMMITTED, shared locks are taken during the read and released after the statement. In REPEATABLE READ, read locks persist longer, and in SERIALIZABLE, range locks prevent phantoms. Locks do not wait solely until an update occurs; reads also take locks as needed to ensure isolation semantics.

Step-by-Step Solution:
Run a SELECT under READ COMMITTED: observe shared locks with sys.dm_tran_locks.Run concurrent UPDATE: note potential blocking due to existing shared locks.Escalate isolation to REPEATABLE READ or SERIALIZABLE: observe longer-lived or range locks.

Verification / Alternative check:
Use SET TRANSACTION ISOLATION LEVEL and DMVs to view lock types and lifetimes during reads vs. updates.

Why Other Options Are Wrong:
Asserting “no locks until update” contradicts locking rules. READ UNCOMMITTED minimizes read locks, but that is not “pessimistic” and still does not defer all locking to updates. SNAPSHOT uses versioning, not no-lock semantics.

Common Pitfalls:
Confusing READ UNCOMMITTED (dirty reads) with no locking at all; assuming readers never block writers under pessimistic levels.

Final Answer:
Incorrect — pessimistic locking acquires locks during reads as required