Unclear update conflict

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:

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