sql-server add tag
joshschreuder (imported from SE)
Given two tables


    KeyID   GroupID   Name  Active


    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:

CREATE PROCEDURE dbo.[usp_InsertRecords] (
	@journal dbo.ParentType READONLY,
	@journalItems dbo.ChildType READONLY,
	@tenantId INT
) AS
	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

  [1]: https://i.stack.imgur.com/bsLWD.png
  [2]: https://i.stack.imgur.com/iR0q4.png
Top Answer
Paul White (imported from SE)
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

This room is for discussion about this question.

Once logged in you can direct comments to any contributor here.

Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.