sql try/catch rollback / commit-prévenir les erreurs de propagation après un roll back

j'essaie d'écrire un script ms sql qui a une transaction et un bloc "essayer/attraper". Si il attrape une exception, la transaction est annulée. Sinon, la transaction est validée. J'ai vu quelques sites différents disant de faire comme ceci:

begin transaction
begin try
    --main content of script here
end try
begin catch
    rollback transaction
end catch

commit transaction

mais ne va-t-on pas quand même cliquer sur la ligne "commit transaction", même dans le cas d'une exception? Cela ne conduira-t-il pas à une erreur SQL parce que la transaction a déjà été annulée? Je pense que ça devrait être fait comme ceci:

declare @success bit = 1

begin transaction
begin try
    --main content of script here
end try
begin catch
    rollback transaction
    set @success = 0
end catch

if(@success = 1)
begin
    commit transaction
end

comment se fait-il que la solution couramment affichée n'inclut pas la variable @success? N'y a-t-il pas d'erreur sql qui se produit à la suite d'une transaction qui a déjà été annulée? Est-ce que j'ai tort de dire que la ligne "commit transaction" du premier exemple de code sera quand même touchée dans le cas d'une exception?

39
demandé sur user3666839 2014-08-05 23:17:58

5 réponses

j'ai toujours pensé c'était l'un des meilleurs articles sur le sujet. Il inclut l'exemple suivant qui, je pense, le rend clair et inclut le @@trancount souvent négligé qui est nécessaire pour des transactions imbriquées fiables

PRINT 'BEFORE TRY'
BEGIN TRY
    BEGIN TRAN
     PRINT 'First Statement in the TRY block'
     INSERT INTO dbo.Account(AccountId, Name , Balance) VALUES(1, 'Account1',  10000)
     UPDATE dbo.Account SET Balance = Balance + CAST('TEN THOUSAND' AS MONEY) WHERE AccountId = 1
     INSERT INTO dbo.Account(AccountId, Name , Balance) VALUES(2, 'Account2',  20000)
     PRINT 'Last Statement in the TRY block'
    COMMIT TRAN
END TRY
BEGIN CATCH
    PRINT 'In CATCH Block'
    IF(@@TRANCOUNT > 0)
        ROLLBACK TRAN;

    THROW; -- raise error to the client
END CATCH
PRINT 'After END CATCH'
SELECT * FROM dbo.Account WITH(NOLOCK)
GO
57
répondu Gary Walker 2018-03-13 17:48:48

Dans ton premier exemple, vous avez raison. Le lot atteindra la transaction de propagation, que le bloc d'essai démarre ou non.

dans votre second exemple, je suis d'accord avec les autres commentateurs. L'utilisation du drapeau de succès est inutile.

je considère que l'approche suivante est, essentiellement, une approche de meilleure pratique de poids léger.

si vous voulez voir comment il gère une exception, changez la valeur sur le second insert de 255 à 256.

CREATE TABLE #TEMP ( ID TINYINT NOT NULL );
INSERT  INTO #TEMP( ID ) VALUES  ( 1 )

BEGIN TRY
    BEGIN TRANSACTION

    INSERT  INTO #TEMP( ID ) VALUES  ( 2 )
    INSERT  INTO #TEMP( ID ) VALUES  ( 255 )

    COMMIT TRANSACTION
END TRY
BEGIN CATCH
    DECLARE 
        @ErrorMessage NVARCHAR(4000),
        @ErrorSeverity INT,
        @ErrorState INT;
    SELECT 
        @ErrorMessage = ERROR_MESSAGE(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE();
    RAISERROR (
        @ErrorMessage,
        @ErrorSeverity,
        @ErrorState    
        );
    ROLLBACK TRANSACTION
END CATCH

SET NOCOUNT ON

SELECT ID
FROM #TEMP

DROP TABLE #TEMP
26
répondu Jim V. 2014-08-05 19:43:48

j'ai utilisé sous ms sql script pattern plusieurs fois avec succès qui utilise Try-Catch,Commit De La Transaction Rollback Transaction, Suivi Des Erreurs.

votre bloc D'essai sera comme suit

 BEGIN TRY
 BEGIN TRANSACTION T
 ----
 //your script block
 ----
 COMMIT TRANSACTION T 
 END TRY

votre bloc CATCH sera comme suit

BEGIN CATCH
DECLARE @ErrMsg NVarChar(4000), 
        @ErrNum Int, 
        @ErrSeverity Int, 
        @ErrState Int, 
        @ErrLine Int, 
        @ErrProc NVarChar(200)
 SELECT @ErrNum = Error_Number(), 
       @ErrSeverity = Error_Severity(), 
       @ErrState = Error_State(), 
       @ErrLine = Error_Line(), 
       @ErrProc = IsNull(Error_Procedure(), '-')
 SET @ErrMsg = N'ErrLine: ' + rtrim(@ErrLine) + ', proc: ' + RTRIM(@ErrProc) + ', 
       Message: '+ Error_Message()

votre script de retour fera partie du bloc CATCH comme suit

IF (@@TRANCOUNT) > 0 
BEGIN
PRINT 'ROLLBACK: ' + SUBSTRING(@ErrMsg,1,4000)
ROLLBACK TRANSACTION T
END
ELSE
BEGIN
PRINT SUBSTRING(@ErrMsg,1,4000);   
END

END CATCH

au-Dessus de différents blocs de script, vous devez utiliser comme un bloc. Si tout erreur se produit dans le bloc il va aller à l' CATCH bloc. Là, il définit divers détails sur le numéro d'erreur,la gravité de l'erreur,la ligne d'erreur ..etc. Enfin tous ces détails seront ajoutés au paramètre @ErrMsg. Ensuite, il vérifiera le compte de transaction (@@TRANCOUNT >0), c'est-à-dire s'il y a quelque chose dans la transaction pour le retour. Si elle est là, puis afficher le message d'erreur et ROLLBACK TRANSACTION. Sinon, il suffit d'imprimer l'erreur message.

Nous avons gardé notre COMMIT TRANSACTION T script vers la dernière ligne du bloc TRY afin de s'assurer qu'il ne commette la transaction(changement final dans la base de données) qu'après que tout le code du bloc TRY a été exécuté avec succès.

1
répondu Rinoy Ashokan 2018-06-21 11:21:44

compteur de transactions

--@@TRANCOUNT = 0
begin try
--@@TRANCOUNT = 0
BEGIN TRANSACTION tran1
 --@@TRANCOUNT = 1

        --your code
        -- if failed  @@TRANCOUNT = 1
        -- if success @@TRANCOUNT = 0

COMMIT TRANSACTION tran1

end try

begin catch
    print 'FAILED'
end catch
0
répondu Arun Prasad E S 2015-12-03 06:50:48

ci-dessous pourrait être utile.

Source:https://msdn.microsoft.com/en-us/library/ms175976.aspx

BEGIN TRANSACTION;

BEGIN TRY
    -- your code --
END TRY
BEGIN CATCH
    SELECT 
        ERROR_NUMBER() AS ErrorNumber
        ,ERROR_SEVERITY() AS ErrorSeverity
        ,ERROR_STATE() AS ErrorState
        ,ERROR_PROCEDURE() AS ErrorProcedure
        ,ERROR_LINE() AS ErrorLine
        ,ERROR_MESSAGE() AS ErrorMessage;

    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
END CATCH;

IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;
GO
0
répondu Sreedhar Chintakunta 2016-02-16 08:32:50