[SQL Server] Transaction : présentation élémentaire et règles ACID

Ce billet présente brièvement ce qu’est une transaction, ainsi que ses propriétés ACID et quelques éléments de gestion d’erreurs transactionnelles.

Notions de transaction

    Qu’est-ce qu’une transaction

Une transaction est une instruction ou une séquence de plusieurs instructions définies comme une simple unité logique de travail. Toutes ces instructions doivent donc se terminer avec succès (ou échouer) comme une simple unité de travail.

Il existe 3 types de transactions :

  • Les transactions locales à il s’agit des transactions créées localement par un utilisateur.
  • Les transactions imbriquées à cela signifie qu’une nouvelle transaction peut être lancée au sein d’une autre transaction, même si cette dernière n’est pas terminée. La variable système @@TRANCOUNT peut être utilisée pour déterminer le niveau d’imbrication d’une transaction (0 : pas imbriquée ; 1 : 1er niveau d’imbrication ; … ; 4 : 4ème niveau d’imbrication), sachant que :
    • Chaque création de transaction incrémente @@TRANCOUNT.
    • Chaque validation de transaction décrémente @@TRANCOUNT :


    • Une annulation repasse @@TRANCOUNT à 0 en annulant toutes les transactions imbriquées de la transaction cible :



  • Les transactions distribuées à il s’agit de transactions réparties et gérées par MSDTC (Microsoft Distributed Transaction Coordinator).

Les règles ACID

Une transaction possède 4 propriétés qui forment l’acronyme ACID :

  • Atomicité à Une transaction doit être atomique. Cela signifie que soit toutes les instructions de la transaction seront exécutées, soit aucune ne le sera. Cette règle est basée sur le principe du « Tout ou rien ».
  • Consistance à Une transaction doit garantir la consistance d’une base de données. Cela signifie qu’une base de données doit démarrer une transaction dans un état consistant et retourner à un état également consistant une fois la transaction terminée. De ce fait, une base de données doit être capable de non seulement valider toute la transaction, mais également de l’annuler si les modifications réalisées sont inconsistantes. Cette règle sert donc à s’assurer que seules les données valides sont écrites au sein de la base de données en se basant sur le principe d’atomicité transactionnelle.
  • Isolation à Une transaction doit être isolée, et donc séparée des effets des autres transactions. Indépendamment de la finalité des autres transactions, une transaction doit être en mesure de continuer à travers sur exactement les mêmes jeux de résultats qu’il a générés dès le début de son exécution. Cette règle permet donc d’assurer qu’une transaction puisse travailler efficacement sans subir d’anomalies transactionnelles pouvant nuire à la consistance des données.
  • Durabilité à La durabilité transactionnelle signifie qu’une fois une transaction validée, ses modifications réalisées sur la base de données ne doivent pas être perdues en cas de sinistre au niveau matériel ou logiciel impactant ladite base. Cette règle est assurée via l’utilisation d’un journal de transactions (et principalement pour une base de données ayant le mode de récupération FULL), et permet de garantir la durabilité des effets de la transaction après validation de celle-ci.

Les règles ACID sont des concepts qui permettent d’évaluer des bases de données et leur architecture.

Modes d’utilisation d’une transaction en T-SQL

SQL Server offre différentes modes de création et de complétion d’une transaction : utilisation explicite, utilisation implicite et auto-validation.

Utilisation explicite d’une transaction et d’un savepoint

        Création d’une transaction

Pour créer une transaction, il suffit de faire appel à BEGIN suivi de TRAN ou TRANSACTION. Voici la syntaxe de création d’une transaction :

BEGIN {TRAN | TRANSACTION}
 [ { nom_transaction | @variable_nom_transaction }
 [ WITH MARK [ 'description' ] ] ]
[ ; ]

Où :

  • nom_transaction est le nom de la transaction, et peut être omis (indépendamment de @variable_transaction).
  • @variable_nom_transaction est le nom de la variable (de type CHAR, NCHAR, VARCHAR ou NVARCHAR) précédemment déclarée et correspondant au nom que l’on souhaite donner à la transaction (dans ce cas, nom_transaction peut réellement être omis). Il est facultatif.
  • WITH MARK permet de donner la description (limitée à 128 caractères) de la transaction à créer. La spécification du nom de la transaction (via nom_transaction ou @variable_nom_transaction)
    devient obligatoire. Concrètement, en cas d’utilisation d’un marqueur, le nom de la transaction est placé dans le journal de transactions, et sa description stockée dans la table dbo.logmarkhistory située au sein de la base de données système msdb. Seules les transactions qui contribuent à la modification de données sont marquées.

