SQL Server : à propos des transactions “imbriquées”
Il existe des cas où nous aurions besoin d’utiliser des transactions imbriquées (des sous-transactions) constituant des petites unités de travail à l’intérieur d’une plus grande. Nous voulons que chaque petite unité soit autonome, tout en pouvant être annulée par la transaction l’englobant.
Partons du principe que :
- chaque tâche (1, 2, 3, 4 et 5) peut s’accomplir même si une autre est en échec
- l’accomplissement des sous-tâches 4.1 et 4.2 est critique pour celui de la tâche 4
En cas de problème sur les sous-tâches 4.1 et/ou 4.2, nous aimerions ici pouvoir annuler les modifications effectuées par la tâche 4 (et ses sous-tâches) sans pour autant annuler celles effectuées par les autres tâches (1, 2, 3 et 5).
Si votre première idée a été d’imbriquer des transactions, vous venez de vous engager sur une mauvaise piste : il faut savoir qu’imbriquer des ordres “BEGIN TRANSACTION;” (“BEGIN TRAN;”) ne vous permettra pas d’obtenir le comportement de transactions imbriquées auquel vous vous attendiez.
En effet, si les ordres “COMMIT TRANSACTION;” (“COMMIT;”, “COMMIT WORK;”, “COMMIT TRAN;”) suivront bel et bien vos différents ordres “BEGIN TRAN;”, il n’en est absolument pas de même pour un ordre “ROLLBACK TRANSACTION;” (“ROLLBACK;”, “ROLLBACK WORK;”, “ROLLBACK TRAN;”).
Nous parlons ici de transactions explicites standards (non distribuées), pas des modes autocommit et implicite. Sauf mention contraire, nous assumerons la valeur par défaut pour “SET XACT_ABORT”, c’est à dire OFF.
Par ailleurs ce post est basé sur SQL Server 2005, mais il n’y a pas à ma connaissance de changement du côté de la gestion des “transactions imbriqués” sur une version SQL Server 2008 (le comportement sous SQL Server 2000 devrait être aussi le même mais n’a pas du tout été étudié/testé).
Quelques explications sur le comportement de COMMIT et ROLLBACK dans une imbrication de transactions
Déjà, il est important de noter qu’un ordre COMMIT sur une transaction autre que celle se situant le plus à l’extérieur de l’imbrication (celle qui “contient” toutes les autres) n’a pas de réel effet sur les données et les ressources : elle ne fait au final que décrémenter de 1 le compte de transactions représenté par @@TRANCOUNT.
C’est au moment où vous effectuerez le commit sur la transaction globale (@@TRANCOUNT vaudra 1) que les modifications seront effectivement appliquées et que les ressources seront libérées.
Petit schéma représentant le comportement pour une imbrication de 3 transactions, en donnant la valeur de @@TRANCOUNT associée :
Le comportement de ROLLBACK présente de son côté une petite spécificité à garder à l’esprit : contre toute attente il ne fait pas une décrémentation du nombre de transactions, mais effectue directement un rollback de tout ce qui a été fait entre le tout premier ordre “BEGIN TRAN;” et l’ordre ROLLBACK.
De nouveau un petit schéma pour une imbrication de 3 transactions :
Si on ne prend pas conscience de ce fonctionnement suffisamment tôt, la découverte peut être assez douloureuse…
Petit exemple de script pour illustrer :
USE [Tests];
GO
IF EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[TestsTransactions]')
AND type in (N'U'))
DROP TABLE [dbo].[TestsTransactions];
GO
CREATE TABLE [dbo].[TestsTransactions]
(
[Number] int NOT NULL
)
GO
CREATE UNIQUE NONCLUSTERED INDEX [UQ_TestsTransactions_Number]
ON [dbo].[TestsTransactions]
(
[Number] ASC
)
GO
BEGIN TRAN;
BEGIN TRY
INSERT INTO [dbo].[TestsTransactions] ([Number])
VALUES (1);
INSERT INTO [dbo].[TestsTransactions] ([Number])
VALUES (2);
-- Ces ordres INSERT sont censés pouvoir échouer
-- sans conséquence pour les ordres précédents
BEGIN TRY
BEGIN TRAN;
INSERT INTO [dbo].[TestsTransactions] ([Number])
VALUES (3);
INSERT INTO [dbo].[TestsTransactions] ([Number])
VALUES (1);
INSERT INTO [dbo].[TestsTransactions] ([Number])
VALUES (4);
COMMIT;
END TRY
BEGIN CATCH
-- "ROLLBACK 1"
ROLLBACK;
-- TODO : log & co
PRINT N'Erreur : ' + ERROR_MESSAGE();
END CATCH
INSERT INTO [dbo].[TestsTransactions] ([Number])
VALUES (5);
END TRY
BEGIN CATCH
-- "ROLLBACK 2"
ROLLBACK;
-- TODO : log & co
PRINT N'Erreur : ' + ERROR_MESSAGE();
END CATCH
-- Cet ordre provoque une erreur si "ROLLBACK 1" ou "ROLLBACK 2"
-- ont été exécutés :
-- Msg 3902, Level 16, State 1, Line ...
-- The COMMIT TRANSACTION request has no corresponding
-- BEGIN TRANSACTION.
-- Et au final seule la valeur 5 sera présente en base, les
-- valeurs 1 et 2 auront été annulées
COMMIT;
Le code ci-dessus tentant d’insérer une valeur en double dans une colonne sur laquelle un index UNIQUE est défini, l’ordre “ROLLBACK 2” est exécutée, entrainant la levée d’une erreur quand notre code parvient à l’ordre COMMIT : l’ordre COMMIT n’a pas de BEGIN TRANSACTION correspondant.
La levée de cette erreur peut être évitée en testant au préalable certaines conditions au moyen de @@TRANCOUNT ou XACT_STATE() :
- @@TRANCOUNT permet de connaitre le nombre de “transactions imbriquées” :
- si la valeur est 0, il n’y a pas de transaction active
- si la valeur vaut x et x est supérieure à 0, il y a x “transactions imbriquées”, ce qui ne veut pour autant pas dire que la transaction courante est dans un état validable
- XACT_STATE() permet de déterminer l’état de la transaction active, mais ne permettra pas de connaitre le niveau d’imbrication :
Cette fonction scalaire peut retourner 3 valeurs : - 1 : une transaction est active, dans un état validable : nous pouvons faire ce que nous voulons
- 0 : il n’y a pas de transaction active
- -1 : une transaction est active, mais dans un état non validable : tant qu’elle n’aura pas été invalidée (par un ordre ROLLBACK) nous ne pouvons effectuer que des opérations de lecture (écrire n’aurait pas de sens : nous ne pourrons pas valider la transaction)
Il est assez clair que nous ne parviendrons pas à notre but avec une imbrication de transactions au sens “pur”. Le provider ADO.NET pour SQL Server devrait d’ailleurs lever une exception en cas d’appel multiple à BeginTransaction sans fermeture de transaction intermédiaire.
Utiliser un nom pour les transactions ne servira pas à grand chose : on ne doit en théorie nommer que la première transaction, les noms des autres ne seront à ma connaissance même pas persistés (mais ça reste cependant pratique à la lecture du code).
Il ne s’agit au final, de mon point de vue, que d’une seule transaction.
D’après la documentation ce mécanisme a d’ailleurs été mis en place principalement pour que des procédures stockées gérant leur propre transaction interne puissent être exécutées aussi bien au sein d’un processus définissant sa propre transaction que d’un processus n’en définissant pas.
Les points de sauvegarde (savepoints) à la rescousse !
Malgré les mauvaises nouvelles énoncées plus haut, nous avons un moyen de parvenir à obtenir ce que nous voulons : nous allons utiliser les points de sauvegarde (savepoints), qui vont nous permettre de n’invalider qu’une partie de la transaction.
Il s’agit bien ici de n’avoir qu’une seule transaction, et de bâtir le mécanisme de “transactions imbriquées” que nous voulions grâce aux points de sauvegarde.
Point important à noter : les points de sauvegarde ne sont pas supportés dans une transaction distribuée, qu’elle ait été distribuée dès le début ou promue depuis une transaction locale standard.
Veuillez bien à prendre ce détail en compte dès le début du projet, surtout si vous planifiez d’utiliser les éléments exposés dans le namespace System.Transactions.
Côté T-SQL, l’ordre permettant de créer un point de sauvegarde est “SAVE TRANSACTION” (“SAVE TRAN”).
Les noms des points de sauvegardes sont soumis aux règles standards des identificateurs sous SQL Server, hormis que la longueur maximale est limitée à 32 caractères (si le nom est plus long, il sera tronqué à 32 caractères).
Côté client, le provider ADO.NET pour SQL Server fournit la méthode Save sur l’instance de SqlTransaction représentant la transaction courante.
Cette méthode devant au final générer un ordre “SAVE TRANSACTION”, il serait bon de s’en tenir aux mêmes limitations au moment du choix du nom.
En même temps si vous avez besoin de noms unique ça tombe bien, 32 caractères est justement le nombre de caractères minimum (sans habillage) de la représentation sous forme de chaine de caractères d’un GUID.
Ainsi, notre exemple bateau de tout à l’heure pourrait être corrigé de la manière suivante :
USE [Tests];
GO
IF EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[TestsTransactions]')
AND type in (N'U'))
DROP TABLE [dbo].[TestsTransactions];
GO
CREATE TABLE [dbo].[TestsTransactions]
(
[Number] int NOT NULL
)
GO
CREATE UNIQUE NONCLUSTERED INDEX [UQ_TestsTransactions_Number]
ON [dbo].[TestsTransactions]
(
[Number] ASC
)
GO
BEGIN TRAN;
BEGIN TRY
INSERT INTO [dbo].[TestsTransactions] ([Number])
VALUES (1);
INSERT INTO [dbo].[TestsTransactions] ([Number])
VALUES (2);
-- Ces ordres INSERT sont censés pouvoir échouer
-- sans conséquence pour les ordres précédents
BEGIN TRY
SAVE TRANSACTION mysavepoint;
INSERT INTO [dbo].[TestsTransactions] ([Number])
VALUES (3);
INSERT INTO [dbo].[TestsTransactions] ([Number])
VALUES (1);
INSERT INTO [dbo].[TestsTransactions] ([Number])
VALUES (4);
END TRY
BEGIN CATCH
-- "ROLLBACK 1"
IF XACT_STATE() <> -1
BEGIN
-- Transaction valide, nous pouvons
-- effectuer un rollback vers notre
-- point de sauvegarde
ROLLBACK TRANSACTION mysavepoint;
END
ELSE
BEGIN
-- Transaction invalide,
-- rollback total
ROLLBACK;
-- Remontée de l'erreur
DECLARE @ErrorMessage nvarchar(4000);
DECLARE @ErrorSeverity int;
DECLARE @ErrorState int;
SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
RAISERROR (@ErrorMessage,
@ErrorSeverity,
@ErrorState
);
END
-- TODO : log & co
PRINT N'Erreur (bloc 2) : ' + ERROR_MESSAGE();
END CATCH
-- Nous n'effectuons l'ordre que si
-- une transaction est active
IF XACT_STATE() = 1
INSERT INTO [dbo].[TestsTransactions] ([Number])
VALUES (5);
END TRY
BEGIN CATCH
-- "ROLLBACK 2"
ROLLBACK;
-- TODO : log & co
PRINT N'Erreur (bloc 1) : ' + ERROR_MESSAGE();
END CATCH
IF XACT_STATE() = 1
COMMIT;
Une fois ce script exécuté, sans erreur entrainant un rollback total de la transaction, la table contiendra bien les entrées 1, 2 et 5.
Quelques notes complémentaires sur les points de sauvegarde
Vous pouvez utiliser plusieurs fois le même nom de point de sauvegarde : un appel ROLLBACK TRANSACTION sur ce nom effectuera simplement un retour au dernier point de sauvegarde crée avec le nom, un second appel permettra de revenir au précédent et ainsi de suite.
Exemple illustrant assez bien la chose :
BEGIN TRAN;
SAVE TRANSACTION [INSERT];
INSERT INTO [dbo].[TestsTransactions] ([Number])
VALUES (1);
SAVE TRANSACTION [INSERT];
INSERT INTO [dbo].[TestsTransactions] ([Number])
VALUES (2);
SAVE TRANSACTION [INSERT];
INSERT INTO [dbo].[TestsTransactions] ([Number])
VALUES (3);
SAVE TRANSACTION [INSERT];
INSERT INTO [dbo].[TestsTransactions] ([Number])
VALUES (4);
SAVE TRANSACTION [INSERT];
INSERT INTO [dbo].[TestsTransactions] ([Number])
VALUES (5);
ROLLBACK TRANSACTION [INSERT];
ROLLBACK TRANSACTION [INSERT];
IF XACT_STATE() = 1
COMMIT;
La table contiendra en sortie les valeurs 1, 2 et 3.
Pour reprendre un schéma similaire à celui présenté en début de post, en donnant le contenu de la table :
Ceci est bien entendu aussi valable dans le contexte d’une boucle :
BEGIN TRAN;
DECLARE @CurrentValue int;
SET @CurrentValue = 0;
WHILE (@CurrentValue < 5)
BEGIN
SET @CurrentValue = @CurrentValue +1;
SAVE TRANSACTION [INSERT];
INSERT INTO [dbo].[TestsTransactions] ([Number])
VALUES (@CurrentValue);
END
ROLLBACK TRANSACTION [INSERT];
ROLLBACK TRANSACTION [INSERT];
IF XACT_STATE() = 1
COMMIT;
Enfin, gardez bien à l’esprit le fonctionnement linéaire : utiliser des noms différents ne vous permettra pas d’annuler les changements d’un point de sauvegarde antérieur sans annuler les modifications représentées par ceux qui ont eu lieu après.
Dans l’exemple suivant la table contiendra les valeurs 1, 2 et 3 (pas 1, 2, 3 et 5) :
BEGIN TRAN;
SAVE TRANSACTION [INSERT1];
INSERT INTO [dbo].[TestsTransactions] ([Number])
VALUES (1);
SAVE TRANSACTION [INSERT2];
INSERT INTO [dbo].[TestsTransactions] ([Number])
VALUES (2);
SAVE TRANSACTION [INSERT3];
INSERT INTO [dbo].[TestsTransactions] ([Number])
VALUES (3);
SAVE TRANSACTION [INSERT4];
INSERT INTO [dbo].[TestsTransactions] ([Number])
VALUES (4);
SAVE TRANSACTION [INSERT5];
INSERT INTO [dbo].[TestsTransactions] ([Number])
VALUES (5);
ROLLBACK TRANSACTION [INSERT4];
IF XACT_STATE() = 1
COMMIT;
Le petit schéma associé :
Et c’est là encore valable dans le contexte d’une boucle :
BEGIN TRAN;
DECLARE @CurrentValue int;
SET @CurrentValue = 0;
DECLARE @SavePointName nvarchar(32);
WHILE (@CurrentValue < 5)
BEGIN
SET @CurrentValue = @CurrentValue + 1;
-- Génération du nom de savepoint à partir
-- de la valeur courante : 'INSERT#'
SET @SavePointName = N'INSERT'
+ CAST(@CurrentValue AS nvarchar);
SAVE TRANSACTION @SavePointName;
INSERT INTO [dbo].[TestsTransactions] ([Number])
VALUES (@CurrentValue);
END
ROLLBACK TRANSACTION [INSERT4];
IF XACT_STATE() = 1
COMMIT;
Exemple d’implémentation du cas cité en début de post
En enlevant les remontées d’erreurs pour que le script soit moins long.
Changez les valeurs des variables @EchecEnEtape* pour provoquer des échecs dans les étapes désignées.
USE [Tests];
GO
IF EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[EtapesAccomplies]')
AND type in (N'U'))
DROP TABLE [dbo].[EtapesAccomplies];
GO
CREATE TABLE [dbo].[EtapesAccomplies]
(
[ID] int IDENTITY(1,1),
[Etape] nvarchar(32) NOT NULL
)
GO
SET NOCOUNT ON;
-- Variables servant a définir si une étape doit échouer
DECLARE @EchecEnEtape1 bit;
DECLARE @EchecEnEtape2 bit;
DECLARE @EchecEnEtape3 bit;
DECLARE @EchecEnEtape41 bit;
DECLARE @EchecEnEtape42 bit;
DECLARE @EchecEnEtape5 bit;
-- Définition des étapes devant échouer
SET @EchecEnEtape1 = 'true';
SET @EchecEnEtape2 = 'false';
SET @EchecEnEtape3 = 'true';
SET @EchecEnEtape41 = 'false';
SET @EchecEnEtape42 = 'true';
SET @EchecEnEtape5 = 'false';
DECLARE @DummyInt int;
BEGIN TRAN;
-- ###########
-- # Etape 1 #
-- ###########
BEGIN TRY
PRINT N'Etape 1';
SAVE TRANSACTION [Etape1];
INSERT INTO [dbo].[EtapesAccomplies] ([Etape])
VALUES (N'Etape 1');
-- Echec si demandé
SELECT @DummyInt = 1/0 WHERE @EchecEnEtape1='true';
END TRY
BEGIN CATCH
PRINT N'Echec étape 1';
IF XACT_STATE() <> -1
ROLLBACK TRANSACTION [Etape1];
ELSE
ROLLBACK;
END CATCH
IF XACT_STATE() = 1
BEGIN
-- ###########
-- # Etape 2 #
-- ###########
BEGIN TRY
PRINT N'Etape 2';
SAVE TRANSACTION [Etape2];
INSERT INTO [dbo].[EtapesAccomplies] ([Etape])
VALUES (N'Etape 2');
-- Echec si demandé
SELECT @DummyInt = 1/0 WHERE @EchecEnEtape2='true';
END TRY
BEGIN CATCH
PRINT N'Echec étape 2';
IF XACT_STATE() <> -1
ROLLBACK TRANSACTION [Etape2];
ELSE
ROLLBACK;
END CATCH
END -- IF XACT_STATE() = 1
IF XACT_STATE() = 1
BEGIN
-- ###########
-- # Etape 3 #
-- ###########
BEGIN TRY
PRINT N'Etape 3';
SAVE TRANSACTION [Etape3];
INSERT INTO [dbo].[EtapesAccomplies] ([Etape])
VALUES (N'Etape 3');
-- Echec si demandé
SELECT @DummyInt = 1/0 WHERE @EchecEnEtape3='true';
END TRY
BEGIN CATCH
PRINT N'Echec étape 3';
IF XACT_STATE() <> -1
ROLLBACK TRANSACTION [Etape3];
ELSE
ROLLBACK;
END CATCH
END -- IF XACT_STATE() = 1
IF XACT_STATE() = 1
BEGIN
-- ###########
-- # Etape 4 #
-- ###########
BEGIN TRY
PRINT N'Etape 4';
SAVE TRANSACTION [Etape4];
INSERT INTO [dbo].[EtapesAccomplies] ([Etape])
VALUES (N'Etape 4');
-- #############
-- # Etape 4.1 #
-- #############
PRINT N'Etape 4.1';
INSERT INTO [dbo].[EtapesAccomplies] ([Etape])
VALUES (N'Etape 4.1');
-- Echec si demandé
SELECT @DummyInt = 1/0 WHERE @EchecEnEtape41='true';
-- #############
-- # Etape 4.2 #
-- #############
PRINT N'Etape 4.2';
INSERT INTO [dbo].[EtapesAccomplies] ([Etape])
VALUES (N'Etape 4.2');
-- Echec si demandé
SELECT @DummyInt = 1/0 WHERE @EchecEnEtape42='true';
END TRY
BEGIN CATCH
PRINT N'Echec étape 4';
IF XACT_STATE() <> -1
ROLLBACK TRANSACTION [Etape4];
ELSE
ROLLBACK;
END CATCH
END -- IF XACT_STATE() = 1
IF XACT_STATE() = 1
BEGIN
-- ###########
-- # Etape 5 #
-- ###########
BEGIN TRY
PRINT N'Etape 5';
SAVE TRANSACTION [Etape5];
INSERT INTO [dbo].[EtapesAccomplies] ([Etape])
VALUES (N'Etape 5');
-- Echec si demandé
SELECT @DummyInt = 1/0 WHERE @EchecEnEtape5='true';
END TRY
BEGIN CATCH
PRINT N'Echec étape 5';
IF XACT_STATE() <> -1
ROLLBACK TRANSACTION [Etape5];
ELSE
ROLLBACK;
END CATCH
END -- IF XACT_STATE() = 1
IF XACT_STATE() = 1
COMMIT;
SELECT [Etape]
FROM [dbo].[EtapesAccomplies]
ORDER BY [ID] ASC;
SET NOCOUNT OFF;
Ce post vous a plu ? Ajoutez le dans vos favoris pour ne pas perdre de temps à le retrouver le jour où vous en aurez besoin :
