How to Find and Prevent Deadlocks in SQL Server

SQL Server deadlocks occur when two or more transactions hold locks that the others need, creating a cycle in which none of the transactions can continue. SQL Server detects the deadlock and automatically chooses one transaction as the victim so the others can proceed.

Quick Answer: To find SQL Server deadlocks, start with the built-in system_health Extended Events session on SQL Server or Azure SQL Managed Instance. It captures deadlock graphs automatically. For Azure SQL Database, use Extended Events with the sqlserver.database_xml_deadlock_report event. Analyze the deadlock graph to identify the statements, resources, indexes, and transaction order involved, then fix the underlying locking pattern rather than repeatedly killing sessions.

What Is a SQL Server Deadlock?

A deadlock is different from ordinary blocking. With blocking, one transaction waits for another transaction to release a resource. With a deadlock, two or more transactions form a circular dependency.

For example:

1. Transaction A locks Table A and requests a resource in Table B.
2. Transaction B already holds the required resource in Table B and requests a resource in Table A.
3. Neither transaction can continue without the other releasing a lock.
4. SQL Server detects the cycle and selects one transaction as the deadlock victim.

The application can then receive an error similar to:

Transaction (Process ID ...) was deadlocked on lock resources with another process
and has been chosen as the deadlock victim. Rerun the transaction.

Deadlock vs. Blocking

A long-running query can cause blocking without creating a deadlock. Conversely, a deadlock can happen even when individual queries are not especially slow. Treat these as related but different troubleshooting problems.

How to Find Deadlocks in SQL Server

1. Check the system_health Extended Events Session

For SQL Server, the built-in system_health Extended Events session captures deadlock information, including deadlock graphs. This is usually the first place to look because you do not need to create a separate trace just to start collecting deadlocks.

In SSMS, open:

Management
  → Extended Events
    → Sessions
      → system_health
        → View Target Data

Look for xml_deadlock_report events. SSMS can display the captured deadlock graph so you can inspect the participating sessions and locked resources.

2. Query the system_health Ring Buffer

You can also query the deadlock events captured by system_health:

SELECT
    xdr.value('@timestamp', 'datetime2') AS deadlock_time,
    xdr.query('.') AS event_data
FROM
(
    SELECT CAST(xet.target_data AS XML) AS target_data
    FROM sys.dm_xe_session_targets AS xet
    INNER JOIN sys.dm_xe_sessions AS xe
        ON xe.address = xet.event_session_address
    WHERE xe.name = N'system_health'
      AND xet.target_name = N'ring_buffer'
) AS XML_Data
CROSS APPLY target_data.nodes(
    'RingBufferTarget/event[@name="xml_deadlock_report"]'
) AS XEventData(xdr)
ORDER BY deadlock_time DESC;

The resulting XML contains the deadlock graph, including the victim, participating processes, and resources involved in the cycle.

3. Use Extended Events for targeted capture

Extended Events is the recommended tracing technology for deadlock investigation. For a targeted session, capture the xml_deadlock_report event and write the results to an appropriate target such as an event file.

This is particularly useful when you need to correlate deadlocks with application activity or collect information over a longer period.

4. Find current blocking with DMVs

DMVs can help identify what is blocked right now, but they are not a replacement for a captured deadlock graph. For current blocking, inspect request and session information such as session_id, blocking_session_id, wait information, and open transactions.

SELECT
    r.session_id,
    r.blocking_session_id,
    r.status,
    r.wait_type,
    r.wait_time,
    r.wait_resource,
    DB_NAME(r.database_id) AS database_name,
    r.command,
    t.text AS sql_text
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE r.blocking_session_id <> 0
ORDER BY r.wait_time DESC;

This query is useful for investigating an active blocking chain. If the deadlock has already been resolved, use the captured deadlock graph instead.

5. Use Query Store to investigate the queries

After identifying the queries involved in a deadlock, use Query Store where available to examine their execution history and plans. This can help determine whether a plan change, missing index, inefficient access pattern, or increased workload is contributing to the problem.

How to Find Deadlocks in Azure SQL Database

Azure SQL Database does not use the SQL Server instance-level system_health session in the same way as SQL Server. Instead, use database-scoped Extended Events to capture deadlock information.

