Why Two Reads Disagree in a SQL Server Transaction

A transaction can succeed, commit cleanly, and still fail a business requirement that two reads inside that transaction describe one consistent state. The misleading part is the wrapper: BEGIN TRANSACTION looks like a natural boundary, so application code often treats it as the timestamp that fixes what all later reads can see. In SQL Server, that conclusion depends on the selected isolation contract. With READ COMMITTED plus READ_COMMITTED_SNAPSHOT (RCSI), each statement gets its own versioned view. With explicit SNAPSHOT, the transaction gets a transaction-level view, and the decisive boundary is the transaction’s first data access rather than the mere execution of BEGIN TRANSACTION. Microsoft documents those distinctions separately in SET TRANSACTION ISOLATION LEVEL and the SQL Server 16.x transaction locking and row versioning guide.

This acceptance playbook fixes the laboratory at self-managed SQL Server 2022 (16.x), not as a claim about the newest release, and uses a disposable disk-based database, two explicit client sessions, no connection pool, no ORM, no lock hints, and no external side effects. The research cutoff is September 22, 2026. The demonstrations below are reproducible proposed tests with expected assertions; no output is presented as if these runs were executed while the brief was prepared. The goal is an operational decision: ACCEPT, HOLD, CHANGE ISOLATION, or RETRY THE WHOLE UNIT.

State the read-consistency requirement in business terms

Start with the operation, not the database option. Assume a backend report transaction reads one configuration row twice: once to choose a calculation path and later to label the result. The row has id = 1, revision = 1, and quota = 100. A concurrent administrator may commit revision = 2, quota = 200 while the report transaction is open.

There are two legitimate but different requirements. A statement-consistent operation may accept revision 1 on the first read and revision 2 on the second, provided each individual statement sees committed data. A transaction-consistent report instead requires both reads to describe the same committed business revision: if its first data access sees revision 1, later reads in the same unit must continue to describe that view even after revision 2 commits. The first requirement can fit RCSI; the second points toward an explicit transaction-level contract.

That decision belongs in the application requirement before anyone changes a database setting. Broader transactional database integration concerns include drivers, transactions, schema changes, and operational reliability, but this test deliberately narrows the question to read-view lifetime. The acceptance statement should therefore be concrete: “For this report operation, once the first business row is read, no concurrent committed revision may appear in later reads in the same transaction.” If the business instead wants each query to see the newest committed state, freezing the view would be the wrong solution.

Record database options and effective session isolation

Evidence must separate engine eligibility from session choice. Microsoft’s SQL Server contract says that RCSI is a database option changing how READ COMMITTED reads are implemented, while ALLOW_SNAPSHOT_ISOLATION merely permits transactions to select SNAPSHOT; it does not turn an ordinary READ COMMITTED session into SNAPSHOT. The ALTER DATABASE SET options page also documents the different connection-state requirements for changing these options.

Run this evidence capture before beginning a test transaction. It records the server build, compatibility level, RCSI, snapshot eligibility, accelerated database recovery (ADR), client/session fields, and effective isolation code. In sys.dm_exec_sessions, client_version is the Tabular Data Stream (TDS) protocol version and client_interface_name identifies the driver or library; neither substitutes for a human-readable SSMS, sqlcmd, or other client package build. Record that client package version separately from the client's About dialog or version command. Microsoft documents these field meanings for sys.dm_exec_sessions.

USE RcsiSnapshotLab;
GO

SELECT
    @@VERSION AS server_version_text,
    SERVERPROPERTY('ProductVersion') AS product_version,
    SERVERPROPERTY('ProductMajorVersion') AS product_major_version,
    SERVERPROPERTY('Edition') AS edition;

SELECT
    name,
    compatibility_level,
    is_read_committed_snapshot_on,
    snapshot_isolation_state_desc,
    is_accelerated_database_recovery_on
FROM sys.databases
WHERE name = DB_NAME();

