SQL Server / Intermediate / Lesson 8

Isolation, locks, and concurrent decisions

Explain what simultaneous sessions can observe and why a correct single-session query can fail as a workflow. Compare locking READ COMMITTED, RCSI, SNAPSHOT, and optimistic updates.

About 5 min readContent reviewed 2026-10-23

On this page

Lesson goals

Distinguish statement and transaction consistency.
Trace a blocking sequence.
Choose a concurrency rule from an invariant.

Ask what must remain stable

Isolation controls interactions between concurrent transactions. Atomicity alone does not guarantee that a decision based on an earlier read remains valid. If two sessions both read available capacity and later insert reservations, each transaction may be internally atomic while their combined result exceeds capacity. Define the cross-session invariant before choosing an isolation level or hint.

READ COMMITTED prevents dirty reads, but separate statements can observe different committed states. REPEATABLE READ protects already-read rows more strongly but does not generally prevent new matching rows. SERIALIZABLE adds protection for qualifying key ranges, which can increase blocking. Stronger isolation is a deliberate correctness and throughput tradeoff, not a universal performance improvement.

Trace ownership and waiting

Locks coordinate incompatible operations on resources such as keys, pages, and schema. A writer commonly holds exclusive protection until the transaction ends under the SQL Server 2019 model used here. Another session may wait rather than fail immediately. The conceptual scripts assume dbo.TrainingStock already exists in a dedicated lab; they must not be submitted to any configured application database.

In session A, updating product 7 and leaving the transaction open can block session B's update to that product. Committing or rolling back A allows B to proceed, subject to its predicate. This is blocking with a one-way dependency, not a deadlock. Observe who holds the resource and why the owning transaction remains open.

sql A conceptual blocking timeline

-- Conceptual session A; requires a disposable lab table.
BEGIN TRANSACTION;
UPDATE dbo.TrainingStock SET Quantity=Quantity-1 WHERE ProductId=7;
-- Session B updating ProductId=7 may wait here.
ROLLBACK TRANSACTION;

No fixed elapsed time is predicted. Lock footprint depends on the plan, indexes, and concurrent work.

Compare RCSI and SNAPSHOT

READ_COMMITTED_SNAPSHOT, or RCSI, changes READ COMMITTED reads to use row versions and gives statement-level consistency. ALLOW_SNAPSHOT_ISOLATION enables sessions to request SNAPSHOT, giving transaction-level version consistency. These are separate database options. Enabling either changes resource and operational requirements, so it is not a casual query-local repair. The example only inspects their current states.

Versioning reduces many reader-writer conflicts, but writers still coordinate and schema locks still matter. SNAPSHOT updates can fail when a row changed after the snapshot, requiring the application to handle an update conflict. Long-lived transactions retain versions and consume space. SQL Server 2025 optimized-locking behavior is outside this course's 2019 baseline; do not assume every version uses identical lock lifetimes.

sql Inspect versioning options without changing them

SELECT name,is_read_committed_snapshot_on,
 snapshot_isolation_state_desc
FROM sys.databases WHERE database_id=DB_ID();

A row reports actual database configuration. OFF is not proof of a bug; the correct setting depends on the workload contract.

Detect stale application edits

A rowversion column is an automatically changing binary token, not a wall-clock timestamp. Return it with a row and require the original token in an update predicate. If the row changed since it was read, the update affects zero rows and the application can ask the user to reload or merge. This prevents silent overwriting of another editor's work.

Optimistic checks should use a stable key plus the token, and return the new token after success. Keep the token as binary data through the driver. A zero-row update can also mean the row was deleted, so response design matters. A token does not coordinate a multirow invariant by itself; use a transaction strategy appropriate to the complete operation.

Avoid isolation shortcuts

NOLOCK allows dirty and otherwise inconsistent reads; it is not a safe general cure for blocking or an assurance of no locks. A financial report can omit or double-count data under unsuitable access patterns. First inspect transaction duration, access paths, and the consistency requirement. A deliberately approximate monitoring screen has different needs from inventory availability.

Test concurrency with controlled session schedules and explicit cleanup in a disposable environment. Record what each statement should see, when each transaction ends, and which retry is permitted. A test that runs two scripts without controlling their overlap may miss the race entirely. Correctness reasoning must cover an actual interleaving, not just two successful sequential runs.

Common errors and fixes

A user overwrote another edit.

Why it happens: The update did not check the originally read version.
How to fix it: Use a key-and-rowversion predicate and report zero-row conflicts.
Prevent it next time: Test simultaneous editors.

Snapshot isolation transaction failed due to update conflict.

Why it happens: A target changed after the snapshot.
How to fix it: Roll back and retry the complete eligible operation under a bounded policy.
Prevent it next time: Design conflict handling before enabling SNAPSHOT.

NOLOCK produced inconsistent totals.

Why it happens: The query allowed uncommitted or unstable reads.
How to fix it: Choose a documented consistency model instead.
Prevent it next time: Review read accuracy as a requirement.

Check your understanding

1. Two SELECT statements under RCSI run before and after another transaction commits. Must they match?

Show worked solution

No. RCSI provides a committed snapshot per statement, so the later statement can see the new commit. SNAPSHOT has a different transaction-level contract when enabled and requested.

2. A client updates by key and old rowversion, affecting zero rows. What next?