SQL Server – How to Create an Update Conflict in Snapshot Isolation Level

isolation-leveloptimizationsql servertransaction

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

Best Answer

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

Related Solutions

Postgresql – Read Committed Isolation Level

The difference lies between a query and a transaction. A transaction can contain any number of queries. To illustrate the difference, I set up a small example:

CREATE TABLE table_to_be_updated (
    id serial PRIMARY KEY,
    other_column text,
    column_changing text
);

INSERT INTO table_to_be_updated (other_column, column_changing)
VALUES
('value', 'old_value'),
('value', 'other_value'),
('nonvalue', 'doesnt matter');

Then run two transactions concurrently (issuing the commands one by one, the middle line wants to depict the timeline):

                                | <-- BEGIN;
                                |
                                |
                                |     UPDATE table_to_be_updated
BEGIN; -----------------------> |     SET column_changing = 'new_value'
                                |     WHERE
                                |         other_column = 'value' AND
                                |         column_changing = 'old_value';
                                |
                                |
SELECT column_changing -------> |     -- update not yet committed
FROM table_to_be_updated        |
WHERE other_column = 'value';   | <-- COMMIT;
                                |
                                |
SELECT column_changing -------> |
FROM table_to_be_updated        |
WHERE other_column = 'value';   |
                                |
                                |
COMMIT; ----------------------> |

Running these in READ COMMITTED isolation level, the first query returns a row with 'old_value', while the second one shows a row with 'new_value'. On an other run, I change the left-hand-side transaction isolation level:

SET transaction ISOLATION LEVEL REPEATABLE READ;

(The command must be the first statement in a transaction.) Now both SELECTs return the same rowset, while a third one after committing both transactions will show the new row.

Sql-server – How do these snapshot isolation level configurations interact on a SQL Server 2005 instance

As @AlexKuznetsov noted, SNAPSHOT and READ_COMMITTED_SNAPSHOT are two different isolation levels. As such, snapshot_isolation_state indicates the former, while is_read_committed_snapshot_on the latter. Here are a few comments that I found that summarize the differences between the two:

READ COMMITTED SNAPSHOT does optimistic reads and pessimistic writes. In contrast, SNAPSHOT does optimistic reads and optimistic writes.

~Bill Paetzke here

and ...

[READ_COMMITTED_SNAPSHOT] differs from the SNAPSHOT isolation level in that instead of providing a reader with the last committed version of the row that was available when the transaction started (SNAPSHOT ISOLATION), a reader gets the last committed version of the row that was available when the statement started (READ_COMMITTED_SNAPSHOT).

~Uri Dimant (emphasis mine), here

Best Answer

Related Solutions

Postgresql – Read Committed Isolation Level

Sql-server – How do these snapshot isolation level configurations interact on a SQL Server 2005 instance

Related Question