how to design with no deadlock

• chonlatis.je

  SSCommitted

  Points: 1606

  More actions

  July 19, 2017 at 9:16 pm

  #343643

  Now a day the transaction on this table getting more and make database lock from this SP, should I normalization or any idea (this SP is run as a job every 10 sec)

  PROCEDURE [dbo].[USP_AUTO_MOVE_TBMESSAGEREDUCE_DONE_AND_REJECT_TO_SENT]
@TopRecord INT = 50000
, @ManagementID TINYINT
, @ResText VARCHAR(MAX) OUTPUT
AS
DECLARE
@NEWLINE CHAR(1)
,@SubLoop_Rows INT
,@Success_Rows INT
,@Error_Rows INT
,@TopSubRecord SMALLINT
,@TopSubRecord_Check SMALLINT
,@ISEndSubLoop TINYINT
,@Total_Rows INT
,@Loop INT;

SET NOCOUNT ON
SET @NEWLINE = CHAR(10)
SET @ResText = ''
SET @TopSubRecord = 1000
SET @TopSubRecord_Check = 1000
SET @Success_Rows = 0
SET @Error_Rows = 0
SET @Total_Rows = 0
SET @MaxLoop = 15

  IF EXISTS (SELECT TOP 1 1 FROM MCP_DEV.dbo.TBMESSAGEREDUCEQUEUE (NOLOCK) WHERE MANAGEMENTID = @ManagementID AND IDOPERATORHANDLESTATUS IN(2,4))
BEGIN


  SET @ISEndSubLoop = 0
SET @Loop = 0
WHILE @Total_Rows < @TopRecord AND @ISEndSubLoop = 0 AND @Loop < @MaxLoop
BEGIN
BEGIN TRY

  DELETE FROM MCP_DEV_TEMP.dbo.TempMoveMTByManagementID WHERE SESSION_ID = @ManagementID

  SET @SubLoop_Rows = 0

  IF @TopRecord - @Total_Rows < @TopSubRecord_Check
BEGIN
SET @TopSubRecord = @TopRecord - @Total_Rows
END
ELSE
BEGIN
SET @TopSubRecord = @TopSubRecord_Check
END

--- Check MOVE FROM TBMESSAGEREDUCEQUEUE to TBMESSAGEREDUCESENT ---
INSERT INTO MCP_DEV_TEMP.dbo.TempMoveMTByManagementID
(
IDMESSAGE
, SESSION_ID
)
SELECT
TOP (@TopSubRecord)
IDMESSAGE
, @ManagementID
FROM MCP_DEV.dbo.TBMESSAGEREDUCEQUEUE (READCOMMITTED)
WHERE
MANAGEMENTID = @ManagementID
AND IDOPERATORHANDLESTATUS IN (2,4)
ORDER BY
MTSENTDATETIME ASC

  -- Check all sub rows
SET @SubLoop_Rows = @@ROWCOUNT

  IF @SubLoop_Rows > 0
BEGIN
BEGIN TRY
BEGIN TRANSACTION

  IF OBJECT_ID('tempdb..#TempMessageReduceQueue') IS NOT NULL
DROP TABLE #TempMessageReduceQueue

  SELECT * INTO #TempMessageReduceQueue
FROM MCP_DEV.dbo.TBMESSAGEREDUCEQUEUE (NOLOCK)
WHERE IDMESSAGE IN (
SELECT IDMESSAGE
FROM MCP_DEV_TEMP.dbo.TempMoveMTByManagementID (NOLOCK)
WHERE SESSION_ID = @ManagementID
)

  DELETE FROM MCP_DEV.dbo.TBMESSAGEREDUCEQUEUE
WHERE IDMESSAGE IN (
SELECT IDMESSAGE
FROM MCP_DEV_TEMP.dbo.TempMoveMTByManagementID (NOLOCK)
WHERE SESSION_ID = @ManagementID
)

  INSERT INTO MCP_DEV_SENT.dbo.TBMESSAGEREDUCESENT
