Sql-server – Deadlock Explanation

deadlocksql server

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.

Best Answer

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.