Archives for June 2012 - Page 208

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.

Monthly Archives

Articles indexed in June 2012

Page 208/464 | < Previous Page | 204 205 206 207 208 209 210 211 212 213 214 215 | Next Page >

- by Dave Ballantyne

- by Cinzia Mascanzoni

- by Bob Rhubart

- by Maria Sandu

- by arungupta

- by Frederic Pariente

- by terrencebarr

- by JaneZhang(???)

- by JuergenKress

- by mseika

- by darcy

- by igor

- by Allen Gao

- by James Zhang

- by Allen Gao

- by JaneZhang(???)

- by ???02

- by llaszews

- by darcy

- by Kristin Rose

- by Divya Malik

- by heathervc

- by arungupta

- by user793553

- by ferhat

< Previous Page | 204 205 206 207 208 209 210 211 212 213 214 215 | Next Page >