SELECT * FROM #TempMessageReduceQueue (NOLOCK)

  SET @Success_Rows = @Success_Rows + @SubLoop_Rows

  -- Drop Temp Table
IF OBJECT_ID('tempdb..#TempMessageReduceQueue') IS NOT NULL
DROP TABLE #TempMessageReduceQueue

  COMMIT TRANSACTION
END TRY
BEGIN CATCH
PRINT 'CATCH'
SET @ResText = @ResText + 'Execute error: ' + (CASE WHEN ERROR_PROCEDURE() IS NULL THEN '' ELSE ERROR_PROCEDURE() END) + ' ' + ERROR_MESSAGE() + @NEWLINE
SET @Error_Rows = @Error_Rows + @SubLoop_Rows
ROLLBACK TRANSACTION
END CATCH

  SET @Total_Rows = @Success_Rows + @Error_Rows
END
ELSE
BEGIN
SET @ISEndSubLoop = 1
END

  DELETE FROM MCP_DEV_TEMP.dbo.TempMoveMTByManagementID WHERE SESSION_ID = @ManagementID
END TRY
BEGIN CATCH
PRINT 'CATCH2'
END CATCH

  SET @Loop = @Loop + 1

PRINT CONVERT(VARCHAR, GETDATE(), 121) + @NEWLINE
WAITFOR DELAY '00:00:00.100'
END

END

  SET @ResText = @ResText + 'Success: ' + CAST(@Success_Rows AS VARCHAR(10)) + ' record(s)' + @NEWLINE

  IF @Error_Rows > 0 AND @Success_Rows = 0
BEGIN
SET @ResText = @ResText + 'Execute error: ' + (CASE WHEN ERROR_PROCEDURE() IS NULL THEN '' ELSE ERROR_PROCEDURE() END) + ' ' + ERROR_MESSAGE() + @NEWLINE
RETURN 0

END
ELSE
BEGIN
RETURN 1
END
END

• pietlinden

  SSC Guru

  Points: 63503

  More actions

  July 19, 2017 at 9:48 pm

  #1951778

  Go crazy. Post your code in here...

  PROCEDURE [dbo].[USP_AUTO_MOVE_TBMESSAGEREDUCE_DONE_AND_REJECT_TO_SENT]
    @TopRecord INT = 50000
    , @ManagementID TINYINT
    , @ResText VARCHAR(MAX) OUTPUT
