Can foreign keys cause deadlocks and hinder READ COMMITTED SNAPSHOT?

If the SQLCAT team says that FK validation is done using read-committed isolation, then they must know what they're talking about. Emphasis on validation. The real question is Why would a report trigger FK validation? Validation occurs on writes, and reports are supposed to be reads. Either your reports are causing writes, in which case snapshot isolation levels will help nothing, either the cause of the deadlock is different.

The only way to make progress is to capture the deadlock graph.

As for the other question, how can you check if you operate under snapshot isolation: look in sys.dm_tran_active_snapshot_database_transactions.


Foreign key validation has to occur under (locking) read committed for correctness. See Snapshot isolation: A threat for integrity? by Hugo Kornelis for details.

The deadlock graph shows two concurrent executions of RM2.dbo.RMA causing the deadlock. Your triggers are missing a join condition between RMA and inserted.

It seems likely that this is an oversight and your trigger is accidentally updating all rows in RMA so deadlocks are extremely likely to occur if there is more than one concurrent trigger execution.