Un blocage est un phénomène normal dans tous SGBD Relationnels pour éviter les conflits d’accès en écriture qui, sans cela, permettrait de lire des données incohérentes. Les mécanismes sous-jacents aux blocages sont les verrous que le moteur de stockage pose pour garantir la consistance de toute transaction. Rappelons que toute manipulation des données de la base en lecture comme en écriture est une transaction, mais que les blocages sont généralement le fait de la durée de la transaction, comme de la stratégie de verrouillage. Nous démarrerons donc par quelques petits rappels avant de vous montrer comment voir et capturer les informations de ces blocages et en tirer tout bénéfice…

Publication le 2024-05-20

Qu’est ce qu’une transaction

C’est une opération de nature à lire (transaction de lecture) ou modifier (transaction d’écriture) l’état de la base . Les modifications de la base portent aussi bien sur les données que sur les métadonnées (structure). Par exemple le fait de modifier une table (pour y ajouter une colonne par exemple) est une transaction d’écriture (elle ne fait que mettre à jour des informations dans les tables systèmes de métadonnées de la base).

Quelle sont les caractéristiques des transactions ?

Il existe quatre caractéristiques aux transactions :

• leur nature : lecture ou écriture (read, write);
• leur niveau d’isolation : read uncommitte, read committed, repeatable read, serializable;
• leur durée : le temps mis depuis la pose du premier verrou jusqu’à la finalisation de la transaction;
• leur frontière : le moment de leur démarrage (BEGIN TRANSACTION ou BEGIN WORK) jusqu’à leur fin par validation (COMMIT) ou annulation (ROLLBACK).

La nature de la transaction comme le niveau d’isolation va avoir une influence sur les différentes caractéristiques des verrous.

Qu’est ce qu’un verrou ?

C’est un mécanisme posé par le moteur de stockage, pour un utilisateur de la base afin d’indiquer aux autre qu’il lit ou modifie des informations de manière à ce que ces autres utilisateur puissent accéder ou pas à la même information.

Quelle sont les caractéristiques des verrous ?

Les verrous sont caractérisées par :

• leur mode : partagés pour les lectures (S pour « Shared ») ou bien exclusif (X pour « eXclusive ») pour les écritures.
• leur granularité : le verrou peut concerner une ligne, une page, une partition, une table entière…
• leur type : optimiste ou pessimiste
• l’étendue : à quel événement le verrou va t-il être abandonné (fin de la requête ou fin de la transaction)
• la durée : c’est le temps entre la pose du verrou et son abandon

