SQL 2008 R2: Trigger causing a deadlock?

The deadlock happens because you are accessing UserBalancestory -> UserBalancestory -> User whereas some other update is User -> UserBalance It's more complex than that because of lock granularity and index locks etc.

The deadlock happens because you are accessing UserBalancestory -> UserBalancestory -> User whereas some other update is User -> UserBalance It's more complex than that because of lock granularity and index locks etc. The root cause is probably a scan on UserBalancestory for UserID and Amount. I'd have an index on (UserID) INCLUDE (Amount) on UserBalancestory to change this SNAPSHOT isolation models can still deadlock: there are examples out there (One, Two Finally, Why not do it all in one to avoid different and multiple update paths? CREATE TRIGGER TR_UserBalancestory_1 ON UserBalance UPDATE, DELETE AS BEGIN DECLARE @UserID INT; UPDATE U SET Balance = ISNULL(t2.

Balance, 0) FROM ( SELECT UserID FROM INSERTED UNION SELECT UserID FROM DELETED ) t1 JOIN User U ON t1. UserID = u. UserID LEFT JOIN ( SELECT UserID, SUM(Amount) AS Balance FROM UserBalancestory GROUP BY UserID ) t2 ON t1.

UserID = t2. UserID; END.

I've implemented this but I'm still getting a deadlock. – Josh M. Jun 8 at 18:45 @Josh M.

: same deadlock graph? With index? With trigger?

– gbn Jun 8 at 18:46 Yes. I actually already had the index in place but neglected to include it in my question. – Josh M.

Jun 8 at 19:07.

I can't tell from your code, but if your business logic would allow the trigger to run asynchronously you could consider using Service Broker (Asynchronous Trigger ). I've followed a pattern like that in the when you start running into the "deadly embrace" with triggers deadlocking each other.

Change the clustered key to userid in your UserBalancestory table and drop the non-clustered index because you are using userid to access the table there is no reason to use an identity column for the clustered index as it will always force the non-clustered index to be used and then a read from the clustered index to the change the money value. Clustered indexes are best for range searches which is what you are doing when you sum the balance. Your present situation may cause SQL to request every data page in the table just to get the user payments, some fragmentation in the clustered index is offset by the contigiously(sp) linked pages for a single userid.

Changing the cluster and dropping the non-cluster will save time and memory. Do not run any stored proc from the trigger because it will lock the triggered table while the SP finishes. The balance table could be made from a view with a computed column (SO link here) on the UserBalance Test in a development system, and then test again!

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.