SELECT
    s.session_id,
    s.program_name,
    s.client_interface_name,
    s.client_version,
    s.transaction_isolation_level,
    CASE s.transaction_isolation_level
        WHEN 1 THEN 'READ UNCOMMITTED'
        WHEN 2 THEN 'READ COMMITTED'
        WHEN 3 THEN 'REPEATABLE READ'
        WHEN 4 THEN 'SERIALIZABLE'
        WHEN 5 THEN 'SNAPSHOT'
        ELSE 'UNSPECIFIED'
    END AS transaction_isolation_level_desc,
    @@TRANCOUNT AS trancount,
    XACT_STATE() AS xact_state
FROM sys.dm_exec_sessions AS s
WHERE s.session_id = @@SPID;

A run is HOLD if the major version is not 16, compatibility is not the intended lab value, option state is still transitioning, the client cannot be identified, or the session isolation is not the one the run calls for. Those are not cosmetic differences; they change whether the evidence answers the specified question.

Distinguish RCSI from permission to use SNAPSHOT

The smallest useful matrix is this:

The SQL Server 16.x row-versioning guide explicitly describes RCSI as statement-level consistency and SNAPSHOT as transaction-level consistency; it also states that application selection is a separate step from enabling the database option. The Snapshot Isolation in SQL Server (ADO.NET) overview uses “snapshot” more broadly in places, so use its conflict example but use the T-SQL and engine pages as the controlling terminology for RCSI versus explicit SNAPSHOT.

Make option changes safe in a disposable database

Do not normalize this laboratory script into a production change recipe. Microsoft states that changing READ_COMMITTED_SNAPSHOT requires no other active connections to the database except the connection issuing ALTER DATABASE; changing snapshot eligibility has its own transition behavior. For a disposable database, create it first, set the options before opening Sessions A and B, and verify completed state.

USE master;
GO

IF DB_ID(N'RcsiSnapshotLab') IS NOT NULL
    THROW 51000, 'RcsiSnapshotLab already exists. Use a disposable instance or clean up first.', 1;
GO

CREATE DATABASE RcsiSnapshotLab;
GO
ALTER DATABASE RcsiSnapshotLab SET COMPATIBILITY_LEVEL = 160;
GO
ALTER DATABASE RcsiSnapshotLab SET ACCELERATED_DATABASE_RECOVERY = OFF;
GO
ALTER DATABASE RcsiSnapshotLab SET READ_COMMITTED_SNAPSHOT ON;
GO
ALTER DATABASE RcsiSnapshotLab SET ALLOW_SNAPSHOT_ISOLATION ON;
GO

SELECT
    name,
    compatibility_level,
    is_read_committed_snapshot_on,
    snapshot_isolation_state_desc,
    is_accelerated_database_recovery_on
FROM sys.databases
WHERE name = N'RcsiSnapshotLab';
GO

This lab explicitly fixes ADR OFF, so its common row-version store context is tempdb. That is a laboratory choice, not advice to disable ADR elsewhere. Never use WITH ROLLBACK IMMEDIATE here as a convenient way to evict a production application. If the option change cannot obtain the documented connection state, stop with HOLD rather than forcing users out.

Build the two-session configuration-revision fixture

Create one ordinary disk-based table. There is no memory-optimized filegroup, replication object, linked server, distributed transaction, connection pool, ORM, or table hint in the fixture. The row is intentionally simple because the experiment is about which committed version a read is allowed to see, not query-plan complexity.

USE RcsiSnapshotLab;
GO
SET NOCOUNT ON;
SET IMPLICIT_TRANSACTIONS OFF;
GO

CREATE TABLE dbo.ReportConfig
(
    id       int NOT NULL CONSTRAINT PK_ReportConfig PRIMARY KEY,
    revision int NOT NULL,
    quota    int NOT NULL,
    CONSTRAINT CK_ReportConfig_revision CHECK (revision > 0),
    CONSTRAINT CK_ReportConfig_quota CHECK (quota >= 0)
);
GO

INSERT dbo.ReportConfig(id, revision, quota)
VALUES (1, 1, 100);
GO

SELECT id, revision, quota
FROM dbo.ReportConfig
WHERE id = 1;
GO

Use two dedicated client connections named Session A and Session B. Before each canonical run, both sessions execute their own preamble and evidence query, then stop. Do not reset the row while either session still has an open transaction.

