Le contrôle de concurrence multi-version (MVCC) est la colonne vertébrale des bases de données transactionnelles modernes. Bien qu'il excelle à empêcher les lecteurs de bloquer les rédacteurs et vice versa, il introduit des anomalies de concurrence subtiles comme les lectures fantômes et le skew d'écriture. Pour les ingénieurs construisant des applications à haute concurrence, comprendre les nuances entre les niveaux d'isolation SQL standard et les implémentations distribuées est critique. Cet article analyse ces phénomènes dans PostgreSQL et CockroachDB.
Comprendre les anomalies
Une
lecture fantôme (phantom read) se produit lorsqu'une transaction réexécute une requête avec une condition de plage et trouve des lignes qui ont été insérées par une autre transaction validée. Contrairement à une lecture non reproductible (où une ligne existante change), une lecture fantôme implique l'apparition de nouvelles lignes « comme par magie ».
Le
skew d'écriture (write skew) est souvent mal compris. Il se produit lorsque deux transactions lisent des ensembles de lignes qui se chevauchent, prennent des décisions basées sur ces lectures, puis mettent à jour des sous-ensembles disjoints de ces lignes. L'état final viole une contrainte qui aurait été détectée si les transactions avaient été sérialisées. Les exemples classiques incluent la planification des quarts de travail où pas plus de deux personnes ne peuvent travailler le même jour, ou le transfert de fonds entre comptes où le solde total doit rester positif.
PostgreSQL : La solution des verrous de plage
PostgreSQL implémente l'Isolation de Snapshot Sérialisable (SSI). Par défaut, il fonctionne en mode Read Committed, mais pour la sérialisabilité, il offre une approche puissante. Contrairement à MySQL, PostgreSQL n'utilise pas de verrous de plage (verrous sur des plages d'index). Au lieu de cela, il détecte les conflits de lecture-écriture.
Si deux transactions lisent la même clé et que l'une d'elles écrit sur une clé différente dans le même ensemble, PostgreSQL génère une erreur d'échec de sérialisation. Cela force l'application à réessayer la transaction. Bien qu'il ne s'agisse pas d'une prévention véritablement « basée sur les verrous », cela garantit la sérialisabilité au prix de réessais occasionnels.
-- Exemple : Détection d'un échec de sérialisation dans PostgreSQL
BEGIN;
SELECT count(*) FROM orders WHERE status = 'pending';
-- La transaction A met à jour une ligne
-- La transaction B lit et met également à jour
-- PostgreSQL peut générer : serialization_failure
COMMIT;
Pour atténuer les lectures fantômes sans sérialisation complète, les développeurs utilisent souvent
SELECT ... FOR UPDATE, qui verrouille les lignes spécifiques retournées. Cependant, cela ne verrouille pas les futures lignes correspondant à la condition, laissant les lectures fantômes possibles sauf si elles sont combinées avec une logique au niveau de l'application ou des niveaux d'isolation plus élevés.
CockroachDB : Sérialisation distribuée
CockroachDB est conçu dès le départ pour une forte cohérence à travers des clusters distribués. Il utilise un modèle de cohérence linéarisable par défaut. Cela signifie que même entre différentes régions, les lectures retournent l'écriture la plus récente.
Pour les lectures fantômes et le skew d'écriture, CockroachDB gère la sérialisabilité différemment de PostgreSQL. Il utilise une horloge logique hybride pour ordonner les événements. Si une transaction détecte une anomalie potentielle qui viole la sérialisabilité, elle annule la transaction immédiatement. Contrairement au mécanisme de réessai de PostgreSQL qui se produit au moment de la validation, CockroachDB peut détecter les conflits pendant l'exécution grâce à sa nature distribuée.
-- Exemple sérialisable de CockroachDB
BEGIN;
-- Lire le solde
SELECT balance FROM accounts WHERE id = 1;
-- Écrire sur le compte 2
UPDATE accounts SET balance = balance - 100 WHERE id = 2;
COMMIT;
-- Si une autre transaction a modifié simultanément le compte 1,
-- CockroachDB annulera cette transaction avec une erreur réessayable.
Stratégies pratiques
1.
Retours applicatifs : Pour les deux bases de données, envelopper les transactions sérialisables dans une boucle de réessai avec un backoff exponentiel est essentiel.
2.
Verrouillage optimiste : Utilisez des colonnes de version pour détecter les conflits tôt.
3.
Dénormalisation : Parfois, déplacer les contraintes vers la couche application ou utiliser des vues matérialisées peut réduire la contention des verrous.
Conclusion
Résoudre les lectures fantômes et le skew d'écriture nécessite un changement d'état d'esprit. Vous n'écrivez plus seulement du SQL ; vous concevez pour la concurrence. PostgreSQL offre de la flexibilité avec SSI et les réessais, tandis que CockroachDB fournit une forte cohérence avec une détection automatique des conflits. Choisissez le niveau d'isolation qui équilibre performance et correction stricte exigée par votre logique métier.