SQL Server deadlocks

Understanding and Avoiding Them

Deadlocks are a common problem in all relational database systems that can lead to errors, lost work and poor performance. A deadlock occurs when two or more transactions are waiting for each other to release a resource, such as a database row or page, that they both need to complete their work. Process A is waiting on process B and process B is waiting on process A - we have a deadlock!

SQL Server has a built-in mechanism to detect deadlocks and resolve them automatically. The lock monitor checks for them periodically (every 5 seconds by default), and when a deadlock is detected, SQL Server chooses one of the transactions as the victim (by default the one that is cheapest to roll back, unless you've set DEADLOCK_PRIORITY), rolls it back and returns error 1205 to the client, allowing the other transactions to proceed. The rollback keeps your data consistent, but the victim's work is lost unless your application catches the error and retries the transaction.

To avoid deadlocks, it's important to understand the conditions that can lead to them. One common cause is holding locks on too many resources for too long. For example, if one transaction locks row 1 and then tries to lock row 2, while another transaction has locked row 2 and is now waiting for row 1, neither can proceed and a deadlock occurs. Locking fewer resources reduces the chances of this happening: queries that are well indexed touch (and lock) only the rows they need, and row-level locks conflict less often than page-level or table-level locks. Lock escalation, which converts many row or page locks into a table lock, can make things worse, so keep an eye on it.

In addition, it's important to keep transactions as short as possible to minimize the time that resources are locked. This can be achieved by breaking long transactions into smaller ones, committing as soon as the work is done (most locks are held until the transaction ends), and adding indexes so statements complete quickly. In a multi-statement transaction, it can also help to take the locks you know you'll need up front, for example by reading with an UPDLOCK hint the rows you are going to update later, so that two sessions can't each hold a shared lock and then both try to upgrade it.

Vroom Performance Suite provides comprehensive tracking and analysis of deadlocks. These tools can help identify the transactions and resources involved in a deadlock, and provide insights into how to prevent them from occurring in the future.

Want some more details?

Ok, you asked!

Deadlocks occur when two or more transactions are waiting for each other to release a resource, such as a database row or page, that they both need to complete their work. In other words, the transactions form a circular chain of waits that can never resolve on its own.

Simple blocking (one transaction waiting for another that will eventually finish) is not a deadlock. It only becomes one when the transaction being waited on is itself waiting, directly or indirectly, on the first. The cause of a deadlock can typically be traced back to two main factors: inconsistent locking order and transactions that hold locks for too long.

Inconsistent locking

Inconsistent locking occurs when transactions acquire locks in a different order. For example, if transaction A locks resource X and then tries to lock resource Y, while transaction B locks resource Y and then tries to lock resource X, a deadlock can occur. This is because both transactions are waiting for the other to release the resource that they need to proceed, resulting in a circular waiting pattern.

Resource contention

Resource contention occurs when multiple transactions are attempting to access the same resources at the same time. Contention by itself only causes blocking, but the more transactions are competing for the same rows and the longer they hold their locks, the more likely it becomes that a circular wait forms. Poor indexing makes this worse, because scans lock far more rows than the query actually needs.

Other common causes

Deadlocks can also occur due to lock conversion (two sessions each hold a shared lock on the same row and both try to upgrade it to an exclusive lock to update it), because different queries reach the same data through different indexes (one through a nonclustered index and a key lookup, another through the clustered index), or because of parallel query execution. Long-running transactions don't cause deadlocks by themselves, but they make them far more likely.

To prevent deadlocks, it's important to use appropriate locking strategies, access objects in a consistent order, and keep transactions as short as possible. Additionally, monitoring and troubleshooting tools can help identify and resolve deadlocks in SQL Server. SQL Server itself also records deadlock graphs in the built-in system_health Extended Events session (look for the xml_deadlock_report event), so you can see exactly which statements and resources were involved even after the fact.

Want to mitigate deadlocks?

Access objects in a consistent order: If every transaction touches tables (and rows within them) in the same order, circular waits can't form. This is the single most effective fix for the classic two-transaction deadlock.

Use appropriate locking strategies: Lock only what you need. Good indexing keeps statements from locking rows they don't need, and in a multi-statement transaction an UPDLOCK hint on the initial read avoids lock-conversion deadlocks. Be careful with the ROWLOCK, PAGLOCK and TABLOCK hints, because forcing a granularity can hurt more than it helps.

Keep transactions as short as possible: Break long transactions into smaller ones and commit as soon as the work is done to minimize the time that resources are locked. Don't wait on user input or external calls while a transaction is open.

Use isolation levels appropriately: Use the appropriate isolation level for each transaction to balance concurrency and consistency. Higher levels such as REPEATABLE READ and SERIALIZABLE hold locks longer and make deadlocks more likely.

Consider enabling READ_COMMITTED_SNAPSHOT: This is a database-level option that makes the default READ COMMITTED level use row versioning instead of shared locks, so readers no longer block writers and writers no longer block readers. That removes a whole class of reader/writer deadlocks. It does not remove writer/writer deadlocks, and the row versions are stored in tempdb, so test it first.

ALTER DATABASE YourDatabaseName SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;

A note on NOLOCK: NOLOCK (or READ UNCOMMITTED) doesn't cause deadlocks, but it isn't a fix either. It allows dirty reads, and rows can be missed or read twice, so you trade deadlocks for incorrect results. Row versioning is the safer way to stop readers from blocking.

Retry on error 1205: Even a well-tuned system will see an occasional deadlock. Have your application catch error 1205 and retry the transaction a few times, so the victim's work isn't simply lost.

Monitor and troubleshoot deadlocks: These tools can help identify the transactions and resources involved in a deadlock, and can provide insights into how to prevent them from occurring in the future.

Should I do any deep dives on these strategies? Let me know, I'd be happy to dig in!