3

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
  • What happen when you commit Transaction Session 1? – SqlWorldWide Nov 29 '17 at 22:01
  • nothing happens on my end, feel free to try code above on your computer – user129291 Nov 29 '17 at 23:40
  • 1
    I ran your scripts and session 2 was blocked by the uncommitted session 1 transaction. When I committed session 1, session 2 proceeded and raised the expected update conflict error. – Dan Guzman Nov 30 '17 at 2:03
2

I am using SQL Server version:

Microsoft SQL Server 2017 (RTM-CU1) (KB4038634) - 14.0.3006.16 (X64)

Run your scripts in this order and you will be able to simulate a Update Conflict.

Connection 1

CREATE DATABASE FoodDatabase;
GO
ALTER DATABASE FoodDatabase SET ALLOW_SNAPSHOT_ISOLATION ON;
GO 

USE [FoodDatabase];
GO

CREATE TABLE dbo.food
(
    FoodId int PRIMARY KEY IDENTITY(1,1),
    FoodDesc varchar(255) null,
);
GO

INSERT INTO dbo.food
VALUES ('broccoli'), ('strawberry');
GO

--Transaction Session 1:
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
BEGIN TRANSACTION -- do not commit transaction yet
UPDATE dbo.Food
SET FoodDesc = 'Grape' WHERE FoodId = 1;

Output:

(2 rows affected)

(1 row affected)

Connection 2

--Transaction Session 2:
USE [FoodDatabase];
GO
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
BEGIN TRANSACTION -- do not commit transaction yet
UPDATE dbo.Food
SET FoodDesc = 'Lettuce' WHERE FoodId = 1;

Session from connection 2 is blocked by connection 1. You can see details about the lock by using sp_whoIsActive written by Adam Machanic.

<lock_type>keylock</lock_type>
    <database_name>FoodDatabase</database_name>
    <hobt_id>72057594043105280</hobt_id>
    <schema_name>dbo</schema_name>
    <object_name>food</object_name>

Go back to Connection 1 window and run a COMMIT statement. You will see following error message in Connection 2 messages tab.

Msg 3960, Level 16, State 3, Line 6 Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot isolation to access table 'dbo.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.

Helpful resource:

Understanding the Available Transaction Isolation Levels

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy