18

This is a followup question from: https://stackoverflow.com/questions/7684477/is-it-possible-to-set-transaction-isolation-level-snapshot-automatically

I'm still having deadlock/timeout situations in the ASP.NET application when running large reports simultaneously although READ_COMMITTED_SNAPSHOT ON.

So I have two questions:

  1. How can i check if the Transaction Isolation Level Snapshot is working as expected/at all?
  2. 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 :)

migrated from stackoverflow.com Oct 17 '11 at 23:07

This question came from our site for professional and enthusiast programmers.

16
+50

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.

2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.