SQL Server - Gestion des exceptions TRY .. CATCH.. Rendre une application plus robuste

I - Introduction

Depuis SQL Server 2005, (et donc valable également sous SQL Server 2008, 2008 R2 etc.), il est enfin possible de gérer sérieusement les exceptions au travers les constructions TRY CATCH.
La gestion des erreurs dans les versions précédentes, sous SQL Server 2000 par exemple, n'était pas vraiment à la hauteur et n'était pas digne d'un langage évolué moderne.
Sous SQL Server 2000, la détection et la gestion des erreurs déclenchées par les commandes T-SQL ne pouvaient être effectuées qu'en vérifiant le contenu de la variable système globale @@error. Celle-ci retourne le numéro d'erreur déclenchée par la dernière instruction T-SQL exécutée. Donc, sous SQL Server 2000, Il fallait lire le contenu de la variable @@error après chaque instruction T-SQL ! Il faillait, en plus, généralement, stocker le contenu de @@error dans une variable locale ! En effet, la variable globale @@error est effacée et réinitialisée (remise à zéro) à chaque exécution d'une instruction. Cette approche complètement archaïque conduisait à surcharger le code T-SQL des procédures par des instructions comme « IF @@error <> 0 .. » jusqu'à le rendre illisible !

La construction TRY ..CATCH disponible depuis SQL Server 2005 (et donc valable également sous SQL Server 2008, 2008 R2 etc.), offre une syntaxe beaucoup plus lisible, avec laquelle les développeurs sont déjà habitués au travers d'autres langages évolués comme C# ou C++.

Dans cet article, je vais vous présenter, au travers d'exemples concrets, comment grâce à la construction TRY CATCH, combinée à l'option XACT_ABORT, et à la fonction XACT_STATE(), vous pouvez écrire du code T-SQL robuste intégrant une gestion sérieuse et solide des erreurs. Dans cet article, j'explique également comment, au travers ces nouvelles constructions (TRY CATCH, etc.),  annuler et mettre fin aux transactions en erreur et libérer ainsi les verrous posés sur les enregistrements par les transactions.
Les verrous, posés sur les enregistrements, non libérés, acquis par une transaction en erreurs, inachevée, sont la sources de nombreux problèmes graves de blocages dans les applications.
Le paragraphe § II, ci-dessous, présente un modèle de code d'une procédure stockée, mettant en œuvre ces nouveaux concepts.

II - Modèle de procédure stockée mettant en ouvre la construction TRY CATCH

