We have two tables
- Parent (Id int identity, Date datetime, Name nvarchar)
- Child (Id int identity, ParentId int, Date datetime, Name nvarchar)
The Child having a foreign key relationship to the Parent.
We have enabled database level read committed snapshot isolation.
We only ever insert and delete rows for Parent and Child (no updates)
We have one process (transaction) which deletes old data from Child (and then Parent)
We have multiple other processes (transactions) which insert new data into Parent (and then Child)
The delete process regularly (but not all of the time) gets rolled back, even though the insert process does not insert new Child rows which refer to the Parent rows which the delete wants to delete – it simply creates new Parent rows and one ore more new Child rows which refer to the new Parent
The error when deleting the Parent rows is:
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.
I am aware the people suggest having an index on the foreign key column – we'd prefer not to have to do this ideally (for space/performance reasons) – unless this is the only reliably way to get this to work.
Noted this: https://stackoverflow.com/questions/10718668/snapshot-isolation-transaction-aborted-due-to-update-conflict
And pretty good article: https://sqlperformance.com/2014/06/sql-performance/the-snapshot-isolation-level
But neither of these gives me the understanding I would like to have 🙂
Best Answer
When deleting from the parent table, SQL Server must check for the existence of any FK child rows that refer to that row. When there is no suitable child index, this check performs a full scan of the child table:
If the scan encounters a row that has been modified since the delete command's snapshot transaction started, it will fail with an update conflict (by definition). A full scan will obviously touch every row in the table.
With a suitable index, SQL Server can locate and test just the rows in the child table that could match the to-be-deleted parent. When these particular rows have not been modified, no update conflict occurs:
Note that foreign key checks under row versioning isolation levels take shared locks (for correctness) as well as detecting update conflicts. For example, the internal hints on the child table accesses above are:
PhyOp_Range TBL: [dbo].[Child] Hints( READ-COMMITTEDLOCK FORCEDINDEX DETECT-SNAPSHOT-CONFLICT )Sadly this is not currently exposed in execution plans.
Related articles of mine: