Given two tables
KeyID GroupID Name Active
KeyID ParentID Name
Child.ParentID is FKed to
We insert both
Child in a single transaction.
If a different
Parent row gets updated (eg.
Active 1 -> 0) while the transaction is active, the
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
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.
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.
Insert sproc is roughly this:
CREATE PROCEDURE dbo.[usp_InsertRecords] (
@journal dbo.ParentType READONLY,
@journalItems dbo.ChildType READONLY,
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
And concurrent update would be something like:
UPDATE dbo.Parent Set Active = 0 WHERE KeyID = 1234 -- row not being inserted