-- SESSION A preamble
USE RcsiSnapshotLab;
SET NOCOUNT ON;
SET IMPLICIT_TRANSACTIONS OFF;
SET XACT_ABORT OFF;
SELECT 'A' AS session_label, @@SPID AS session_id, @@TRANCOUNT AS trancount;

-- SESSION B preamble
USE RcsiSnapshotLab;
SET NOCOUNT ON;
SET IMPLICIT_TRANSACTIONS OFF;
SET XACT_ABORT OFF;
SELECT 'B' AS session_label, @@SPID AS session_id, @@TRANCOUNT AS trancount;

The synchronization protocol is an explicit barrier, not a WAITFOR guess. Execute one numbered block, retain its result, and acknowledge completion before the other session advances. A human can do this with two query windows; an automated harness can wait for a process result and then release the next step. Wall-clock timestamps may be useful supporting evidence, but the ordered barrier is authoritative.

Before showing any “result,” write the expected assertion. For RCSI, the canonical pair is (1, 2). For explicit SNAPSHOT where A reads before B commits, the pair is (1, 1). For the first-access timing control where B commits before A’s first read, A should first see 2. If a run yields something else, mark HOLD and inspect schedule, settings, transaction cleanup, and client execution order rather than rewriting the expected result after the fact.

Reset only after both sessions have ended their transactions:

-- RESET: run only after Session A and Session B each show @@TRANCOUNT = 0.
UPDATE dbo.ReportConfig
SET revision = 1, quota = 100
WHERE id = 1;

SELECT id, revision, quota
FROM dbo.ReportConfig
WHERE id = 1;

Reproduce two different reads under RCSI

RCSI does not make a multi-statement transaction read from one frozen snapshot. Microsoft documents that with READ_COMMITTED_SNAPSHOT = ON, a READ COMMITTED statement sees a transactionally consistent version as of the start of that statement. A later statement may therefore see a later commit. The canonical schedule proves that distinction with the same row and the same transaction wrapper.

Session A: execute block A1, then stop:

USE RcsiSnapshotLab;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRANSACTION;

SELECT 'A1-first-read' AS step, revision, quota
FROM dbo.ReportConfig
WHERE id = 1;
-- EXPECT: revision = 1, quota = 100.
-- BARRIER: do not continue until Session B has committed B1.

Session B: execute only after A1 is acknowledged:

USE RcsiSnapshotLab;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRANSACTION;

UPDATE dbo.ReportConfig
SET revision = 2, quota = 200
WHERE id = 1;

COMMIT TRANSACTION;

SELECT 'B1-post-commit' AS step, revision, quota
FROM dbo.ReportConfig
WHERE id = 1;
-- EXPECT: revision = 2, quota = 200.

Session A: execute block A2 only after B1 is acknowledged:

SELECT 'A2-second-read' AS step, revision, quota
FROM dbo.ReportConfig
WHERE id = 1;
-- EXPECT under RCSI + READ COMMITTED: revision = 2, quota = 200.

COMMIT TRANSACTION;
SELECT @@TRANCOUNT AS a_trancount_after_commit;
-- EXPECT: 0.

The expected pair is therefore 1 then 2, even though both A reads are enclosed by the same explicit transaction. That is not evidence of a broken transaction; it is evidence that the transaction has statement-level read consistency under this contract. The first statement’s view protected that statement from uncommitted or mid-statement changes, but it did not reserve revision 1 as the view for all later statements.

Acceptance depends on the business sentence from the first section. If the operation explicitly allows each statement to see the latest eligible committed state, this run can support ACCEPT for RCSI. If the operation requires the first and second reads to describe the same configuration revision, the correct decision is CHANGE ISOLATION, not “wrap it in another BEGIN TRANSACTION.” A transaction boundary controls atomic work; it does not, by itself, define a transaction-long read view under RCSI.

Run the identical schedule under explicit SNAPSHOT

Now change one contract and preserve the schedule. Leave both database options enabled, but explicitly select SNAPSHOT in Session A before the transaction. ALLOW_SNAPSHOT_ISOLATION = ON makes this selection legal; it does not select it on A’s behalf. Microsoft’s T-SQL documentation says a SNAPSHOT transaction reads the committed version associated with the transaction view and that a transaction starts, for this purpose, on its first data access.

