Contrôle de concurrence dans les SGDB : comment le verrouillage, le MVCC et les stratégies optimistes maintiennent la cohérence des données

Le contrôle de concurrence est l'ensemble des mécanismes qu'un système de gestion de base de données (SGBD) utilise pour gérer les transactions simultanées sans corrompre les données. Lorsque plusieurs utilisateurs ou processus lisent et écrivent en même temps, un accès non contrôlé entraîne des anomalies de données, telles que des mises à jour perdues, des lectures sales et des résultats incohérents qui peuvent se propager aux systèmes en aval.

Le contrôle de concurrence applique le « I » (Isolation) des transactions ACID : les transactions concurrentes doivent produire le même résultat que si elles s'exécutaient les unes après les autres, une propriété connue sous le nom de sérialisabilité. Sans cela, une application bancaire pourrait perdre des virements, un système de gestion des stocks pourrait vendre des produits en surstock et un pipeline d'analyse pourrait produire des rapports basés sur des données partiellement écrites.

Ce guide couvre les problèmes fondamentaux de concurrence, les principaux mécanismes pour les résoudre — verrouillage, contrôle de concurrence multi-version (MVCC) et stratégies optimistes et pessimistes — ainsi que les niveaux d'isolation, la gestion des interblocages et des conseils pratiques pour choisir l'approche adaptée à votre charge de travail.

Pourquoi le contrôle de concurrence est important

Les systèmes de bases de données modernes gèrent couramment des milliers à des millions de transactions concurrentes par seconde. Chaque fois qu'un utilisateur soumet une requête, met à jour un enregistrement ou lance un pipeline ETL, le SGBD doit coordonner cette opération avec toutes les autres opérations en cours au même moment. Sans contrôle de concurrence, les transactions entrelacées produisent des résultats imprévisibles et incorrects.

Considérez un scénario concret : deux utilisateurs mettent à jour le solde du même compte bancaire simultanément. L'utilisateur A lit le solde (1 000 $), soustrait 200 $ et écrit 800 $. L'utilisateur B lit également 1 000 $, ajoute 500 $ et écrit 1 500 $. Le résultat correct dépend de l'ordre — soit 800 $ soit 1 500 $ — mais sans coordination, une écriture écrase simplement l'autre. C'est un cas classique de mise à jour perdue, l'une des plusieurs anomalies que le contrôle de concurrence vise à prévenir.

