Can Deadlocks be Resolved by Adding an Index? Yes. Really, I’m not kidding. Here, I’ll show you how.
First, let me set the scene: One of the applications I support has had issues with deadlocks for some time now. The deadlocks are really a result of design issues that it would be too expensive to fix. One of the challenges of the current design is that some of the data is partitioned the old fashioned way, in multiple tables, based on request type. The challenge of this design is it means there are two tables that have a sequential integer that is shared between them. The idea is to be able to query both tables, union the result sets together in sequence as though they had been in the same table. To accomplish this task a third table was added to track the sequence number as an identity column. A stored procedure inserts a record into the table, grabs the identity value and then deletes the inserted row. The deadlocks have been happening in the stored procedure that grabs that identity value.
Digging into the issue, my first step was to reproduce the deadlock to make sure I had a grasp of what was going on. I will include the code here in case anyone wants to follow along at home.
Step 1 is to create a table:
1 2 3 4 5 6 7 8 9 10 11 12 | IF EXISTS( SELECT * FROM sys.tables WHERE name = 'FriendlyNumberSeed' ) DROP TABLE dbo.FriendlyNumberSeed GO CREATE TABLE dbo.FriendlyNumberSeed LastCaseNumber int IDENTITY(1,1) NOT NULL, DateRetrieved datetime NOT NULL GO |
Step 2 is to insert some data and create some locks. Run this code in the same window:
1 2 3 4 | BEGIN TRANSACTION INSERT dbo.FriendlyNumberSeed SELECT GETDATE() AS DateRetrieved |
Step 3 is to create more data and more blocking. Run this code in a separate window:
1 2 3 4 | BEGIN TRANSACTION INSERT dbo.FriendlyNumberSeed SELECT GETDATE() AS DateRetrieved |
Step 4 is to create some blocking. Run this in the first window:
1 2 | DELETE dbo.FriendlyNumberSeed WHERE LastCaseNumber = SCOPE_IDENTITY() |
Step 5 is to create the deadlock. Run this code in the second window:
1 2 | DELETE dbo.FriendlyNumberSeed WHERE LastCaseNumber = SCOPE_IDENTITY() |
During my testing I had a few seconds between setting up the deadlock and the deadlock monitor reacting to it so I was able to pull the “All Transactions” report by right clicking on the database in Management Studio and selecting Reports > Standard Reports > All Transactions.
Here are the results I saw:
Locks before adding an index
I spent a while digging into this, even breaking out DBCC PAGE to look at the page structure to try to figure out what was happening. After working through several wild theories, I looked at the locks again. That’s when it hit me: Why would the first transaction try to grab an update lock on the row inserted by the second transaction? There are no indexes on the table.
To delete a record from a table with no indexes SQL Server has to do a table scan, meaning it has to touch every single row to see if they qualify. To avoid deadlocks SQL Server grabs an update lock before reading the record. In this case the update lock is prevented by the exclusive lock still held by the insert. To see if I was on the right track I looked at the plan for the delete.
Here it is:
Query plan showing table scan while performing a delete
At this point I am pretty sure I am on the right track so it is time for more testing. The next thing I did was to put ROLLBACK WORK commands in both of my query windows to make sure that both transactions were completely out of the way. I then ran the following code:
1 2 3 4 | CREATE CLUSTERED INDEX IX_FriendlyNumberSeed_LastCaseNumber ON dbo.FriendlyNumberSeed (LastCaseNumber) WITH (FILLFACTOR=100) GO |
With the index in place I re-ran steps 2-5 above and everything worked perfectly. Let’s look at why that happened. The first step is to look at the new query plan for the delete after the index create:
Query plan for delete statement with index yielding a seek
The final proof that I have fixed the deadlock by adding an index is to look at the transactions report. If the index fixed the problem the report should show that the update lock has disappeared. Here is the report:
Locks after index was added
The update lock is gone and so is the deadlocking. Adding an index to the table has resolved my deadlock problem. The test that I did here used a clustered index but the same results would be available with a non-clustered index.
I know this was a long post. Thanks for sticking with me all the way to the end.
