0

The below SP fails when running parallely in multiple threads. Tried to use different isolation levels in the SP, but still i face the same error.

Violation of UNIQUE KEY constraint 'AK_MerchantTransactionEnd_MerchantReferenceCode_BankReferenceCode'. Cannot insert duplicate key in object 'dbo.EpayTransaction'. The duplicate key value is (20160503171525689435, 20160329221725169, 0).

Table has UNIQUE Constraint for MerchantReferenceCode_BankReferenceCode

CREATE PROCEDURE [dbo].[uspUpdateEpayConfirmation]
    @merchantReferenceCode VARCHAR(30) ,
    @bankGatewayCode VARCHAR(30) ,
    @transactionNumber VARCHAR(100) 
AS
    BEGIN
      SET NOCOUNT ON;
      SET XACT_ABORT ON;

     BEGIN TRY

        DECLARE @timestamp DATETIME;
        SET @timestamp = GETDATE();
        IF EXISTS ( SELECT  1
                    FROM    [dbo].EpayTransaction WITH (NOLOCK)
                    WHERE   [dbo].EpayTransaction.[MerchantReferenceCode] = @merchantReferenceCode 
                  )
            BEGIN
                RETURN 0;
            END;

        SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

        BEGIN TRANSACTION;
        -- update the epayment transaction information with the merchent reference code

             -- updating the status of the transaction 
        UPDATE  [dbo].[CustomerTransaction]
        SET     [dbo].[CustomerTransaction].[ModifiedBy] = 1 ,
                [dbo].[CustomerTransaction].[ModifiedOn] = @timestamp
        WHERE   [dbo].[CustomerTransaction].[MerchantReferenceCode] = @merchantReferenceCode;

            -- adding a record to EpayTransaction table  to conclude the transaction is successful
        INSERT  INTO [dbo].EpayTransaction
                ( [dbo].EpayTransaction.[BankReferenceCode] ,
                  [dbo].EpayTransaction.[BankTransactionDate] ,
                  [dbo].EpayTransaction.[MerchantReferenceCode]
                )
        VALUES  ( @bankGatewayCode ,
                  @timestamp ,
                  @merchantReferenceCode
                );
        COMMIT TRANSACTION;

        RETURN 1;
    END TRY

    BEGIN CATCH

        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;

    -- Raise an error with the details of the exception
        DECLARE @errMsg NVARCHAR(4000) ,
            @errSeverity INT;

        SELECT  @errMsg = ERROR_MESSAGE() ,
                @errSeverity = ERROR_SEVERITY();

        RAISERROR(@errMsg, @errSeverity, 1);
    END CATCH;

END;
James Z
  • 12,209
  • 10
  • 24
  • 44

0 Answers0