Read Committed Snapshot Isolation: Does Update Conflict Rollback appear as Deadlock?

Question

I have read committed snapshot isolation and allow isolation ON for my database. I'm still receiving a deadlock error. I'm pretty sure I know what is happening...

1. First transaction gets a sequence number at the beginning of its transaction.
2. Second one gets a later sequence number at the beginning of its transaction, but after the first transaction has already gotten its (second sequence number is more recent than first).
3. Second transaction makes it to the update statement first. When it checks the row versioning it sees the record that precedes both transactions since the first one hasn't reached the update yet. It finds that the row's sequence number is in a committed state and moves on it's merry way.
4. The first transaction takes it's turn and like the second transaction finds the same committed sequence number because it won't see the second one because it is newer than itself. When it tries to commit it finds that another transaction has already updated records that are trying to be committed and has to roll itself back.

Here is my question: Will this rollback appear as a deadlock in a trace?

Answer 1

To prevent deadlock enable both

ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT

ALTER DATABASE [BD] SET READ_COMMITTED_SNAPSHOT ON; ALTER DATABASE [BD] SET ALLOW_SNAPSHOT_ISOLATION ON;

here explain the differences http://technet.microsoft.com/en-us/sqlserver/gg545007.aspx

Answer 2

In a comment attached to the original question you said: "I'm just wondering if an update conflict will appear as a deadlock or if it will appear as something different." I actually had exactly these types of concerns when I started looking into using snapshot isolation. Eventually I realized that there is significant difference between READ_COMMITTED_SNAPSHOT and isolation level SNAPSHOT.

The former uses row versioning for reads, but continues to use exclusive locking for writes. So, READ_COMMITTED_SNAPHOT is actually something in between pure pessimistic and pure optimistic concurrency control. Because it uses locks for writing, update conflicts are not possible, but deadlocks are. At least in SQL Server those deadlocks will be reported as deadlocks just as they are with 'normal' pessimistic locking.

The latter (isolation level SNAPSHOT) is pure optimistic concurrency control. Row versioning is used for both reads and writes. Deadlocks are not possible, but update conflicts are. The latter are reported as update conflicts and not as deadlocks.

Answer 3

The snapshot transaction is rolled back, and it receives the following error message:

 Msg 3960, Level 16, State 4, Line 1
 Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot
 isolation to access table 'Test.TestTran' directly or indirectly in database 'TestDatabase' to
 update, delete, or insert the row that has been modified or deleted by another transaction.
 Retry the transaction or change the isolation level for the update/delete statement.