[SQL] Transactions, ROLLBACK et XACT_STATE

J’étais en train de lire quelques procédures stockées (oui essayez c’est divertissant) quand je suis tombé sur deux blocs de T-SQL qui m’ont laissé dubitatif la première fois, et qui sont pourtant assez fréquents.

BEGIN TRAN suivi de SAVE TRAN

Le premier concerne le ROLLBACK d’une transaction ouverte dans une procédure stockée. Avec un bout de code de ce style:

BEGIN TRAN
SAVE TRAN MaTransaction
--...
--Traitements
--...
IF @@ERROR 0
BEGIN
ROLLBACK TRAN MaTransaction
COMMIT TRAN
RETURN (1)
END

A quoi cela sert-t-il? J’avoue avoir été dubitatif. Pourquoi ne pas faire un simple ROLLBACK? Et bien c’est mal connaitre SQL Server que de croire qu’exécuter un ROLLBACK au sein d’une transaction ne va rollbacker que la transaction imbriquée du niveau le plus bas. Je cite l’amie MSDN:

[…] dans l’instruction ROLLBACK TRANSACTION, l’annulation sera effectuée à partir du début de la transaction. En cas d’imbrication des transactions, cette même instruction annule toutes les transactions internes jusqu’à l’instruction BEGIN TRANSACTION la plus extérieure. Dans les deux cas, ROLLBACK TRANSACTION ramène à 0 la fonction système @@TRANCOUNT.

Pour rollbacker une transaction appellée hypothétiquement depuis un contexte transactionnel (sans prendre le risque de tout rollbacker) il faut sauver la transaction. Comme le SAVE doit être appellé depuis un contexte transactionnel (dont on n’est pas certain je le rappelle) on est obligé d’en démarrer une quand même (d’ou le BEGIN TRAN initial).

En cas d’erreur on peut alors rollbacker, ou committer (ce qui ramène TRANCOUNT à sa valeur initiale en le décrémentant).

Utilisation de XACT_STATE et de XACT_ABORT

XACT_ABORT est une variable système assez simple: elle stipule simplement si SQL doit interrompre automatiquement une transaction en cas d’erreur. Par défaut (OFF) il le fait pour des erreurs d’une sévérité suffisante. On voit donc souvent du T-SQL commencer par

SET XACT_ABORT ON

On rencontre cependant moins souvent son camarade XACT_STATE, qui présente pourtant un intérêt non négligeable….
Pour faire simple, SQL Server a introduit un statut particulier pour la transaction, le cas ou elle n’est pas commitable.

Un bout de code assez connu est une tentative de casting implicite de CHAR dans un INT, le tout dans un TRY, que l’on tente de committer.

On récupère alors la fameuse erreur:

Msg 3930, Level 16, State 1, Line 15 The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.

C’est là qu’intervient l’ami XACT_STATE: il indique si la transaction est committable. Vous verrez donc quelquefois ce bout de code:


BEGIN TRY
...
END TRY

BEGIN CATCH
IF (XACT_STATE()) = -1
BEGIN
ROLLBACK TRANSACTION;
END;
IF (XACT_STATE()) = 1
BEGIN
COMMIT TRANSACTION;
END;
END CATCH;

Merci encore à mes « papas T-SQL » Cédric Del Nibbio et Radu Negru qui m’ont expliqué toutes ces petites subtilités🙂.

A bientôt!

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