|
La gestion des transactions est le socle de tous les moteurs de base de données relationnels actuels. Une transaction est une écriture ou un ensemble d’écritures dans une ou plusieurs bases de données. Ces opérations sont réalisées en tout-ou-rien : soit l’ensemble des écritures est réalisée, soit aucunes.
Cette gestion des transactions permet de garantir une cohérence des données qui est très importante dans des applications modernes. Illustrons par un exemple l’intérêt des transactions : Nous avons une application servant à la gestion des comptes de clients dans une banque et nous avons un module dédié aux virements entre les comptes de clients. Un client A dispose de 2000 € sur son compte, tandis qu’un second B dispose de 1000 €. Le client A souhaite virer 500 € vers B, l’opération se décomposera comme un débit de A de 500 €, puis un crédit de B de 500 € (où l’inverse l’ordre n’étant pas important ici). Avec une transaction le moteur de base de données s’assurera que la totalité des 2 opérations est bien exécutée. Sans transaction, une seule des 2 opérations pourrait être exécutée si une erreur s’était déclenchée, ce qui amènerait une perte d’argent pour la banque et le client. Sans aller à des conséquences aussi importantes, il n’est pas rare de trouver des données orphelines dans une base de données, dont l’inexistence de données liées est assurée par l’absence de transactions. L’aspect multiutilisateur du moteur est souvent écarté par le développeur lors de l’écriture de traitements de modification de données. Pourtant même si la probabilité que vos travailliez sur un même client à un instant t est faible, elle existe toujours. Pour savoir quand utiliser une transaction, les propriétés essentielles d’une transaction peuvent vous aider. Le sigle ACID les désigne, petit point sur signification : A : Atomicité L’ensemble des opérations contenues dans la transaction doit être accepté ou refusé en totalité C : Cohérence Il n’est pas possible pour la transaction de mettre les données dans un état inconsistant. En pratique on ne pourra pas violer une contrainte sans annuler la transaction. I : Isolation La transaction ne doit pas interférer dans le déroulement d’une autre transaction et vice-et-versa. D : Durabilité Une fois la transaction validée, l’état des données suite à la transaction doit perdurer. En pratique, le journal de transaction de la base de données sert à garantir la durabilité. La durabilité est assurée par le moteur de base de données, grâce au journal de transaction. Elle ne nous importe peut dans la gestion courante des transactions, son rôle étant avant tout de pouvoir redémarrer un serveur à tout moment sans pertes de données. Un point utile la concernant est que la transaction, si la mémoire du serveur le permet, est généralement traitée intégralement en mémoire, aucune écriture ne sera nécessaire avant de valider celle-ci via un COMMIT. De cet aspect découle une autre conséquence intéressante, l’annulation d’une transaction est immédiate, l’appel du ROLLBACK sera rapide et indiquera au moteur d’invalider les pages modifiées en mémoire. La cohérence est assurée au niveau de l’exécution d’une requête, comme nous avons pu le voir dans la partie précédente, une requête qui viole une contrainte est abandonnée dans son intégralité. Mais par défaut la transaction n’est pas annulée lorsqu’une telle erreur est levée. use MaBase go
set xact_abort on
begin tran
insert dbo.Couleur values (10, 'Rouge') insert dbo.Couleur values (11, 'Jaune') insert dbo.Couleur values (2, 'Violet') insert dbo.Couleur values (13, 'Marron')
insert dbo.Produit values (11, 'Verre', 1) insert dbo.Produit values (12, 'Souris', 1) insert dbo.Produit values (13, 'Téléphone', 4) insert dbo.Produit values (14, 'Bureau', 2)
commit go
select * from dbo.Produit select * from dbo.Couleur
go L’option SET XACT_ABORT sert à forcer l’arrêt de la transaction lors de la détection d’erreur comme la violation de contrainte lors d’insertion de données.
(1 row(s) affected)
(1 row(s) affected) Msg 2627, Level 14, State 1, Line 8 Violation de la contrainte PRIMARY KEY 'PK__Couleur__0B0589E803317E3D'. Impossible d'insérer une clé en double dans l'objet 'dbo.Couleur'.
(0 row(s) affected)
(2 row(s) affected) Le résultat est bien aux rendez-vous, les 2 premiers enregistrements ont été insérés dans la table Couleur, la 3ème insertion échoue en violant la clef primaire de la table. Celle-ci déclenche l’annulation de la transaction et termine l’exécution du lot. Dans cet exemple, il est indispensable de placer les 2 SELECT hors du premier lot, les erreurs de violation de contrainte abondant l’exécution du lot lorsque que l’option XACT_ABORT est activée. Les 2 caractéristiques qui nous intéressent plus particulièrement sont l’atomicité et l’isolation. Le rôle du développeur de base de données est ici essentiel, car le niveau d’isolation ainsi que la portée de la transaction sont tout deux ses choix. Les modes implicite et explicite SQL Server dispose de 2 modes de gestion des transactions. Soit la gestion se fait de manière explicite, où le début et la fin de la transaction sont déclarés à l’aide d’instructions SQL, soit implicitement, géré de manière transparente par SQL Server. Le mode explicite, profite les instructions SQL suivantes : BEGIN TRANSACTION / BEGIN TRAN Démarrer une transaction COMMIT TRANSACTION / COMMIT TRAN / COMMIT Valider une transaction ROLLBACK TRANSACTION / ROLLBACK TRAN / ROLLBACK / ROLLBACK WORK Annuler une transaction Il est possible de nommer la transaction en indiquant un nom derrière chaque instruction (excepté ROLLBACK WORK) pour associer les instructions de début et fin de la transaction. Cependant SQL Server ne gère pas la notion de transaction imbriquée, l’intérêt de des noms donnés aux transactions n’est alors pas si évident. Entre une l’instruction de commencement de la transaction et celles de terminaison prennent place toutes les requêtes d’écriture de données souhaitées. Ces écritures peuvent être des insertions, suppressions et modifications de données. La nouvelle commande MERGE de SQL Server 2008 est aussi acceptée au sein d’une transaction. Exemple de transaction explicite : use MaBase go
begin transaction
insert dbo.Couleur values (25, 'Fushia')
update dbo.Produit set Couleur_Id = 25 where Produit_Id = 1
commit transaction On pourra avoir une logique d’annulation de la transaction si nécessaire à l’aide des contrôles d’erreur disponible depuis SQL Server 2005 : begin transaction begin try
insert dbo.Couleur values (25, 'Fushia')
update dbo.Produit set Couleur_Id = 25 where Produit_Id = 1
commit transaction
end try begin catch rollback transaction end catch Malgré la violation de la clef primaire dans le second exemple, aucune erreur n’est renvoyée. C’est l’intérêt du bloc TRY / CATCH, la gestion d’erreur est rendue transparente et la commande ROLLBACK a été appelée dès détection de la violation. Toutes les opérations de modification du schéma, comme par exemple la création d’une table, la modification d’une procédure stockée, sont acceptées dans une transaction. Bien que surprenant, ce mécanisme est assez logique : la création ou modification d’une table, n’est que la manipulation d’une ou plusieurs tables systèmes. Une transaction peut donc s’appliquer à ces données systèmes et par extensions à tout ce qu’il leur ait lié dans le moteur de base de données. Attention Une transaction peut être démarrée dans un lot et se terminer dans un autre. Certaines opérations serveur sont proscrites dans une transaction, car elles impliquent des bases de données systèmes. Par exemple la création de base de données ou la sauvegarde données seront refusés à l’intérieur d’une transaction. Côté gestion implicite des transactions, SQL Server considère qu’une requête est transactionnelle. Une requête qui a pour objectif de mettre à jour tous les enregistrements d’une table, soit réussira à mettre à jour la totalité de ces données et le moteur validera la transaction par un COMMIT implicite, soit une erreur sera levée est la transaction sera annulée et la totalité des enregistrements restaurés via un ROLLBACK implicite. Ce comportement est aussi appelé auto-commit car la validation de la transaction est systématique si aucune erreur ne justifie son annulation. Il est possible de basculer en mode implicite, mais non automatique. Ce mode est semblable à celui par défaut d’Oracle, qui oblige après l’exécution d’une commande de modification de données à la valider ou l’annuler manuellement. use MaBase go
set implicit_transactions on
delete dbo.Produit La commande DELETE a implicitement démarré une transaction, mais contrairement à ce qui se produit dans le mode par défaut de SQL Server elle n’a pas encore été validée. Le moteur attend de vous de valider cette transaction, l’annuler ou même continuer les traitements dans celle-ci. L’option IMPLICIT_TRANSACTIONS permet ce démarrage automatique de la transaction et son maintient ouvert. Cela peut se révéler utilise sur un serveur de production pour éviter de fâcheuses opérations. A ce stade si l’on consulte le contenu de la table dans la même connexion : select * from dbo.Produit On s’apercevra qu’elle est bien vide. (0 row(s) affected) Si vous tentez de le faire dans une autre connexion, vous serez bloqué, mais nous reviendrons bientôt sur l’explication de ce phénomène. Maintenant, annulons la transaction et regardons à nouveau le contenu de la table. rollback
select * from dbo.Produit La table est à nouveau complète. (3 row(s) affected) Que se passerait-il si je décidais d’encapsuler toutes mes modifications dans une procédure stockée ? SQL Server nous fournir une solution toute prête avec les points de sauvegarde. Les points de sauvegarde Comme indiqué plus haut, SQL Server ne gère pas la notion d’imbrication de transactions. Il offre cependant un mécanisme pour permettre d’annuler une partie de l’opération réalisée durant la transaction. Ce mécanisme est idéal si vous souhaitez appeler une procédure dans laquelle vous modifiez les données. Dans cette situation on est dépendant du contexte, il est tout à fait possible que le code appelant ai déjà démarré une transaction, ou non. Pour connaitre à tout moment dans de l’exécution, le nombre de transaction ouverte sur la connexion, utilisez @@trancount. Cette commande vous renverra 0 si aucunes transactions n’ont été démarrées sur la connexion et un nombre supérieur si un ou plusieurs BEGIN TRAN ont étés rencontrés. La commande SAVE TRAN indiquera au moteur de mémoriser l’état de la transaction actuelle pour, si besoin est, revenir à cet état. Cette possibilité n’est possible que pour l’annulation par un ROLLBACK. Le nom de la transaction est d’ailleurs indispensable à cette commande pour savoir à quel point de sauvegarde revenir. Voici un exemple complet d’utilisation de SAVE TRAN dans le contexte d’une procédure stockée. create procedure MaProc as begin
declare @deja_ds_tran bit;
if @@trancount > 0 begin set @deja_ds_tran = 1; save tran debut_sp; end else begin set @deja_ds_tran = 0; begin tran debut_sp; end begin try
update MaBase.dbo.Couleur set Nom = 'rien'
end try begin catch rollback tran debut_sp; return -1; end catch
if @deja_ds_tran = 0 commit tran debut_sp;
end go Attention Le nombre de transactions ouvertes au début et à la fin d’une procedure stockée doivent être identiques, sinon un erreur sera levee. Le premier bloc vérifie que qu’une transaction est déjà ouverte ou non. Si aucune transaction n’existe, la procédure stockée la crée avec BEGIN TRAN, si une transaction est en cours on se contente juste de mémoriser l’emplacement dans la transaction via SAVE TRAN. Notez que le nom utilisé pour la transaction est identique pour simplifier la gestion de la transaction par la suite. Le second bloc est celui de la gestion des erreurs. En cas d’erreur, l’instruction ROLLBACK est appelée ce qui correspond soit à une annulation de la transaction, soit un retour à l’état de SAVE TRAN. On voit ici l’intérêt du nom unique, pas besoin de se poser une nouvelle fois la question de savoir si la transaction a été démarrée avant le début de la procédure stockée. Le dernier bloc, n’exécutera l’instruction COMMIT que si la procédure stocké a démarré la transaction. En effet le SAVE TRAN ne change pas l’état de la transaction, effectuer un COMMIT nous conduirait à une erreur. D’une manière plus générale, les points de sauvegarde permettent de réaliser une opération dans le contexte d’une transaction, vous laissant la possibilité d’annuler cette opération uniquement et de conserver la transaction ouverte.
|