Transactions imbriquées et SAVE TRANSACTION

Lorsque l’on travaille avec des transactions imbriquées, on veut parfois pour voir annuler une transaction intermédiaire mais garder les précédentes opérations.

En T-SQL, l’opération de ROLLBACK annule l’ensemble des niveaux de transactions (scopes). Pour s’en convaincre, cette citation de la documentation (ROLLBACK) :

When nesting transactions, this same statement rolls back all inner transactions to the outermost BEGIN TRANSACTION statement.

Le code suivant montre un exemple du problème. Le premier ROLLBACK annule tout depuis la transaction A. La variable @@trancount permet de voir qu’il n’y a plus de transaction en cours

BEGIN TRAN A
-------on démarre le premier niveau de transaction

INSERT INTO [AdventureWorks].[dbo].[truc]([a],[b])
VALUES ('transaction A','transaction A')

select @@trancount --returns 1

BEGIN TRAN B
-------on démarre le deuxième niveau de transaction

INSERT INTO [AdventureWorks].[dbo].[truc]([a], [b])
VALUES ('transaction B','transaction B')

select @@trancount --returns 2

ROLLBACK TRANSACTION B
--on annule la transaction intermédiaire

select @@trancount
--returns 0 : plus de transaction en cours

COMMIT TRAN A --génère une erreur
--Msg 6401, Level 16, State 1, Line 19
--Cannot roll back B. No transaction or savepoint of that name was found.

Ce comportement ne nous permet donc pas d’annuler une partie du traitement tout en gardant le reste. Heureusement, l’instruction SAVE TRANSACTION nous permet de corriger le tir. SAVE TRANSACTION permet de mettre un point de sauvegarde dans la transaction en cours pour pouvoir y revenir par la suite, avec l’instruction ROLLBACK.

Voici le script modifié. A la fin, la ligne A est insérée mais pas la B.

BEGIN TRAN A
-------on démarre le premier niveau de transaction

INSERT INTO [AdventureWorks].[dbo].[truc]([a],[b])
VALUES ('transaction A','transaction A')

select @@trancount --returns 1

BEGIN TRAN B
-------on démarre le deuxième niveau de transaction

SAVE TRANSACTION B_Saved
--on met un point de sauvegarde

INSERT INTO [AdventureWorks].[dbo].[truc]([a], [b])
VALUES ('transaction B','transaction B')

select @@trancount --returns 2

ROLLBACK TRANSACTION B_Saved

COMMIT B
--on force quand même un commit pour valider la transaction B

select @@trancount --returns 1

COMMIT TRAN A

A noter que l’on doit quand même faire un COMMIT de la transaction enfant car le retour à un point de sauvegarde ne termine pas la transaction.

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s