Here’s a real SQL Server deadlock example caused by two procedures accessing the same tables in reversed order.
An SQL Server Deadlock Example
Solving Deadlocks Caused by Concurrent Access to the Same Resources
We’ve talked about deadlocks before. This time, let’s walk through a concrete example.
Here’s a script you can use to create the sample tables:
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 produce a deadlock by running one statement from Session 1 and one from Session 2.
Session 1: this represents Transaction A, which updates the ProductSold table and then tries 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 tries 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;
Notice that in one session the ProductSold table is updated first and the UpdatedInventory table second, while the other session does it in the opposite order. When both sessions access the same tables in reverse order like this, the end result is a deadlock.
To prevent deadlocks like this, we access the objects in the same order in both sessions: ProductSold first, then UpdatedInventory. We’ll use stored procedures to make the data changes.
Need expert support for SQL Server?
Our senior database team supports SQL Server performance tuning, health checks, migrations, Remote DBA services and urgent operational issues.
Here’s the stored procedure we created. Since there are only two tables and both go through similar operations, a single stored procedure is enough for both.
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 two sessions to use this stored procedure:
Session 1:
BEGIN TRAN;
-- Use the stored procedure
EXEC UpdateProductSoldAndInventory1 @ProductID = 1, @Quantity = 1;
COMMIT;Session 2:
BEGIN TRAN;
-- Use the stored procedure
EXEC UpdateProductSoldAndInventory1 @ProductID = 1, @Quantity = 10;
COMMIT;
By moving the data changes into stored procedures and accessing the objects in a consistent order, we minimized the risk of deadlocks.
Related Reading
SQL Server High Availability
Let’s Design the Right HA Architecture for Uninterrupted Access
Aryasoft designs and implements the high availability architecture that best fits your business, from Always On to FCI to log shipping.