Start the read view before the concurrent commit

After resetting the fixture to revision 1, run the following.

Session A: A1:

USE RcsiSnapshotLab;
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION;

SELECT 'A1-first-snapshot-read' AS step, revision, quota
FROM dbo.ReportConfig
WHERE id = 1;
-- EXPECT: 1, 100. This is A's first business-data access.
-- BARRIER: stop here.

Session B: B1:

USE RcsiSnapshotLab;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRANSACTION;

UPDATE dbo.ReportConfig
SET revision = 2, quota = 200
WHERE id = 1;
COMMIT TRANSACTION;

SELECT 'B1-post-commit' AS step, revision, quota
FROM dbo.ReportConfig
WHERE id = 1;
-- EXPECT in B's fresh statement: 2, 200.

Session A: A2:

SELECT 'A2-repeat-snapshot-read' AS step, revision, quota
FROM dbo.ReportConfig
WHERE id = 1;
-- EXPECT: still 1, 100.

COMMIT TRANSACTION;
SELECT @@TRANCOUNT AS a_trancount_after_commit;
-- EXPECT: 0.

The expected pair is 1 then 1, while B has already committed revision 2. This is the bounded proof the report requirement needed: the later A statement does not move to B’s newer commit because A is using a transaction-level snapshot. The SQL Server 16.x guide describes this as transaction-level read consistency and also notes that versioned reads still take schema-stability (Sch-S) locks rather than “no locks whatsoever.”

Do not mix A’s own writes into this control. SQL Server documents that a SNAPSHOT transaction can see changes it makes itself, which is a different rule from visibility of another transaction’s later commits. Keeping this first run read-only prevents that legitimate behavior from obscuring the test.

Confirm that a fresh transaction sees the new revision

After A commits, start a new explicit snapshot transaction. This is the positive control showing that revision 1 was retained only by the prior transaction view, not because B’s commit was lost.

-- SESSION A, after the prior COMMIT
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION;

SELECT 'A3-fresh-transaction' AS step, revision, quota
FROM dbo.ReportConfig
WHERE id = 1;
-- EXPECT: 2, 200.

COMMIT TRANSACTION;

If this fresh transaction does not see revision 2, HOLD. Check whether B really committed, whether the reset was accidentally run at the wrong time, whether A is still inside a previous transaction, and whether the test is actually connected to the intended database. A retained earlier snapshot is not a permanently stale database; its lifetime ends with the transaction.

Move the commit before the first data access

The most important negative control moves B’s commit without changing A’s BEGIN TRANSACTION. Reset the row to revision 1. In Session A, select SNAPSHOT and issue BEGIN TRANSACTION, but do not query the table, a DMV, or other data before the barrier. Environment inspection belongs before this transaction. This preserves the question: what happens when the concurrent commit occurs after BEGIN but before A’s first data access?

Session A: A0:

USE RcsiSnapshotLab;
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION;
-- BARRIER A0: stop immediately. No data read yet.

Session B: B0 after A0 is acknowledged:

USE RcsiSnapshotLab;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRANSACTION;

UPDATE dbo.ReportConfig
SET revision = 2, quota = 200
WHERE id = 1;

COMMIT TRANSACTION;
-- BARRIER B0: acknowledge COMMIT completion.

Session A: A1 only after B0:

SELECT 'A1-first-data-access-after-B-commit' AS step, revision, quota
FROM dbo.ReportConfig
WHERE id = 1;
-- EXPECT: 2, 200.

SELECT 'A2-repeat' AS step, revision, quota
FROM dbo.ReportConfig
WHERE id = 1;
-- EXPECT: 2, 200.

COMMIT TRANSACTION;

That expected result follows the documented first-access rule. The T-SQL page states that a transaction starts the first time it accesses data, and the SQL Server 16.x row-versioning guide distinguishes BEGIN TRANSACTION from assignment of the row-versioning transaction sequence number, which begins with the first read or write after BEGIN.

