How to fix Error 3960 on Windows Server 2025 / Windows 11

by

Troubleshooting Guide: Error 3960 – Snapshot Isolation Transaction Aborted Due to Update Conflict

Why This Error Happens

This error occurs when two or more concurrent transactions use snapshot isolation to read and then update the same row, and one transaction commits its changes before the other, causing SQL Server to detect a conflict and abort the later transaction to preserve data consistency. It is a core design behavior of snapshot isolation (not a bug), typically triggered by high-concurrency write patterns, long-running transactions holding stale row versions, or application logic that reads data under snapshot isolation and later attempts to update it without accounting for intervening changes. This can also surface more frequently if the tempdb version store is under pressure or if transaction scope/isolation levels are mismatched across application layers.

Step-by-Step Resolution Guide

Step 1: Identify the Conflicting Transactions and Queries

  • Query sys.dm_tran_active_snapshot_database_transactions to see active snapshot transactions.
  • Enable Extended Events or check SQL Server error logs for the exact table and session IDs involved.
  • Run SELECT * FROM sys.databases WHERE snapshot_isolation_state_desc = 'ON' to confirm which databases have snapshot isolation enabled.

Step 2: Review and Adjust Isolation Level Usage in Application Code

  • Check if the application explicitly sets SET TRANSACTION ISOLATION LEVEL SNAPSHOT before UPDATE statements on hot tables.
  • Replace snapshot isolation with READ COMMITTED SNAPSHOT for read-heavy operations, reserving true SNAPSHOT isolation only for reporting/read-only transactions.
  • Add retry logic (e.g., TRY...CATCH with exponential backoff) around UPDATE statements to gracefully handle Error 3960 when it occurs.

Step 3: Optimize Transaction Scope and Locking Strategy

  • Shorten transaction duration by committing changes as soon as possible instead of holding open transactions across multiple user interactions.
  • Use UPDLOCK or ROWLOCK hints on SELECT statements that precede an UPDATE within the same transaction to reduce conflict windows.
  • Review indexing on the affected table (e.g., in SSMS, right-click table > Properties > Indexes/Keys) to ensure UPDATE operations target narrow row sets efficiently.

Step 4: Monitor and Tune tempdb Version Store

  • Navigate to SQL Server Management Studio > Databases > tempdb > Properties > Files and confirm tempdb has adequate size and is split across multiple data files (ideally matching CPU core count up to 8).
  • Monitor version store usage via SELECT * FROM sys.dm_tran_version_store_space_usage.
  • Increase tempdb autogrowth settings or pre-size tempdb files to prevent version store bottlenecks under heavy snapshot isolation load.

Alternative or Verification Steps

  • To verify the fix, run a controlled concurrency test (two sessions updating the same row under snapshot isolation) and confirm no Error 3960 is thrown, or that retry logic successfully recovers the transaction.
  • If the error persists despite code changes, consider disabling snapshot isolation on the specific database (ALTER DATABASE [YourDB] SET ALLOW_SNAPSHOT_ISOLATION OFF) and switching to READ COMMITTED SNAPSHOT ISOLATION (RCSI) instead, which avoids update-conflict aborts entirely.