Comprendre et utiliser les transactions en SQL

Les transactions sont un concept fondamental en SQL pour garantir l’intégrité et la cohérence des bases de données. Les transactions sont des unités de travail qui regroupent une ou plusieurs opérations SQL (INSERT, UPDATE, DELETE) en unités logiques. Elles offrent de plus des mécanismes pour gérer les erreurs et restaurer un état initial si nécessaire.

Cet article a pour vocation d’expliquer ce que sont les transactions, les avantages qu’elles apportent et comment les utiliser, à l’aide d’exemples pratiques.

Propriétés ACID des transactions

Les transactions suivent les principes ACID, qui garantissent leur bon fonctionnement :

Atomicité : Une transaction est indivisible. Soit toutes les opérations qu'elle contient sont exécutées avec succès, soit aucune ne l'est. Si une erreur survient, toutes les modifications effectuées par la transaction sont annulées.

Cohérence : Une transaction commence dans un état cohérent de la base de données et doit se terminer dans un état cohérent. Cela signifie que les règles de gestion et les contraintes de la base de données sont respectées.

Isolation : Les modifications effectuées dans une transaction ne sont (par défaut) pas visibles pour les autres transactions tant que la transaction n'est pas validée. Cela évite les interférences entre transactions concurrentes.

Durabilité : Une fois une transaction validée (committed), ses modifications sont persistantes, même en cas de panne du système.

Avantages des transactions

Cohérence des données :  Les transactions empêchent les modifications partielles ou incohérentes.

Gestion des erreurs : Facilitent l'annulation des modifications en cas de problème.

Concurrence sécurisée : Garantissent que plusieurs utilisateurs peuvent interagir avec la base de données sans affecter les résultats les uns des autres.

Limitations

Performance : Les transactions verrouillent souvent des ressources (comme des tables ou des lignes), ce qui peut ralentir le système si de nombreuses transactions sont en cours simultanément.

Complexité : La gestion des transactions dans les applications complexes peut nécessiter des mécanismes sophistiqués, comme les niveaux d'isolation.

Transactions dans la vie réelle

Les transactions sont utilisées dans de nombreux scénarios, tels que :

-          Systèmes bancaires : Transferts de fonds.

-          E-commerce : Validation de commandes et mise à jour des stocks.

-          Systèmes de réservation : Réservation de billets (avion, train, hôtel).

Étapes d'une transaction

-          Début de la transaction : Le système commence à enregistrer les opérations.

-          Exécution des opérations : Les requêtes de la transaction sont exécutées.

-          Validation (COMMIT) : Si toutes les opérations sont réussies, la transaction est validée, et les modifications deviennent permanentes.

OU Annulation (ROLLBACK) : Si une erreur survient, la transaction est annulée, et toutes les modifications effectuées jusqu'à ce point sont annulées.

Exemple de transaction simple

Imaginons une banque avec une table comptes contenant des informations sur les soldes des clients. Si un client souhaite transférer 100 € d'un compte à un autre, cela implique deux opérations :

Débiter 100 € du compte A.

Créditer 100 € sur le compte B.

Ces deux opérations doivent être traitées comme une transaction unique pour garantir la cohérence des données.

Voici le code permettant d’effectuer cette transaction, et que nous étofferons au fil de cet article afin de profiter des principaux avantages des transactions en SQL :

BEGIN TRANSACTION;

-- Débiter 100 € du compte A

UPDATE comptes SET solde = solde - 100 WHERE id_compte = 1;

-- Créditer 100 € sur le compte B

UPDATE comptes SET solde = solde + 100 WHERE id_compte = 2;

-- Valider la transaction

COMMIT;

Si une erreur survient (par exemple, si le compte A n'a pas assez d'argent), on peut utiliser ROLLBACK à la place de COMMIT, pour revenir à l’état initial, par exemple à l’aide d’un TRY…CATCH.

Gestion des erreurs avec TRY…CATCH

SQL Server permet de gérer les erreurs dans les transactions grâce à TRY...CATCH. Cela garantit que les erreurs ne laissent pas la base de données dans un état incohérent.

Exemple : Transfert d’argent entre comptes

BEGIN TRANSACTION;

BEGIN TRY

    -- Débit du compte source

    UPDATE comptes

    SET solde = solde - 100

    WHERE id_compte = 1;

    -- Crédit du compte cible

    UPDATE comptes

    SET solde = solde + 100

    WHERE id_compte = 2;

    -- Validation

    COMMIT;

    PRINT 'Transaction effectuée avec succès.';

END TRY

BEGIN CATCH

    -- Annulation en cas d'erreur

    ROLLBACK;

    PRINT 'Erreur détectée, transaction annulée.';

END CATCH;

Points de sauvegarde intermédiaires : SAVEPOINT

Les points de sauvegarde permettent de revenir à un état précis dans une transaction sans tout annuler.

Exemple : Ajout d’un nouveau compte bancaire dans la base de données

BEGIN TRANSACTION;

-- Point de sauvegarde

SAVEPOINT Etape1;

-- Première opération

INSERT INTO comptes (titulaire, solde) VALUES ('Bernard Dupont', 100);

-- Revenir à l'état initial si besoin

ROLLBACK TO Etape1;

-- Deuxième opération

INSERT INTO comptes (titulaire, solde) VALUES ('Bernard Dupont', 150);

COMMIT;

Dans cet exemple, ROLLBACK TO Etape1 annule uniquement les modifications depuis le point de sauvegarde.

Transactions imbriquées

Les transactions imbriquées permettent d’exécuter des transactions secondaires à l’intérieur d’une transaction principale.

Exemple : Ajout d’un nouveau compte bancaire et des infos personnelles de son titulaire

BEGIN TRANSACTION;

-- Transaction principale

INSERT INTO comptes (titulaire, solde) VALUES ('Bernard Dupont', 150);

BEGIN TRANSACTION;

-- Transaction imbriquée

INSERT INTO titulaires (prenom, nom, adresse) VALUES ('Bernard', 'Dupont', '3 place d’Italie, Bordeaux');

COMMIT; -- Valide la transaction imbriquée

COMMIT; -- Valide la transaction principale

Transactions distribuées

Ces transactions sont complexes : elles s'exécutent sur plusieurs serveurs ou bases de données simultanément. Cela peut être parce qu’elles nécessitent l’accès à des bases de données stockées sur des serveurs différents, par exemple. La gestion des transactions distribuées permet de garantir que toutes les opérations de la transaction sont validées ou annulées ensemble, en respectant la cohérence et l’intégrité des données.

Exemple : Transaction distribuée sur SQL Server

Imaginons que l’on souhaite réaliser un transfert d’argent depuis un compte A vers un compte B, mais que les deux comptes sont accessibles depuis des serveurs différents (respectivement A et B). Gérer ces opérations en transaction distribuée permet de s’assurer que les opérations réussiront ou échoueront ensemble, de manière cohérente.

-- Démarrer une transaction distribuée

BEGIN DISTRIBUTED TRANSACTION;

-- Effectuer des opérations sur différentes bases de données ou serveurs

UPDATE ServeurA.dbo.comptes SET solde = solde - 100 WHERE ID = 1;

UPDATE ServeurB.dbo.comptes SET solde = solde + 100 WHERE ID = 2;

-- Si tout se passe bien, valider la transaction

COMMIT TRANSACTION;

Gestion des verrous

SQL Server utilise des verrous pour garantir l'intégrité des données pendant une transaction. Ces verrous peuvent être partagés, c’est-à-dire qu’ils interdisent la modification (mais pas la lecture) de données, à plusieurs transactions simultanément ; ou exclusifs, c’est-à-dire qu’ils interdisent la modification mais aussi la lecture d’un objet lors d’une seule transaction.

Voici les différents types de verrous :

Row Lock : Empêche la modification d'une ligne spécifique, et la lecture si le verrou est exclusif.

Page Lock : Empêche la modification de toutes les lignes contenues dans une certaine page mémoire, et la lecture si le verrou est exclusif. Une page mémoire est la plus petite unité de stockage des données utilisée par les SGBD. De taille fixe, elle contient généralement l’équivalent de quelques lignes d’une table.

Table Lock : Empêche la modification des éléments d’une table, et la lecture si le verrou est exclusif.

Exemple :

BEGIN TRANSACTION;

UPDATE clients

SET adresse = ‘3 place Victor Hugo, Paris’

WHERE ID = 1

WITH (ROWLOCK, XLOCK);

-- La ligne avec ID = 1 est verrouillée à l’aide d’un Row Lock exclusif

-- Personne ne peut la lire ou la modifier jusqu'à COMMIT ou ROLLBACK

-- Même en attendant 10 secondes avant de COMMIT, la ligne reste verrouillée

WAITFOR DELAY '00:00:10';

COMMIT; -- Libère le verrou après validation