Un verrou partagé (donc de lecture) empêche les verrous exclusif (donc d’écriture) mais accepte d’autre verrous partagés concurrents. Il existe un type de verrou particulier pour la modification des données (UPDATE) car cette commande commence par lire les informations (verrou en mode S pour, une fois trouvées les ressources, se transformer en X (on les notes U pour « Update »).

Lorsqu’un verrou est posé sur une page, alors toutes les lignes contenues dans la page sont considérées comme verrouillés au même niveau. De même lorsqu’un verrou est posé au niveau de la partition, toutes les pages de cette partition sont considérées comme verrouillés, de même que tous les lignes des pages des partitions, etc.

Un verrou pessimiste bloque immédiatement la ressource, tandis qu’un verrou optimiste permet à d’autres de poser des verrous concurrents en toute indépendance du fait que chaque accès est fait sur une copie des données manipulées. Mais le verrouillage optimiste coûte cher en ressources (il faut gérer les multiples copies qui prennent de la place) et l’on peut aboutir à des blocages qui sont des pertes de mise à jour. Ceci se produit lorsque deux accès concurrent veulent mettre à jour leurs données, lorsque l’on termine a avant l’autre.

Du fait que la modification de la structure d’une base (ajout, suppression ou modification d’objet relationnels) d’une base est une transaction, il existe des verrous spécifiques souvent notés verrous de schéma (schema lock), et dans SQL Server il s’appellent SCH-Shared pour une lecture des tables d systèmes et SCH-Modify pour une mise à jour des tables système lorsque l’on modifie la structure d’un objet.

Comment voir les blocages ?

C’est dans la DMV (Data Management View – Vue contenant des données d’administration) sys.dm_exec_requests que l’on trouve dans la colonne « blocking_session_id » l’identifiant de session d’une requête qui bloque la requête en cours. Si zéro alors pas de blocage…

La difficulté étant que les sessions bloquante ne sont pas forcément vues dans la vue des requêtes en cours car elle peuvent avoir eu lieu, mais que la transaction soit toujours ouverte… Il faut donc concaténer les id de sessions des bloqueurs et des bloquants, par exemple à l’aide de la requête suivante :

Le processus de visualisation des blocages…

Le processus que nous allons utiliser est bien plus sophistiquée que la requête précédente. Ce processus détecte ces blocages en fournissant tout un tas d’information, mais surtout elle représente schématiquement l’arbre de blocage… En effet il n’est pas rare qu’un bloqueur bloque plusieurs sessions qui elles mêmes en bloquent d’autres, etc. Il en résulte une arborescence de blocage qui peut concerner de nombreuses sessions et avoir une profondeur  de plusieurs niveaux.

Un tel blocage peut être représenté comme suit :

Au sommet de cet arbre figure le bloqueur de tête (lead blocker).

Dans l’exemple de la figure ci-avant, le blocage concerne 20 sessions sur 3 niveaux de blocage…

Le code

Il commence par une boucle dont la sortie est l’apparition d’un blocage. La boucle WHILE fait un tour toutes les secondes et si un blocage apparait, elle exécute le code à la suite qui :

• crée une variable table;
• insère dans cette variable table les éléments primaires du blocage (INSERT INTO)
• analyse l’ensemble des sessions bloquées de manière récursive pour présenter l’arbre de blocage et les différentes informations qui nous intéressent à partir d’une jointure avec la variable table.

Le principe

Soit vous utilisez ce processus de manière unique au moment opportun, quand un blocage généralisé se fait sentir, soit vous encapsulez ce processus dans une tâche de l’Agent SQL que vous lancez régulièrement (par exemple toutes les 30 secondes) en s’abstenant de la lancer si elle tourne déjà (cas le plus probable), travail qui enregistre dans une table les blocages et lance une alerte en fonction de seuils à définir…

Dans ce dernier cas, nous créons une table dans la base msdb, de nom S_SURVEY.T_LOCKING_TREE_LKT pour y stocker le résultat des différentes exécutions du processus d’analyse des blocages.

Le processus est lui même encapsulé dans une procédure de nom S_SURVEY.P_SQLPRO_TREEBLOKERS qui lance une exception si certains seuils sont dépassés et dont les paramètres d’exécution sont :

• @DURATION_S_ALERT SMALLINT = 15 : alerte si la durée est supérieure à 30 secondes;
• @BLOCKED_COUNT_ALERT TINYINT = 5 : alerte si le nombre de bloqués est de plus de 5;
• @BLOCKED_DEEP_ALERT TINYINT = 3 : alerte si la profondeur des blocage est supérieure à 3;
• @DELETE_DEEP_DAYS TINYINT = 90 : supprime les données stockées vieille de plus de 90 jours

Pour éviter de relancer cette tâche si elle tourne déjà, il faut utiliser les tables systèmes de l’Agent SQL dans msdb, de la manière suivante :

Qui scrute la table d’activité des travaux de l’agent pour voir si je job est déjà en cours en regardant si la date de fin d’exécution est NULL. Auquel cas il ne lance pas la procédure qui contient ce processus. Ceci suppose que le nom du job est « ! SQLpro – ALERTE : transactions blocages ».

Vous trouverez l’ensemble du code dans le fichier suivant : Procédure ALERTE transactions blocages Version 2023-02-11 OK.txt

Et pour la mise en place dans l’Agent SQL utilisez ce fichier : Ajout alerte transaction blocage à l’Agent SQL.txt en ajoutant un opérateur à informer en cas de dépassement des seuils…

Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
* Le site sur les SGBD relationnels et le SQL : https://sqlpro.developpez.com *
* le blog SQL, SQL Server, SGBDR... sur : https://blog.developpez.com/sqlpro/ *
* Expert Microsoft SQL Server, MVP (Most valuable Professional) depuis 14 ans *
* Entreprise SQL SPOT : modélisation, conseil, audit, optimisation, formation *
* * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *