Does anyone have an example of how to create an "Update Conflict" using Transaction Level Snapshot (Not ReadCommmittedSnapshotIsolation)?
Error message example:
Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot isolation to access table ‘Food’ directly or indirectly in database 'FoodDatabase’ 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.
The following is not working:
create database FoodDatabase; alter database FoodDatabase SET ALLOW_SNAPSHOT_ISOLATION ON; create table dbo.food ( FoodId int primary key identity(1,1), FoodDesc varchar(255) null, ) insert into dbo.food values ('broccoli'), ('strawberry') Transaction Session 1: set transaction isolation level snapshot begin transaction -- do not commit transaction yet update Food set FoodDesc = 'Grape' where FoodId = 1 Transaction Session 2: set transaction isolation level snapshot begin transaction -- do not commit transaction yet update food update Food set FoodDesc = 'Lettuce' where FoodId = 1