I have a stored procedure that deletes X rows. This procedure is called by a Job Scheduler (Not SQL Server Agent) from one of three Azure Instances. The deletion task is run every 30 minutes from each instance and almost always results in a deadlock. Basically, multiple/simultaneous calls of this same stored procedure keep blocking eachother out.
Any suggestions on how to make this stored procedure threadsafe ?
CREATE PROCEDURE [dbo].[RawData_d_DeleteObsoleteRawData] @RemoteReadingMeasureIdList [IntListType] READONLY, @MaxSavingDurationInDays INT AS BEGIN DECLARE @CurrentDate SMALLDATETIME = CAST(GETUTCDATE() as SMALLDATETIME) DECLARE @MaxTime SMALLDATETIME DECLARE @TotalDeleted INT = 0 -- Max time for raw data SET @MaxTime = DATEADD(DAY, -@MaxSavingDurationInDays, @CurrentDate) -- Delete non remote reading raw data DELETE TOP (10000) FROM [dbo].[RawData] WHERE MeasureId NOT IN (SELECT Item FROM @RemoteReadingMeasureIdList) AND DateEnergy < @MaxTime SET @TotalDeleted = @TotalDeleted + @@ROWCOUNT -- Delete non remote reading raw data (keep 1 raw data per day for remote reading) ;WITH cte AS ( SELECT AreaId, MeasureId, DateEnergy, ROW_NUMBER() OVER (PARTITION BY MeasureId, YEAR(DateEnergy), DAY(DateEnergy) ORDER BY DateEnergy DESC) AS rn FROM [RawData] rd WITH (NOLOCK) INNER JOIN @RemoteReadingMeasureIdList m ON rd.MeasureId = m.Item WHERE DateEnergy < @MaxTime ) DELETE TOP (10000) rd FROM [RawData] rd INNER JOIN cte ON rd.AreaId = cte.AreaId AND rd.DateEnergy = cte.DateEnergy AND rd.MeasureId = cte.MeasureId WHERE rn <> 1 SET @TotalDeleted = @TotalDeleted + @@ROWCOUNT SELECT @TotalDeleted as DeletedRow
Using the READPAST solution recommended by Jon was the best overall option.
To test I created two new queries, each with their own spid
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ Begin transaction Exec [RawData_d_DeleteObsoleteRawData]
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ Exec [RawData_d_DeleteObsoleteRawData]
The first SPID does not commit and the second one is run immediately after.
Without the READPAST hint, all other executions of RawData_d_DeleteObsoleteRawData never complete.
With the READPAST hint added to the two DELETEs and the SELECT, all other executions of RawData_d_DeleteObsoleteRawData are able to continue.
Thank you again to wBob and Jon Seigel for their assistance