11

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): Working insert

Failing insert (50 child records): Failing insert

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
  • If the update occurs while the insert transaction is open, the insert fails with a snapshot error. The update is on a row that does exist in the table, but is not part of the insert (ie. existing data). – joshschreuder Nov 26 '19 at 23:07
7
+50

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.

| improve this answer | |
  • 1
    Great answer, thank you! I did a quick and dirty benchmark, using default query plan (2 x Parent, 2000 x child, over 2000 runs): 89s (0.35s avg. per run). With the explicit loop join hint: 90s (0.36s avg. per run). So even with larger Child insert sets I did not see any significant difference in the time taken to verify the foreign key. – joshschreuder Nov 28 '19 at 22:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.