Snapshot isolation transaction aborted due to update conflict.

Question

To avoid deadlocks, we switched from ReadCommittedSnapshot isolation to SnapShot isolation for a SQL Server database at the database level and transaction level in the client code. Now, when two users perform concurrent operations on the database through the client, one of the clients get this error:

"Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot isolation to access table 'dbo.cust_table' directly or indirectly in database 'cust_database' 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."

What can we do to avoid deadlocks and update conflicts at the same time?

(The same code with Oracle database and Oracle client works without any issues with the default Read Committed Snapshot isolation level)

Answer 1

Hello

Ok, you might be confused: READ_COMMITED_SNAPSHOT and the transaction isolation level SNAPSHOT are different. 

When you set a database to READ_COMMITED_SNAPSHOT your database will add 16 bytes to each row in the table for version storing.  So whenever modifications to the data happen the readers are able to access a version of the row.  This is statement level meaning the phantom rows and repeatable reads within the default transaction level (now READ_COMMITTED_SNAPSHOT) are possible. 

SET TRANSACTION ISOLATION SNAPSHOT is different.  There is no version store initially, it is on request.  So modifications will create the versioning so readers will not be blocked.  This transaction level (similar to SERIALIZABLE without the blocking) phantom rows and non-repeatable reads are not possible.  SNAPSHOT isolation is transaction level.  With this transaction level you can run into problems when you have two (or more) writers, when the first transaction one creates a row version the next transaction comes along it also uses the version store.  When the first transaction tries to commit it's fine as the its version is up to date but as the second user/transaction goes to commit its transaction details (from the version it read) are now out of date.  The second transaction won't commit as it's not consistent.

So what your problem is you are using both methods but when you are setting the transaction isolation level (from your clients) to SNAPSHOT the READ_COMMITED_SNAPSHOT is ignored.  You need to ask whether your procedures require the snapshot equivalent of COMMITED or SERIALIZABLE?

Hope this helps

 

Rob