Il arrive, souvent à cause d’un mauvais développement (voir en gras italique plus loin), qu’un serveur SQL se bloque du fait des verrous. Une session en bloque autre qui en bloque plusieurs autres… et c’est l’effet boule de neige. La plupart du temps, les développeurs ou pseudo DBA réagissent mal en relaçant le service SQL Server ce qui fait perdre une grande partie des mises à jour demandées, empêchent les utilisateurs de travailler et pour peu qu’une longue transaction n’ai pas encore enregistrées les données, rend indisponible la base après le redémarrage du serveur, souvent pendant de longues minutes… Tout cela pouvant être évité avec un peu d’analyse et surtout la bonne requête que je vous présente, destinées à débloquer sans frustrer !
Un blocage est une situation normale dans une base de données, comme dans la vie courante. Les feux rouges, l’accès à des toilettes, les portiques de sécurité des aéroports, sont autant de points ou vous pouvez vous retrouvé bloqué pendant un certain temps, et cela est normal et fait partie de la vie. Les bases de données devant assurer la cohérence entre ceux qui lisent les données et ceux qui les écrivent, doivent poser des verrous afin de bloquer les utilisateurs concurrents dans certaines situations. Le principe est le suivant :
- un utilisateur qui lit des données, doit bloquer l’accès aux utilisateurs qui veulent modifier les mêmes données, mais n’empêchent pas d’autres utilisateurs de lire en même temps les mêmes données. Dans ce cas la base pose un verrou partagé (shared), qui peut être pessimiste (posé préventivement) ou optimiste (actionné après coup).
- un utilisateur qui écrit des données doit empêcher toute lecture et toute écriture concurrente. On parle alors de verrou exclusif.
Si une session doit faire différentes modification au sein d’une même transaction, les verrous vont être maintenus jusqu’à ce qu’intervienne la finalisation de la transaction, par un COMMIT (validation) ou un ROLLBACK (annulation). Pendant ce temps, si d’autres utilisateurs doivent accéder à ces données, il faut qu’ils patientent. C’est la notion de blocage. Un blocage est donc une situation normale dans une base de données, comme c’est le cas de madame Michu qui patiente dans sa 4L au feu rouge pour laisser passer les automobiliste de la voie latérale.
Ce qui n’est pas toujours normal, c’est un blocage qui dure, longtemps, voire, très, très longtemps… mais jamais de manière éternelle, car la situation doit se débouquer à la fin du traitement bloquant, c’est une certitude !
Dans ce cas, il est possible que d’autres utilisateurs se retrouvent bloqués, alors que même, ils ont déjà entamé des modification de données, susceptible d’entrainer d’autres blocages ! D’où l’effet boule de neige… Là encore cette situation peut être normale, si le bloqueur de tête met à jour une très importante quantité de données alors que beaucoup d’utilisateurs sont en train de parcourir ces mêmes données ! Mais dans bien des cas, c’est une situation anormale, généralement due à des mauvaises pratiques de développement, tel que des transaction démarré côté client, des transactions avec interactions de l’utilisateur, le forçage de verrous (les SGBDR se débrouillent généralement mieux tous seuls), des transactions non finalisées, l’usage d’un niveau d’isolation inappropriée, l’utilisation de code itératif eu lieu de code ensembliste, l’oubli de poser les index adéquats, l’utilisation de tables obèses, le recours systématique aux curseur, l’usage immodéré des déclencheurs, etc !
Bref, la question est, en présence d’un tel blocage, comment faire ?
LA SOLUTION
Elle consiste tout simplement à recherche qui bloque qui, et remonter jusqu’à bloqueur de tête pour le forcer à abandonner son traitement. Problème, cela nécessite de parcourir récursivement l’arbre de blocage de blocage… Second problème, il peut y avoir plusieurs chaines de blocage !
La DMV sys.dm_exec_requests permet de savoir qui bloque qui dans l’état actuel de l’instance. Les deux colonnes qui nous intéressent pour ce faire sont :
- session_id : l’identifiant de la session en cours
- blocking_session_id : l’identifiant de la session qui bloque
Différentes chaines de blocage de sessions dans SQL Server (en fait des arbres)
Dans la figure ci avant, les sessions 53 et 57 sont les leaders et bloquent respectivement :
- pour le 53, 11 session avec une profondeur maximale de 5
- pour le 57, 3 sessions avec une profondeur maximale de 3
Il faudrait débloquer en priorité le 53, attendre quelques instants pour voir si cela ne débloque pas le 57.
À partir de ces seules deux informations (session_id et blocking_session_id), nous pouvons remonter jusqu’aux bloqueurs de tête. L’écriture de cette requête doit procéder en deux temps :
rechercher les bloquers de tête, c’est à dire ceux qui ne sont pas bloqués par d’autres, mais qui en bloque d’autres. Une fois ces « leaders » trouvés, il suffit de descendre dans l’arbre de blocage pour comptabiliser le nombre de sessions bloquées et la profondeur du blocage.
Il suffit alors de tuer les « lead blockers » les plus prégnants, c’est à dire en général ceux qui bloquent le plus de session, ceux qui ont la plus grande profondeur de blocage, ceux dont le temps de traitement a été le plus long, ceux ayant consommé le plus d’IO, ceux ayant la requête la moins critique…. À vous de choisir !
La requête !
T_SESSION AS
(
-- on récupère les sessions en cours des utilisateurs
SELECT session_id, blocking_session_id
FROM sys.dm_exec_requests AS tout
WHERE session_id > 50
),
T_LEAD AS
(
-- on recherche les bloqueurs de tête
SELECT session_id, blocking_session_id
FROM T_SESSION AS tout
WHERE session_id > 50
AND blocking_session_id = 0
AND EXISTS(SELECT *
FROM T_SESSION AS tin
WHERE tin.blocking_session_id = tout.session_id)
),
T_CHAIN AS
(
-- requête récursive pour trouver les chaines de blocage
SELECT session_id AS lead_session_id, session_id, blocking_session_id, 1 AS p
FROM T_LEAD
UNION ALL
SELECT C.lead_session_id, S.session_id, S.blocking_session_id, p+1
FROM T_CHAIN AS C
JOIN T_SESSION AS S
ON C.session_id = S.blocking_session_id
),
T_WEIGHT AS
(
-- calculs finaux
SELECT lead_session_id AS LEAD_BLOCKER,
COUNT(*) -1 AS BLOCKED_SESSION_COUNT,
MAX(p) - 1 AS BLOCKED_DEEP,
'KILL ' + CAST(lead_session_id AS VARCHAR(16)) + ';' AS SQL_CMD
FROM T_CHAIN
GROUP BY lead_session_id
)
SELECT T.*,
DB_NAME(r.database_id) AS database_name, host_name, program_name,
nt_user_name,
q.text AS sql_command,
DATEDIFF(ms, last_request_start_time,
COALESCE(last_request_end_time, GETDATE())) AS duration_ms,
s.open_transaction_count,
r.cpu_time, r.reads, r.writes, r.logical_reads, r.total_elapsed_time
FROM T_WEIGHT AS T
JOIN sys.dm_exec_sessions AS s
ON T.LEAD_BLOCKER = s.session_id
JOIN sys.dm_exec_requests AS r
ON s.session_id = r.session_id
OUTER APPLY sys.dm_exec_sql_text(sql_handle) AS q
ORDER BY BLOCKED_SESSION_COUNT DESC, BLOCKED_DEEP DESC;
Cette requête présente les sessions bloquant les autres sessions, en déterminant la session à la tête d’une chaine de blocage (LEAD_BLOCKER), avec le nombre de sessions bloquées (BLOCKED_SESSION_COUNT) et la longueur maximale de la chaîne de blocage (BLOCKED_DEEP). Ceci permet de déterminer quelle session est à annuler en priorité en cas de blocage intempestif et d’utiliser la commande KILL (SQL_CMD) qui termine la session fautive en forçant un ROLLBACK. Les autres colonnes donnent des informations sur la requête qui a bloqué (texte SQL, base, origine du code, métriques diverses…)
ATTENTION
Ne pas confondre blocage et interblocage (ou étreinte fatale, ou encore verrou mortel…). Dans un interblocage, une session bloque des ressources qu’une autre session veut obtenir, tandis que cette même session demandeuse a bloqué des objets que la première a besoin. Dans ce cas, nous pouvons atteindre éternellement, car le blocage ne peut être résolu par l’attente. Le seul moyen consiste donc à sacrifier une des victimes de ce blocage et lui forçant une annulation de la transaction. Ceci est détecté automatiquement dans les bons SGBDR, qui procèdent automatiquement à l’achèvement d’une des victimes. Dans SQL Server, le message est intentionnellement significatif : « La transaction (ID de processus %1!) a été bloquée sur les ressources … par un autre processus et a été choisie comme victime. Réexécutez la transaction. »
LE CODE * LE CODE * LE CODE * LE CODE * LE CODE * LE CODE * LE CODE * LE CODE * LE CODE
Le code !
Expert S.G.B.D relationnelles et langage S.Q.L
Moste Valuable Professionnal Microsoft SQL Server
Société SQLspot : modélisation, conseil, formation,
optimisation, audit, tuning, administration SGBDR
Enseignant: CNAM PACA, ISEN Toulon, CESI Aix en Prov.
L’entreprise SQL Spot
Le site web sur le SQL et les SGBDR
Développez et administrez pour la performance avec SQL Server 2014