Snapshot Isolation Update Conflicts: Handling Error 3960

Under snapshot isolation, readers see a consistent version without blocking writers, but two transactions still cannot change the same row independently. If another transaction commits a change after your snapshot began, your update can fail with error 3960. Handle the conflict by retrying the whole unit of work.

Check Snapshot Isolation Settings First

ALLOW_SNAPSHOT_ISOLATION enables explicit SET TRANSACTION ISOLATION LEVEL SNAPSHOT. READ_COMMITTED_SNAPSHOT changes the default read-committed behavior to use row versions for many reads. They are separate database options. Inspect both before testing, and enable snapshot only in a lab where the application impact is understood.

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

I do not infer isolation from a query plan. The database options and the session's transaction isolation level both matter. What isolation level did the failing connection actually use when error 3960 occurred?

Prepare a Two-Window Reproduction

In a disposable test database, enable snapshot isolation and create one account row. Open two query windows against that database. Keep the window labels visible so the order is unambiguous. Do not run this against an application account table; the example is designed to create a deliberate conflict.

-- Run once in the lab database, outside active transactions.
ALTER DATABASE CURRENT SET ALLOW_SNAPSHOT_ISOLATION ON;
GO
DROP TABLE IF EXISTS dbo.SnapshotConflictDemo;
CREATE TABLE dbo.SnapshotConflictDemo
(
    AccountID int NOT NULL PRIMARY KEY,
    Balance decimal(12,2) NOT NULL
);
INSERT dbo.SnapshotConflictDemo VALUES (1,100.00);

The database option can take time to transition if active transactions exist. Wait until sys.databases reports ON before opening the two snapshot transactions. Leave READ_COMMITTED_SNAPSHOT unchanged for this test so the explicit snapshot rule is easy to see.

Let the First Window Read an Older Version

In window A, begin a SNAPSHOT transaction and read the balance. Keep the transaction open. The first data access establishes its view of the database. The row currently says 100.00. Do not issue the update yet.

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRAN;
SELECT Balance
FROM dbo.SnapshotConflictDemo
WHERE AccountID = 1;
-- Leave this transaction open while window B runs.

A long-lived snapshot retains row versions and can increase version-store pressure. Keep the lab test short and clean it up afterward. In a real application, shorten transactions where possible instead of leaving a screen open while a transaction waits.

Commit a Competing Change in Window B

In window B, update the same row and commit. This is the newer committed value. It does not wait for window A's read lock because the snapshot reader used a version. The test now has the required conflict: A's view began before B's committed update.

UPDATE dbo.SnapshotConflictDemo
SET Balance = Balance + 10.00
WHERE AccountID = 1;
SELECT Balance
FROM dbo.SnapshotConflictDemo
WHERE AccountID = 1;

The balance is 110.00 in window B. If another lab process touched the row, reset the sample and repeat. The exact values matter less than the order: A reads, B commits, A attempts to write.

Watch the First Window Fail and Roll Back

Return to window A and run the UPDATE, which fails on purpose. SQL Server detects that the row changed after A's snapshot began and raises error 3960. The transaction cannot simply continue from the stale snapshot; roll it back. Then start a new transaction to read the latest committed row and recompute the intended change.

UPDATE dbo.SnapshotConflictDemo
SET Balance = Balance + 5.00
WHERE AccountID = 1;
-- After error 3960, clean up in this session:
IF @@TRANCOUNT > 0 ROLLBACK;

The exact transaction state after an error depends on surrounding settings and handling, so a production CATCH should inspect XACT_STATE and roll back when active. Do not retry only the UPDATE inside the same snapshot transaction. That would still use the old view or an invalid transaction.

Compare Snapshot Isolation With Read Committed Snapshot

Under READ_COMMITTED_SNAPSHOT, each read-committed statement sees a versioned view, rather than one fixed snapshot for the entire transaction. Updates still coordinate through locks and use current write semantics. The same stale transaction-level update conflict pattern is specific to explicit SNAPSHOT isolation; RCSI has different blocking and consistency behavior.

I test the application's actual transaction sequence under both settings, including multiple SELECT statements. RCSI can make two reads in one transaction see different committed versions, while SNAPSHOT keeps one consistent view. Choosing between them is a correctness decision as well as a concurrency decision.

Retry the Whole Business Operation

A bounded retry catches error 3960, rolls back, waits briefly, and starts a fresh transaction. Re-read the row and recompute the update from current values. Keep external side effects outside the retried unit or make them idempotent; a retry can otherwise send a message twice.

DECLARE @attempt int = 0;
WHILE @attempt < 3
BEGIN
    SET @attempt += 1;
    BEGIN TRY
        SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
        BEGIN TRAN;
        DECLARE @balance decimal(12,2);
        SELECT @balance = Balance
        FROM dbo.SnapshotConflictDemo WHERE AccountID = 1;
        UPDATE dbo.SnapshotConflictDemo
        SET Balance = @balance + 5.00 WHERE AccountID = 1;
        COMMIT;
        BREAK;
    END TRY
    BEGIN CATCH
        IF XACT_STATE() <> 0 ROLLBACK;
        IF ERROR_NUMBER() <> 3960 OR @attempt = 3 THROW;
        WAITFOR DELAY '00:00:01';
    END CATCH;
END;

This example makes the read and calculation part of the retried transaction. A real transfer needs both debit and credit plus invariant checks in one unit. Log conflict counts; frequent retries suggest a hot-row design issue, not merely an exception to catch.

Keep Snapshot Isolation Transactions Short

Snapshot isolation reduces some reader-writer blocking by storing older row versions. Long transactions keep those versions alive and can put pressure on tempdb or the persistent version store, depending on the database configuration. A user transaction that waits for a web request or external service is a poor fit. Read, calculate, and commit within a short bounded unit. A conflict that retries repeatedly against one hot row can still be a design bottleneck.

For a counter or balance, consider whether a single atomic UPDATE under read committed semantics is enough, or whether the application truly needs a transaction-wide consistent read. Do not change isolation just to suppress error 3960 without checking the business invariants. A different isolation level changes what the transaction can observe.

Make Retries Safe for Side Effects

The sample loop retries only database reads and writes. In a real order workflow, an email, card charge, or queue message sent before COMMIT cannot be rolled back with the SQL transaction. Use an outbox pattern, idempotency key, or separate post-commit step so a retry does not repeat the outside action. Keep a maximum attempt count and surface the final failure to the caller.

I add a small varying backoff when many workers contend for the same row, and log the attempt number, error, and row key. If conflicts cluster on one record, redesigning that hot spot is better than raising the retry limit. The retry is a recovery mechanism for occasional concurrent edits, not a way to make conflicting writes disappear.

Related reading on this blog: Difference Between Read Committed Snapshot and Snapshot Isolation Level and How to Check Snapshot Isolation State of Database.

Error 3960 is not a request to retry one statement, it is a reason to restart the transaction.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.