A deadlock occurs when two or more processes or transactions block each other from continuing because each has locked a database resource that the other transaction needs. This article describes how to find deadlock in SQL Server or Azure SQL database.
Symptoms of deadlock situations on the application are typically below error.
Transaction (Process ID ) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.”
Various ways to check deadlock in SQL Server database
1. Enable Deadlock Detection: set the trace flag 1222 using the DBCC TRACEON command. This will cause SQL Server to capture deadlock information in the error log.
2. Query the SQL Server Error Log: The error log contains deadlock information in XML format. You can query the error log using the sys.sp_readerrorlog stored procedure and filter for deadlock messages.
3. Use SQL Server Management Studio: The SQL Server Management Studio has a graphical interface for deadlock detection. You can use the Activity Monitor to view deadlock information or the Profiler to capture deadlock information as it occurs.
4. Use DMV queries: Use below query to find out deadlock situation in a given SQL Server or Azure SQL database.
WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)
SELECT
D.session_id,
D.blocking_session_id,
D.database_id,
T1.text AS blocking_statement,
T2.text AS blocked_statement
FROM sys.dm_exec_requests D
CROSS APPLY sys.dm_exec_sql_text(D.sql_handle) T1
JOIN sys.dm_exec_connections C
ON D.blocking_session_id = C.session_id
CROSS APPLY sys.dm_exec_sql_text(C.most_recent_sql_handle) T2
WHERE D.blocking_session_id <> 0;
5. Use the SQL Server Profiler: You can use the SQL Server Profiler to capture a trace of the events leading up to a deadlock, including the TSQL commands being executed, the database objects being accessed, and the resources being locked.
We have seen various ways to find deadlock in SQL Server databases.
How to resolve deadlock in SQL Server
The only way to resolve the deadlock in SQL Server or Azure SQL database is to terminate one of the processes involved. This should free up the resources for another process to continue. Moreover, this happens automatically when SQL Server detects a deadlock and kills off one of the processes. To kill a process manually, we can run the below command on the SQL Server database.
KILL <sessionid>
Here, sessionid is the id of the process you want to choose as a deadlock victim. You can get this id from the above SQL query (pt. 4).
Pro tips:
1. Learn how to grant access to Azure SQL database using various authentication options.
2. Read how to find stored procedures referring to a particular table in it.
3. Learn how to implement transaction batching in SQL Server transactions.
4. Refer to this post to know how to find long running queries in Azure SQL database.
See more
Kunal Rathi
With over 13 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.