Snapshot isolation transaction aborted due to update conflict in SQL Server due to FK checks - Part 2

Question

This work follows on from Snapshot isolation transaction aborted due to update conflict in SQL Server due to FK checks.

After looking at this excellent article (https://sqlperformance.com/2021/06/sql-performance/foreign-keys-blocking-update-conflicts#comment-167645)

I run this code to create the database:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Parent]
(
    [ParentID] [int] NOT NULL,
    [UpdateTime] [datetime] NOT NULL,

    CONSTRAINT [PK dbo.Parent ParentID] 
        PRIMARY KEY CLUSTERED ([ParentID] ASC)
                WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                      IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                      ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Child]
(
    [ChildID] [int] NOT NULL,
    [ParentID] [int] NULL,
    [UpdateTime] [datetime] NULL,

    CONSTRAINT [PK dbo.Child ChildID] 
        PRIMARY KEY CLUSTERED ([ChildID] ASC)
                WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                      IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                      ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Child] WITH CHECK 
    ADD CONSTRAINT [FK dbo.Child to dbo.Parent] 
        FOREIGN KEY([ParentID]) REFERENCES [dbo].[Parent] ([ParentID])
GO

ALTER TABLE [dbo].[Child] CHECK CONSTRAINT [FK dbo.Child to dbo.Parent]
GO

CREATE TABLE [dbo].[Dummy]
(
    [x] [int] NULL
) ON [PRIMARY]
GO

The database must allow snapshot isolation and have read committed snapshot enabled.

Populated the database as follows:

DELETE FROM dbo.Child
DELETE FROM dbo.Parent
GO

-- Insert parent rows
INSERT INTO dbo.Parent (ParentID, UpdateTime) VALUES (1, GETUTCDATE());
INSERT INTO dbo.Parent (ParentID, UpdateTime) VALUES (2, GETUTCDATE());
INSERT INTO dbo.Parent (ParentID, UpdateTime) VALUES (3, GETUTCDATE());

-- Insertion a child row
INSERT INTO dbo.Child select 101, 2, GetUTCDate()
go

Then run these 2 scripts (as directed):

-- Session 1 - part one (1st bit to run)
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION;
 
-- Ensure snapshot transaction is started
SELECT COUNT_BIG(*) FROM dbo.Dummy AS D;

-- Session 1 - part two (3rd bit to run)
DELETE FROM dbo.Parent WHERE ParentID = 3

-- Session 2 - part one (2nd bit to run)
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRANSACTION;

UPDATE dbo.Child
SET UpdateTime = GETUTCDATE()
WHERE ParentID = 1

INSERT INTO dbo.Child 
    SELECT 201, 2, GetUTCDate()

-- Session 2 - part two (4th bit to run)
COMMIT TRANSACTION;

and Session 1 generates the update error as expected:

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

According to the article this should be eliminated by changing the PK to non-clustered and adding a unique clustered index but the same error is generated. Doing this should get round the FK issue but does not seem to even though the execution plan implies that it should.

Why is this now the case?

Thanks Ian

Answer 1

Your issue is that the foreign-key ParentID does not have an index, so every DELETE on Parent needs to scan the whole Child table to ensure there are no FK consistency issues. This is causing locking conflicts in SNAPSHOT isolation, and in other isolation levels will cause deadlocks.

Add the index to Child:

CREATE INDEX IX_Parent ON Child (ParentID);

You will see that the locking conflict has disappeared.

It is essential that all primary keys and foreign keys have an index (with those columns as the leading keys). If you are missing an index on the foreign key then you will get locking issues on UPDATE and DELETE against the primary key of the parent table. If you are missing an index on the primary key then you will get locking issues on INSERT and UPDATE against the child table.

You can add other columns as part of the key or as INCLUDE, but the PK or FK must be the leading column in the index.

You can see the effect of the index in this fiddle.

---

Changing the primary key on Parent to non-clustered and adding another clustered key on the same columns is a bad idea and completely pointless.

The particular example in that article refers to a case where there are two unique keys on each table, and a foreign key between one set. In that case, there may be instances where creating two separate indexes is wise. However, you should always have an index on all primary, unique and foreign keys.