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