Les transactions SQL constituent l'un des piliers fondamentaux de la gestion des bases de données relationnelles. Une transaction SQL ACID garantit l'intégrité, la cohérence et la fiabilité des données même en cas d'erreur système ou de concurrence entre utilisateurs.
En entretien technique, les recruteurs testent régulièrement votre compréhension des propriétés ACID, des commandes COMMIT et ROLLBACK, ainsi que votre capacité à gérer les situations d'erreur complexes. Ces concepts déterminent souvent la différence entre un candidat junior et un profil expérimenté.
Dans cet article, nous explorons en détail les mécanismes transactionnels SQL, des propriétés ACID aux techniques avancées de gestion d'erreurs, en passant par les problèmes de concurrence comme les deadlocks.
📌 Ce qu'il faut retenir
- Une transaction SQL ACID respecte 4 propriétés : Atomicité, Cohérence, Isolation, Durabilité
- COMMIT valide définitivement les modifications, ROLLBACK les annule
- Les niveaux d'isolation contrôlent la visibilité des données entre transactions concurrentes
- Les deadlocks surviennent quand 2 transactions s'attendent mutuellement
Qu'est-ce qu'une transaction SQL ACID ?
Une transaction représente une séquence d'opérations SQL traitée comme une unité logique indivisible. Le modèle ACID définit 4 propriétés essentielles que tout système de base de données relationnel doit respecter.
L'Atomicité garantit qu'une transaction s'exécute entièrement ou pas du tout. Si une seule opération échoue, toutes les modifications sont annulées automatiquement. Imaginez un virement bancaire : le débit et le crédit doivent réussir ensemble, sinon aucune modification ne persiste.
La Cohérence assure que la base reste dans un état valide avant et après chaque transaction. Les contraintes d'intégrité, clés étrangères et règles métier sont respectées à tout moment.
L'Isolation empêche les transactions concurrentes d'interférer entre elles. Chaque transaction voit un état cohérent de la base, indépendamment des autres opérations en cours.
La Durabilité garantit que les modifications validées survivent aux pannes système. Une fois le COMMIT effectué, les données persistent même en cas de redémarrage du serveur.
Syntaxe et utilisation de BEGIN, COMMIT et ROLLBACK
La gestion explicite des transactions s'appuie sur 3 commandes fondamentales. BEGIN (ou START TRANSACTION) marque le début d'une transaction explicite.
BEGIN TRANSACTION;
UPDATE comptes
SET solde = solde - 100
WHERE numero_compte = '12345';
UPDATE comptes
SET solde = solde + 100
WHERE numero_compte = '67890';
COMMIT;
COMMIT valide définitivement toutes les modifications effectuées depuis le BEGIN. Les changements deviennent visibles pour les autres utilisateurs et persistent en cas de panne.
ROLLBACK annule toutes les opérations depuis le dernier BEGIN. La base retrouve son état initial, comme si aucune modification n'avait eu lieu.
BEGIN TRANSACTION;
DELETE FROM commandes WHERE statut = 'brouillon';
-- Oups, condition trop large !
ROLLBACK; -- Annule la suppression
En l'absence de transaction explicite, chaque instruction SQL s'exécute en mode autocommit. Chaque requête constitue une micro-transaction automatiquement validée.
Les 4 niveaux d'isolation en SQL
L'isolation contrôle la visibilité des données entre transactions concurrentes. SQL définit 4 niveaux d'isolation standard, du plus permissif au plus restrictif.
READ UNCOMMITTED autorise la lecture de données non validées (dirty reads). Une transaction peut voir les modifications en cours d'une autre transaction, même avant son COMMIT.
READ COMMITTED empêche les dirty reads mais autorise les lectures non reproductibles. La même requête peut retourner des résultats différents si une autre transaction modifie les données entre-temps.
REPEATABLE READ garantit que les lectures sont reproductibles dans une même transaction. Cependant, de nouveaux enregistrements peuvent apparaître (phantom reads).
SERIALIZABLE offre l'isolation maximale. Les transactions s'exécutent comme si elles étaient séquentielles, éliminant tous les phénomènes de concurrence.
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION;
-- Opérations protégées contre les lectures non reproductibles
COMMIT;
Le choix du niveau d'isolation représente un compromis entre cohérence et performance. Plus l'isolation est élevée, plus le système pose de verrous et réduit la concurrence.
Gestion d'erreurs avec TRY-CATCH
La gestion robuste d'erreurs combine les mécanismes transactionnels avec les structures de contrôle du SGBD. SQL Server propose la syntaxe TRY-CATCH pour capturer et traiter les exceptions.
BEGIN TRANSACTION;
BEGIN TRY
INSERT INTO clients (nom, email)
VALUES ('Dupont', 'dupont@example.com');
INSERT INTO commandes (client_id, montant)
VALUES (SCOPE_IDENTITY(), 150.00);
COMMIT TRANSACTION;
PRINT 'Transaction réussie';
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION;
PRINT 'Erreur : ' + ERROR_MESSAGE();
END CATCH
PostgreSQL utilise une approche différente avec les savepoints et la gestion d'exceptions dans les fonctions PL/pgSQL.
BEGIN;
SAVEPOINT avant_modification;
UPDATE produits SET prix = prix * 1.1 WHERE categorie = 'electronique';
-- Test de validation
IF (SELECT COUNT(*) FROM produits WHERE prix > 10000) > 0 THEN
ROLLBACK TO avant_modification;
RAISE EXCEPTION 'Prix maximum dépassé';
END IF;
COMMIT;
Cette approche permet une gestion granulaire des erreurs sans annuler toute la transaction.
⚠️ Attention
Les transactions longues augmentent le risque de deadlocks et réduisent les performances. Limitez leur durée et évitez les interactions utilisateur au milieu d'une transaction.
Comprendre et résoudre les deadlocks
Un deadlock survient quand 2 transactions ou plus s'attendent mutuellement pour accéder à des ressources verrouillées. Le SGBD détecte automatiquement ces situations et annule l'une des transactions (victim).
Exemple de deadlock classique :
Transaction A :
BEGIN TRANSACTION;
UPDATE clients SET nom = 'Martin' WHERE id = 1;
-- Attend le verrou sur commandes...
UPDATE commandes SET statut = 'confirmé' WHERE client_id = 2;
Transaction B (simultanément) :
BEGIN TRANSACTION;
UPDATE commandes SET montant = 200 WHERE client_id = 2;
-- Attend le verrou sur clients...
UPDATE clients SET email = 'nouveau@email.com' WHERE id = 1;
Chaque transaction détient un verrou attendu par l'autre, créant un blocage circulaire.
Stratégies de prévention :
- Accédez toujours aux tables dans le même ordre
- Utilisez des index appropriés pour réduire la durée des verrous
- Divisez les grosses transactions en opérations plus petites
- Implémentez une logique de retry avec backoff exponentiel
-- Ordre cohérent d'accès aux tables
BEGIN TRANSACTION;
UPDATE clients SET ... WHERE id = @client_id;
UPDATE commandes SET ... WHERE client_id = @client_id;
COMMIT;
Niveaux d'isolation : comparaison pratique
| Niveau | Dirty Read | Non-Repeatable Read | Phantom Read | Performance |
|---|---|---|---|---|
| READ UNCOMMITTED | Oui | Oui | Oui | Maximale |
| READ COMMITTED | Non | Oui | Oui | Élevée |
| REPEATABLE READ | Non | Non | Oui | Moyenne |
| SERIALIZABLE | Non | Non | Non | Faible |