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 |
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 |
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 |
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 |
EXEC dbo.PS_U_TEST_TRY_CATCH_XACT_ABORT;| Code : | Sélectionner tout |
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é |
| Code SQL : | Sélectionner tout |
SELECT * FROM dbo.Temp01
| Code : | Sélectionner tout |
(Aucun enregistrement)
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 |
Truncate TABLE dbo.Temp01;
| Code SQL : | Sélectionner tout |
EXEC dbo.PS_U_TEST_TRY_CATCH_XACT_ABORT;| Code : | Sélectionner tout |
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é) |
| Code SQL : | Sélectionner tout |
SELECT * FROM dbo.Temp01
| Code : | Sélectionner tout |
1 2 | Id Libelle 1 Ecartez la vanité, elle gêne l'orgueil |
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+