On peut appliquer un LOCK_TIMEOUT avant une transaction, afin de laisser le temps nécessaire pour qu’une autre transaction soit validée ou annulée, et qu’un verrou disparaisse. Si le verrou ne disparaît pas dans le temps imparti, la transaction échouera. Cette pratique permet de limiter le temps d’exécution des transactions, afin d’éviter les blocages qui seraient dus à de nombreux verrous successifs.

Exemple :

SET LOCK_TIMEOUT 5000; -- Timeout de 5 secondes

BEGIN TRANSACTION;

UPDATE clients SET adresse = ‘3 place Victor Hugo, Paris’ WHERE ID = 1;

COMMIT;

Isolation des transactions

L'isolation des transactions contrôle le niveau d'interférence entre les transactions concurrentes.

Les niveaux d'isolation définis par SQL sont :

READ UNCOMMITTED : Permet de lire des données non validées (committed). Permet d'éviter les verrous, mais peut conduire à des lectures incohérentes, comme la lecture de données non validées ou de données en cours de modification par une autre transaction en parallèle. Utile pour obtenir des lectures rapides, lorsqu’il y a peu de transactions concurrentes ou que l’exactitude des valeurs en temps réel n’est pas primordiale (par exemple pour analyser des tendances dans les données sur une grande échelle de temps).

READ COMMITTED : N'autorise que la lecture des données validées.

REPEATABLE READ : Garantit que les données lues par une transaction restent inchangées jusqu'à son achèvement.

SERIALIZABLE : Évite tout conflit entre transactions concurrentes, en n’autorisant que la lecture des données validées, et en garantissant en même temps que les données lues restent inchangées jusqu’à l’achèvement de la transaction. C’est le niveau d’isolation défini par SQL le plus strict.

Exemple : Utilisation de READ UNCOMMITED

Imaginons que nous avons une table Clients, dans laquelle se trouvent les informations personnelles des clients de notre établissement bancaire. Deux utilisateurs se connectent à la même base de données en même temps, chacun sur sa session. L’utilisateur de la session 1 modifie des données :

BEGIN TRANSACTION;

UPDATE clients

SET adresse = ‘3 place Victor Hugo, Paris’

WHERE ID = 1;

-- Finalement, l’utilisateur de la session 1 peut décider d’annuler sa transaction

ROLLBACK;

Pendant ce temps, et avant que l’utilisateur de la session 1 ait eu le temps d’exécuter un ROLLBACK ou COMMIT, l’utilisateur de la session 2 souhaite accéder aux données, et choisit le niveau d’isolation READ UNCOMMITTED :

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SELECT *

FROM clients

WHERE ID = 1;

L’utilisateur de la deuxième session peut lire les données modifiées par la première session grâce à READ UNCOMMITTED, même si ces données sont toujours en attente de validation. Ainsi, l’utilisateur de la deuxième session va lire des données qui n’étaient pas vouées à être publiées et qui peuvent être erronées.

Les journaux de transaction

Les bases de données enregistrent toutes les opérations d'une transaction dans un journal. Vous pouvez contrôler ces journaux pour récupérer des données après un crash : le journal de transactions aura enregistré toutes les modifications apportées depuis la dernière sauvegarde. En changeant le mode de transaction, on peut aussi optimiser la taille du journal de transactions.

Pour vérifier l'état des journaux :

DBCC LOGINFO;

Pour récupérer des données après un crash :

-- Restaurer la dernière sauvegarde complète de la base de données

RESTORE DATABASE base1

FROM DISK = 'C:\Backup\base1_Full.bak'

WITH NORECOVERY;  -- La base reste en mode de récupération pour restaurer les journaux

-- Restaurer le premier journal des transactions

RESTORE LOG base1

FROM DISK = 'C:\Backup\base1_Log1.trn'

WITH NORECOVERY;  -- La base reste en mode de recuperation

-- Répéter l’opération avec tous les journaux de transactions sauvegardés jusqu’au bug

-- Restaurer le dernier journal des transactions et finir la restauration

RESTORE LOG base1

FROM DISK = 'C:\Backup\base1_LogX.trn'

WITH RECOVERY;  -- La base revient à son état opérationnel

Bonnes pratiques

-          Toujours utiliser ROLLBACK ou COMMIT pour terminer une transaction.

-          Prévoir une gestion des erreurs avec TRY...CATCH.

-          Limiter la durée des transactions, à l’aide de SET LOCK_TIMEOUT.

-          Utiliser les points de sauvegarde (SAVEPOINT) pour plus de flexibilité.

-          Tester les transactions sur une base de données de développement avant de les appliquer en production.