SQL Server Deadlock implementing High Concurrency in Stored Procedures
Could someone explain I'm having a hard time understanding Does anyone know how to I'm working with a deadlock scenario in SQL Server 2019 when two stored procedures are executed concurrently, and I'm struggling to resolve it... My stored procedures handle high-volume updates to a single table, which is resulting in a deadlock situation. The two stored procedures, `UpdateOrderStatus` and `UpdateInventory`, both try to update the `Orders` table and the `Inventory` table respectively. Here's a simplified version of both procedures: ```sql CREATE PROCEDURE UpdateOrderStatus @OrderId INT, @Status VARCHAR(50) AS BEGIN SET NOCOUNT ON; UPDATE Orders SET Status = @Status WHERE Id = @OrderId; -- Simulate some processing time WAITFOR DELAY '00:00:01'; END CREATE PROCEDURE UpdateInventory @ProductId INT, @Quantity INT AS BEGIN SET NOCOUNT ON; UPDATE Inventory SET Quantity = Quantity - @Quantity WHERE ProductId = @ProductId; -- Simulate some processing time WAITFOR DELAY '00:00:01'; END ``` I'm calling these procedures from a .NET Core application using Dapper. When two users attempt to execute these procedures at the same time, I receive the following deadlock behavior: ``` Deadlock found when trying to get lock; try restarting transaction. ``` I’ve attempted to address this by implementing retry logic in my application, but it doesn’t solve the underlying deadlock scenario. I've also tried setting the transaction isolation level to `READ COMMITTED` but it hasn’t made a important impact. After reviewing the execution plans, I noticed that both procedures are locking rows on the `Orders` and `Inventory` tables. I’m considering adding `WITH (ROWLOCK)` hints to my updates, but I’m not sure if that would be effective or if it could lead to performance degradation. Can anyone suggest best practices or strategies to handle this deadlock situation effectively? What changes can I make to my stored procedures or my calling mechanism to prevent these deadlocks from occurring? I'm using Sql LTS in this project. Any pointers in the right direction? Thanks, I really appreciate it! This issue appeared after updating to Sql LTS.