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