Concernant l’utilisation d’un savepoint (ou point de sauvegarde), cela se fait au sein d’une transaction. Voici la syntaxe de création :

SAVE {TRAN | TRANSACTION}
 {nom_savepoint | @variable_nom_savepoint }
[ ; ]

Où :

  • nom_savepoint correspond au nom d’une instruction SAVE TRANSACTION.
  • @variable_nom_savepoint renvoie à une variable préalablement créée et définissant le nom d’une instruction SAVE TRANSACTION.

L’intérêt d’un savepoint est de permettre à une transaction de faire machine-arrière vers un endroit spécifique de son exécution si une partie de ladite transaction rencontre une condition d’annulation.

Tout savepoint a une durée de vie limitée à la transaction au sein de laquelle il a été déclaré. Et son comportement n’affecte pas la valeur de @@TRANCOUNT.

        COMMIT et ROLLBACK

Le mot-clé COMMIT permet de valider le travail d’une transaction débutée, tandis que ROLLBACK sert à l’annuler. Du point de vue syntaxique :

  • COMMIT :
COMMIT {TRAN | TRANSACTION}
 [ nom_transaction | @variable_nom_transaction ] ]
[ ; ]
  • ROLLBACK :
ROLLBACK {TRAN | TRANSACTION}
 [ nom_transaction | @variable_nom_transaction
 | nom_savepoint | @variable_savepoint ]
[ ; ]

Si aucune des options (nom_transaction, nom_savepoint, les variables…) n’est spécifiée, la validation ou l’annulation se fera à partir du début de la transaction.

Notons que les instructions COMMIT WORK et ROLLBACK WORK peuvent être utilisées à place de COMMIT TRAN(SACTION) et ROLLBACK TRAN(SACTION). La seule différence étant que WORK ne permet pas de spécifier explicitement le nom de la transaction.
Notons également que si COMMIT et ROLLBACK sont utilisées sans mot-clé (TRAN,…), SQL Server se contentera de valider ou d’annuler la transaction depuis sa création.

Utilisation implicite d’une transaction

Sous SQL Server, quand l’option IMPLICITE_TRANSACTIONS est activée, toute requête (qu’il s’agisse d’une requête ad hoc, d’une procédure stockée,…) est implicitement encapsulée au sein d’une transaction dès lors qu’elle exécute au moins un ordre SQL DDL (Data Definition Language ; par exemple, CREATE, DROP, ALTER) ou DML (Data Manipulation Language ; par exemple, SELECT, INSERT, DELETE). Cela signifie qu’en cas de programmation d’une requête, il n’est pas nécessaire de préciser à SQL Server que l’on démarre une transaction (via l’utilisation de BEGIN).

En revanche, l’utilisation de COMMIT (pour la validation) ou de ROLLBACK (pour l’annulation) est nécessaire pour compléter la requête.

A noter que :

  • La spécification implicite d’une transaction par SQL Server se fait à la connexion. Et chaque fois qu’une transaction implicite a été validée ou annulée, SQL Server en recrée une autre implicitement de façon à ce qu’elle soit prête à accueillir toute autre requête. Et ainsi de suite, et ainsi de suite…
  • Les instructions relatives à la gestion des droits utilisateurs (GRANT et REVOKE) sont également encapsulées dans une transaction implicite. Et idem concernant les ordres OPEN (pour l’ouverture d’un curseur, par exemple), FETCH (pour le parcours d’un jeu de résultats) et TRUNCATE TABLE.

Pour contrôler le mode de création d’une transaction par SQL Server, vous pouvez utiliser la requête suivante :

SET IMPLICIT_TRANSACTIONS {ON | OFF}
GO

La valeur OFF est, bien sûr, celle par défaut.

Et pour savoir si le mode implicite est actuellement utilisé ou non :

DECLARE @IMPLICIT_TRANSACTIONS VARCHAR(3)='OFF'
IF ((2 &@@OPTIONS)=2)
 SET @IMPLICIT_TRANSACTIONS='ON'
SELECT @IMPLICIT_TRANSACTIONS AS IMPLICIT_TRANSACTIONS
GO

Utilisation d’une transaction auto-validée (AUTO-COMMIT)

Par défaut, SQL Server encapsule implicitement toute requête au sein d’une transaction (on parle, alors, de transaction implicite) et se charge de valider automatiquement celle-ci (AUTO-COMMIT).

Pour désactiver le mode d’auto-validation, il suffit de changer la valeur de l’option IMPLICIT_TRANSACTION à ON (voir section précédente). Ceci va forcer l’utilisateur à spécifier lui-même COMMIT.