Le contrôle de concurrence applique la sérialisabilité : le résultat d'une exécution concurrente doit correspondre à un certain ordre sériel des mêmes transactions. Cela prend directement en charge les quatre garanties ACID — atomicité (tout ou rien), cohérence (transitions d'état valides), isolation (pas d'interférence) et durabilité (les modifications validées sont permanentes). Cela garantit la base sur laquelle chaque application fonde la confiance dans ses données.

Problèmes de concurrence à résoudre

Avant d'explorer les solutions, il est utile de comprendre les anomalies spécifiques qui surviennent lorsque des transactions concurrentes s'exécutent sans contrôles appropriés.

• Lecture sale. Une transaction lit des données écrites par une autre transaction non validée. Si cette deuxième transaction est annulée, la première transaction a basé sa logique sur des données qui n'ont jamais réellement existé. Les lectures sales sont parmi les anomalies les plus dangereuses car elles introduisent un état fantôme dans la prise de décision.
• Mise à jour perdue. Deux transactions lisent le même élément de données, puis toutes deux écrivent des valeurs mises à jour. La deuxième écriture écrase la première sans incorporer ses modifications, effaçant ainsi une mise à jour valide. Le scénario du compte bancaire ci-dessus en est un exemple typique.
• Lecture non répétable. Une transaction lit la même ligne deux fois et obtient des valeurs différentes parce qu'une autre transaction a modifié et validé la ligne entre les lectures. Cela brise toute logique qui dépend de données stables au sein d'une seule transaction.
• Lecture fantôme. Une transaction réexécute une requête de plage et obtient un ensemble de lignes différent parce qu'une autre transaction a inséré ou supprimé des lignes correspondantes dans l'intervalle. Les fantômes sont particulièrement problématiques pour les requêtes d'agrégation et les charges de travail de reporting.

Ces quatre anomalies expliquent l'existence de chaque mécanisme de contrôle de concurrence. Chaque mécanisme en empêche certaines ou toutes, en fonction du niveau d'isolation qu'il applique.

Contrôle de concurrence basé sur le verrouillage

Le verrouillage est l'approche la plus ancienne et la plus intuitive du contrôle de concurrence. Le SGBD attribue des verrous aux éléments de données — lignes, pages ou tables entières — avant d'autoriser les transactions à y accéder. Un verrou agit comme un gardien : si une autre transaction détient déjà un verrou conflictuel, la transaction demandeuse doit attendre.

Verrous partagés et exclusifs

• Les verrous partagés (lecture) permettent à plusieurs transactions de lire les mêmes données simultanément. Comme personne ne modifie les données, les lectures concurrentes sont sûres.
• Les verrous exclusifs (écriture) accordent à une seule transaction un accès exclusif à un élément de données. Tant qu'un verrou exclusif est détenu, aucune autre transaction ne peut lire ou écrire cet élément. Cela empêche les mises à jour perdues et les lectures sales, mais limite également la concurrence.

La granularité des verrous introduit un compromis important. Les verrous au niveau de la ligne maximisent la concurrence car les lignes non liées restent accessibles, mais ils ajoutent une surcharge car le système doit suivre de nombreux verrous individuels. Les verrous au niveau de la table sont plus simples et moins coûteux à gérer, mais obligent les transactions non liées à attendre, ce qui réduit le débit.

Verrouillage en deux phases (2PL)

Le verrouillage en deux phases est le protocole standard pour garantir la sérialisabilité par le biais de verrous. Il divise chaque transaction en deux phases. Pendant la phase de croissance, une transaction acquiert tous les verrous dont elle a besoin mais n'en relâche jamais. Une fois qu'elle relâche son premier verrou, elle entre dans la phase de décroissance et ne peut que relâcher des verrous, jamais en acquérir de nouveaux. Cette règle garantit que l'ordre dans lequel les transactions verrouillent les éléments de données est cohérent, ce qui garantit à son tour un ordonnancement sérialisable.

Le 2PL strict est une variante courante qui conserve tous les verrous jusqu'à ce que la transaction soit validée. Cela empêche les annulations en cascade, une situation où une transaction annulée oblige d'autres transactions qui ont lu ses données non validées à être également annulées. La plupart des bases de données relationnelles qui utilisent le contrôle de concurrence basé sur le verrouillage implémentent une forme de 2PL strict.

L'inconvénient du 2PL est qu'il introduit des blocages — les transactions doivent attendre les verrous détenus par d'autres — et crée les conditions des interblocages.

Contrôle de concurrence multi-version (MVCC)

MVCC adopte une approche fondamentalement différente : au lieu de bloquer l'accès, il maintient plusieurs versions de chaque élément de données afin que chaque transaction voie un instantané cohérent de la base de données. Lorsqu'une transaction écrit une ligne, MVCC crée une nouvelle version au lieu d'écraser celle existante. Les lecteurs continuent de voir la version qui était actuelle au moment où leur transaction a commencé.

L'avantage principal est que les lecteurs ne bloquent jamais les écrivains et les écrivains ne bloquent jamais les lecteurs. C'est un avantage de performance majeur pour les charges de travail à forte lecture, c'est pourquoi MVCC est le mécanisme de contrôle de concurrence dominant dans les bases de données modernes. PostgreSQL, MySQL/InnoDB, Oracle et la plupart des systèmes OLTP et OLAP contemporains utilisent une forme de MVCC.

Les conflits d'écriture sont détectés au moment de la validation. Si deux transactions modifient la même ligne, la première à valider gagne et la seconde doit réessayer. Cette approche optimiste de la résolution des conflits d'écriture maintient un débit élevé lorsque les conflits sont rares, ce qui est généralement le cas.

L'isolation par instantané est le niveau d'isolation le plus courant basé sur MVCC. Chaque transaction voit la base de données telle qu'elle existait au moment où la transaction a commencé, empêchant les lectures sales et les lectures non répétables. Cependant, l'isolation par instantané permet une anomalie subtile appelée dérive d'écriture, où deux transactions lisent des données qui se chevauchent, puis effectuent des écritures basées sur ce qu'elles ont lu, résultant en un état qu'aucune des deux transactions n'aurait produit seule. L'isolation par instantané sérialisable (SSI) comble cette lacune au prix d'une surcharge supplémentaire.

MVCC introduit ses propres compromis. La maintenance de plusieurs versions consomme du stockage supplémentaire, et le système doit périodiquement nettoyer les versions obsolètes — un processus connu sous le nom de VACUUM dans PostgreSQL. En cas de forte contention d'écriture, le coût de réessai et la surcharge de gestion des versions peuvent devenir importants.

Contrôle de concurrence optimiste vs. pessimiste

Au-delà des mécanismes spécifiques de verrouillage et de MVCC, les stratégies de contrôle de concurrence se répartissent en deux camps philosophiques : pessimiste et optimiste. Comprendre quel camp convient à votre charge de travail est l'une des décisions architecturales les plus importantes qu'une équipe de données puisse prendre.

Contrôle pessimiste

Le contrôle de concurrence pessimiste suppose que les conflits sont probables et les empêche en acquérant des verrous avant d'accéder aux données. Cette approche est préférable pour les charges de travail à forte écriture où les conflits sont fréquents et le coût d'annulation et de réessai d'une transaction est élevé. Les systèmes de grand livre financier, où chaque écriture doit être séquencée et vérifiée, en sont un cas d'utilisation classique.

L'inconvénient est le blocage. En cas de forte concurrence, les transactions se mettent en file d'attente en attendant les verrous, ce qui réduit le débit. Dans le pire des cas, les demandes de verrouillage concurrentes créent des interblocages.

Contrôle optimiste

Le contrôle de concurrence optimiste suppose que les conflits sont rares. Les transactions s'exécutent librement sans acquérir de verrous, en opérant sur leurs propres copies privées des données. Au moment de la validation, le système vérifie si les lectures et écritures de la transaction entrent en conflit avec une autre transaction validée. Si aucun conflit n'est détecté, la transaction est validée. Si un conflit est détecté, la transaction est annulée et réessayée.

Ce modèle en trois phases — lecture, validation, écriture — excelle dans les environnements où la plupart des transactions ne touchent pas aux mêmes données. Les systèmes de gestion de contenu, les applications de navigation de catalogue et les tableaux de bord de reporting en sont des exemples typiques.

Le revers de la médaille est le travail perdu. Lorsque les conflits sont fréquents, les transactions exécutent de manière répétée leur logique complète pour être ensuite annulées lors de la validation, consommant des ressources sans produire de résultats.

Quand utiliser quoi

Le choix entre le contrôle pessimiste et optimiste dépend des caractéristiques de la charge de travail. Une contention d'écriture élevée, des transactions courtes et une faible tolérance aux nouvelles tentatives orientent vers le contrôle pessimiste. Les charges de travail à forte dominante de lecture avec une faible probabilité de conflit et des exigences de concurrence élevées favorisent les approches optimistes ou basées sur MVCC.

En pratique, de nombreux systèmes de production combinent les deux stratégies. Un modèle courant utilise MVCC pour les lectures et écritures générales tout en appliquant des verrous pessimistes sur des lignes connues comme points chauds — par exemple, en utilisant SELECT ... FOR UPDATE pour verrouiller une ligne spécifique pour laquelle plusieurs transactions sont susceptibles de se disputer simultanément.

Niveaux d'isolation et leurs compromis

Les niveaux d'isolation définissent les anomalies de concurrence qu'une transaction peut rencontrer. Des niveaux plus stricts empêchent plus d'anomalies mais réduisent la concurrence. La norme SQL définit quatre niveaux, et la plupart des bases de données en implémentent une variante.

• Lecture non validée (Read uncommitted) offre une concurrence maximale mais autorise toutes les anomalies, y compris les lectures non validées. Il est rarement utilisé en production car les risques l'emportent largement sur les gains de performance.
• Lecture validée (Read committed) empêche les lectures non validées en garantissant qu'une transaction ne voit que les données validées avant l'exécution de chaque instruction. Il autorise les lectures non répétables et les lectures fantômes, ce qui est un compromis acceptable pour la plupart des applications web et des charges de travail générales. PostgreSQL et Oracle l'utilisent par défaut.
• Lecture répétable (Repeatable read) empêche les lectures non validées et les lectures non répétables en garantissant que toute ligne lue pendant une transaction retournera la même valeur si elle est relue. Des lectures fantômes peuvent encore survenir. MySQL/InnoDB utilise ce niveau par défaut.
• Sérialisable (Serializable) empêche toutes les anomalies. Les transactions se comportent comme si elles étaient exécutées une par une, dans un ordre sériel. C'est la garantie de cohérence la plus élevée mais elle s'accompagne de la concurrence la plus faible. Elle est généralement réservée aux charges de travail financières, de conformité ou de sécurité critique où la correction absolue est non négociable.

La plupart des applications fonctionnent en toute sécurité au niveau Lecture validée (Read Committed) ou Lecture répétable (Repeatable Read). Choisir Sérialisable (Serializable) est un compromis délibéré qui doit être motivé par des exigences métier spécifiques plutôt qu'utilisé par défaut.

Interblocages : prévention et résolution

Un interblocage se produit lorsque deux transactions ou plus détiennent chacune des verrous dont l'autre a besoin, créant un cycle d'attente mutuelle. Aucune des deux transactions ne peut progresser et, sans intervention, les deux attendraient indéfiniment.

• Détection. La plupart des bases de données détectent les interblocages en recherchant périodiquement des cycles dans un graphe d'attente — une structure de données qui mappe quelle transaction attend quel verrou. Lorsqu'un cycle est trouvé, le SGBD sélectionne une transaction « victime » et l'annule, libérant ainsi les verrous pour que les transactions restantes puissent continuer.
• Prévention. La stratégie de prévention la plus efficace consiste à acquérir les verrous dans un ordre cohérent et prévisible sur toutes les transactions. Si chaque transaction verrouille les ressources dans la même séquence, les conditions d'attente circulaire ne peuvent pas se former. Les délais d'attente des verrous fournissent une mesure de sécurité supplémentaire — si une transaction attend trop longtemps, elle est annulée plutôt que de contribuer à un interblocage potentiel.
• Résolution. La transaction victime annulée est réinitialisée et généralement relancée automatiquement par l'application. Les interblocages étant généralement peu fréquents, le coût de la nouvelle tentative est modeste.

Conseils pratiques pour minimiser les interblocages : gardez les transactions aussi courtes que possible, accédez aux ressources dans un ordre prévisible, définissez des valeurs de délai d'attente de verrouillage appropriées et surveillez la fréquence des interblocages comme indicateur de santé du système.

Choisir la bonne approche de contrôle de concurrence

Il n'y a pas de réponse universelle. Le mécanisme approprié dépend des caractéristiques de la charge de travail, des exigences de cohérence et des compromis opérationnels. Plusieurs facteurs guident la décision.

• Ratio lecture/écriture. Les charges de travail dominées par les lectures bénéficient du contrôle MVCC ou optimiste, qui permettent des lectures concurrentes sans blocage. Les charges de travail à forte dominante d'écriture nécessitent souvent un verrouillage pessimiste pour éviter le coût des nouvelles tentatives fréquentes.
• Fréquence des conflits. Lorsque les conflits sont rares — comme dans la plupart des charges de travail analytiques et web — le contrôle optimiste offre un meilleur débit. Lorsque les conflits sont fréquents, la surcharge des nouvelles tentatives rend le contrôle pessimiste plus efficace globalement.
• Durée des transactions. Les transactions courtes fonctionnent bien avec le verrouillage car les verrous sont détenus brièvement. Les transactions de longue durée bénéficient de MVCC, qui évite les blocages prolongés.
• Exigences de cohérence. Les exigences strictes (financières, conformité) favorisent l'isolation sérialisable avec 2PL. Les exigences modérées (applications web, catalogues) sont bien servies par Lecture validée (Read Committed) avec MVCC.
• Distribué vs. nœud unique. Les systèmes distribués introduisent une surcharge de coordination — latence réseau, tolérance aux pannes et exigences de consensus — qui rend le contrôle de concurrence plus complexe et plus coûteux.

De nombreux systèmes de production combinent des stratégies : MVCC par défaut, verrous pessimistes sur les lignes connues comme points chauds et logique de nouvelle tentative au niveau de l'application pour la résolution des conflits optimistes.

Contrôle de concurrence dans les systèmes distribués et analytiques

Les bases de données distribuées sont confrontées à des défis de coordination supplémentaires. La latence réseau entre les nœuds, la nécessité de tolérance aux pannes et les exigences de consensus augmentent le coût et la complexité du contrôle de concurrence. Les approches incluent le 2PL distribué, le MVCC distribué avec des horodatages globaux — comme utilisé dans le système TrueTime de Google Spanner — et des protocoles de consensus comme Raft et Paxos.

Les plateformes analytiques et lakehouse gèrent la concurrence différemment des bases de données OLTP traditionnelles. Ces systèmes sont optimisés pour les charges de travail à forte dominante de lecture et de scan de grande taille avec isolation par instantané plutôt que pour les modèles de verrouillage au niveau des lignes courants dans les systèmes transactionnels.

Databricks et Delta Lake utilisent le contrôle de concurrence optimiste pour les écritures concurrentes sur la même table. Les écritures suivent un processus en trois étapes : lire la dernière version de la table pour identifier les fichiers affectés, écrire de nouveaux fichiers de données, puis valider au moment de l'engagement qu'aucun changement conflictuel ne s'est produit. Si les transactions ne sont pas en conflit, elles réussissent. Si un conflit est détecté, une nouvelle tentative automatique ou une résolution de conflit s'en charge. Delta Lake implémente un modèle MVCC sans verrouillage, de sorte que les lecteurs voient toujours un instantané cohérent pendant que les écrivains progressent indépendamment.

Les transactions ACID sur les tables Delta Lake garantissent la cohérence des données même lorsque plusieurs pipelines, utilisateurs et requêtes accèdent simultanément aux mêmes données. Pour un examen détaillé de la façon dont la concurrence au niveau des lignes fonctionne en pratique, le blog d'ingénierie de Databricks propose une analyse approfondie des mécanismes sous-jacents.

Cette approche étend les garanties de concurrence au-delà de l'OLTP traditionnel aux charges de travail d'ingénierie de données, ETL et ML où plusieurs écrivains et lecteurs opèrent sur des ensembles de données partagés. En combinant le contrôle de concurrence optimiste, l'isolation par instantané et la résolution automatique des conflits, les plateformes lakehouse modernes apportent la fiabilité des transactions de niveau base de données à l'échelle et à la flexibilité des lacs de données cloud.

Contrôle de concurrence sans la complexité : comment Databricks le gère

Comprendre la théorie du contrôle de concurrence est un défi. L'implémenter de manière fiable sur des données distribuées, plusieurs écrivains et des charges de travail diverses en est un autre. Les équipes qui gèrent leurs propres stratégies de concurrence consacrent un temps d'ingénierie considérable à la configuration des niveaux d'isolation, à l'ajustement du comportement des verrous et à la création de logique de nouvelle tentative — un temps qui ne fait pas avancer leur travail réel.

Databricks Lakebase élimine cette charge opérationnelle. Construit sur la plateforme Databricks Data Intelligence Platform, Lakebase est une base de données entièrement gérée et native dans le cloud qui apporte la fiabilité transactionnelle au lakehouse sans obliger les équipes à implémenter ou configurer elles-mêmes le contrôle de concurrence. Prête à l'emploi, elle offre un contrôle de concurrence optimiste, une isolation par instantané pour les lectures et une isolation sérialisable en écriture pour les écritures — les mêmes mécanismes que cet article couvre, appliqués automatiquement.

Parce que Lakebase utilise le contrôle de concurrence optimiste sur Delta Lake, il n'y a pas de verrous à gérer et pas d'interblocages à déboguer. Les écritures concurrentes suivent le modèle lire-valider-confirmer décrit précédemment dans ce guide : chaque transaction lit la dernière version de la table, écrit de nouveaux fichiers de données et valide au moment de la confirmation qu'aucun changement conflictuel ne s'est produit. Lorsque plusieurs pipelines, utilisateurs ou requêtes écrivent sur la même table, la détection de conflit au niveau des lignes de Delta Lake résout automatiquement les changements non chevauchants — même pour les opérations MERGE, UPDATE et DELETE concurrentes. Les lecteurs voient toujours un instantané cohérent, non affecté par les écritures en cours.

Cela ne se limite pas aux charges de travail analytiques. Lakebase étend les garanties transactionnelles aux charges de travail opérationnelles, à l'ingénierie des données, aux pipelines ETL et au ML — le tout sur la même plateforme. Au lieu de maintenir une base de données OLTP distincte à côté d'un lakehouse et de les relier avec du code d'intégration personnalisé, les équipes exécutent tout via une architecture unique et gouvernée. Les données restent dans des formats ouverts sur un stockage cloud peu coûteux, avec la couche de calcul transactionnelle fonctionnant indépendamment au-dessus.

Le résultat : tous les concepts de contrôle de concurrence abordés dans cet article — MVCC, validation optimiste, isolation des instantanés, résolution des conflits — fonctionnent par défaut sur Databricks. Les équipes peuvent se concentrer sur leurs données et leurs charges de travail, pas sur leur stratégie de concurrence. Explorer Lakebase pour voir comment cela fonctionne en pratique.

(Cet article de blog a été traduit à l'aide d'outils basés sur l'intelligence artificielle) Article original