Snapshot isolation transaction aborted due to update conflict

Question

I encountered this error

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

When I run the below two transactions

Transaction 1

Set transaction isolation level snapshot
Begin Transaction
Update product set price =100 where id =1
commit transaction

Transaction 2

Begin Transaction
Update product set price =150 where id =1
commit transaction

Why I got this error and How I solve it?

Answer 1

Snapshot Isolation in SQL:

• The snapshot isolation is used to enhance concurrency for OLTP applications.
• Snapshot isolation uses an optimistic concurrency model. If a snapshot transaction attempts to commit modifications to data that has changed since the transaction began, the transaction will roll back and an error will be raised. You can avoid this by using UPDLOCK hints for SELECT statements that access data to be modified.
• Snapshot isolation must be enabled by setting the ALLOW_SNAPSHOT_ISOLATION ON database option before it is used in transactions.

The available choices for Isolation Level

• READ UNCOMMITTED
• READ COMMITTED
• REPEATABLE READ
• SNAPSHOT
• SERIALIZABLE

Read more at Snapshot Isolation in SQL Server

Snapshot isolation transaction aborted due to update conflict

Therefore, this Error "Snapshot isolation transaction aborted due to update conflict." occurs becaues you want to modify data using snapshot isolation, you try to update the same data that another transaction is updating at the same time.

You should know that Transaction 2 is blocked until Transaction 1 completes. so you should wait until Transaction 1 is completed, then run Transaction 2 again to avoid the blocking issue!

Begin Transaction
Update product set price =150 where id =1
commit transaction