SQL Server deadlocks seem to be inevitable for any project that has multiple users hitting the system at a time. As a developer who has played the role of a DBA quite often in my career, I’ve certainly been bit by the deadlock bug. It is important during development to know the potential pitfalls so it can be avoided as much as possible. This article isn’t meant to be the whole counsel on the topic of deadlocks but is laser focused on one cause: the clustered index scan. In my current project, this has been the culprit of deadlocks most of the time.
Quick definition of a clustered index scan: a table scan. In other words, SQL Server scans the table looking for a certain column value. The reason it causes deadlocks is that one request is updating or deleting using a clustered index scan and then another comes along with a request on the same table and when they scan the same record, they wait for each other. Of course that is the deadlock.
How to Create a Deadlock
For this article, I will be using the well-known AdventureWorks database (my specific version is AdventureWorksLT2008R2). I’ll be looking at just two tables: SalesLT.Product and SalesLT.ProductModel.
Here is the definition of Product:
This is ProductModel:
The Product table has a foreign key to the ProductModel table via the ProductModelID field.
Now that I have the tables defined, I can create a stored procedure that is sure to deadlock your users at some point. The scenario is this, a business requirement has come in that we need to be able to update the Product.ListPrice based on the ProductModel. So this is a possible approach in a stored procedure:
1: CREATE PROCEDURE [dbo].[UpdateProductByModelID] 2: @modelID int, 3: @listPrice money 4: AS 5: BEGIN 6: 7: UPDATE SalesLT.Product SET ListPrice = @listPrice 8: WHERE ProductModelID = @modelID 9: 10: END
What I’ve done is created a genuine deadlock scenario and here’s why:
Line 8 is limiting the update to ProductModelID = @modelID, so SQL Server will do a table scan on the ProductModelID column in the Product table. If more than one user does this at the same time and the same record is reached, one of the unlucky users will become the victim of the deadlock.
How to Fix a Deadlock
This problem can be seen in SQL Server Management Studio by looking at the estimated execution plan for the UpdateProductByModelID stored procedure.
Do the following to run the plan since a plan cannot be run on a stored procedure as scripted:
- Remove the following lines:
- ALTER or CREATE PROCEDURE
- AS BEGIN and it’s corresponding END
- Make the parameters DECLARES instead
- Remove the =NULL or OUTPUT parts
Now display the estimated execution plan by doing a CTRL+L or choosing the menu option Query -> Display Estimated Execution Plan option (for both, make sure the cursor is in the query window).
- If there are missing indexes, you will see that right away
- The most important thing to look for is a Clustered Index Scan
Here is the estimated execution plan that shows the clustered index scan (see the far right of the image):
You can hover over the clustered index scan to see more info:
The “Predicate” section tells you the problem: Product.ProductModelID
To fix the problem, we need to create a non-clustered index on the ProductModelID column. The effect of doing this will be a non-clustered index seek (which is more of a direct path to the data than a scan) and it will bypass the deadlock problem.
Here is the code for it:
CREATE NONCLUSTERED INDEX [IX_Product_ProductModelID] ON [SalesLT].[Product] ( [ProductModelID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
Now I’ll run the estimated execution plan again to get the following:
That should do it! No more deadlocks for this scenario.
General Rule for Avoiding This Deadlock
I gave an example of how to create a deadlock and fix it but what is the general rule to avoid clustered index scans? If you are doing an update or delete that has a WHERE clause (most will), make sure there is a non-clustered index on the fields in the clause. If the primary key is in the WHERE clause, it should already be indexed so you should be fine with that.
Most likely any DBA reading this article will scoff at my feeble explanation of this complex problem but hopefully developers can use the information here to prevent a common cause of deadlocks in their application.
For SQL Server for Developers training, please see the Intertech offering here: