Contention des verrous en base de données : corriger rapidement les tables chaudes et les attentes de verrou

À quoi ressemble la contention de verrous en termes simples

Un verrou de base de données, c'est comme un panneau « réservé » sur une ligne ou une table. Tant qu'une requête modifie des données, la base peut bloquer d'autres requêtes qui veulent modifier les mêmes données en même temps. Certaines opérations peuvent encore lire, d'autres doivent attendre, selon la base et le type de verrou.

La contention de verrous, c'est ce qui se produit quand la file d'attente devient longue. La base n'est pas « en panne ». Elle fait tourner les requêtes à tour de rôle.

Souvent, tout se concentre sur une table chaude : une table très sollicitée en écritures. Exemples fréquents : une table d'orders pendant le checkout, une table de sessions mise à jour à chaque vue de page, ou une table de compteurs utilisée pour générer des numéros séquentiels.

Ce que ressentent les utilisateurs est frustrant et incohérent. Des pages qui chargent habituellement en 200 ms prennent soudain 10 à 30 secondes, puis reviennent à la normale. Les jobs en arrière-plan semblent bloqués. On voit des timeouts, des retries, et parfois une vague d'erreurs qui disparaît avant que quelqu'un puisse la reproduire.

Le phénomène peut paraître aléatoire parce que les serveurs ont l'air en bonne santé. Le CPU n'est pas saturé, la mémoire est stable, et le temps moyen des requêtes peut sembler normal. Mais lors des pics, quelques écritures bloquées créent un embouteillage, et tout ce qui a besoin des mêmes lignes ou de la même table se met à attendre.

Pourquoi les tables chaudes deviennent des goulots d'étranglement

Une table chaude est touchée par de nombreuses requêtes en même temps, souvent sur les mêmes lignes. Quand plusieurs sessions essaient d'update les mêmes données, la base doit les mettre en file. Cette file, c'est la contention : les pages chargent, puis se figent, puis finissent d'un coup.

Le déclencheur le plus courant est un volume d'écritures élevé concentré en un même endroit. Une grande table peut bien se porter si les écritures sont réparties. Une petite table peut poser problème si chaque requête la touche, comme une seule ligne qui stocke un compteur global, le « last invoice number », ou un drapeau d'état partagé.

Les points chauds proviennent souvent des mêmes motifs :

• Une ligne que tout le monde met à jour (compteurs, paramètres globaux)
• Boucles read-modify-write sur les mêmes enregistrements
• Conception « upsert everything » où les conflits sont attendus
• Jobs en arrière-plan touchant les mêmes lignes que les utilisateurs
• Travail supplémentaire à l'intérieur d'une transaction (appels API, uploads de fichiers, calculs longs)

Les transactions longues sont particulièrement néfastes parce qu'elles gardent des verrous pendant qu'elles font du travail sans rapport. Même si l'update est rapide, garder la transaction ouverte empêche les autres sessions d'avancer.

Le facteur temps compte aussi. Un job nocturne qui recalculerait des totaux peut être sans conséquence à 3h du matin, mais douloureux s'il se chevauche avec le trafic de pointe. Les déploiements peuvent aggraver la situation : un changement de schéma, un index ajouté, ou un nouveau trigger peuvent rallonger la durée d'écriture, étirant le temps de verrou.

Exemple : un flux de paiement met à jour une ligne d'inventaire puis appelle un prestataire de paiement avant de commit. Si l'appel prend 10 secondes, tous les autres checkouts qui ont besoin de la même ligne attendent derrière, et tout le système donne l'impression d'être bloqué.

Signes rapides que ce sont des verrous, pas seulement une DB lente

Les problèmes de verrous donnent l'impression que « tout est lent », mais les signaux diffèrent d'une base simplement sous-dimensionnée.

Séparez les symptômes :

Si le CPU est saturé et que les requêtes font beaucoup de calcul, vous avez souvent des requêtes coûteuses, des index manquants ou trop de travail par requête. Si le CPU est normal mais que le nombre de connexions actives ne cesse d'augmenter, les requêtes s'entassent. Quand vous voyez en plus beaucoup de requêtes bloquées en état d'attente (pas en exécution), la contention de verrous est un excellent candidat.

