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:
- Bookmark lookup deadlock
- Range scan deadlock
- Reverse object order deadlock
- Cascading constraint deadlock
- 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
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)') 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:
- Using a covering index can help to lessen the likelihood of a deadlock induced by bookmark lookups.
- Creating indexes that correspond to your foreign vital columns can help you avoid deadlocks caused by cascading referential integrity.
- When writing code, it is beneficial to keep transactions as short as feasible and to access objects in the same logical order wherever possible.
- Consider selecting one of the isolation levels based on the row version. READ SNAPSHOT OR COMMITTED SNAPSHOT.
- The DEADLOCK_PRIORITY session variable specifies how important it is for the current session to continue processing if it is deadlocked with another session.
- 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.
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.