AS
DECLARE
    @NEWLINE CHAR(1)
    ,@SubLoop_Rows INT
    ,@Success_Rows INT
    ,@Error_Rows INT
    ,@TopSubRecord SMALLINT
    ,@TopSubRecord_Check SMALLINT
    ,@ISEndSubLoop TINYINT
    ,@Total_Rows INT
    ,@Loop INT;
    
    SET NOCOUNT ON    
    SET @NEWLINE = CHAR(10)
    SET @ResText = ''
    SET @TopSubRecord = 1000
    SET @TopSubRecord_Check = 1000
    SET @Success_Rows = 0
    SET @Error_Rows = 0
    SET @Total_Rows = 0
    SET @MaxLoop = 15

      IF EXISTS (SELECT TOP 1 1 FROM MCP_DEV.dbo.TBMESSAGEREDUCEQUEUE (NOLOCK) WHERE MANAGEMENTID = @ManagementID AND IDOPERATORHANDLESTATUS IN(2,4))
    BEGIN
    

          SET @ISEndSubLoop = 0
        SET @Loop = 0
        WHILE @Total_Rows < @TopRecord AND @ISEndSubLoop = 0 AND @Loop < @MaxLoop
        BEGIN
            BEGIN TRY

                  DELETE FROM MCP_DEV_TEMP.dbo.TempMoveMTByManagementID WHERE SESSION_ID = @ManagementID

                  SET @SubLoop_Rows = 0

                  IF @TopRecord - @Total_Rows < @TopSubRecord_Check
                BEGIN
                    SET @TopSubRecord = @TopRecord - @Total_Rows
                END
                ELSE
                BEGIN
                    SET @TopSubRecord = @TopSubRecord_Check
                END
                
                --- Check MOVE FROM TBMESSAGEREDUCEQUEUE to TBMESSAGEREDUCESENT ---
                INSERT INTO MCP_DEV_TEMP.dbo.TempMoveMTByManagementID    
                (
                    IDMESSAGE
                    , SESSION_ID
                )        
                SELECT
                    TOP (@TopSubRecord)
                    IDMESSAGE
                    , @ManagementID
                FROM MCP_DEV.dbo.TBMESSAGEREDUCEQUEUE (READCOMMITTED)
                WHERE
                    MANAGEMENTID = @ManagementID
                    AND IDOPERATORHANDLESTATUS IN (2,4)
                ORDER BY
                    MTSENTDATETIME ASC

                  -- Check all sub rows
                SET @SubLoop_Rows = @@ROWCOUNT

                  IF @SubLoop_Rows > 0
                BEGIN
                    BEGIN TRY
                        BEGIN TRANSACTION     

                          IF OBJECT_ID('tempdb..#TempMessageReduceQueue') IS NOT NULL
                        DROP TABLE #TempMessageReduceQueue

                          SELECT * INTO #TempMessageReduceQueue
                        FROM MCP_DEV.dbo.TBMESSAGEREDUCEQUEUE (NOLOCK)
                        WHERE IDMESSAGE IN (
                            SELECT IDMESSAGE
                            FROM MCP_DEV_TEMP.dbo.TempMoveMTByManagementID (NOLOCK)
                            WHERE SESSION_ID = @ManagementID
                        )

                          DELETE FROM MCP_DEV.dbo.TBMESSAGEREDUCEQUEUE
                        WHERE IDMESSAGE IN (
                            SELECT IDMESSAGE
                            FROM MCP_DEV_TEMP.dbo.TempMoveMTByManagementID (NOLOCK)
                            WHERE SESSION_ID = @ManagementID
                        )

                          INSERT INTO MCP_DEV_SENT.dbo.TBMESSAGEREDUCESENT
                        SELECT * FROM #TempMessageReduceQueue (NOLOCK)

                          SET @Success_Rows = @Success_Rows + @SubLoop_Rows

                          -- Drop Temp Table
                        IF OBJECT_ID('tempdb..#TempMessageReduceQueue') IS NOT NULL
                        DROP TABLE #TempMessageReduceQueue

                          COMMIT TRANSACTION
                    END TRY
                    BEGIN CATCH
                        PRINT 'CATCH'
                        SET @ResText = @ResText + 'Execute error: ' + (CASE WHEN ERROR_PROCEDURE() IS NULL THEN '' ELSE ERROR_PROCEDURE() END) + ' ' + ERROR_MESSAGE() + @NEWLINE
                        SET @Error_Rows = @Error_Rows + @SubLoop_Rows
                        ROLLBACK TRANSACTION
                    END CATCH

                      SET @Total_Rows = @Success_Rows + @Error_Rows
                END
                ELSE
                BEGIN
                    SET @ISEndSubLoop = 1
                END

                  DELETE FROM MCP_DEV_TEMP.dbo.TempMoveMTByManagementID WHERE SESSION_ID = @ManagementID
            END TRY
            BEGIN CATCH
                PRINT 'CATCH2'
            END CATCH

              SET @Loop = @Loop + 1
            
            PRINT CONVERT(VARCHAR, GETDATE(), 121) + @NEWLINE
            WAITFOR DELAY '00:00:00.100'
        END                
            
    END

      SET @ResText = @ResText + 'Success: ' + CAST(@Success_Rows AS VARCHAR(10)) + ' record(s)' + @NEWLINE

      IF @Error_Rows > 0 AND @Success_Rows = 0
    BEGIN
        SET @ResText = @ResText + 'Execute error: ' + (CASE WHEN ERROR_PROCEDURE() IS NULL THEN '' ELSE ERROR_PROCEDURE() END) + ' ' + ERROR_MESSAGE() + @NEWLINE
        RETURN 0
            
    END
    ELSE
    BEGIN
        RETURN 1
    END
END