First, a quick definition and example for those that don’t know what deadlocks are inside of a database.
A deadlock happens when two (or more) transactions block each other by holding locks on resources that each of the transactions also need.
Transaction 1 holds a lock on Table A.
Transaction 2 holds a lock on Table B.
Transaction 1 now requests a lock on Table B, and is blocked by Transaction 2.
Transaction 2 now requests a lock on Table A, and is blocked by Transaction 1.
Transaction 1 cannot complete until Transaction 2 is complete, and Transaction 2 cannot complete until Transaction 1 is complete. This is a cyclical dependency and results in what is called a deadlock. Deadlocks can involve more than two transactions, but two is the most common scenario.
If you scrub the intertubz for deadlock information you will find a common theme. Most people will write that deadlocks cannot be avoided in a multi-user database. They will also write about how you need to keep your transactions short, and to some that means having your stats and indexes up to date, rather than a good discussion over what a normalized database would look like.
(And before I go any further, let me offer you some advice. If you believe that constantly updating your stats is a way to prevent deadlocks in SQL Server, then you should find a new line of work. Actually, stay right where you are. That way people like me will continue to have jobs, cleaning up behind people such as yourself. Thanks.)
What causes deadlocks?
The database engine does not seize up and start deadlocking transactions because it happens to be tired that day. Certain conditions must exist in order for a deadlock to happen, and all of those conditions require someone, somewhere, to be using the database.
Deadlocks are the result of application code combined with a database schema that results in an access pattern that leads to a cyclical dependency.
That’s right. I said it. Application code causes deadlocks.
Therefore it is up to the database administrator to work together with the application developer to resolve deadlocks.
Another thing worth noting here is that deadlocking is not the same as blocking. I think that point is often overlooked. I have had several people explain that their database is suffering blocking all the time. When I try to explain that a certain amount of blocking is to be expected, I am usually met with, "Yeah, yeah, whatever. Can you just update the stats and rebuild my indexes so it all goes away?”
A better response would be, "Yeah, I know I need to look at my design, but can you rebuild the indexes for me right now, and see if that will help for the time being?" My answer would be, “Yes. Can I help you with your design?"
Oh, and you do not need large tables with indexes to facilitate a deadlock. Blocking and deadlocks can happen on small tables, as well. It really is a matter of application code, design, access patterns, and transaction isolation levels.
Look, no one likes to be told they built something horrible. And chances are when it was built it worked just fine, but as the data changes, so could the need for an updated design. So if you are a database developer, do not be offended if someone says, "We need to examine the design, the data, and the code.” It is just a simple fact that things change over time.
Here is a link to Bart Duncan's blog series that helps to explain deadlocking as well as the use of trace flag T1222. If you are experiencing deadlocks and want to turn this on now, simply issue the following statement:
DBCC TRACEON (1222, -1)
The flag will be enabled and will start logging detailed deadlock information to the error log. The details from this trace flag are much easier to understand than the original Klingon returned by T1204. Unfortunately, by using the DBCC, this trace flag will be lost after the next service restart. If you want the trace flag to be enabled and always running against your instance, you need to add -T1222 as a startup parameter to your instance.
Another method for seeing detailed deadlock information is to query the default Extended Event system health session. You can use the following code to examine deadlock details:
SELECT XEvent.query('(event/data/value/deadlock)') AS DeadlockGraph
FROM ( SELECT XEvent.query('.') AS XEvent
FROM ( SELECT CAST(target_data AS XML) AS TargetData
FROM sys.dm_xe_session_targets st
JOIN sys.dm_xe_sessions s
ON s.address = st.event_session_address
WHERE s.name = 'system_health'
AND st.target_name = 'ring_buffer'
) AS Data
AS XEventData ( XEvent )
) AS src;
There are additional ways to discover if deadlocks are happening, such as using SQL Server Profiler (or a server trace), as well as Performance Monitor (i.e., Perfmon) counters. Each of the methods above will be reset upon a server restart, so you will need to manually capture the deadlock details for historical purposes, if desired.
Resolving a deadlock requires an understanding of why the deadlocks are happening in the first place. Even if you know a deadlock has happened, and you are looking at the deadlock details, you need to have an idea about what steps are possible.
I’ve collected a handful of tips and tricks over the years to use to minimize the chances that deadlocks happen. Always consult with the application team before making any of these changes.
- Using a covering index can reduce the chance of a deadlock caused by bookmark lookups.
- Creating indexes that match your foreign key columns can reduce your chances of having deadlocks caused by cascading referential integrity.
- When writing code, it is useful to keep transactions as short as possible and access objects in the same logical order when it makes sense to do so.
- Consider using one of the row-version based isolation levels READ COMMITTED SNAPSHOT or SNAPSHOT.
- The DEADLOCK_PRIORITY session variable will specify the relative importance that the current session is allowed to continue processing if it is deadlocked with another session.
- You can trap for the deadlock error number using TRY…CATCH logic and then retry the transaction.
The impact of a deadlock on end-users is a mixture of confusion and frustration. Retry logic is helpful, but having to retry a transaction simply results in longer end-user response times. This leads to the database being seen as a performance bottleneck, and pressures the DBA and application teams to track down the root cause and fix the issue.
As always, I hope this information helps you when you encounter deadlocks in your shop.