1 - Création d’une table temporaire pour le test
CREATE TABLE [dbo].[Temp01](
      Id       INT NOT NULL, 
      Libelle  VARCHAR(50) NULL
) 
ON [PRIMARY]; 
GO
2 - Création de la procédure modèle pour les tests
DROP PROCEDURE dbo.PS_U_TEST_TRY_CATCH_XACT_ABORT;
GO
CREATE PROCEDURE dbo.PS_U_TEST_TRY_CATCH_XACT_ABORT
AS
BEGIN
  SET NOCOUNT ON;
  SET XACT_ABORT ON;      -- (1) On fait notre premier test avec  XACT_ABORT ON
  -- SET XACT_ABORT OFF;  -- (2) Faites également un test avec SET XACT_ABORT OFF, 
                          --     vous verrez les différences
                          --     en terme de résultat et vous comprendrez mieux 
                          --     la subtilité de cette option (XACT_ABORT) !
  DECLARE @Var1 FLOAT;

  BEGIN TRY
        BEGIN TRAN;
           INSERT INTO dbo.Temp01
               (Id, Libelle)
           VALUES
               (1, 'Ecartez la vanité, elle gêne l''orgueil'); -- (Citation de L. Pauwels !)

           SET @Var1 = 2.5/0;  -- (3) On effectue volontairement une division par zéro 
                               --     pour générer une exception
        COMMIT TRAN;
        PRINT 'COMMIT BLOC TRY ..'  -- (4) On ne passe jamais ici, que l'option 
                                    --     SET XACT_ABORT soit à ON ou à OFF
  END TRY
  BEGIN CATCH
        PRINT  'ErrNumber ' + CONVERT(varchar(50), ERROR_NUMBER()) +
               ', ErrSeverity ' + CONVERT(varchar(5), ERROR_SEVERITY()) +
               ', ErrState ' + CONVERT(varchar(5), ERROR_STATE()) +
               ', ErrProcedure ' + ISNULL(ERROR_PROCEDURE(), '-') +
               ', ErrLine ' + CONVERT(varchar(5), ERROR_LINE()) ;
         PRINT 'ErrMessage ' + ERROR_MESSAGE();

        IF (XACT_STATE() = -1)
          BEGIN
             -- Il existe une transaction active mais une erreur a entraîné le classement
             -- de la transaction comme non validable (uncommittable transaction).

             -- A ce niveau, il est fortement recommandé de faire un ROLLBACK.
             -- En effet, à ce stade toute instruction Insert, Update, Delete ne peut être 
             -- exécutée avec succès puisqu'elle serait exécutée dans le cadre de la 
             -- transaction en cours déjà ouverte et considérée non validable !

             -- Ne pas faire un ROLLBACK à ce stade générerait assurément d'autres erreurs 
              -- par la suite, très difficiles à cerner. 
              PRINT 'XACT_STATE() = -1  → ROLLBACK' ;
              ROLLBACK TRAN;
          END;
        ELSE IF (XACT_STATE() = 1)
         BEGIN
            -- Il existe une transaction active. la validation ou l’annulation de la
            -- transaction sont possibles. C'est à vous de voir.
            PRINT 'XACT_STATE() = 1 → ROLLBACK OU COMMIT A vous de voir ...'

            -- Vous pouvez aussi, à ce niveau, si vous le jugez nécessaire, faire un COMMIT
            -- c'est à vous de voir selon les contraintes fonctionnelles de votre application
            -- Mais attention, il faut faire quelque chose (soit un COMMIT soit un ROLLBACK),
            -- ne rien faire à ce stade serait de nature à générer assurément d'autres 
            -- erreurs par la suite, très difficiles à cerner.
            ROLLBACK TRAN;  -- (5) On fait notre premier test avec  ROLLBACK TRAN; 
            -- COMMIT TRAN; -- (6) Faites également un test avec COMMIT TRAN; 
         END;
  END CATCH;
END;
GO
3 - Test n° 1 : Créez la procédure avec les options ci-dessous
SET XACT_ABORT ON;     -- (1)
-- SET XACT_ABORT OFF; -- (2)
...
ROLLBACK TRAN;  -- (5)
-- COMMIT TRAN; -- (6)
EXEC dbo.PS_U_TEST_TRY_CATCH_XACT_ABORT;
Résultat :
 
ErrNumber 8134, ErrSeverity 16, ErrState 1, ErrProcedure PS_U_TEST_TRY_CATCH_XACT_ABORT,
ErrLine 20
ErrMessage Division par zéro.
XACT_STATE() = -1  → ROLLBACK
SELECT * FROM dbo.Temp01
Résultat :
(Aucun enregistrement)
Remarque : Aucune ligne n'a été insérée dans la table dbo.Temp01.
4 - Test n° 2 : Dropez puis créez à nouveau la procédure avec les options ci-dessous
-- SET XACT_ABORT ON; -- (1)
SET XACT_ABORT OFF;   -- (2)
...
-- ROLLBACK TRAN; -- (5)
COMMIT TRAN;      -- (6)
Videz également la table dbo.Temp01
Truncate TABLE dbo.Temp01;
EXEC dbo.PS_U_TEST_TRY_CATCH_XACT_ABORT;
Résultat :
ErrNumber 8134, ErrSeverity 16, ErrState 1, ErrProcedure PS_U_TEST_TRY_CATCH_XACT_ABORT, 
ErrLine 20
ErrMessage Division par zéro.
XACT_STATE() = 1 → ROLLBACK OU COMMIT A vous de voir ...
SELECT * FROM dbo.Temp01
Résultat :
Id Libelle
1 Ecartez la vanité, elle gêne l'orgueil
Remarque : Malgré la levée de l'exception, la transaction en cours n'était pas considérée comme non validable et le COMMIT a bien eu lieu et une ligne a bien été insérée dans la table dbo.Temp01.

