SQL SERVER – Resolving Deadlock by Accessing Objects in the Same Order

In the previous blog post, Understanding SQL Server Deadlocks: A Beginner’s Guide, I mentioned that one way to avoid or resolve deadlock is by accessing objects in the same order. Lots of people reached out to me asking for an example. Well, here is an example of the same.

While we will be using the same example as the earlier blog post, we will include the entire code here again so you do not have to go back and forth between two blog posts.

Here is the script to generate the table for examples.

CREATE TABLE ProductSold
(
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(50),
    QuantitySold INT
);
--
INSERT INTO ProductSold (ProductID, ProductName, QuantitySold)
VALUES (1, 'Product A', 10),
       (2, 'Product B', 20),
       (3, 'Product C', 30);
--
CREATE TABLE UpdatedInventory
(
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(50),
    QuantityInStock INT
);
--
INSERT INTO UpdatedInventory (ProductID, ProductName, QuantityInStock)
VALUES (1, 'Product A', 100),
       (2, 'Product B', 200),
       (3, 'Product C', 300);

Next, we can create a deadlock by running one statement each from Session 1 and Session 2. Here is the T-SQL for the same.

Session 1: This represents Transaction A, which updates the ProductSold table and then attempts to update the UpdatedInventory table:

-- Session 1
BEGIN TRAN;
--
-- Transaction A acquires a shared lock
UPDATE ProductSold
SET QuantitySold = QuantitySold + 1
WHERE ProductID = 1;
--
-- Transaction A requests an exclusive lock
UPDATE UpdatedInventory
SET QuantityInStock = QuantityInStock - 1
WHERE ProductID = 1;

Session 2: This represents Transaction B, which updates the UpdatedInventory table and then attempts to update the ProductSold table:

-- Session 2
BEGIN TRAN;
--
-- Transaction B acquires a shared lock on
UPDATE UpdatedInventory
SET QuantityInStock = QuantityInStock - 10
WHERE ProductID = 1;
--
-- Transaction B requests an exclusive lock
UPDATE ProductSold
SET QuantitySold = QuantitySold + 10
WHERE ProductID = 1;

Here you can see that the one-time ProductSold table is updated first, and the second time it upgrades the UpdateInventory table first. This eventually leads to deadlock when the same tables are accessed in the same session later.

To avoid deadlocks, we’ll access the objects in the same order for both sessions: first ProductSold, then UpdatedInventory. We’ll also use stored procedures for data modifications.

Here is the stored procedure I have created. As there are only two tables, and both do similar operations, we can create a single Stored Procedure.

CREATE PROCEDURE UpdateProductSoldAndInventory1
    @ProductID INT,
    @Quantity INT
AS
BEGIN
    -- Update the ProductSold table
    UPDATE ProductSold SET QuantitySold = QuantitySold + @Quantity 
    WHERE ProductID = @ProductID;
    -- Update the UpdatedInventory table
    UPDATE UpdatedInventory SET QuantityInStock = QuantityInStock - @Quantity 
    WHERE ProductID = @ProductID;
END;

Now, let’s rewrite the sessions using these stored procedures:

Session 1:

BEGIN TRAN;
-- Use the first stored procedure
EXEC UpdateProductSoldAndInventory1 @ProductID = 1, @Quantity = 1;
COMMIT;

Session 2:

BEGIN TRAN;
-- Use the first stored procedure
EXEC UpdateProductSoldAndInventory1 @ProductID = 1, @Quantity = 10;
COMMIT;

By accessing the objects in the same order and using stored procedures for data modifications, we have minimized the chances of encountering deadlocks. I hope this blog post clarifies how we can resolve the post Accessing Objects in the Same Order. You can always connect with me on LinkedIn for follow-up questions.

If, after following everything, you still face issues with deadlocks or SQL Server performance tuning, you can always engage in Comprehensive Database Performance Health Check.

Reference: Pinal Dave (http://blog.SQLAuthority.com)