Why am I getting a snapshot isolation issue on INSERT?

Question

Given two tables

Parent

KeyID   GroupID   Name  Active

Child

KeyID   ParentID  Name

Child.ParentID is FKed to Parent.KeyID

We insert both Parent and Child in a single transaction.

If a different Parent row gets updated (eg. Active 1 -> 0) while the transaction is active, the Child INSERT fails with:

Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot isolation to access table 'dbo.Child' directly or indirectly in database 'Test' 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.

From what I can tell from Why am I getting "Snapshot isolation transaction aborted due to update conflict"? this is probably due to a full scan to verify the foreign key.

Indeed, removing the foreign key does allow the Child INSERT to complete as expected.

With that said, no amount of non-clustered indexes on the foreign key on Child table seem to be helping to resolve this issue, so I'm a bit at a loss of what to do.

We have RCSI turned on for this database and the transaction is running in Snapshot isolation mode.

Additional details

I have discovered this issue manifests when the insert to Child is larger than a given number of rows. At this point the query optimizer switches from a Nested Loops (Left Semi Join) to a Merge Join (Left Semi Join).

Apologies for not including the fact that multiple Child records are inserted for a single Parent record.

Working insert (20 child records):

Failing insert (50 child records):

Insert sproc is roughly this:

CREATE PROCEDURE dbo.[usp_InsertRecords] (
    @journal dbo.ParentType READONLY,
    @journalItems dbo.ChildType READONLY,
    @tenantId INT
) AS
BEGIN
    INSERT INTO dbo.Parent(GroupID, Name, Active, TenantId)
        SELECT GroupID, Name, Active, @tenantId FROM @journal

    DECLARE @JournalId INT = convert(int,scope_identity());

    INSERT INTO dbo.Child(ParentID, Name, TenantId)
        SELECT @JournalId, Name, @tenantId 
        FROM @journalItems j2

END
GO

And concurrent update would be something like:

UPDATE dbo.Parent Set Active = 0 WHERE KeyID = 1234 -- row not being inserted

Answer 1

Add an OPTION (LOOP JOIN) hint to the INSERT statement.

Or use a plan guide (or query store) to force the nested loops semi join plan shape.

You might find that OPTION (FAST 1) works as well.

The point is to avoid a merge semi join, where many (potentially all) of the referenced tables' rows are touched by the current transaction. If any parent row with a change (including creation) is encountered, an update conflict error is raised.