SQL Server Deadlocks

Techniques to identify and resolve them!

Deadlocks in SQL Server and Azure SQL can significantly impact performance and often become a contention between those providing a database solution and those customizing it. In this article, I will explore how to effectively identify and resolve deadlocks.

Understanding locks vs. deadlocks

Before defining deadlocks, it is essential to distinguish them from standard locks, as they are often confused.

When a transaction T2 requests a lock on a resource already locked by another transaction T1, transaction T2 must wait until transaction T1 releases the lock. In this scenario, T2 is blocked but not in a deadlock state. A deadlock occurs only when two or more transactions block each other, preventing any of them from proceeding. Deadlocks are resolved almost immediately; the SQL Server engine detects them automatically and terminates one of the involved transactions to free up resources. Standard locks, however, can theoretically persist indefinitely. By default, transactions in SQL Server and Azure SQL do not have a timeout. They may wait indefinitely unless a timeout is set using the LOCK_TIMEOUT option or a DBA manually intervenes.

The following picture shows the concept of locking: a car has acquired a lock on the “ferry exit” resource, and other cars must wait until the lock is released.

Misconceptions about deadlocks

• Deadlocks are bugs of SQL Server: False! Deadlocks are not bugs but normal occurrences when multiple transactions compete for the same resources
• Deadlocks cannot be prevented: False! While deadlocks cannot be eliminated in concurrent systems, their frequency can be significantly reduced through proper transaction management, consistent resource access patterns, and effective indexing strategies
• Using NOLOCK on all SELECT statements prevents deadlocks: False! The NOLOCK query hint ignores existing locks, behaving like the READ UNCOMMITTED isolation level. It allows reading uncommitted, potentially inconsistent data. It is not a deadlock solution but a trade-off with significant side effects
• Adding covering indexes for every query prevents deadlocks: False! Indexes can reduce locking and improve performance, but they are not a universal solution for deadlocks. Their use must be evaluated on a case-by-case basis

What are deadlocks?

A deadlock is a specific type of lock that occurs when two or more tasks permanently block each other, creating a cyclic dependency situation. In other words, each task waits for another task to release a resource it needs, but none of them can proceed. This infinite waiting cycle prevents all involved transactions from advancing unless one of them is forcibly terminated to break the cycle.

Example of a cyclic dependency

Consider two transactions, T1 and T2, executing data modification commands:

1. T1 acquires a lock on row 1
2. T2 acquires a lock on row 2
3. T1 attempts to acquire a lock on row 2 but is blocked because T2 already holds a lock on that row
4. T2 then tries to acquire a lock on row 1 but is blocked because T1 already holds a lock on that row

Neither transaction can proceed because each is waiting for the other to release the lock it needs. This creates a cyclic dependency, where T1 depends on T2, and vice versa. Without external intervention, both transactions would remain blocked indefinitely.

Source: https://x.com/ladataplatform/status/1608859212053975040?s=46

SQL Server Lock Monitor

The Lock Monitor is a system process responsible for detecting and resolving deadlocks. It performs periodic scans to identify blocked transactions, analyze the involved resources, and follow the blocking chain to check for a deadlock. By default, the Lock Monitor scans every 5 seconds, but if deadlocks are detected, the scan interval decreases to as low as 100 milliseconds to identify new deadlocks more quickly.

Get Sergio Govoni’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

When a deadlock is detected, one of the transactions is selected as the “victim” and terminated with SQL error 1205 allowing the other to complete. How Is the Deadlock Victim Chosen?

• The transaction with the lowest priority (DEADLOCK_PRIORITY) is terminated first
• If both transactions have the same priority, the one with the lowest rollback cost is terminated

Configuring DEADLOCK_PRIORITY

Developers and DBAs can influence deadlock resolution by setting the DEADLOCK_PRIORITY session option. This option allows assigning a low, normal, or high priority to transactions within a session. The following T-SQL code demonstrates setting this option:

-- Increases the chance that this session will be terminated in a deadlock scenario
SET DEADLOCK_PRIORITY LOW;

-- Decreases the chance that this session will be terminated
SET DEADLOCK_PRIORITY HIGH;

Tools for identifying deadlocks

To identify deadlocks in SQL Server, the following monitoring tools are available:

• Extended Events (recommended): Provides an efficient way to track and analyze deadlocks
• SQL Profiler: Probably the older tool but still useful for analyzing transaction behavior and detecting deadlocks
• Trace Flags 1204 and 1222: Provide detailed deadlock information in SQL Server’s error log but they are not recommended for high-load systems
• Event Notifications: Allow receiving alerts on deadlock events

Once a deadlock is identified, analyzing the deadlock graph is crucial for understanding the involved resources and conflicting transactions. This helps pinpoint the root causes and implement corrective actions.

Best practices for deadlock prevention

While deadlocks cannot be completely avoided in a multi-user system, several strategies can significantly reduce their occurrence. Following best practices for coding and transaction management minimizes the risk of deadlocks:

• Consistent resource access order: When multiple transactions need to access the same objects, ensuring they follow a consistent order reduces cyclic dependencies
• Avoid user interaction within transactions: Delaying transaction completion due to user input increases deadlock risk
• Reduce transaction duration: The longer a transaction holds a lock, the higher the likelihood of deadlocks
• Adopt row versioning-based isolation levels when possible: Isolation levels like READ_COMMITTED_SNAPSHOT and SNAPSHOT reduce read locks, thereby lowering deadlock occurrences

Summary

Deadlocks in SQL Server are not bugs but a natural consequence of resource contention in high-concurrency environments. Preventing deadlocks requires careful resource access planning, maintaining a consistent order, and adopting row versioning-based isolation levels when applicable.

Tools such as Extended Events, SQL Profiler, Trace Flags, and First Responder Kit are essential for detecting and monitoring deadlocks; they provide detailed insights that help developers and DBAs optimize concurrency management and improve overall database performance.