Un pattern classique : des pics soudains sur le p95 et le p99 alors que le p50 reste presque normal. Une requête tombe sur une ligne ou une table verrouillée et attend, puis une file se forme derrière elle. Vous pouvez aussi voir le débit global chuter même si la base n'est pas occupée en CPU.

Les logs donnent souvent des indices précoces : erreurs de timeout regroupées, messages de deadlock, et un nombre croissant de retries (depuis l'app, les workers ou l'ORM). Si vous avez récemment augmenté le nombre de workers et que la situation s'est dégradée, c'est un autre signe fort que des écrivains concurrents se heurtent.

Pendant un incident, capturez un petit lot d'informations pour pouvoir relier le pic à une attente de verrou spécifique plus tard :

• Horodatages exacts (début, pic, récupération)
• Échantillons de requêtes lentes et leurs paramètres (ou équivalents anonymisés)
• Endpoints ou jobs en arrière-plan les plus actifs à ce moment
• Instantanés de la base : nombre de connexions actives et requêtes en attente
• Comptes de workers et de queues (web, jobs, cron)

Exemple : la latence du checkout passe de 300 ms à 20 s, le CPU reste stable, et les connexions doublent. Cette combinaison signifie généralement « attente », pas « travail ».

Comment identifier les attentes de verrou pas à pas

Les attentes de verrou cachent facilement la contention à la vue. La base tourne, le CPU a l'air correct, mais les requêtes s'accumulent parce qu'une transaction détient un verrou dont d'autres ont besoin.

Étape 1 : Trouver qui est bloqué, puis qui bloque

