Gérer les verrous dans MS SQL Server peut s’avérer très complexe, notamment lors de l’exécution de requêtes qui se bloquent ou expirent. Il est fréquent qu’une transaction se bloque, ralentissant d’autres processus et provoquant une cascade de ralentissements. Ce problème est généralement dû à des transactions non validées ou inachevées, par exemple lorsqu’un utilisateur oublie de valider ou d’annuler une transaction ; SQL conserve alors le verrou. Dans les cas les plus graves, vos requêtes peuvent même se figer ou être complètement bloquées. Savoir identifier et libérer ces verrous est donc essentiel.
Cela peut parfois s’avérer délicat, notamment sur les serveurs très sollicités ou lorsque l’on ignore quelle transaction a provoqué le problème. Heureusement, SQL Server intègre des outils et des commandes permettant d’identifier les responsables. Consulter la table sysprocesses, utiliser le Moniteur d’activité ou même créer rapidement des procédures stockées peut éviter bien des frustrations. Attention toutefois à l’arrêt brutal des processus : une commande d’arrêt peut parfois perturber le fonctionnement du système si l’on ne maîtrise pas son fonctionnement. Néanmoins, si vous devez forcer le déverrouillage d’une table ou d’une session, la connaissance des principes de base simplifie considérablement la procédure.
Comment trouver et supprimer les verrous dans MS SQL Server
Quelles sont les causes des blocages et comment les identifier ?
Tout d’abord, pourquoi est-ce utile ? Parce qu’une fois les sessions ou requêtes bloquantes identifiées, vous savez précisément où concentrer vos efforts. Par exemple, si un processus reste bloqué pendant une durée excessive, vous pouvez l’interrompre ou approfondir l’investigation. Si vous constatez des temps de réponse lents aux requêtes ou si SQL Server signale un taux de blocage élevé, ces commandes constituent un excellent point de départ.
Pour vérifier les processus bloqués, exécutez la commande suivante dans SQL Server Management Studio (SSMS) :
select cmd, spid, blocked, waittype, waittime, waitresource from sys.sysprocesses where blocked > 0Cette liste affichera tous les processus en cours d’exécution qui sont bloqués, ainsi que l’entité qui les bloque. Les colonnes vous indiqueront si une session est en attente depuis trop longtemps (par exemple, plus de quelques secondes), ce qui signale un problème de verrouillage en cours.
Si vous souhaitez limiter la recherche à une base de données spécifique, exécutez :
SELECT spid, blocked, dbid, lastwaittype, waittime FROM master.dbo.sysprocesses WHERE dbid = DB_ID('yourdatabase') AND blocked <> 0 ORDER BY waittime DESCIci, vous pouvez voir l’identifiant du processus (SPID), la base de données et la durée d’attente de chaque processus. La colonne « Bloqué » indique le processus à l’origine du blocage. Si vous repérez un blocage prolongé, vous pouvez examiner la session correspondante avec :
DBCC INPUTBUFFER(spid)Ce qui donne la dernière requête exacte exécutée — un peu comme si on jetait un coup d’œil par-dessus son épaule pour voir ce qu’il essayait de faire.
Recherche de l’origine de la serrure
Certains verrous sont causés par des arbres de transactions entiers, c’est-à-dire un processus parent avec plusieurs processus enfants. Pour trouver le processus initial qui détient le verrou, vous pouvez exécuter :
select * from master.dbo.sysprocesses where spid = your_spidRemplacez your_spidpar l’ID du processus que vous avez trouvé précédemment. Généralement, la cause première est le premier processus dont l’ attribut blocked=0, ce qui signifie qu’il détient le verrou. Sur certaines configurations, cela permet d’identifier si une transaction est en attente d’une ressource externe ou bloquée au cours d’une transaction longue.
Comment interrompre un processus et déverrouiller le verrou
C’est une solution un peu radicale, mais parfois la seule possible. Si vous avez identifié un processus à l’origine du blocage et qu’il est possible de l’arrêter sans risque, exécutez :
KILL spidPar exemple:
KILL 59Attention : sur certains serveurs, l’arrêt brutal d’un processus peut entraîner des dysfonctionnements ou des annulations de modifications. Si vous travaillez sur un système de production, vérifiez bien avant d’effectuer cette opération, car SQL Server a tendance à compliquer les choses inutilement. Il est recommandé d’arrêter les processus uniquement lorsque vous êtes certain qu’ils n’effectuent pas de tâches critiques ou que vous disposez de sauvegardes.
Automatisation des contrôles de verrouillage avec des procédures stockées
Vous manquez de temps ou souhaitez un moyen plus rapide de suivre l’activité ? Vous pouvez créer une petite procédure stockée comme celle-ci, qui récupère la requête ayant provoqué le verrouillage :
CREATE PROCEDURE GetCurrentQueryCode @SPID int AS DECLARE @sql_handle varbinary(64) DECLARE @sql_text nvarchar(max) SELECT @sql_handle = sql_handle FROM sys.dm_exec_sessions WHERE session_id = @SPID SET @sql_text = (SELECT text FROM sys.dm_exec_sql_text(@sql_handle)) PRINT @sql_text Ensuite, il suffit d’exécuter :
EXEC GetCurrentQueryCode 51Cela permet d’avoir un aperçu rapide de la dernière commande SQL exécutée par cette session. Il est également possible de récupérer la requête SQL directement depuis le cache avec :
select * from sys.dm_exec_sql_text(0xYOURSQLHANDLE)Où 0xYOURSQLHANDLEse trouve le descripteur binaire obtenu lors des requêtes précédentes ? Cela peut s’avérer complexe, mais c’est extrêmement utile pour diagnostiquer les problèmes de verrouillage sur un serveur très sollicité.
Utilisation des outils intégrés de SQL Server Management Studio
Bien sûr, SSMS offre des outils visuels pratiques pour identifier les verrous. Faites un clic droit sur le serveur, sélectionnez Moniteur d’activité, puis développez Processus. De nombreuses informations sur les requêtes s’affichent, indiquant qui est en attente, qui bloque et depuis combien de temps, notamment si vous consultez l’ état SUSPENDU. Vous pouvez également accéder à Rapports > Toutes les transactions bloquantes. Vous y trouverez la liste des requêtes bloquées, leurs SPID et les sources des verrous. En cas de forte activité, l’activation des événements étendus ou de SQL Server Profiler peut s’avérer utile, mais ces outils sont plus avancés.
En fin de compte, la gestion des blocages repose sur un équilibre subtil entre la compréhension des mécanismes en jeu et la capacité d’agir sans aggraver la situation. En cas d’urgence, une approche rapide d’identification et de désactivation suffit généralement. N’oubliez pas : les blocages ont une raison d’être, et les débloquer consiste parfois simplement à patienter. Toutefois, disposer de ces commandes vous permettra d’y voir plus clair lorsqu’un élément bloque le système.