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

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