Geek Answers Handbook

How to identify deadlock in SQL Azure?

I have a two-instance Windows Azure role. From time to time, the transaction will end with SqlException with the following text

The transaction (process identifier N) has reached an impasse on locking resources with another process and was chosen as a victim of a deadlock. Restart the transaction.

Now I searched Google for a while and read this post about detecting deadlocks using SQL Server logs.

The problem is that...

How to do it in SQL Azure? What tools do I use to access the internal components of SQL Azure and get enough data?

+9
sql-server deadlock azure database-deadlocks azure-sql-database
source share
3 answers

Monitoring Azure SQL is more limited than SQL Server, but tools are becoming more accessible to you:

http://social.technet.microsoft.com/wiki/contents/articles/troubleshoot-and-optimize-queries-with-sql-azure.aspx

+4
source share

Run the following query to the "Master" database in the Azure SQL database,

 select * from sys.event_log where event_type='deadlock' and database_name='<Databasename>';

There was a performance issue in this request. If the timeout period has passed, try the following:

 SELECT * ,CAST(event_data AS XML).value('(/event/@timestamp)[1]', 'datetime2') AS TIMESTAMP , CAST(event_data AS XML).value('(/event/data[@name="error"]/value)[1]', 'INT') AS error ,CAST(event_data AS XML).value('(/event/data[@name="state"]/value)[1]', 'INT') AS STATE ,CAST(event_data AS XML).value('(/event/data[@name="is_success"]/value)[1]', 'bit') AS is_success ,CAST(event_data AS XML).value('(/event/data[@name="database_name"]/value)[1]', 'sysname') AS database_name FROM sys.fn_xe_telemetry_blob_target_read_file('dl', NULL, NULL, NULL) WHERE object_name = 'database_xml_deadlock_report'

The second query contains data in XML format related to the processes being performed. Good luck

+2
source share

Azure SQL Database now supports two ways to get xml lock reports. You can create an XE session with the db node with the database_xml_deadlock_report event to track them yourself, or you can change the sys.fn_xe_telemetry_blob_target_read_file call from the earlier answer to use 'dl' instead of 'el'. Dead ends are now routed to their own file, rather than being mixed with login events.

This MSDN article contains the latest information.

+1
source share

More articles:


All Articles