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.
