Toutes les modifications de données dans SQL SERVER sont effectuées dans le cadre de
transactions. Par défaut, SQL SERVER démarre une transaction pour chaque instruction
individuelle et la valide automatiquement si l’exécution de l’instruction se termine
normalement.
Une transaction est caractérisée les critères ACID.
– Atomique : Si une des instructions échoue, toute la transaction échoue.
– Cohérente, car la base de données est dans un état cohérent avant et après la transaction,
c'est-à-dire respectant les règles de structuration énoncées.
– Isolée : Les données sont verrouillées : il n’est pas possible depuis une autre transaction de
visualiser les données en cours de modification dans une transaction.
– Durable : Les modifications apportées à la base de données par une transaction sont
validées.
Par exemple, une transaction bancaire peut créditer un compte et en débiter un autre, ces
actions devant être validées ensemble.
Les transactions utilisateurs sont implémentées sous TRANSACT SQL grâce aux instructions
BEGIN TRANSACTION, COMMIT TRANSACTION et ROLLBACK TRANSACTION.
Les verrous empêchent les conflits de mise à jour :
– Ils permettent la sérialisation des transactions de façon à ce qu’une seule personne à la fois
puisse modifier un élément de données. Dans un système de réservation de places, les
verrous garantissent que chaque place n’est attribuée qu’à une seule personne.
– SQL Server définit et ajuste de manière dynamique le niveau de verrouillage approprié
pendant une transaction : Il est également possible de contrôler manuellement le mode
d’utilisation de certains verrous.
– Les verrous sont nécessaires aux transactions concurrentes pour permettre aux utilisateurs
d’accéder et de mettre à jour les données en même temps.
180
Le contrôle de la concurrence permet de s’assurer que les modifications apportées par un
utilisateur ne vont pas à l’encontre de celles apportées par un autre.
– Le contrôle pessimiste verrouille les données qui sont lues en vue d’une mise à jour,
empêchant tout autre utilisateur de modifier ces données
– Le contrôle optimiste ne verrouille pas les données : Si les données ont été modifiées depuis
la lecture, un message d’annulation est envoyé à l’utilisateur.
Le choix entre ces deux types de contrôle est effectué en fonction de l’importance du risque
de conflit de données et de l’évaluation du coût de verrouillage des données (nombre
d’utilisateurs, nombre de transactions, temps de réponse…). Le type de contrôle sera explicité
en spécifiant le niveau d’isolement de la transaction.
BEGIN TRAN[SACTION] [nom_transaction] [WITH MARK]
Cette instruction marque le début de la transaction. Le nom de transaction n’est pas
obligatoire, mais il facilite la lecture du code. L'option WITH MARK permet de placer le nom
de la transaction dans le journal des transactions. Lors de la restauration d'une base de
données à un état antérieur, la transaction marquée peut être utilisée à la place d'une date et
d'une heure.
COMMIT TRAN[SACTION] [nom_transaction]
Cette instruction valide la transaction : les modifications sont effectives dans la base de
données.
ROLLBACK TRAN[SACTION] [nom_transaction]
Cette instruction annule la transaction : les données de la base sont celles d’avant les ordres
de modification, insertion ou suppression.
SAVE TRAN[SACTION] [nom_point_de_sauvegarde]
Cette instruction permet de définir un point d’enregistrement à l’intérieur d’une transaction.
Le code T-SQL
La première chose à faire est d'encapsuler vos requêtes dans une transaction. On va donc
faire démarrer une transaction au début et la committer à la fin :
-- On démarre une transaction et on lui donne un nom
BEGIN TRAN changement_etat_civil
-- Vos requêtes
COMMIT TRAN changement_etat_civil
-- on commit cette transaction, c'est-à-dire qu'on valide ses modifications
Mais cela ne change rien au problème, en cas d'erreur, l'ensemble est tout de même
committé et nous avons toujours le risque d'avoir des données incohérentes.
Exemple : À partir de notre base de données « Papyrus », on modifie le nom du fournisseur
120.
BEGIN TRAN
PRINT 'Valeur de Trancount: ' + CAST(@@TRANCOUNT AS varchar(5))
PRINT 'Avant Maj : '
SELECT NOMFOU FROM vente.FOURNISSEUR WHERE NUMFOU = 120
UPDATE vente.FOURNISSEUR SET NOMFOU = 'LEBRIGAND' WHERE NUMFOU = 120
PRINT 'Après Maj : '
181
SELECT NOMFOU FROM vente.FOURNISSEUR WHERE NUMFOU = 120
PRINT 'Valeur de Trancount : ' + CAST(@@TRANCOUNT AS varchar(5))
COMMIT TRANSACTION
COMMIT TRANSACTION (ou COMMIT TRAN) permet de valider notre transaction.
@@TRANCOUNT retourne le nombre de transactions actives de la connexion actuelle.
COMMIT TRAN -- Pour valider ma transaction
-- OU
ROLLBACK TRAN -- Pour annuler ma transaction
Si l’une ou l’autre des deux instructions est absente de la transaction, vous ne pourrez plus
interroger la table en question tant que la transaction n’est pas terminée.