This is why an approximate note such as “A began at 10:03:02 and B committed at 10:03:04” is insufficient evidence. The decisive ordering is A first data access versus B commit, not merely A BEGIN versus B commit. An automated harness should therefore record symbolic barriers such as A_BEGIN_ACK, B_COMMIT_ACK, and A_FIRST_READ_RESULT. If the first A data read returns 1 in this schedule, the run is HOLD until the actual ordering and settings are explained.

Introduce a conflicting update without hiding its outcome

A stable read view does not mean a stale transaction may overwrite a row that another transaction changed after that view was established. Snapshot isolation uses optimistic conflict detection for writes. Microsoft’s documented ADO.NET example establishes a snapshot, lets another transaction modify and commit a selected row, then attempts to update that row from the original snapshot and reports error 3960. The engine error catalog describes 3960 as a snapshot-isolation update conflict and directs the caller to retry the transaction or change the write strategy.

Reset to revision 1. Then use this schedule.

Session A: establish the snapshot:

USE RcsiSnapshotLab;
SET XACT_ABORT OFF;
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION;

SELECT 'A1-conflict-baseline' AS step, revision, quota
FROM dbo.ReportConfig
WHERE id = 1;
-- EXPECT: 1, 100.
-- BARRIER: stop.

Session B: commit the competing write:

USE RcsiSnapshotLab;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRANSACTION;

UPDATE dbo.ReportConfig
SET revision = 2, quota = 200
WHERE id = 1;

COMMIT TRANSACTION;
-- BARRIER: B_COMMIT_ACK.

Preserve the conflict and transaction-state evidence

Now Session A attempts the conflicting update. Keep the failing statement visibly inside TRY; do not describe the error as necessarily waiting until COMMIT.

-- SESSION A, after B_COMMIT_ACK
BEGIN TRY
    UPDATE dbo.ReportConfig
    SET revision = revision + 1,
        quota = quota + 10
    WHERE id = 1;  -- Expected failing statement in this schedule.

    -- Reaching here would contradict the expected conflict.
    SELECT 'UNEXPECTED_UPDATE_SUCCESS' AS assertion_failure;

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    SELECT
        ERROR_NUMBER() AS error_number,
        ERROR_MESSAGE() AS error_message,
        XACT_STATE() AS xact_state_in_catch,
        @@TRANCOUNT AS trancount_in_catch;

    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;

    SELECT
        XACT_STATE() AS xact_state_after_cleanup,
        @@TRANCOUNT AS trancount_after_cleanup;
END CATCH;

The acceptance assertion is error number 3960 for this controlled interleaving, followed by verified cleanup with @@TRANCOUNT = 0. Capture ERROR_MESSAGE(), XACT_STATE(), and the in-catch transaction count as observations rather than inventing values in advance. Microsoft’s worked example establishes the expected conflict pattern, but the acceptance artifact should preserve what the actual client and session report rather than replacing observations with documentation text. A different error number, an unexpected successful update, or an unclosed transaction is HOLD, not permission to edit the expected ledger.

Retry the whole business unit from a fresh view

When a snapshot write loses this race, retrying only the last UPDATE is logically unsafe if earlier decisions were based on the old snapshot. Roll back whatever transaction remains, establish a fresh transaction, reread the business inputs, recompute, and then write. The following executable pattern keeps all effects inside the database fixture and caps the attempts.

-- SESSION A: bounded whole-unit retry pattern
USE RcsiSnapshotLab;
SET NOCOUNT ON;
SET XACT_ABORT OFF;
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

DECLARE @attempt int = 0,
        @max_attempts int = 3,
        @done bit = 0,
        @revision int,
        @quota int;

WHILE @attempt < @max_attempts AND @done = 0
BEGIN
    SET @attempt += 1;

    BEGIN TRY
        BEGIN TRANSACTION;

        -- Restart the complete business read/decision unit.
        SELECT
            @revision = revision,
            @quota = quota
        FROM dbo.ReportConfig
        WHERE id = 1;

        -- Illustrative in-database decision derived from the fresh view.
        UPDATE dbo.ReportConfig
        SET revision = @revision + 1,
            quota = @quota + 10
        WHERE id = 1;

        COMMIT TRANSACTION;
        SET @done = 1;

        SELECT
            @attempt AS successful_attempt,
            id, revision, quota
        FROM dbo.ReportConfig
        WHERE id = 1;
    END TRY
    BEGIN CATCH
        DECLARE @error_number int = ERROR_NUMBER();

        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;

        IF @error_number <> 3960
            THROW;

        IF @attempt >= @max_attempts
            THROW;

        -- Otherwise loop: the next attempt obtains a fresh snapshot.
    END CATCH;
