Update Conflict in snapshot isolation level in SQL server

Update conflict in SNAPSHOT isolation occurs when a transaction attempts to update a row that has been modified and committed by another transaction after the snapshot transaction started. SNAPSHOT uses row versioning stored in TempDB and implements optimistic concurrency control. When an UPDATE is issued, SQL Server compares the current row version with the version visible when the transaction began. If the versions do not match, SQL Server raises Error 3960 and aborts the transaction. This behavior prevents lost updates while allowing readers and writers to operate without blocking each other. In production systems, update conflicts are usually handled by retry logic in the application layer, and transactions should be kept short to minimize conflict frequency.

Let’s see the demo

Session 1

SET TRANSACTION ISOLATION LEVEL SNAPSHOT; GO BEGIN TRAN; SELECT * FROM Employee WHERE ID = 1; --commit;

Transaction is still open.

Session 2

Updating this same record

SET TRANSACTION ISOLATION LEVEL SNAPSHOT; GO BEGIN TRAN;  UPDATE Employee SET Salary = 80000 WHERE ID = 1; COMMIT;

Record update successfully

Session 3

Getting updated value.

Now we are running the session 1

We are getting still old value.

Now running the session 2 again with different value.

SET TRANSACTION ISOLATION LEVEL SNAPSHOT; GO BEGIN TRAN;  UPDATE Employee SET Salary = 60000 WHERE ID = 1; COMMIT;

Getting below error.