SQL Server: best practices for deadlock issues when inserting into two related tables with triggers?
I tried several approaches but none seem to work... I've been struggling with this for a few days now and could really use some help..... I am currently working with deadlock issues while inserting records into two related tables in SQL Server 2019. The tables `Orders` and `OrderDetails` have a one-to-many relationship, where each order can have multiple details. I have triggers on both tables that update a `LastModified` timestamp whenever a new record is inserted. The inserts are performed within a transaction that locks both tables. Here’s a simplified version of my insert statements: ```sql BEGIN TRANSACTION; INSERT INTO Orders (OrderID, CustomerID, OrderDate) VALUES (1, 'C001', GETDATE()); INSERT INTO OrderDetails (OrderDetailID, OrderID, ProductID, Quantity) VALUES (1, 1, 'P001', 5); COMMIT; ``` The triggers look like this: ```sql CREATE TRIGGER trg_UpdateLastModified ON Orders AFTER INSERT AS BEGIN UPDATE Orders SET LastModified = GETDATE() WHERE OrderID IN (SELECT OrderID FROM inserted); END; CREATE TRIGGER trg_UpdateLastModifiedDetails ON OrderDetails AFTER INSERT AS BEGIN UPDATE OrderDetails SET LastModified = GETDATE() WHERE OrderDetailID IN (SELECT OrderDetailID FROM inserted); END; ``` When I run the inserts concurrently from multiple sessions, I get deadlocks. The behavior I receive is: ``` Transaction (Process ID XX) was deadlocked on lock resources with another process and has been chosen as the deadlock victim.``` I’ve tried adding `WITH (NOLOCK)` hints on the select statements inside the triggers, but it hasn’t resolved the scenario. I am also considering implementing an `OUTPUT` clause and restructuring the transaction. Is there a best practice or pattern I can follow to avoid these deadlocks, especially when using triggers across related tables? Any advice would be greatly appreciated. I'm working on a web app that needs to handle this. Any ideas what could be causing this? This is part of a larger web app I'm building. Any examples would be super helpful.