Indexed view deadlocking
- by Dave Ballantyne
Deadlocks can be a really tricky thing to track down the root cause of. There are lots of articles on the subject of tracking down deadlocks, but seldom do I find that in a production system that the cause is as straightforward. That being said, deadlocks are always caused by process A needs a resource that process B has locked and process B has a resource that process A needs. There may be a longer chain of processes involved, but that is the basic premise. Here is one such (much simplified) scenario that was at first non-obvious to its cause: The system has two tables, Products and Stock. The Products table holds the description and prices of a product whilst Stock records the current stock level. USE tempdb
GO
CREATE TABLE Product
(
ProductID INTEGER IDENTITY PRIMARY KEY,
ProductName VARCHAR(255) NOT NULL,
Price MONEY NOT NULL
)
GO
CREATE TABLE Stock
(
ProductId INTEGER PRIMARY KEY,
StockLevel INTEGER NOT NULL
)
GO
INSERT INTO Product
SELECT TOP(1000) CAST(NEWID() AS VARCHAR(255)),
ABS(CAST(CAST(NEWID() AS VARBINARY(255)) AS INTEGER))%100
FROM sys.columns a CROSS JOIN sys.columns b
GO
INSERT INTO Stock
SELECT ProductID,ABS(CAST(CAST(NEWID() AS VARBINARY(255)) AS INTEGER))%100
FROM Product
There is a single stored procedure of GetStock:
Create Procedure GetStock
as
SELECT Product.ProductID,Product.ProductName
FROM dbo.Product
join dbo.Stock
on Stock.ProductId = Product.ProductID
where Stock.StockLevel <> 0
Analysis of the system showed that this procedure was causing a performance overhead and as reads of this data was many times more than writes, an indexed view was created to lower the overhead.
CREATE VIEW vwActiveStock
With schemabinding
AS
SELECT Product.ProductID,Product.ProductName
FROM dbo.Product
join dbo.Stock
on Stock.ProductId = Product.ProductID
where Stock.StockLevel <> 0
go
CREATE UNIQUE CLUSTERED INDEX PKvwActiveStock on vwActiveStock(ProductID)
This worked perfectly, performance was improved, the team name was cheered to the rafters and beers all round. Then, after a while, something else happened…
The system updating the data changed, The update pattern of both the Stock update and the Product update used to be:
BEGIN TRAN
UPDATE...
COMMIT
BEGIN TRAN
UPDATE...
COMMIT
BEGIN TRAN
UPDATE...
COMMIT
It changed to:
BEGIN TRAN
UPDATE...
UPDATE...
UPDATE...
COMMIT
Nothing that would raise an eyebrow in even the closest of code reviews. But after this change we saw deadlocks occuring.
You can reproduce this by opening two sessions. In session 1
begin transaction
Update Product
set ProductName ='Test'
where ProductID = 998
Then in session 2
begin transaction
Update Stock
set Stocklevel = 5
where ProductID = 999
Update Stock
set Stocklevel = 5
where ProductID = 998
Hop back to session 1 and..
Update Product
set ProductName ='Test'
where ProductID = 999
Looking at the deadlock graphs we could see the contention was between two processes, one updating stock and the other updating product, but we knew that all the processes do to the tables is update them. Period. There are separate processes that handle the update of stock and product and never the twain shall meet, no reason why one should be requiring data from the other.
Then it struck us, AH the indexed view.
Naturally, when you make an update to any table involved in a indexed view, the view has to be updated. When this happens, the data in all the tables have to be read, so that explains our deadlocks. The data from stock is read when you update product and vice-versa.
The fix, once you understand the problem fully, is pretty simple, the apps did not guarantee the order in which data was updated. Luckily it was a relatively simple fix to order the updates and deadlocks went away. Note, that there is still a *slight* risk of a deadlock occurring, if both a stock update and product update occur at *exactly* the same time.
