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
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.
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?