title: Trigger causing a deadlock?
body: <p>I'm running into a deadlock after I added a trigger. There is a <code>UserBalanceHistory</code> table which has one row for each transaction and an <code>Amount</code> column. A trigger was added to sum the <code>Amount</code> column and place the result in the related <code>User</code> table, <code>Balance</code> column.</p>
  
  <pre><code>CREATE TABLE [User]
  (
      ID INT IDENTITY,
      Balance MONEY,
      CONSTRAINT PK_User PRIMARY KEY (ID)
  );
  
  CREATE TABLE UserBalanceHistory
  (
      ID INT IDENTITY,
      UserID INT NOT NULL,
      Amount MONEY NOT NULL,
      CONSTRAINT PK_UserBalanceHistory PRIMARY KEY (ID),
      CONSTRAINT FK_UserBalanceHistory_User FOREIGN KEY (UserID) REFERENCES [User] (ID)
  );
  
  CREATE NONCLUSTERED INDEX IX_UserBalanceHistory_1 ON UserBalanceHistory (UserID) INCLUDE (Amount);
  
  CREATE TRIGGER TR_UserBalanceHistory_1 ON UserBalanceHistory AFTER INSERT, UPDATE, DELETE AS
  BEGIN
      DECLARE @UserID INT;
  
      SELECT TOP 1 @UserID = u.UserID
      FROM
      (
              SELECT UserID FROM inserted
          UNION
              SELECT UserID FROM deleted
      ) u;
  
      EXEC dbo.UpdateUserBalance @UserID;
  END;
  
  CREATE PROCEDURE UpdateUserBalance
      @UserID INT
  AS
  BEGIN
      DECLARE @Balance MONEY;
  
      SET @Balance = (SELECT SUM(Amount) FROM UserBalanceHistory WHERE UserID = @UserID);
  
      UPDATE [User]
      SET Balance = ISNULL(@Balance, 0)
      WHERE ID = @UserID;
  END;
  </code></pre>
  
  <p>I've also turned on <code>READ_COMMITTED_SNAPSHOT</code>:</p>
  
  <pre><code>ALTER DATABASE MyDatabase SET READ_COMMITTED_SNAPSHOT ON;
  </code></pre>
  
  <p>I have a parallel process running which is creating <code>UserBalanceHistory</code> entries, apparently if it is working on the same <code>User</code> at the same time, the deadlock occurs. Suggestions?</p>