III - Notion de Transaction non validable

Comme vous avez pu l'observer au travers l'exemple ci-dessus, au sein d'une construction TRY…CATCH, une transaction peut passer dans un état dans lequel elle demeure ouverte mais qui ne permet pas sa validation.
Le plus préoccupant, dans cet état est que les verrous acquis par la transaction sont conservés tant qu'une instruction ROLLBACK n'est pas émise !
Par exemple, la plupart des erreurs d'une instruction DDL (Data Definition Language), telle que CREATE TABLE, ou des erreurs qui se produisent lorsque SET XACT_ABORT a la valeur ON mettent fin à la transaction en dehors d'un bloc TRY mais rendent une transaction non validable à l'intérieur d'un bloc TRY.

Comme vous l'avez vu au travers l'exemple du paragraphe II, le code d'un bloc CATCH doit tester l'état d'une transaction à l'aide de la fonction XACT_STATE. XACT_STATE retourne -1 si la session contient une transaction qui ne peut pas être validée. Le bloc CATCH ne doit en aucun cas valider la transaction (c.à ne doit pas faire un COMMIT) si XACT_STATE retourne une valeur -1.

SET XACT_ABORT indique l'action que SQL Server doit effectuer suite à une erreur d'exécution. Le paramètre de session par défaut est SET XACT_ABORT OFF, ce qui signifie que seule l'instruction T-SQL qui a déclenché l'erreur est annulée, et la transaction se poursuit. Même lorsque SET XACT_ABORT est définie à OFF, selon la gravité de l'erreur, la transaction entière ou un lot T-SQL peut être annulée.

IV - Comment définir SET XACT_ABORT ON ou OFF ?

Notez qu'avec les pools de connexions, le fait de fermer la connexion sans effectuer un ROLLBACK explicite, aura juste pour effet de retourner la connexion au pool de connexion pour une réutilisation ultérieure, mais la transaction restera ouverte jusqu'à ce que la connexion soit réutilisée ou retirée du pool. Et pendant tout ce temps les verrous posés par la transaction resteront actifs. Il en résulte des « Locks » et des blocages de l'application.

SET XACT_ABORT ON demande à SQL Server d'annuler la totalité de la transaction, lorsqu'une erreur se produit pendant l'exécution du traitement. Retenez toutefois que les erreurs de compilation (erreurs de syntaxe, par exemple) ne sont pas affectés par SET XACT_ABORT.

L'expérience montre que SET XACT_ABORT ON contribue à ce que les applications deviennent plus stables et plus robustes. En effet, SET XACT_ABORT ON permet d'assurer, qu'en cas d'erreur, les transactions seront annulées, et les verrous libérés, et ce même si le code des applications n'effectue pas correctement le nettoyage.

A moins que vous ayez vraiment une raison particulière, valable, qui vous oblige de définir SET XACT_ABORT OFF, SET XACT_ABORT ON est fortement recommandé et doit être inclus dans toutes les procédures stockées mettant en jeux des transactions explicites.

Rappelez-vous que les conséquences d'une application qui, sans le vouloir, laisserait des transactions ouvertes et des verrous posés sur les enregistrements, sont désastreuses.

Aucun commentaire:

Enregistrer un commentaire

Remarque : Seul un membre de ce blog est autorisé à enregistrer un commentaire.