1

I've inherited a SQL Server 2012 database that uses triggers to copy all changes made to dbo tables into an audit schema, providing functionality similar to system-versioned temporal tables.

One of these triggers has recently been causing deadlocks:

ALTER TRIGGER [dbo].[ClientBusinessUnitBehaviour_Trigger_Update] 
   ON  [dbo].[ClientBusinessUnitBehaviour] 
   AFTER UPDATE
AS

BEGIN

        SET NOCOUNT ON;

        INSERT INTO adt.[ClientBusinessUnitBehaviour] 
        SELECT getdate(),'Update', * FROM inserted

        UPDATE [a] 
        SET a.[RowVersion] = CASE WHEN ea.MaxRowVersion IS NULL THEN 0x01 ELSE CONVERT(VARBINARY(1), CONVERT(INT, ea.MaxRowVersion) + 1) END 
        FROM adt.[ClientBusinessUnitBehaviour] AS [a]
        LEFT JOIN (SELECT Id, MAX([RowVersion]) AS MaxRowVersion FROM adt.[ClientBusinessUnitBehaviour] WHERE Id IN (SELECT Id FROM inserted) GROUP BY Id) AS [ea] ON ea.Id = a.Id
        WHERE a.[RowVersion] IS NULL

END

I haven't seen actual SQL Server logs, but I can access reports that expose data about deadlocks, and these say that both processes involved in the deadlocks are running the second part of this trigger, the UPDATE statement.

Please could someone explain how this can cause a deadlock? My very limited understanding of deadlocks is that they normally occur when two processes are accessing objects in a different order, so I'm not sure how two processes running the same SQL can end up deadlocking.

The transaction isolation level is READ COMMITTED, and READ_COMMITTED_SNAPSHOT is on.

The audit tables don't have primary keys, so I suspect the problem may be similar to the one described here, but that doesn't seem to explain why there's a deadlock, instead of one process just blocking the other temporarily.

Having spoken to the original developer it's become clear that the UPDATE is not required, but I'd like to understand what the problem was.

  • Do you expect the update in the trigger to affect anything other than the rows that were just inserted by the trigger? Are you updating after the insert so you can avoid typing out the column names to put the calculated rowversion in the right spot? There seems to be a lot of convoluted logic here that should be able to be simplified away, but we don't know your business rules, so... – Aaron Bertrand Feb 18 at 19:09
2

When running the second statement, each session has already inserted rows in adt.[ClientBusinessUnitBehaviour], and has those new rows exclusively locked.

The UPDATE statement may need to read the new rows inserted by the other session to discover if they have a NULL RowVersion column.

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.