Why am I getting a snapshot isolation issue on INSERT?

by joshschreuder   Last Updated September 12, 2019 00:06 AM

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 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.



Related Questions