Why am I getting "Snapshot isolation transaction aborted due to update conflict"?

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:

• The SNAPSHOT Isolation Level
• Data Modifications under Read Committed Snapshot Isolation

I came across this reply by a guy at Microsoft on a thread asking a similar question, and, I thought it was quite insightful:

Without a supporting index on CustomerContactPerson, the statement

DELETE FROM ContactPerson WHERE ID = @ID; Will require a "current" read of all the rows in CustomerContactPerson to ensure that there are no CustomerContactPerson rows that refer to the deleted ContactPerson row. With the index, the DELETE can determine that there are no related rows in CustomerContactPerson without reading the rows affected by the other transaction.

Additionally, in a snapshot transaction the pattern for reading data which you are going to turn around and update is to take an UPDLOCK when you read. This ensures that you are making your update on the basis of "current" data, not "consistent" (snapshot) data, and that when you issue the DML, it the data won't be locked, and you won't unwittingly overwrite another session's change.

I have received the update from our dev team. it seems that my understanding on the issue is correct.

Here is their explanation. SNAPSHOT isolation guarantees that you will see a single, consistent version of the database. When you read the CustomerContactPerson row in the beginning of the transaction, you will never be able to read a later version of the row. The DELETE on ContactPerson would require you to read a version of CustomerContactPerson row later than your transaction's snapshot, so you get an update conflict. It doesn't matter that you wouldn't really update the CustomerContactPerson row, reading it to validate a FK is treated just the same.

Besides, When the table scan meets the record that is affected by the other transaction, we can avoid the conflict by locking rows you intend to update as you read them.

Snapshot isolation, on the other hand, is truly optimistic because data that is to be modified is not actually locked in advance, but the data is locked when it is selected for modification. When a data row meets the update criteria, the snapshot transaction verifies that the data has not been modified by another transaction after the snapshot transaction started. If the data has not been modified by another transaction, the snapshot transaction locks the data, updates the data, releases the lock, and moves on. If the data has been modified by another transaction, an update conflict occurs and the snapshot transaction rolls back.

http://msdn.microsoft.com/en-us/library/ms345124.aspx