END;

This pattern is intentionally bounded. It also excludes irreversible external actions such as sending an email, charging a card, or publishing a message. Such effects cannot be blindly replayed just because the database transaction is safe to retry. A real application needs an application-specific replay-safety design before RETRY THE WHOLE UNIT can be approved. Error 3960 itself is a transaction-level failure signal for this stale-snapshot write pattern, which is why restarting from a fresh view is the relevant unit of recovery.

Reconcile every read with the commit timeline

The acceptance artifact should make the schedule auditable without relying on memory. Keep expected and observed fields separate. A suggested ledger is below; populate the “Observed” cells only from an actual run on the recorded environment.

Read-view evidence

Write, cleanup, and decision evidence

For the step-by-step interleaving, retain a raw log such as: R1 A_BEGIN, R2 A_READ=1, R3 B_BEGIN, R4 B_UPDATE, R5 B_COMMIT_ACK, R6 A_READ=..., R7 A_END. In the first-access control, explicitly split A_BEGIN_ACK from A_FIRST_DATA_ACCESS. The same principle applies to the conflict test: preserve the exact update statement that fails, then the catch record, then cleanup.

A single final SELECT showing revision 2 cannot prove intermediate consistency. It only proves the final row at that later statement. Keep scripts, client output, engine/configuration evidence, and the ordered ledger together. If any barrier was skipped, a session was reused with unknown state, or the reset occurred before both transactions ended, mark the run HOLD even if the final row “looks right.”

Account for locks and row-version resource pressure

Row versioning changes specific read/write interactions; it does not abolish locking or operational cost. The SQL Server 16.x guide states that RCSI and snapshot reads rely on row versions and that versioned reads can avoid ordinary page and row read locks, while schema-stability locks still exist and data modifications retain their required write locking behavior. That is enough to reject the shorthand “SNAPSHOT means no locks.” This playbook therefore makes no universal latency, memory, or throughput claim.

The same guide documents that old row versions must remain available while active versioning transactions may need them. Long-running transactions can delay cleanup. With ADR disabled, as in this lab, the relevant common version store is in tempdb; with ADR enabled, SQL Server 16.x uses the persistent version store (PVS) in the user database for these row versions. Adoption review must use the actual target database’s ADR state rather than importing a different platform’s defaults.

A bounded operational inspection can include:

SELECT
    name,
    is_accelerated_database_recovery_on,
    is_read_committed_snapshot_on,
    snapshot_isolation_state_desc
FROM sys.databases
WHERE name = N'RcsiSnapshotLab';

SELECT *
FROM sys.dm_tran_version_store_space_usage
WHERE database_id = DB_ID(N'RcsiSnapshotLab');

SELECT
    session_id,
    elapsed_time_seconds,
    is_snapshot,
    transaction_sequence_num,
    first_snapshot_sequence_num
FROM sys.dm_tran_active_snapshot_database_transactions
WHERE session_id IN (@@SPID); -- Broaden only with appropriate permissions/evidence plan.

The guide lists dynamic management views for tempdb version-store usage and active snapshot transactions, and documents the operational reason to pay attention to long-running versioned transactions. Production sizing is outside this article; the acceptance requirement is to identify where versions live, verify permissions to monitor that resource, and establish who owns long-reader detection before extending snapshot lifetimes. The broader database performance and operational foundations are relevant context, but they are not evidence that row versioning will improve performance in a particular workload.

If the team cannot observe version-store pressure, does not know ADR state, or cannot bound transaction duration, use HOLD for production adoption even if the functional two-session test passes.

Keep snapshot consistency separate from invariant protection

