Deadlocks often happen in intensively used transaction databases. RCSI (READ_COMMITTED_SNAPSHOT) transaction isolation level can improve the situation. But changing the transaction isolation level on a production database is not the best option (without proper testing) and is a good theme for another post. Though deadlocks can and should be handled on an application side, it is good to know how to fight them on the database level. In this post, I am going to share some simple ideas that worked for me on how you can fix the deadlocks. I am assuming that you already have some experience investigating deadlocks, and you know that the best way to investigate it is to get a deadlock graph. All examples here are very simple and used only to represent the logic of one of the processes that are involved in a deadlock, but they are similar to ones that I faced in real environments and fixed as described below.

I used a ‘StackOverflow2010’ database for my scripts. I downloaded it from here, but the original copy is here.

Performance

The idea is straightforward – the faster your code, the less chance that another process can deadlock it. At the same time, it can be the most difficult approach to implement. If a new index can be added, you are lucky. If you need to refactor the code, it may take a lot more time and additional tests to implement. My suggestion is to start with the most simple changes: indexes, non-sargable conditions in the WHERE clause, select fewer columns, etc. For example:

/* 1 performance optimisation using indexes and sargable parameters */
SELECT top 1 u.id  
FROM Users AS u INNER JOIN Comments AS c ON u.Id = c.UserId  
WHERE c.Score IS NOT NULL  
AND CAST(c.CreationDate as DATE) = '20100120'  
ORDER BY u.Id ASC;

Can be fixed like:

/*
AND c.CreationDate >='20100120' and c.CreationDate < '20100120'
*/

and by creating an index on ‘CreationDate’.

Split resources

If you can split the deadlocked resources from one transaction, you can fix the deadlock. Let’s look at a simple example here.

SELECT top 1 u.id  
FROM Users AS u INNER JOIN Comments AS c ON u.Id = c.UserId  
WHERE c.Score IS NOT NULL  
AND CAST(c.CreationDate as DATE) = '20100120'  
ORDER BY u.Id ASC;

In this case, we join two tables. SQL Server processes it in one transaction and keeps a lock on both of them. If another process also needs both tables in a different order, you can see a deadlock. The solution, in this case, can be to use a temp table like this:

SELECT u.Id INTO #UserID
FROM Users AS u;

SELECT top 1 u.id  
FROM #UserID AS u INNER JOIN Comments AS c ON u.Id = c.UserId  
WHERE c.Score IS NOT NULL  
AND CAST(c.CreationDate as DATE) = '20100120'  
ORDER BY u.Id ASC;

DROP TABLE #UserID;

Change lock order

If a sequence of queries (and locks) can be changed, it can help as well. Just make the lock order the same as in the conflict process. For example, in this case:

USE [StackOverflow2010]
GO
DECLARE @maxCreationDate DATETIME;
SELECT @maxCreationDate = MAX(c.CreationDate)
FROM Comments AS c;

UPDATE Users SET Age = Age+1;

order can be changed without breaking the logic:

USE [StackOverflow2010]
GO
UPDATE Users SET Age = Age+1;

DECLARE @maxCreationDate DATETIME;
SELECT @maxCreationDate = MAX(c.CreationDate)
FROM Comments AS c;

Make more resources

If a deadlock happens on a heap or a clustered index, creating a non-clustered index can save the situation. For example, in this case, to support a select statement, a non-clustered index can be used. We just create one more ‘table’ that can be used to select data instead of the table itself.

USE [StackOverflow2010]
GO

SELECT top 1 u.id  
FROM Users AS u INNER JOIN Comments AS es ON u.Id = es.UserId  
WHERE es.Score IS NOT NULL  
AND c.CreationDate >='20100120' and c.CreationDate < '20100120'
ORDER BY u.Id ASC;
/*new non-clustered index */
CREATE NONCLUSTERED INDEX [ix_Comments_UserId] ON [dbo].[Comments]
([UserId] ASC) INCLUDE([CreationDate],[Score]) 

As I mentioned already, it is not a comprehensive view of the deadlocks problem but I hope these simple ideas will help you to save some time fixing deadlocks.

See you in the next post!