Free SQL Help for Devs, DBAs, and the Curious

In the world of SQL Server, understanding and managing blocking chains and deadlocks is crucial to maintaining optimal performance. Blocking chains occur when one process holds a lock on a specific resource, preventing another process from accessing it. On the other hand, deadlocks happen when two or more tasks permanently block each other by each having a lock on a resource which the other tasks are trying to lock. This blog post will delve into how to analyze and resolve these issues in SQL Server 2016, SQL Server 2017, MySQL 5.7, DB2 11.1, and Azure SQL.

Blocking chains can be identified in SQL Server using the sys.dm_os_waiting_tasks dynamic management view. This view returns information about the wait queue of tasks that are waiting on some resource. The following T-SQL code will return all current waiting tasks:

SELECT * FROM sys.dm_os_waiting_tasks;

To identify deadlocks, SQL Server provides a dedicated extended event named 'xml_deadlock_report'. This event fires whenever a deadlock occurs and a process is chosen as the victim. The following T-SQL code can be used to set up the event:

CREATE EVENT SESSION [DeadlockMonitor] ON SERVER 
ADD EVENT sqlserver.xml_deadlock_report
ADD TARGET package0.ring_buffer
GO

In MySQL 5.7, the SHOW ENGINE INNODB STATUS command is commonly used to analyze deadlocks. This command provides a snapshot of the internal InnoDB engine status, including the latest detected deadlock.

SHOW ENGINE INNODB STATUS;

For DB2 11.1, the MON_GET_LOCKS table function can be used to return information about locks held by all applications currently connected to the database. This function can be used in conjunction with the MON_GET_UNIT_OF_WORK table function to get a complete picture of the blocking situation.

SELECT * FROM TABLE(MON_GET_LOCKS(NULL, -2)) AS T;
SELECT * FROM TABLE(MON_GET_UNIT_OF_WORK(NULL, -2)) AS U;

Deadlock detection in Azure SQL is quite similar to SQL Server. The primary difference is that Azure SQL provides a built-in system view called sys.event_log to query deadlock events directly. This view contains all deadlock events with the associated XML deadlock graph.

SELECT * FROM sys.event_log WHERE event_type = 'deadlock';

In conclusion, understanding and managing blocking chains and deadlocks is crucial for database performance. Knowing how to analyze these situations across different SQL technologies, like SQL Server 2016, SQL Server 2017, MySQL 5.7, DB2 11.1, and Azure SQL, allows for more robust and efficient database management.