Sql-server – nyway to make this stored procedure thread safe

sql serverstored-procedures

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

Update 26/03/2014

Using the READPAST solution recommended by Jon was the best overall option.

To test I created two new queries, each with their own spid

Spid 51

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
Begin transaction
Exec [RawData_d_DeleteObsoleteRawData]

Spid 52

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

Best Answer

I know raising the transaction isolation level and adding a transaction can seem counter-intuitive, but sometimes, you have to make things queue up and wait their turn. Doing those two things worked for me in my simple test rig. Try it and let us know how you get on.

EDIT: Test rig OK, nice simple test rig using tempdb. It's a bit rough and ready but provided an easy repro and provable fix for me. You should enable SQLCMD mode in SSMS before you run. I basically pit the proc against itself in multiple sessions using either ostress or GO 100. Run the initial version of the proc, monitor for deadlocks; I get many. Then, reset the rig, run in the revised version of the proc and try again.

USE tempdb
GO

------------------------------------------------------------------------------------------------
-- Setup START
------------------------------------------------------------------------------------------------

SET NOCOUNT ON
GO

IF OBJECT_ID('[dbo].[RawData_d_DeleteObsoleteRawData]') IS NOT NULL
DROP PROC [dbo].[RawData_d_DeleteObsoleteRawData]
GO

-- Example table type
IF EXISTS ( SELECT * FROM sys.types st JOIN sys.schemas ss ON st.schema_id = ss.schema_id WHERE st.name = N'IntListType' )
    DROP TYPE IntListType
GO

CREATE TYPE IntListType AS TABLE ( Item INT PRIMARY KEY ) 
GO
-- Create a large table
-- DROP TABLE dbo.rawData
IF OBJECT_ID('dbo.rawData') IS NULL
CREATE TABLE dbo.rawData ( 
    rowId INT IDENTITY PRIMARY KEY,
    AreaId INT, 
    MeasureId INT, 
    someData UNIQUEIDENTIFIER DEFAULT NEWID(), 
    DateEnergy DATETIME DEFAULT GETDATE(), 
    addedBy VARCHAR(30) DEFAULT SUSER_NAME(), 
    ts ROWVERSION 
    )
GO


TRUNCATE TABLE dbo.rawData

-- Populate table
;WITH cte AS (
SELECT TOP 1000 ROW_NUMBER() OVER ( ORDER BY ( SELECT 1 ) ) rn
FROM master.sys.columns c1
    CROSS JOIN master.sys.columns c2
    CROSS JOIN master.sys.columns c3
)
INSERT INTO dbo.rawData ( AreaId, MeasureId, DateEnergy )
SELECT areaId.rn, MeasureId.rn, DATEADD( day, DateEnergy.rn % 300, '1 Jan 2014' )
FROM 
    ( SELECT TOP 10 rn FROM cte  ) areaId,
    ( SELECT TOP 100 rn FROM cte  ) MeasureId,
    ( SELECT TOP 1000 rn FROM cte  ) DateEnergy,
    ( SELECT TOP 2 rn FROM cte  ) doubleUp
GO



-- Original proc
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

    END
GO
:exit

-- Setup END
------------------------------------------------------------------------------------------------


------------------------------------------------------------------------------------------------
-- Main proc call START
-- Run this section in two or more connections
-- eg using ostress
-- !!ostress.exe -E -S.\sql2008r2 -dtempdb -i"C:\temp\temp.sql" -n10 -r5 -mstress
------------------------------------------------------------------------------------------------

-- Proc call
SET NOCOUNT ON
DECLARE @tvp AS [IntListType]

INSERT INTO @tvp
VALUES ( 1 ), ( 2 ), ( 3 ), ( 5 ), ( 8 ), ( 13 )

EXEC [dbo].[RawData_d_DeleteObsoleteRawData] @tvp, 30
GO 100

-- Main proc call END
------------------------------------------------------------------------------------------------



------------------------------------------------------------------------------------------------
-- Revised proc START
------------------------------------------------------------------------------------------------

/*  Reset the rig and try with the revised proc 

ALTER 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)

    SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

    BEGIN TRY

        BEGIN TRAN

            -- 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
                    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

             COMMIT TRAN

         END TRY

         BEGIN CATCH

            DECLARE @errorMessage NVARCHAR(2048) = ERROR_MESSAGE()

            IF @@TRANCOUNT > 0 BEGIN PRINT 'Error handler rolling back...' ROLLBACK TRAN END

            RAISERROR( @errorMessage, 16, 1 )

         END CATCH

    END
GO
*/


-- Revised proc END
------------------------------------------------------------------------------------------------

Related Solutions

Sql-server – Can foreign keys cause deadlocks and hinder READ COMMITTED SNAPSHOT

If the SQLCAT team says that FK validation is done using read-committed isolation, then they must know what they're talking about. Emphasis on validation. The real question is Why would a report trigger FK validation? Validation occurs on writes, and reports are supposed to be reads. Either your reports are causing writes, in which case snapshot isolation levels will help nothing, either the cause of the deadlock is different.

The only way to make progress is to capture the deadlock graph.

As for the other question, how can you check if you operate under snapshot isolation: look in sys.dm_tran_active_snapshot_database_transactions.

Sql-server – Handling exceptions in stored procedures called using insert-exec blocks

The error in the EXEC part of the INSERT-EXEC statement is leaving your transaction in a doomed state.

If you PRINT out XACT_STATE() in the CATCH block it is set to -1.

Not all errors will set the state to this. The following check constraint error goes through to the catch block and the INSERT succeeds.

ALTER PROCEDURE test -- or create
AS
  BEGIN try
      DECLARE @retval INT;

      DECLARE @t TABLE(x INT CHECK (x = 0))

      INSERT INTO @t
      VALUES      (1)

      SET @retval = 0;

      SELECT @retval;

      RETURN( @retval );
  END try

  BEGIN catch
      PRINT XACT_STATE()

      PRINT ERROR_MESSAGE();

      SET @retval = -1;

      SELECT @retval;

      RETURN( @retval );
  END catch;

Adding this to the CATCH block

 IF (XACT_STATE()) = -1
BEGIN
    ROLLBACK TRANSACTION;
END;

Doesn't help. It gives the error

Cannot use the ROLLBACK statement within an INSERT-EXEC statement.

I don't think there is any way of recovering from such an error once it has happened. For your specific use case you don't need INSERT ... EXEC anyway though. You can assign the return value to a scalar variable then insert that in a separate statement.

DECLARE @RC INT;

EXEC sp_executesql
  N'EXEC @RC = test',
  N'@RC INT OUTPUT',
  @RC = @RC OUTPUT;

INSERT INTO @t
VALUES      (@RC)

Or of course you could restructure the called stored procedure so that it doesn't raise that error at all.

DECLARE @RetVal INT = -1

IF OBJECT_ID('PrintMax', 'P') IS NULL
  BEGIN
      EXEC('create procedure PrintMax as begin print ''hello world'' end;')

      SET @RetVal = 0
  END

SELECT @RetVal;

RETURN( @RetVal );

Best Answer

Related Solutions

Sql-server – Can foreign keys cause deadlocks and hinder READ COMMITTED SNAPSHOT

Sql-server – Handling exceptions in stored procedures called using insert-exec blocks

Related Question