SOS Verrous bloquants

Par Arian Papillon le dimanche 5 janvier 2020, 14:17 - Lien permanent

Le mode d'isolation par défaut de SQL Server s'appuie sur du verrouillage pour supporter des accès concurrents aux mêmes données : si des transactions de modification ne se terminent pas, d'autres transactions qui veulent accéder aux mêmes ressources restent en attente.

On rencontre donc de temps en temps sur le terrain des applications sujettes à des verrous bloquants. Le problème est classiquement d'ordre applicatif : des transactions trop longues (voire même parfois qui ne sont jamais fermées). Et bien souvent, c'est le serveur SQL qu'on accuse, plutôt que de se pencher sur le fonctionnement de l'application.

Quelles sont les méthodes (les bonnes et les moins bonnes) pour en sortir ?

Faisons déjà le répertoire des mauvaises pratiques, malheureusement aussi fréquentes que dangereuses ou inefficaces :

  • Le plus fréquent : utilisation excessive et systématique du NOLOCK (ou READ UNCOMMITTED) : cela vous promet d'avoir des données incohérentes, la lecture ne respectant aucune protection des données en cours de modification par d'autres transactions.
  • Utilisation du SET LOCK_TIMEOUT, qui abandonne la transaction lorsqu'elle attend trop longtemps un verrou, mais ne règle aucun problème.
  • Gestion par le KILL, dès qu'il y a un blocage (il faut un employé payé à surveiller les verrous...). A ce propos, en attendant d'avoir réellement corrigé le problème, vous pouvez mettre un script qui fait le boulot tout seul...
  • Danse de la pluie autour du serveur ou eau de Lourdes, aussi inefficaces, encore qu'on ne sait jamais...

Regardons maintenant où se situent les causes et quelles sont les bonnes, vraies et seules solutions à ces problèmes :

  • Mauvaise modélisation : un bon modèle relationnel implique de nombreuses petites tables, ce qui permet de réduire la "largeur" des données à mettre à jour. Vous semble-t'il logique de bloquer toutes les données d'un client pour juste mettre à jour son numéro de téléphone ? Une table inutilement obèse concentre les verrous, toute la ligne étant verrouillée par la mise à jour d'une seule colonne.
    • Solution : revoir le modèle et respecter les formes normales lors de la conception. Pas toujours simple lorsque la base existe déjà...
  • Transactions longues. Très fréquent aussi, hélas. Cela peut venir de la conception de l'application ou de la mauvaise utilisation d'un ORM du genre dHibernate ou d'Entity Framework.
    • Solution : Revoir le fonctionnement et le code de l'application, mais cela impose parfois des investissement lourds en développement.
  • Manque d'index. Lorsque l'accès aux données se fait par des parcours de table plutôt que des recherches indexées, la potentialité de verrous bloquants augmente considérablement.
    • Solution : optimisation de la base en ajoutant des index. Un index est utilisé aussi bien par un SELECT, que par un UPDATE dès lors qu'il y a un prédicat de recherche ! Mais si les causes précédentes sont aussi de la partie, cela ne règlera qu'une petite partie du problème...
  • Verrous de mise à jour empêchant la lecture : les lectures sont bloquées par les écritures... (et les écritures sont aussi bloquées par les lectures, comme par les écritures). Est-ce vraiment raisonnable de lancer des requêtes de reporting sur une base transactionnelle ?
    • Une solution à envisager est l'isolation par versioning : Snapshot ou Read Commited Snapshot. Cela peut considérablement fluidifier l'application, mais ce n'est pas forcément sans effets secondaires et demande d'être testé et validé : gestion de conflits propre au versioning, charge système augmentée, en particulier sur la base tempdb.
    • Une autre solution, lorsque les requêtes de lecture sont pour beaucoup des requêtes de reporting, consiste à reporter ce trafic de lecture sur un réplica de la base (réplica always on en lecture par exemple)

Oui, tout va demander du temps et des modifications en profondeur...