A transaction-level snapshot solves a particular visibility requirement: reads in that transaction can be evaluated against a stable committed view, subject to the documented rules. It does not follow that every business invariant spanning multiple rows becomes serializable. SQL Server documents SNAPSHOT and SERIALIZABLE as distinct isolation contracts; SERIALIZABLE adds protections that are not part of the snapshot definition.

Consider a conceptual invariant: two different rows represent two independent approvals, and the business rule requires at least one approval to remain enabled. Two concurrent snapshot transactions could each make a decision from a stable old view and then update different rows. Because the writes target different rows, the single-row 3960 pattern demonstrated above is not a universal mechanism for detecting every cross-row logical conflict. This is an application-design warning, not a second laboratory and not a claim that every schema will exhibit the same anomaly.

The engineering implication is straightforward. Use the configuration-row fixture only to accept the visibility and same-row update-conflict behavior it actually tests. Where correctness depends on coordinated writes across rows, ranges, tables, or an absence condition such as “no other active reservation exists,” require a separate design review. Possible answers may include a different isolation contract, a constraint, a carefully designed serialization point, or a different transaction model, but choosing among them is beyond this playbook.

Do not write “SNAPSHOT guarantees the business invariant.” The defensible statement is narrower: “The selected isolation contract passed the specified repeat-read schedule, and the application has a documented policy for conflicts that this contract actually detects.” Anything broader is HOLD until proven with an invariant-specific test.

Choose an isolation contract with an acceptance matrix

Use requirements and evidence together. Enabling options without a matching schedule is configuration, not acceptance.

“All reads matched once” is not a general proof. The negative controls matter: RCSI should allow the second statement to advance; explicit snapshot should retain the old view when first access precedes B; explicit snapshot should start from the new value when B commits before first access; and the stale snapshot write should surface the expected conflict. A harness that passes only the desired positive case can accidentally be testing no concurrency at all.

Also keep session state explicit. SET TRANSACTION ISOLATION LEVEL is connection-scoped and remains in effect until changed, which is why each script selects its intended isolation level rather than trusting a prior window. The fixture has no pool; production connection-pool behavior is deliberately outside the evidence claimed here.

Add a concurrency regression gate to database delivery

Once the behavior is accepted manually, package it as a small deterministic regression gate. The deliverable should contain a database setup script, Session A script blocks, Session B script blocks, an orchestration manifest defining barriers, expected assertions, environment capture, and cleanup. The orchestration must advance on acknowledgements from completed SQL commands, not on sleep durations.

A vendor-neutral state machine is enough:

PSEUDOCODE: orchestration contract, not T-SQL

create_disposable_database()
assert(server_major_version == 16)
assert(compatibility_level == 160)
assert(RCSI == ON)
assert(snapshot_isolation_state == ON)
record(ADR, client_A_version, client_B_version)

reset_row_to_revision_1()
run_A(RCSI_A1); await A_READ_1 == 1
run_B(B_UPDATE_TO_2_AND_COMMIT); await B_COMMIT_ACK
run_A(RCSI_A2); assert A_READ_2 == 2; assert A_TRANCOUNT == 0

reset_row_to_revision_1()
run_A(SNAPSHOT_A1); await A_READ_1 == 1
run_B(B_UPDATE_TO_2_AND_COMMIT); await B_COMMIT_ACK
run_A(SNAPSHOT_A2); assert A_READ_2 == 1; assert A_TRANCOUNT == 0
run_A(SNAPSHOT_FRESH); assert A_READ == 2

reset_row_to_revision_1()
run_A(SNAPSHOT_BEGIN_ONLY); await A_BEGIN_ACK
run_B(B_UPDATE_TO_2_AND_COMMIT); await B_COMMIT_ACK
run_A(SNAPSHOT_FIRST_READ); assert A_READ == 2

reset_row_to_revision_1()
run_A(SNAPSHOT_CONFLICT_READ); await A_READ_1 == 1
run_B(B_UPDATE_TO_2_AND_COMMIT); await B_COMMIT_ACK
run_A(SNAPSHOT_CONFLICT_UPDATE)
assert ERROR_NUMBER == 3960
assert A_TRANCOUNT_AFTER_CLEANUP == 0

cleanup_disposable_database()

