SQL Server - les transactions sont annulées en cas d'erreur ?

Question

Nous avons une application client qui exécute un certain SQL sur un serveur SQL 2005, tel que le suivant :

BEGIN TRAN;
INSERT INTO myTable (myColumns ...) VALUES (myValues ...);
INSERT INTO myTable (myColumns ...) VALUES (myValues ...);
INSERT INTO myTable (myColumns ...) VALUES (myValues ...);
COMMIT TRAN;

Il est envoyé par une longue chaîne de commande.

Si l'une des insertions échoue, ou si une partie de la commande échoue, le serveur SQL annule-t-il la transaction ? S'il n'annule pas la transaction, dois-je envoyer une deuxième commande pour l'annuler ?

Je peux vous donner des détails sur l'API et la langue que j'utilise, mais je pense que le serveur SQL devrait répondre de la même manière pour n'importe quelle langue.

Answer 1

Vous pouvez mettre set xact_abort on avant votre transaction pour s'assurer que sql revient automatiquement en arrière en cas d'erreur.

Answer 2

Vous avez raison de dire que l'ensemble de la transaction sera annulée. Vous devez émettre la commande pour l'annuler.

Vous pouvez l'envelopper dans un TRY CATCH comme suit

BEGIN TRY
    BEGIN TRANSACTION

        INSERT INTO myTable (myColumns ...) VALUES (myValues ...);
        INSERT INTO myTable (myColumns ...) VALUES (myValues ...);
        INSERT INTO myTable (myColumns ...) VALUES (myValues ...);

    COMMIT TRAN -- Transaction Success!
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRAN --RollBack in case of Error

    -- <EDIT>: From SQL2008 on, you must raise error messages as follows:
    DECLARE @ErrorMessage NVARCHAR(4000);  
    DECLARE @ErrorSeverity INT;  
    DECLARE @ErrorState INT;  

    SELECT   
       @ErrorMessage = ERROR_MESSAGE(),  
       @ErrorSeverity = ERROR_SEVERITY(),  
       @ErrorState = ERROR_STATE();  

    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);  
    -- </EDIT>
END CATCH

Answer 3

Voici le code permettant d'obtenir le message d'erreur avec MSSQL Server 2016 :

BEGIN TRY
    BEGIN TRANSACTION 
        -- Do your stuff that might fail here
    COMMIT
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRAN

        DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE()
        DECLARE @ErrorSeverity INT = ERROR_SEVERITY()
        DECLARE @ErrorState INT = ERROR_STATE()

    -- Use RAISERROR inside the CATCH block to return error  
    -- information about the original error that caused  
    -- execution to jump to the CATCH block.  
    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
END CATCH

Answer 4

Extrait de l'article du MDSN, Contrôle des transactions (moteur de base de données) .

Si une erreur d'instruction d'exécution (telle qu'une violation de contrainte) se produit dans un lot, le comportement par défaut du moteur de base de données consiste à annuler uniquement l'instruction qui a généré l'erreur. Vous pouvez modifier ce comportement à l'aide de l'instruction SET XACT_ABORT. Après l'exécution de l'instruction SET XACT_ABORT ON, toute erreur d'instruction d'exécution entraîne un retour en arrière automatique de la transaction en cours. Les erreurs de compilation, telles que les erreurs de syntaxe, ne sont pas affectées par SET XACT_ABORT. Pour plus d'informations, voir SET XACT_ABORT (Transact-SQL).

Dans votre cas, la transaction complète sera annulée si l'une des insertions échoue.

Answer 5

Si l'une des insertions échoue, ou si une partie de la commande échoue, le serveur SQL annule-t-il la transaction ?

Non, ce n'est pas le cas.

S'il ne fait pas marche arrière, dois-je envoyer une deuxième commande pour le faire ?

Bien sûr, vous devriez émettre ROLLBACK au lieu de COMMIT .

Si vous voulez décider de valider ou d'annuler la transaction, vous devez supprimer l'option COMMIT de l'instruction, vérifiez les résultats des insertions, puis émettez l'une des deux phrases suivantes COMMIT o ROLLBACK en fonction des résultats de la vérification.