What Is a Deadlock in SQL 

When we delete or change a record from the parent table, we encounter a deadlock. It causes locks to be placed on the child’s table to prevent orphan records. As a result, we must first alter data in the child table. When the SQL Server executes a query in parallel mode, multiple threads are created.

Deadlock occurs when two or more operations try to access resources locked by each other. This has a detrimental impact on database resources since the processes are trapped waiting for the other to finish indefinitely. The SQL Server will terminate the contention issue in this case.

What Are the Types of Deadlocks in SQL

The common types of deadlocks that occur in SQL servers are:

1. Bookmark lookup deadlock
2. Range scan deadlock
3. Reverse object order deadlock
4. Cascading constraint deadlock
5. Intra-query parallelism deadlock

What Are the Methods to Detect Deadlocks in SQL

In computer systems that use shared resources, deadlock detection is critical. It entails recognizing and marking situations in which two or more processes are obstructed. The resource allocation graph (RAG) and the wait-for graph (WFG) are the two basic methods for detecting deadlocks.

Resource Allocation Graph (RAG)

The RAG is a graphical representation of the current status of resource allocation and the processes that hold them. The graph’s nodes represent resources and processes, while the edges indicate the allocation relationship between them.

Figure-1: Nodes of RAG

Wait-For Graph (WFG)

The Workflow Diagram (WFG) is a graphical depiction of the interdependence between processes and the resources they are awaiting. The nodes of the WFG represent processes, and the edges represent resources. 

Each edge connects the process that is awaiting a resource to the process that is presently holding that resource.

Figure-2: Nodes and Edges of WFG

Code

The default Extended Event system health session can also be queried for detailed deadlock information. To examine deadlock information, use the following code:

SELECT XEvent.query('(event/data/value/deadlock)[1]') 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
              CROSS APPLY
TargetData.nodes
('RingBufferTarget/event[@name="xml_deadlock_report"]')
              AS XEventData ( XEvent )
      ) AS src;

How to Remove Deadlocks in SQL

Resolving a deadlock necessitates knowledge of why the deadlocks occur in the first place. The following steps can help you to remove a deadlock:

1. Using a covering index can help to lessen the likelihood of a deadlock induced by bookmark lookups.
2. Creating indexes that correspond to your foreign vital columns can help you avoid deadlocks caused by cascading referential integrity.
3. When writing code, it is beneficial to keep transactions as short as feasible and to access objects in the same logical order wherever possible.
4. Consider selecting one of the isolation levels based on the row version. READ SNAPSHOT OR COMMITTED SNAPSHOT.
5. The DEADLOCK_PRIORITY session variable specifies how important it is for the current session to continue processing if it is deadlocked with another session.
6. You can use TRY…CATCH logic to trap the deadlock error number and then retry the transaction.

Frequently Asked Questions

Can Deadlocks Lead to Data Corruption?

No, a deadlock will not lead to corruption. A deadlock occurs when two sessions each have an open transaction. Whatever has been committed is not a factor in the impasse.

Conclusion

End-users experience uncertainty and irritation as a result of a deadlock. Although retry logic is valid, retrying a transaction just results in longer end-user response times. As a result, the database is perceived as a performance bottleneck, putting pressure on the DBA and application teams to identify and resolve the problem.