The gate fails on an incomplete barrier, unexpected option, wrong session isolation, wrong read value, missing 3960, unhandled exception, or open transaction. It should also fail closed when it cannot collect required evidence. That turns concurrency semantics into a regression test rather than an undocumented expectation.

Refonte’s discussion of automated database delivery checks covers version-controlled SQL and pipeline testing concepts. This playbook’s artifact is intentionally independent of Jenkins, GitHub Actions, GitLab CI, or any other runner. The critical asset is the schedule and assertion set; the CI product is merely the executor.

Preserve raw stdout/stderr or query results for both sessions, the setup and cleanup logs, and a machine-readable ledger of barrier transitions. Do not overwrite failed evidence with a rerun. A timing-based test that only “usually passes” is not adequate concurrency evidence; convert it to barrier-driven orchestration or keep the decision at HOLD.

Prepare a rollback and an owner-approved handover

The disposable lab ends by proving that no session remains in a transaction, disconnecting Sessions A and B, checking that no other user session is attached, then removing the database. Do not force-disconnect an unknown session just to make cleanup convenient.

-- Run from a separate administration connection after A and B are disconnected.
USE master;
GO

IF EXISTS
(
    SELECT 1
    FROM sys.dm_exec_sessions
    WHERE is_user_process = 1
      AND database_id = DB_ID(N'RcsiSnapshotLab')
      AND session_id <> @@SPID
)
    THROW 51001, 'Active user session remains in RcsiSnapshotLab. Cleanup is HOLD.', 1;
GO

ALTER DATABASE RcsiSnapshotLab SET READ_COMMITTED_SNAPSHOT OFF;
GO
ALTER DATABASE RcsiSnapshotLab SET ALLOW_SNAPSHOT_ISOLATION OFF;
GO
DROP DATABASE RcsiSnapshotLab;
GO

On SQL Server 2022, seeing all sessions through sys.dm_exec_sessions requires VIEW SERVER PERFORMANCE STATE. If the cleanup operator lacks sufficient permission to verify other connections, record HOLD rather than infer that the database is idle.

Production adoption is a separate, owner-approved change. The handover record should state the application path requiring consistent repeat reads, the intended session isolation, current and proposed database options, current ADR state, previous configuration, client/driver ownership, transaction-duration expectation, monitoring owner, 3960 retry owner, retry cap, external-side-effect policy, verification schedule, and rollback trigger. It should also say what this test did not cover: memory-optimized tables, distributed transactions, replication, linked servers, deadlock behavior, locking-hint alternatives, and arbitrary cross-row serializability.

Managed services can have different defaults and operational controls, so do not extrapolate this self-managed SQL Server 2022 fixture into Azure SQL or another platform. Refonte’s overview of cloud database operating differences distinguishes managed and self-managed operating models. For this acceptance record, the authoritative baseline remains the captured SQL Server 16.x environment.

Rollback ownership is as important as the forward setting. A database option can be technically reversible while the application has already come to depend on its semantics. Therefore rollback means restoring both the previous database configuration and the application’s previous isolation/retry behavior in a controlled release, then rerunning the appropriate verification, not merely flipping a switch.

Build database skills that connect SQL to concurrency evidence

This kind of acceptance work sits between SQL syntax, database operations, application transaction design, monitoring, and clear ownership. The technical review is stronger when the DBA can explain to a backend owner why “inside one transaction” is not precise enough, and the backend owner can state which concurrent commits may become visible. Refonte’s article on DBA communication and change ownership emphasizes cross-team communication as part of database work.

For learners building those foundations, Refonte Learning’s Database Administrator Essentials page lists a three-month program at 12–14 hours per week and includes SQL query optimization, performance tuning, and monitoring/maintenance among its published topics. Those published topics should not be read as a promise that the program teaches this exact SQL Server 2022 RCSI-versus-SNAPSHOT laboratory or error-3960 workflow.

The operational standard remains independent of any course: prove the required read view against an explicit interleaving, record the engine and session state that produced it, preserve the first-data-access boundary, and define what happens when a stale snapshot tries to write. An enabled database option is only a prerequisite. Acceptance comes from evidence that the chosen isolation contract and retry policy match the business unit of work.