Commencez par les vues de sessions et de verrous de votre base (sessions actives, tables de verrous, évènements d'attente). Cherchez une chaîne : beaucoup de sessions en attente et une session en tête qui détient le verrou.

Un workflow pratique :

• Lister les sessions actives et filtrer celles qui attendent des verrous
• Pour chaque attente, récupérer l'id de la session qui bloque
• Vérifier l'âge de la transaction du bloqueur (depuis combien de temps elle est ouverte)
• Récupérer le SQL pour les requêtes bloquées et bloquantes
• Noter le type d'attente et l'objet impliqué (table, index, clé de ligne si disponible)

Étape 2 : Rattacher le SQL à une action réelle côté appli

Le texte SQL seul ne suffit pas. Reliez-le à ce que faisait l'app : un endpoint, un job en arrière-plan, un worker, ou une tâche admin. Les tags de requête, le nom d'application de la connexion et les logs de requêtes/jobs sont utiles.

Une vérification utile : si la même table revient à chaque fois et que la même plage de clés ou index est impliquée, vous avez probablement un vrai point chaud (par exemple, tout le monde met à jour la même ligne d'état).

Étape 3 : Ne tuer la session qu'après évaluation du risque

Tuer le bloqueur peut restaurer le service rapidement, mais peut aussi rollbacker du travail et déclencher des retries. Avant d'agir, confirmez :

• Que la transaction est vraiment bloquée ou largement au-delà du temps normal
• Qu'un rollback n'entraînera pas d'effets externes indésirables (emails envoyés, paiements capturés)
• Que vous savez ce qui la relancera et si ce retry recréera la file

Localiser précisément la table, la requête et le chemin de code

Les gains rapides viennent de la nomination exacte : la table précise, l'instruction précise, et l'endroit exact dans le code qui l'envoie. Sinon vous risquez de modifier la mauvaise chose.

Commencez par lister toutes les écritures qui peuvent toucher la table suspecte : INSERT, UPDATE, DELETE, et UPSERT. Ne vous fiez pas à ce que la fonctionnalité « devrait » faire. Extraire cela des logs, de l'output de l'ORM ou des statistiques de statements. Les tables chaudes reçoivent souvent des écritures simultanées depuis des jobs en arrière-plan, des requêtes web et des retries.

Validez ensuite l'hypothèse « une ligne modifiée ». Une surprise fréquente est un UPDATE avec une clause WHERE trop large (ou sans index utilisable) qui scanne beaucoup de lignes et verrouille bien plus que prévu. Si les attentes de verrou montent pendant une petite mise à jour, vérifiez les index en priorité. Des index manquants ou erronés peuvent transformer une recherche rapide en un scan verrouillant.

Regardez aussi les verrous pris pendant des lectures. Si vous voyez SELECT ... FOR UPDATE ou des patterns similaires, vérifiez que le verrou est vraiment nécessaire. Beaucoup d'apps l'ajoutent « par sécurité » et finissent par bloquer du travail non lié.

Un moyen rapide de relier les preuves DB au code :

• Capturez le texte de la requête bloquante et l'âge de sa transaction
• Associez-le à un endpoint, un nom de job ou un worker dans les logs
• Confirmez combien de lignes il touche (estimé vs réel)
• Vérifiez si l'app réessaie sur timeout et empile des écrivains
• Identifiez la fonction ou méthode du service qui construit la requête

Exemple : un job nocturne de nettoyage lance un UPDATE non indexé, met des minutes, et le checkout commence à timer out. La DB vous donne la requête et les logs indiquent quel worker l'a exécutée. Corriger l'index ou restreindre le WHERE arrête souvent la pile rapidement.

Réduire les transactions longues qui bloquent tout le monde

Les transactions longues sont l'un des moyens les plus rapides de créer de la contention. Tant qu'une transaction est ouverte, elle peut détenir des verrous sur des lignes (et parfois plus) que d'autres requêtes nécessitent. Si la transaction contient du travail lent, tout le monde attend.

Une bonne règle : ne garder à l'intérieur de la transaction que les lectures et écritures de base de données. Tout le reste doit se faire avant le BEGIN ou après le COMMIT.

Les corrections qui améliorent souvent immédiatement la situation incluent : déplacer les étapes lentes hors de la transaction (appels API, uploads, envois d'email, génération de PDF, calculs lourds), committer plus tôt quand c'est sûr, et découper les gros updates en lots plus petits.

Les retries peuvent multiplier le problème. Si les timeouts déclenchent une pluie de rejouements, vous ajoutez plus d'écrivains sur le même goulot. Les clés d'idempotence (par exemple un request id) aident à relancer sans créer de doublons.

Exemple concret : un flux de checkout démarre une transaction, insère une commande, appelle ensuite une API de paiement et envoie un email de confirmation avant de committer. Si l'API de paiement bloque 30 secondes, les lignes de commande restent verrouillées 30 secondes. Écrivez la commande rapidement, committez, puis traitez paiement et email après avec des règles de retry claires.

Repenser les schémas d'écriture pour les tables chaudes

Les tables deviennent chaudes parce que beaucoup de requêtes se battent pour les mêmes lignes. Souvent, vous pouvez corriger la contention sans changer d'infrastructure en modifiant ce que vous écrivez, où vous l'écrivez, et à quelle fréquence vous mettez à jour la même ligne.

Un exemple courant est un compteur partagé unique (comme next_invoice_number ou daily_signups). Chaque écriture se met en file derrière la même ligne. Une meilleure approche est d'avoir des compteurs par tenant, par utilisateur ou par shard, puis agréger quand vous avez besoin d'un nombre global. La plupart des produits n'ont pas besoin d'un compteur global parfaitement temps réel.

Une autre cause fréquente est la mise à jour répétée d'une ligne d'état (last_seen, compteurs de retries, ou updates de progression). Si cette ligne est touchée à chaque requête, elle finira par étouffer. Préférez des lignes append-only (audit_log, events, status_history) et calculez l'état courant depuis le dernier événement, ou faites un roll-up en arrière-plan.

Quelques patterns qui réduisent régulièrement les attentes de verrou :

• Remplacer un compteur unique par des compteurs par-tenant (ou par-compte) puis agréger
• Utiliser des événements append-only plutôt que mettre à jour une même ligne sans cesse
• Déplacer les écritures non critiques vers une queue et traiter en asynchrone
• Scinder les données chaudes par une clé stable (tenant_id, account_id) pour répartir les écritures

Scénario concret : le checkout met à jour une ligne d'inventaire puis aussi user_last_purchase, daily_sales_total et marketing_attribution dans la même transaction. Sous charge, la ligne des totaux partagés devient le goulot et bloque même les updates d'inventaire. Si vous gardez la modification d'inventaire dans la transaction mais basculez les totaux et marketing vers un job en arrière-plan, le checkout reste rapide et la ligne chaude cesse de bloquer tout le monde.

Niveaux d'isolation et portée des verrous : de petits choix, un grand impact

Les niveaux d'isolation déterminent la sévérité des règles quand une transaction lit des données qu'une autre écrit. Les niveaux plus stricts empêchent des lectures incohérentes, mais ils peuvent garder des verrous plus longtemps ou en prendre davantage. C'est pour ça qu'un petit changement de config peut transformer une petite file en incident.

La plupart des équipes pensent en verrous de lignes : « seule une ligne est bloquée ». En pratique, certains moteurs verrouillent aussi les gaps entre lignes ou des plages pour empêcher l'insertion dans cette plage pendant une lecture/update. Un UPDATE qui couvre date X à date Y peut finir par bloquer des inserts dans cette plage, même pour des lignes que vous n'avez pas encore touchées.

Patterns sûrs qui réduisent la portée des verrous sans changer la logique métier :

• Préférer les updates ciblés par clé primaire plutôt que les updates de large plage
• Ajouter des WHERE serrés et traiter par lots quand il faut modifier beaucoup de lignes
• Garder l'ordre d'accès cohérent (par exemple, verrouiller toujours le parent avant l'enfant) pour éviter les deadlocks
• Surveiller les clés étrangères et cascades : un delete ou update peut verrouiller plusieurs tables plus longtemps que prévu
• Éviter les flux « select puis update plus tard » qui gardent les transactions ouvertes pendant du travail externe

Les timeouts vous aident à échouer vite au lieu de construire un embouteillage. Définissez des lock timeouts et statement timeouts raisonnables pour qu'une requête bloquée retourne une erreur rapidement et que votre app puisse réessayer ou afficher un message propre.

Exemple : un job en arrière-plan s'exécute avec un niveau d'isolation strict et met à jour toutes les factures impayées du mois dernier. Il détient des verrous de plage pendant son scan. En même temps, le checkout tente d'insérer une nouvelle facture dans cette plage et attend. Des lots plus petits et un niveau d'isolation moins strict règlent souvent le problème.

Scénario d'exemple : un job bloque le checkout pendant 20 minutes

Une startup a une configuration simple : une table orders (chaude), un endpoint de checkout, et un job en arrière-plan qui « nettoie les données ». Par une après-midi chargée, le checkout se met à ramer. Des utilisateurs voient des timeouts et des messages « essayez encore ».

Le déclencheur est une requête de reporting longue lancée dans une transaction. Elle démarre par BEGIN, puis lit un large segment d'orders pour calculer des métriques. Le développeur pensait « c'est en lecture seule, donc c'est sûr ». Mais la transaction reste ouverte longtemps et retient des verrous plus longtemps que prévu avec le niveau d'isolation courant.

En parallèle, un job en arrière-plan met à jour des milliers de lignes d'orders (par ex. pour backfiller une nouvelle colonne) pendant le trafic de pointe. Ces updates ont besoin de verrous aussi. La file d'attente grandit : le checkout essaie d'écrire une nouvelle commande et attend, les retries s'enclenchent et ajoutent de la pression, les connexions s'accumulent, et tout paraît lent.

Un autre facteur aggrave le tout : le endpoint checkout utilise un upsert du type « insert or update », mais la table n'a pas le bon index unique. La base scanne pour trouver des correspondances, touchant plus de lignes que nécessaire, donc chaque écriture garde des verrous plus longtemps.

La correction change rapidement le comportement : le reporting arrête d'utiliser une longue transaction (ou est déplacé vers une réplique en lecture). Le job en arrière-plan passe à de petits lots (par ex. 500 à 1 000 lignes) avec commit après chaque lot. Et l'index unique manquant est ajouté pour que l'upsert trouve rapidement les lignes.

Checklist rapide avant de modifier le code

Avant de réécrire quoi que ce soit, prenez 15 minutes pour confirmer que vous avez bien affaire à de la contention (et non à une lenteur aléatoire). Ces vérifications indiquent souvent la cause et vous évitent de « réparer » la mauvaise chose.

Commencez par l'âge des transactions pendant le pic. Si vous voyez des transactions ouvertes plus longtemps que quelques secondes, c'est un signal d'alerte. Une transaction longue garde des verrous plus longtemps, et une seule suffit pour faire patienter les requêtes rapides.

Puis trouvez le principal bloqueur. La plupart des bases peuvent montrer quelle session bloque les autres. Le blocage vient souvent d'une opération banale : un script admin, un job en arrière-plan, ou un worker qui retry.

Checklist à exécuter dans l'ordre :

• Trouver la plus ancienne transaction ouverte au moment du pic et noter ce qu'elle fait
• Identifier la requête bloquante principale et capturer son SQL ainsi que l'action applicative qui l'a déclenchée
• Vérifier que les updates et deletes utilisent l'index attendu (la bonne clé, pas un scan complet)
• Chercher des rafales de retries et des timeouts manquants
• Vérifier les changements récents de schéma (les migrations sont un déclencheur fréquent)

Exemple : un worker met à jour des lignes sans utiliser l'index prévu, scanne beaucoup de lignes, et garde la transaction ouverte pendant qu'il appelle une API externe. Les checkouts attendent des verrous, réessaient, et aggrave l'engorgement.

Pièges courants qui font revenir la contention

La contention revient souvent parce que la première correction traite le symptôme (timeouts, deadlocks) au lieu de la cause (comment et quand vous gardez les verrous).

La plus grosse erreur est de garder une transaction ouverte pendant quelque chose qui peut provoquer une pause. Un appel API de paiement, un webhook, un envoi d'email, un upload de fichier ou même une lente requête de cache peut figer la transaction et garder les verrous. Faites le travail DB, committez, puis faites le travail extérieur.

Un autre piège : des requêtes d'écriture massives qui touchent trop de lignes à la fois. Un UPDATE en lot sans LIMIT, sans WHERE sélectif ou sans le bon index peut verrouiller une large plage et bloquer des requêtes non liées.

Les deadlocks sont souvent « corrigés » par l'ajout de retries. Les retries peuvent masquer le problème, mais ils ajoutent aussi de la charge et augmentent la pression sur les verrous. Si deux chemins de code se battent toujours pour les mêmes lignes, les retries répètent le conflit.

Surveillez aussi la présence d'une seule ligne globale utilisée comme compteur ou flag d'état (une ligne dans une table de settings). Sous charge, cette ligne devient un point de congestion partagé.

Enfin, le code généré rapidement ou par IA a souvent des frontières de transaction incorrectes. Il peut entourer trop de travail dans une transaction ou mêler lectures et écritures de façon surprenante. Si vous avez hérité d'un prototype, revoir la portée des transactions donne souvent le gain le plus rapide.

Questions à se poser si ça revient :

• Une transaction inclut-elle des appels réseau ou des boucles longues ?
• Les écritures par lots sont-elles chunkées et compatibles index ?
• Les updates conflictuels touchent-ils les lignes dans un ordre cohérent ?
• Y a-t-il une seule ligne que tout le monde met à jour ?
• Les transactions sont-elles explicites et minimales, et non accidentelles ?

Étapes suivantes : stabiliser maintenant, prévenir ensuite

Les gains rapides viennent généralement de transactions plus courtes, pas de serveurs plus gros. Stabilisez d'abord, puis repensez les écritures pour que le problème ne revienne pas la semaine suivante.

Priorisez les corrections dans cet ordre : raccourcir les transactions, réduire la pression sur la table chaude, puis faire des refontes plus profondes.

Ordre d'opérations pratique :

• Trouver les 1 à 3 requêtes qui gardent les verrous le plus longtemps et réduire le travail dans leur transaction
• Ajouter ou durcir les timeouts pour qu'une requête bloquée ne paralyse pas tout
• Découper les gros updates en lots plus petits, ou déplacer le travail non critique en asynchrone
• Reconcevoir les écritures chaudes (logs append-only, compteurs sharded, tables de queue) une fois le système stabilisé
• Retester les pires flux sous charge (checkout, login, backfills, cron jobs)

La surveillance n'a pas besoin d'être sophistiquée. Quelques signaux suffisent pour vous alerter quand les verrous se reforment : temps d'attente de verrous, âge des transactions, deadlocks, et fréquence des timeouts de statements. Alertez sur les tendances, pas sur des pics uniques.

Si vous héritez d'une base de code générée par IA et que les bugs de verrous sont difficiles à tracer, FixMyMess (fixmymess.ai) se concentre sur le diagnostic et la réparation d'issues comme les transactions longues, les patrons d'écriture dangereux, et les failles de sécurité dans les apps construites par IA. Un audit rapide suffit souvent à repérer la requête bloquante et le chemin de code exact à corriger.