Gestion d’erreurs

Intérêt

Le fait qu’une transaction soit validée ne signifie pas forcément que les données ont été correctement traitées.

Afin d’assurer la consistance des traitements, il est possible d’implémenter de quoi pouvoir annuler la validation potentielle de toute erreur. Pour ce faire, on peut utiliser (indépendamment ou non) des éléments comme :

  • La variable système @@ERROR qui retourne :
    • 0, si une instruction s’exécute avec succès.
    • Un message d’erreur, sinon.
  • RAISERROR pour la définition de messages d’erreurs à retourner (avec leur code et niveau de gravité).
  • Depuis SQL Server 2005, TRY… CATCH, pour la capture et le traitement d’erreurs/exceptions.
  • XACT_ABORT, qui est un indicateur qui, lorsqu’il est à ON (ce qui est le cas par défaut), force SQL Server à annuler automatiquement une transaction en cours d’exécution si une de ses instructions rencontre une erreur. Si XACT_ABORT est à OFF, l’instruction concernée est annulée, mais la transaction à laquelle elle appartient se poursuit (dans la mesure du possible). Cela ne concerne pas les erreurs de syntaxe.
  • La variable système @@TRANCOUNT pour valider ou annuler une transaction en fonction du nombre de transactions actives.
  • Etc…

En outre, SQL Server fournit, depuis la version 2005, une fonction système permettant de récupérer l’état d’une transaction : Xact_State(). Sa valeur vaut 1, si la transaction est active et fonctionnelle, 0 si inactive (ou pas de transaction) et -1, si transaction non-validable.

Il est toutefois plus souhaitable d’éviter d’utiliser XACT_STATE() au sein d’une transaction contenant des instructions DML multiples. En effet, si, par exemple, une des instructions échoue et que la transaction reste toujours validable (XACT_STATE()=1), cela peut conduire à une écriture/validation partielle de ladite transaction, et le risque potentiel d’obtenir des résultats erronés.

Exemples d’utilisation

Voici ci-dessous une série d’exemples basiques permettant de mettre en valeur les différents éléments de la gestion d’erreurs cités dans la sous-section précédente :

  • Exemple d’implémentation d’une transaction qui met à jour la table Voiture, puis se termine par :
    • Une validation si tout se passe bien.
    • Une annulation en cas d’erreur (vérification avec @@ERROR), avec levée du message « Erreur lors de la mise-à-jour de la liste des voitures » via RAISERROR.
BEGIN TRAN
 UPDATE Voiture
 SET Marque='Volvo'
 WHERE Voiture_ID=1
 IF @@ERROR<>0
 BEGIN
  RAISERROR('Erreur lors de la mise-à-jour de la liste des voitures', 16, 1)
  ROLLBACK
 END
COMMIT TRAN
GO
  • Autre exemple, mais avec TRY… CATCH et XACT_STATE :
BEGIN TRAN
 BEGIN TRY
  UPDATE Voiture
  SET Marque='Volvo'
  WHERE Voiture_ID=1
 END TRY
 BEGIN CATCH
  IF XACT_STATE()=1
   COMMIT TRAN
  IF XACT_STATE()=-1
   ROLLBACK TRAN
 END CATCH
GO
  • Encore un autre exemple avec TRY… CATCH et @@TRANCOUNT :
BEGIN TRAN
 BEGIN TRY
  UPDATE Voiture
  SET Marque='Volvo'
  WHERE Voiture_ID=3
 END TRY
 BEGIN CATCH
  SELECT ERROR_NUMBER() AS ErrorNumber,
   ERROR_SEVERITY() AS ErrorSeverity,
   ERROR_STATE() AS ErrorState,
   ERROR_PROCEDURE() AS ErrorProcedure,
   ERROR_LINE() AS ErrorLine,
   ERROR_MESSAGE() AS ErrorMessage
  IF @@TRANCOUNT>0
   ROLLBACK TRAN
END CATCH
COMMIT TRAN
GO

Les fonction d’erreurs (ERROR_NUMBER(),…) inclues au sein de CATCH peuvent être remplacées par RAISERROR (qui peut également contenir lesdites fonctions), par exemple.

Nous parlerons plus en détail de la gestion des erreurs sous SQL Server dans un billet indépendant.

Pour aller plus loin…

Pour comprendre les concepts associés à la concurrence, vous pouvez consulter les billets concernés ici. Et pour tout ce qui concerne le journal de transactions que SQL Server utilise pour garantir le bon fonctionnement d’une transaction, vous pouvez aller .

Publicités

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