Deadlock Queries

Question

We have enabled Log settings via log Analytics in Azure SQL DB.

Recently we are getting multiple deadlock issues in the database and we need to check the logs and exact timings for the deadlocks and the equivalent queries/tasks during that deadlock time via log analytics.

Can anyone help me with the process and query?

Answer 1

Below is an example query to get deadlock reports logged by telemetry. The report will include the inputbuffer of the processes involved in the deadlock as well as the resources involved.

--Run this the context of the master database.   
 WITH deadlock_telemetry AS (  
	SELECT CAST(event_data AS XML) AS event_data  
	FROM sys.fn_xe_telemetry_blob_target_read_file('dl', null, null, null)  
 )  
 SELECT   
	event_data.value('(/event/@timestamp)[1]', 'DateTime2') AS EventTimestamp,  
	event_data.query('/event/data[@name=''xml_report'']/value/deadlock') AS deadlock_xml_report,  
	event_data.query('/event/data[@name=''database_name'']/value').value('(/value)[1]', 'sysname') AS DatabaseName  
 FROM deadlock_telemetry  
 WHERE   
	CAST(event_data.value('(/event/@timestamp)[1]', 'DateTime2') as datetime2)  > DATEADD(day, -1, SYSDATETIME()); --filter on last 24 hours  

Answer 2

We are running the same SP parallelly which changes the schema of the table from one to another.
So we wanted to know how to handle this in SP , is there a way in which we can check if the schema is in lock & then enter a wait period & then check again & once the lock is removed then to procced with rest of the execution.

An application lock would be the most straight forward way to serialize the concurrent ALTER SCHEMA processes, IMHO. Concurrent DDL operations are prone to deadlocks on meta-data and underlying system tables.

Below is example code that uses a transaction-scoped application lock and retries after 5 seconds if the lock cannot be granted. This logic can be incorporated into your existing proc. The debug messages are not necessary but useful for unit testing via SSMS.

SET XACT_ABORT ON;  
DECLARE   
	 @return_code int  
	,@AlterSchemaCompleted bit = 0;  
	  
BEGIN TRY  
	WHILE @AlterSchemaCompleted = 0  
	BEGIN  

		BEGIN TRAN;  

		EXEC  @return_code = sp_getapplock  
			  @Resource = 'ALTER SCHEMA'  
			, @LockMode = 'Exclusive'  
			, @LockOwner = 'TRANSACTION'  
			, @LockTimeout = 5000; --wait up to 5 seconds before retry  
		RAISERROR('DEBUG: sp_getapplock return code is %d', 0, 0, @return_code) WITH NOWAIT;  

		IF(@return_code IN (0,1)) --lock granted successfully (immediately or after waiting)  
		BEGIN  
			ALTER SCHEMA TargetSchema TRANSFER SourceSchema.TableName;  
			SET @AlterSchemaCompleted = 1;  
		END  
		ELSE  
		BEGIN  
			IF @return_code = -1 --lock timeout  
			BEGIN  
				RAISERROR('DEBUG: Retrying after lock timeout', 0, 0, @return_code) WITH NOWAIT;  
			END  
			ELSE  
			BEGIN  
				RAISERROR('Unexpect sp_getapplock return code', 16, 1, @return_code) WITH NOWAIT;  --raise an error to enter CATCH block  
			END;  
		END;  
		COMMIT; --this will also release the app lock, if necessary  
	END;  
END TRY  
BEGIN CATCH  
	IF @@TRANCOUNT > 0 ROLLBACK; --this will also release the app lock, if necessary  
	THROW;  
END CATCH;  
GO