How to capture deadlock using extended events?

This article will guide you How to capture deadlock using extended events?

What is deadlock?

Deadlock is a situation in SQL Server where two or more transactions are waiting for each other to release a resource, such as a table or a row, that is locked by the other transaction. This results in a circular dependency between the transactions, causing them to become stuck in an infinite loop and unable to proceed.

How to capture deadlock using extended events?

Below script can help to capture deadlock using extended events?

CREATE EVENT SESSION [deadlock_capture] ON SERVER 
 
ADD EVENT sqlserver.xml_deadlock_report
 
ADD TARGET package0.event_file (SET filename = N'deadlock_capture')
    WITH (
            MAX_MEMORY = 4096 KB
            , EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS
            , MAX_DISPATCH_LATENCY = 30 SECONDS
            , MAX_EVENT_SIZE = 0 KB
            , MEMORY_PARTITION_MODE = NONE
            , TRACK_CAUSALITY = OFF
            , STARTUP_STATE = OFF
            )
GO

Once the extended events session is created, you can start it by right-clicking on the session and selecting “Start Session”. The session will now capture deadlock events. You can monitor live trace data in SSMS window and also export data once trace stopped. If there is deadlock it will show in the trace data along with deadlock graph.

How to resolve deadlock issue once we get details in above deadlock trace?

SQL Server automatically detects deadlocks and chooses one of the transactions as the victim, rolling it back and allowing the other transaction to proceed. The victim transaction is chosen based on a set of rules that prioritize which transaction is less important to the system or has caused less impact. Once the victim transaction is rolled back, the other transaction is able to proceed and complete its work.

To prevent deadlocks from occurring, it’s important to design transactions in a way that minimizes the time they hold locks on resources, and to use locking hints, such as NOLOCK, that allow multiple transactions to read data without locking it. Additionally, tuning database and query performance can help reduce the likelihood of deadlocks by reducing the time that transactions need to hold locks.

Sometime simple select and update query getting deadlock in application. If deadlock are frequent, you can use UPDATE lock query hint in select query which can avoid this deadlock problem.