Microsoft recommends capturing the sqlserver.database_xml_deadlock_report event when collecting deadlock graphs in Azure SQL Database.

You can also configure Azure Monitor alerts for deadlock events when you want to be notified that deadlocks are occurring rather than discovering them only during an incident.

How to Analyze a Deadlock Graph

Do not stop at identifying the session that was chosen as the victim. The victim is not necessarily the root cause. Analyze the entire deadlock graph.

Look for:

• Victim process: the transaction SQL Server selected to terminate.
• Process list: the sessions and statements participating in the deadlock.
• Resource list: the locks and database resources involved.
• Object and index information: the tables, indexes, keys, pages, or other resources involved in the cycle.
• Transaction order: the sequence in which each transaction acquired and requested resources.

The key question is: why did these transactions acquire resources in an order that allowed a circular dependency?

Common Causes of SQL Server Deadlocks

Inconsistent access order

One transaction updates Table A and then Table B, while another updates Table B and then Table A. Standardizing the order in which related resources are accessed can remove the cycle.

Long-running transactions

The longer a transaction holds locks, the greater the opportunity for another transaction to encounter a conflicting lock. Keep transactions focused and avoid unnecessary work inside the transaction.

Missing or ineffective indexes

An inefficient access path can cause a statement to touch more rows or resources than necessary. Review the execution plans and indexing strategy of statements involved in recurring deadlocks.

Large data modifications

Large UPDATE or DELETE operations can hold locks for longer periods. Where appropriate, process large changes in controlled batches and commit them separately.

Application transaction handling

Application code that opens transactions too early, performs unrelated work while a transaction is active, or does not consistently commit and roll back transactions can increase locking problems.

How to Prevent SQL Server Deadlocks

1. Access shared resources in a consistent order. Make related transactions acquire locks in the same logical sequence.
2. Keep transactions short. Do not perform unnecessary processing, network calls, or user interaction inside a database transaction.
3. Use appropriate indexes. Reduce the amount of data that statements need to scan or lock.
4. Batch large modifications when appropriate. Smaller transactions can reduce lock duration and rollback scope.
5. Review isolation levels and locking behavior. Choose the isolation model that fits the workload instead of relying on lock hints as a general fix.
6. Implement application retry handling. Deadlocks can legitimately occur in concurrent systems. Applications should handle SQL Server deadlock error 1205 and retry the transaction when the operation is safe to retry.
7. Capture and analyze recurring deadlocks. Repeatedly killing sessions does not address the underlying concurrency pattern.

Should You Use KILL to Resolve a Deadlock?

Usually, no. SQL Server automatically detects a deadlock and chooses a victim transaction, releasing its locks so the remaining transaction can continue.

The KILL command can terminate a session involved in blocking, but it is not a general solution for recurring deadlocks. If a deadlock has already been detected and resolved by SQL Server, killing another session afterward does not fix the cause.

KILL <session_id>;

Use KILL deliberately when investigating an active blocking situation and only after confirming that terminating the session is appropriate.

Deadlock Troubleshooting Checklist

• Capture the deadlock graph.
• Identify every session and resource in the cycle.
• Review the statements and execution plans.
• Check whether transactions acquire resources in different orders.
• Check transaction duration and unnecessary work inside transactions.
• Review indexes and access paths.
• Check isolation levels and lock hints.
• Implement safe retry handling for deadlock error 1205.
• Monitor recurring deadlocks instead of treating each incident as an isolated failure.

Summary

SQL Server deadlocks are concurrency problems that should be diagnosed from the complete deadlock graph rather than by focusing only on the transaction chosen as the victim. For SQL Server and Azure SQL Managed Instance, the built-in system_health Extended Events session is a useful starting point. For Azure SQL Database, use database-scoped Extended Events and capture sqlserver.database_xml_deadlock_report.

The long-term fix usually involves transaction design, consistent resource access order, appropriate indexing, shorter transactions, and application-level retry handling.

Pro Tips

See more

Kunal Rathi

With over 15 years of experience in data engineering and analytics, I've assisted countless clients in gaining valuable insights from their data. As a dedicated supporter of Data, Cloud and DevOps, I'm excited to connect with individuals who share my passion for this field. If my work resonates with you, we can talk and collaborate.