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 https://dba.stackexchange.com/questions/173091/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][1]][1] Failing insert (50 child records): [![Failing insert][2]][2] Insert sproc is roughly this: ```mssql 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: ```mssql UPDATE dbo.Parent Set Active = 0 WHERE KeyID = 1234 -- row not being inserted ``` [1]: https://i.stack.imgur.com/bsLWD.png [2]: https://i.stack.imgur.com/iR0q4.png
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][1]. [1]: https://sqlperformance.com/2014/06/sql-performance/the-snapshot-isolation-level