Voir le flux RSS

hmira

[Actualité] SQL Server - Gestion des exceptions TRY .. CATCH Rendre une application plus robuste

Noter ce billet
par , 04/01/2015 à 14h41 (1482 Affichages)
I - Introduction

Depuis SQL Server 2005, (et donc tout ce qui suit est valable également sous SQL Server 2008, 2008R2, 2012, 2014 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ée 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 ..CATCHdisponible depuis la version SQL Server 2005 (et donc valable également sous SQL Server 2008, 2008R2, 2012, 2014 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 des 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.), vous pouvez annuler et mettre fin aux transactions en erreur et libérer ainsi les verrous posés par les transactions sur les enregistrements.

Les verrous, posé sur les enregistrements, non libérés, acquis par une transaction en erreurs, inachevée, sont la sources de nombreux 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

Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
CREATE TABLE [dbo].[Temp01](  
      Id       INT NOT NULL,   
      Libelle  VARCHAR(50) NULL  
)   
ON [PRIMARY];   
GO

2 - Script de la procédure modèle pour les tests

Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
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;
	      PRINT 'ROLLBACK, fortement recommandé, effectué' ;
          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                                                            
	   PRINT 'ROLLBACK effectué (le choix de faire un rollback a été délibéré) ' ; -- (5) On fait notre premier test avec  ROLLBACK TRAN  
            -- COMMIT TRAN; -- (6) On fait autre test avec COMMIT TRAN; 
	    -- PRINT 'COMMIT effectué (le choix de faire un commit a été délibéré) ' ; -- (6) On fait autre test avec COMMIT TRAN  
         END;
  END CATCH;
END;
GO
Remarque : Lisez attentivement les commentaires rajoutées dans le code de la procédure ci-dessus. En effet, toute les explications importantes, ayant trait à la construction TRY CATCH, et à l'option XACT_ABORT, se trouvent dans ces commentaires !

3 - Test n° 1 :

- Créez la procédure avec les options (1) et (5) ci-dessous
Ligne n° 8 :
SET XACT_ABORT ON; -- (1) On fait notre premier test avec XACT_ABORT ON

Ligne n° 63 et 64
ROLLBACK TRAN; -- (5) On fait notre premier test avec ROLLBACK TRAN
PRINT 'ROLLBACK effectué (le choix de faire un rollback a été délibéré) ' ; -- (5) On fait notre premier test avec

- Puis exécutez la procédure :
Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
EXEC dbo.PS_U_TEST_TRY_CATCH_XACT_ABORT;
Résultat :
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
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  
ROLLBACK, fortement recommandé, effectué
Vérifions le contenu de la table dbo.Temp01
Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
SELECT * FROM dbo.Temp01
Résultat :
Remarque : Aucune ligne n'a été insérée dans la table dbo.Temp01. Ceci est du au rollback, fortement recommandé, effectué. Voir trace ci-dessus de l'intruction print.

4 - Test n° 2 :

- Dropez puis créez à nouveau la procédure avec les options (2) et (6) ci-dessous :

Ligne n° 9
SET XACT_ABORT OFF; -- (2) Faites également un test avec SET XACT_ABORT OFF, ..

Ligne n° 65 et 66
COMMIT TRAN; -- (6) On fait autre test avec COMMIT TRAN;
PRINT 'COMMIT effectué (le choix de faire un commit a été délibéré) ' ; -- (6) On fait autre test avec COMMIT TRAN;

NB : N'oubliez pas pour le teste n° 2 de mettre en commentaire les lignes correspondantes aux options (1) et (5) du test n° 1

- Videz également la table dbo.Temp01
Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
Truncate TABLE dbo.Temp01;
- Puis exécutez à nouveau la procédure
Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
EXEC dbo.PS_U_TEST_TRY_CATCH_XACT_ABORT;
Résultat :
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
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 ... 
COMMIT effectué (le choix de faire un commit a été délibéré)
Vérifions le contenu de la table dbo.Temp01
Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
SELECT * FROM dbo.Temp01
Résultat :
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
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 (choix délibéré) a bien eu lieu et une ligne a bien été insérée dans la table dbo.Temp01. Les lignes de trace ci-dessus des instructions print montre bien cet état de fait.

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 ROLLBACKn'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 de l'applications n'effectue pas correctement le nettoyage.

A moins que vous ayez vraiment des raisons particulières, valables, qui vous obligent de définir SET XACT_ABORT OFF, SET XACT_ABORT ON est fortement recommandée et doit être incluse 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.

A+

Envoyer le billet « SQL Server - Gestion des exceptions TRY .. CATCH Rendre une application plus robuste » dans le blog Viadeo Envoyer le billet « SQL Server - Gestion des exceptions TRY .. CATCH Rendre une application plus robuste » dans le blog Twitter Envoyer le billet « SQL Server - Gestion des exceptions TRY .. CATCH Rendre une application plus robuste » dans le blog Google Envoyer le billet « SQL Server - Gestion des exceptions TRY .. CATCH Rendre une application plus robuste » dans le blog Facebook Envoyer le billet « SQL Server - Gestion des exceptions TRY .. CATCH Rendre une application plus robuste » dans le blog Digg Envoyer le billet « SQL Server - Gestion des exceptions TRY .. CATCH Rendre une application plus robuste » dans le blog Delicious Envoyer le billet « SQL Server - Gestion des exceptions TRY .. CATCH Rendre une application plus robuste » dans le blog MySpace Envoyer le billet « SQL Server - Gestion des exceptions TRY .. CATCH Rendre une application plus robuste » dans le blog Yahoo

Commentaires