Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

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

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

5  
To check what is responsible for the Deadlocks you need to capture the deadlock graph. –  Martin Smith Oct 14 '11 at 9:01
    
@Martin: I've used this excellent articleto detect the deadlocks but i must wait until next tuesday because the web-application is only used twice per week. So i wanted to know if anybody else has experiences with deadlock situations caused by foreign keys although READ_COMMITTED_SNAPSHOT ON. –  Tim Schmelter Oct 14 '11 at 9:12
2  
Regarding the question in your bounty message about why FK validation happens under RC. It has to work this way or you could get constraint violations. See this series of blog posts –  Martin Smith Oct 18 '11 at 9:38
    
And if I'm reading that graph correctly it is two concurrent executions of RM2.dbo.RMA that are causing the deadlock. The WHERE clause is completely unsargable so will need a (parallel) scan but also seems odd that you would even run this concurrently as it is not parameterised and the Updates will affect the same rows. –  Martin Smith Oct 18 '11 at 10:03
3  
Actually RE: unsargability I read your query wrong to begin with. I thought that the WHERE CONVERT(VARCHAR, RMA.Manufacture_Date, 112) = tdefDate.Text); was on the UPDATE, I see now that it is on the sub query. Are your triggers missing a JOIN condition between RMA and INSERTED? –  Martin Smith Oct 18 '11 at 10:42

1 Answer 1

up vote 12 down vote accepted
+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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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