This is a followup question from: http://stackoverflow.com/questions/7684477/is-it-possible-to-set-transaction-isolation-level-snapshot-automatically
I'm still having deadlock/timout situations in the ASP.NET application when running large reports simultaneously although
So i have two questions:
- How can i check if the Transaction Isolation Level Snapshot is working as expected/at all?
- I'm assuming that the foreign keys(in the tables of the Web-Application to the report-tables) are responsible for deadlocks. I found this interesting article:
Note SQL Server acquires shared locks when validating foreign keys, even if the transaction is using read committed snapshot (read committed using row versioning) or snapshot isolation level. Be mindful of this when examining deadlock graphs from transactions when these transaction isolation levels are used. If you see shared locks, check to see whether the locks are taken on an object that is referenced by a foreign key.
How can i check if the FK are really responsible for the Deadlock/Timeout situations, does that mean i could delete those foreign keys to prevent deadlocks(what would be an acceptable effort)?
Note: I'm only reading from the tables which cause deadlocks.
Any thoughts on this topic are greatly appreciated.
Edit Here is a Deadlock-Graph. Maybe somebody could help me to understand what causes the deadlock. It seems that it occured without any reports running only caused by the web-application, when two transactions want to write the same table(one update and one insert, the insert is as Stored-Procedure). Why does it aquire page locks and how to enable row locks only? The Insert-SP uses already
TRANSACTION ISOLATION LEVEL REPEATABLE READ.
I have a strong suspicion that two triggers(one update and one insert) are responsible for the deadlocks. Here is the insert-trigger:
CREATE TRIGGER [dbo].[CreateRMAFiDates] ON [dbo].[RMA] AFTER INSERT AS BEGIN SET NOCOUNT ON; UPDATE RMA SET [fiCreationDate]=(SELECT idDate FROM tdefDate WHERE CONVERT(VARCHAR, INSERTED.Creation_Date, 112) = tdefDate.Text), [fiPopDate]=(SELECT idDate FROM tdefDate WHERE CONVERT(VARCHAR, INSERTED.POP_Date, 112) = tdefDate.Text), [fiManufactureDate]=(SELECT idDate FROM tdefDate WHERE CONVERT(VARCHAR, INSERTED.Manufacture_Date, 112) = tdefDate.Text) FROM INSERTED; END
So this trigger updates the RMA-Table what causes the update-trigger to fire(what does similar). Does the deadlock-graph confirms my assumption? I think i'll delete those triggers and create a SP that is running once a day what would be perfectly sufficient, because these column are for a SSAS-Cube(Molap) only.
Edit: By the way, there was no deadlock anymore since i deleted these triggers :)