Unclear update conflict

Question

I have two questions:

1. Why do I get update conflict in this situation instead of just blocking:

-- prepare
drop database if exists [TestSI];
go
create database [TestSI];
go
alter database [TestSI] set READ_COMMITTED_SNAPSHOT ON;
alter database [TestSI] set ALLOW_SNAPSHOT_ISOLATION ON;
go
use [TestSI];
go
drop table if exists dbo.call_test;
create table dbo.call_test ( Id bigint CONSTRAINT [PK_Call] PRIMARY KEY CLUSTERED ( [Id] ASC ), additional int, incl int );
create index ix_Call on dbo.call_test ( additional ) include( incl );
insert into dbo.call_test select 1, 2, 3;
go

First session:

use [TestSI];
go
set transaction isolation level snapshot
begin tran

   UPDATE dbo.call_test SET additional = 22 WHERE [Id] = 1

And second session:

use [TestSI];
go
set transaction isolation level snapshot

   UPDATE dbo.call_test SET additional = 222 WHERE [Id] = 1

In the second session I get immediately:

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

This behavior I have as well if I update include column incl instead of nonclustered index key.

What impact does a nonclustered index have on update conflict in this situation? Why are locks not used in this situation?

2. And the second theoretical question:

How does SQL Server handle include columns update?

I mean how does SQL Server update all nonclustered index which have an include columns when we update this value? I don't see anything related in the query plan.

select @@version

Microsoft SQL Server 2016 (SP2) (KB4052908) - 13.0.5026.0 (X64) Mar 18 2018 09:11:49 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows 10 Pro 10.0 (Build 18363: ) (Hypervisor)

I checked this example on SQL Server 2019, and behavior on that server is as I expected: second session is locked. Is it a bug or I did something wrong?

Answer 1

Why do I get update conflict in this situation instead of just blocking

It is a product defect, which is fixed in SQL Server 2019.

A snapshot write conflict occurs when a snapshot transaction attempts to modify a row that has been modified by another transaction that committed after the snapshot transaction began.

The reason for the incorrect behaviour in your example is somewhat esoteric. The update plan uses something called Rowset Sharing. This means the Clustered Index Seek and Clustered Index Update share a common rowset.

This is an optimization so the Clustered Index Update does not need to locate the row to update via a normal seeking operation. The common rowset is already correctly positioned by the Clustered Index Seek. The update operator performs its work on the "current row" in the rowset.

This causes the erroneous message because the version of the row seen by the seek (the row before the uncommitted change) is shared with the update operator. The update sees that the row it is trying to update has changed and concludes (incorrectly) that an update conflict has occurred.

The correct behaviour can be obtained in many ways. One way to rewrite the update so that rowset sharing is not possible is to force the seek to use a different index. With different access methods, there is no common rowset to share:

UPDATE CT
SET CT.additional = 222
FROM dbo.call_test AS CT WITH (INDEX(ix_Call))
WHERE CT.Id = 1;

A more direct way is to use an undocumented and unsupported trace flag to disable the Rowset Sharing optimization (this is for demo purposes only, do not use it on a real database):

UPDATE dbo.call_test 
SET additional = 222 
WHERE [Id] = 1
OPTION (QUERYTRACEON 8746);

The plan looks the same as the original (the rowset sharing property is not exposed by default) but it will correctly block instead of throwing an update conflict error.

You can also avoid the error (and retain rowset sharing for the Clustered Index Update) by forcing a wide (per-index) update plan:

UPDATE dbo.call_test 
SET additional = 222 
WHERE [Id] = 1
OPTION (QUERYTRACEON 8790);

Encountering the bug requires rowset sharing and a base table update that also maintains secondary indexes (narrow, or per-row update).

If this behaviour is causing you real-world problems, you should open a support case with Microsoft.

---

Josh has correctly answered your second question. I will just add that you can see the nonclustered index maintenance on the Clustered Index Update operator in SSMS -- you need to look in the Properties window and expand the Object node:

Answer 2

2. And the second theoretical question:

How does SQL Server handle include columns update?
I mean how does SQL Server update all nonclustered index which have an include columns when we update this value? I don't see anything related in the query plan.

I'm not sure I understand what's going on with the first point, and I find the difference in behavior between SQL Server 2017 and 2019 to be even more interesting, but I can help remove the mystery here.

The nonclustered index updates are not displayed in the SSMS graphical execution plan, but you can see it mentioned in the XML:

  <Update DMLRequestSort="false">
    <Object Database="[TestSI]" Schema="[dbo]" Table="[call_test]" Index="[PK_Call]" IndexKind="Clustered" Storage="RowStore" />
    <Object Database="[TestSI]" Schema="[dbo]" Table="[call_test]" Index="[ix_Call]" IndexKind="NonClustered" Storage="RowStore" />

Also, Sentry One Plan Explorer puts a nifty little indicator on the update icon to let you know that nonclustered indexes are being updated "behind the scenes:"

This is called a "narrow update plan," at least colloquially (I don't see that in the official docs anywhere). You can see an example of the difference between narrow and wide update plans in this blog post from Paul White: Optimizing T-SQL queries that change data