SQL server deadlocks can be a burdensome headache for database administrators who need to figure out the root of the problem, as well as try to lessen their frequency while hunting down the app code that is causing deadlocks.
Frequent deadlocks can also make the database sluggish for end-users who generally expect SQL server queries to be quite fast, and leave you scrambling to do performance tunings like monitoring your SQL server disk space.
In this article, we’re going to take a look at exactly what SQL server deadlocks are, what causes them, and some useful ways you can reduce the number of deadlocks your database experiences.
An Explanation of SQL Server Deadlocks
In simple terms, an SQL server deadlock is when two transactions block the progress of each other, in what is known as a special concurrency problem in distributed server programming. A deadlock will occur when locks are placed on resources that are called to by multiple processes, and thus the processes are not able to complete.
It’s a bit like a cowboy-western movie standoff between the processes and the resources, as neither side is able to achieve victory, and so just come to a complete stop.
It’s a common occurrence because SQL server transactional processing is technically designed to have many things happening at once, and several transactions happening at once can cause a deadlock by creating a circle of dependencies. You can check out this article for more in-depth examples of what exactly causes SQL server deadlock query.
In any case, after a deadlock / standoff, The SQL server will play arbiter and decide who will be the ‘victim’ in the cowboy standoff, and one of the processes will be killed. When this happens, the SQL server will raise an error along the call stack like this:
“Msg 1205, Level 13, State 51, Line 6”
Transaction (Process ID ) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.”
And what happens after that largely depends on how your app is designed to handle the error. In some cases the app might crash or become semi-functional, and other times the app may be able to just fluidly handle the error and perhaps even attempt the operation again more successfully.
Deadlocks are a coding problem
Some people are confused and might believe SQL server deadlocks are a natural thing and just happen when the database engine starts acting cranky, but that’s not entirely true.
Deadlocks come from application code combined with a database schema, which in turn results in cyclical dependency access patterns.
So in other words, your application code is what causes deadlocks, and it’s the role of the DB admin to thoroughly go over the code with the app developer to fix where the code is allowing deadlocks to happen.
The impact of SQL server deadlocks is obviously going to be the headache in figuring out what’s causing them, as well as decreased server performance when end-users are making queries to the database. But again, it’s a cause-and-effect problem with a root solution.
3 Types of Deadlock
- Order of Operations Deadlocks: These appear when exclusive locks are used, or locks escalate between different processes that need respective resources.
- Lookup Deadlock: It’s estimated that a majority of SQL server deadlocks typically encountered are in fact lookup deadlocks, and they can usually be easily resolved. Lookup deadlocks occur when a query includes a Lookup operation, and that causes the engine to retrieve multiple values from elsewhere beside the index for the specific query.
Parallelism Deadlock: If a query plan uses parallelism operations and the deadlock details include parallel exchange events, deadlocks will occur within the same process but on different threads.
Resolving and Preventing SQL Server Deadlocks
We have some helpful tips for resolving SQL server deadlocks, and you can consider this a checklist of sorts, but again, it’s going to boil down to your database admin putting their head together with the app dev to figure out the root of the problem.
Update the deadlock priority
This technique isn’t going to prevent deadlocks, but it does ensure that queries you give the most priority to will not be killed in the event of a deadlock. So you could use this code to set the deadlock priority for a query:
SET DEADLOCK_PRIORITY LOW / SET DEADLOCK_PRIORITY HIGH
The process with the lowest deadlock priority will then be the one chosen as the deadlock victim.
Configure the NOLOCK Hint
The NOLOCK hint will allow the SQL server to ignore locks that have been set by previous queries, which means it will read the data necessary for a query even if it’s been locked by another. This could result in the server interpreting “dirty data” if the data is updated while the query is being run.
Restrict simultaneous activities
To save yourself a bit of headache, and reduce the workload on the database, thus overall reducing the frequency of deadlocks, you can restrict the number of simultaneous activities. This would allow the SQL server to tap into more available resources for query activity.
However it’s really a temporary measure because ideally your SQL server should be running without any hiccups, so this method just reduces the stress on the server until you can figure out the root problem that is causing SQL server deadlocks.
Some additional tips to reduce deadlock frequency
It’s helpful to create indexes that will match your foreign key columns, because it can reduce the frequency of deadlocks that are caused by cascading referential integrity.
A covering index will reduce deadlocks that are caused by bookmark lookups.
You can also use TRY…CATCH logic to pinpoint the deadlock error number, and then retry the transaction.
