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.