Comprendre et résoudre les problèmes de blocage de SQL Server

Objectif

L’objectif de cet article est de fournir des instructions sur la première compréhension du blocage en termes de SQL Server et sur l’étude de son occurrence.

Dans cet article, le terme connexion fait référence à une seule session ouverte de la base de données. Chaque connexion apparaît sous la forme d’un ID de session (SPID). Chacun de ces SPID est souvent appelé un processus, bien qu’il ne s’agisse pas d’un contexte de processus distinct dans le sens habituel. Au lieu de cela, chaque SPID se compose des ressources du serveur et des structures de données nécessaires pour traiter les demandes d’une connexion unique à partir d’un client donné. Une seule application cliente peut avoir une ou plusieurs connexions. Du point de vue de SQL Server, il n’existe aucune différence entre plusieurs connexions à partir d’une application cliente unique sur un ordinateur client unique et plusieurs connexions à partir de plusieurs applications clientes ou de plusieurs ordinateurs clients ; ils sont atomiques. Une connexion peut bloquer une autre connexion, quel que soit le client source.

Version du produit d’origine :   SQL Server
Numéro de la base de connaissances initiale :   224453

Qu’est-ce que le blocage

Le blocage est une caractéristique inévitable et de conception de tout système de gestion de base de données relationnelle (SGBDR) avec accès concurrentiel basé sur le verrouillage. Comme mentionné précédemment, dans SQL Server, le blocage se produit lorsqu’une session maintient un verrou sur une ressource spécifique et qu’un second SPID tente d’acquérir un type de verrou conflictuel sur la même ressource. En règle générale, le délai pendant lequel le premier SPID verrouille la ressource est faible. Lorsque la session propriétaire libère le verrou, la deuxième connexion est alors libre d’acquérir son propre verrou sur la ressource et de continuer le traitement. Il s’agit d’un comportement normal qui peut se produire à de nombreuses reprises tout au long de la journée sans effet notable sur les performances du système.

La durée et le contexte de transaction d’une requête déterminent la durée de ses verrous et, par conséquent, leur impact sur les autres requêtes. Si la requête n’est pas exécutée dans une transaction (et qu’aucune indication de verrou n’est utilisée), les verrous des instructions SELECT ne seront maintenus sur une ressource qu’en lecture réelle, et non pendant la durée de la requête. Pour les instructions INSERT, UPDATE et DELETE, les verrous sont maintenus pendant la durée de la requête, à la fois pour assurer la cohérence des données et pour permettre la restauration de la requête si nécessaire.

Pour les requêtes exécutées au sein d’une transaction, la durée pendant laquelle les verrous sont maintenus est déterminée par le type de requête, le niveau d’isolation des transactions et si les indicateurs de verrou sont utilisés dans la requête. Pour obtenir une description du verrouillage, des indicateurs de verrou et des niveaux d’isolation des transactions, consultez les rubriques suivantes dans la documentation en ligne de SQL Server :

  • Verrouillage dans le moteur de base de données
  • Personnalisation du verrouillage et de la gestion des versions de ligne
  • Modes de verrouillage
  • Compatibilité de verrou
  • Niveaux d’isolement basés sur la gestion des versions de ligne dans le moteur de base de données
  • Contrôle des transactions (moteur de base de données)

Lorsque le verrouillage et le blocage persistent au point où il y a un effet négatif sur les performances du système, cela est dû à l’une des raisons suivantes :

  • Un SPID maintient des verrous sur un ensemble de ressources pendant une période prolongée avant de les libérer. Ce type de blocage se résout lui-même au fil du temps, mais peut entraîner une dégradation des performances.

  • Un SPID maintient des verrous sur un ensemble de ressources et ne les libère jamais. Ce type de blocage ne se résout pas et empêche l’accès aux ressources affectées indéfiniment.

Dans le premier scénario ci-dessus, la situation peut être très fluide, car les différents SPID provoquent un blocage sur des ressources différentes au fil du temps, créant ainsi une cible mobile. Pour cette raison, ces situations peuvent être difficiles à résoudre à l’aide de SQL Server Management Studio (l’outil Go to for Managing SQL Server) et à limiter le problème aux requêtes individuelles. En revanche, la deuxième situation se traduit par un état cohérent qui peut être plus facile à diagnostiquer.

Méthodologie de résolution des problèmes de blocage

Quelle que soit la situation de blocage, la méthode de résolution des problèmes de verrouillage est la même. Ces séparations logiques indiquent le reste de la composition de cet article. Le concept est de trouver le blocage de l’en-tête et d’identifier ce que fait la requête et pourquoi il bloque. Une fois que la requête problématique est identifiée (c’est-à-dire qu’elle détient des verrous pendant la période prolongée), l’étape suivante consiste à analyser et à déterminer la cause du blocage. Une fois que nous avons compris les raisons, nous pouvons procéder à des modifications en redéfinissant la requête et la transaction.

Pour énumérer brièvement ce qui suit :

  1. Identifier la session de blocage principale (blocage de l’en-tête)

  2. Rechercher la requête et la transaction à l’origine du blocage (ce qui maintient les verrous pendant une période prolongée)

  3. Analyser/comprendre pourquoi le blocage prolongé se produit

  4. Résolution du problème de blocage en reconcevant la requête et la transaction

Nous allons maintenant examiner comment identifier la principale session de blocage avec une capture de données appropriée.

Collecte des informations de blocage

Pour pallier la difficulté de résolution des problèmes de blocage, un administrateur de base de données peut utiliser des scripts SQL qui surveillent constamment l’état de verrouillage et de blocage sur SQL Server. Pour collecter ces données, il existe deux méthodes. La première consiste à capture instantanée des DMV dans SQL Server, et la seconde à utiliser des traces XEvents/Profiler pour diagnostiquer ce qui était en cours d’exécution.

Collecte d’informations de DMV

Le fait de référencer les DMV pour résoudre les problèmes de blocage a pour objectif d’identifier le SPID (ID de session) à la tête de la chaîne de blocage et de l’instruction SQL. Recherchez les SPID victimes bloqués. Si un SPID est bloqué par un autre SPID, examinez le SPID propriétaire de la ressource (le SPID bloquant). Est-ce que le SPID du propriétaire est bloqué ou non (le blocage de l’en-tête) ? Vous souhaitez surtout parcourir la chaîne pour trouver le bloqueur de la tête, puis étudier la raison pour laquelle elle conserve son verrou. Pour ce faire, utilisez l'une des méthodes suivantes :

  • Cliquez avec le bouton droit sur l’objet serveur, développez Rapports, développez  rapports standard, puis cliquez sur  activité – toutes les transactions de blocage. Ce rapport affiche les transactions à la tête de la chaîne de blocage. Si vous développez la transaction, le rapport affiche les transactions bloquées par la transaction Head. Ce rapport affiche également l' instruction SQL de blocage et l' instruction SQL bloquée.

  • Si vous avez déjà identifié une session particulière, vous pouvez utiliser DBCC INPUTBUFFER(<spid>) pour rechercher la dernière instruction envoyée par un SPID.

  • Exécutez la requête ci-dessous pour rechercher les requêtes en cours d’exécution et leur mémoire tampon d’entrée. N’oubliez pas de remplir sys.dm_exec_requests, la requête doit être en cours d’exécution active avec SQL.

    select * from sys.dm_exec_requests er cross apply sys.dm_exec_sql_text (sql_handle)
    
  • Reportez-vous à la master.sys.sysprocesses liste et référencez la colonne bloqué. Plus d’informations sur les processussys.sys ici. Tout processus (actif ou non) sera mis en vente dans sys.sysprocesses .

  • Ouvrez Activity Monitor dans SSMS et reportez-vous à la colonne bloqué par . Pour plus d’informations sur Activity Monitor , consultez la rubrique.

  • Utilisez la procédure stockée sp_who intégrée pour interroger des sessions et faire référence à la colonne BLK . Plus d’informations sur sp_who ici.

  • Utilisez la sys.dm_tran_locks DMV. Plus d’informations sur sys.dm_tran_locks ici.

  • Vous souhaitez savoir si vous avez une transaction ouverte à long terme ? Exécutez DBCC OPENTRAN. N’oubliez pas qu’il n’est pas parfait de parcourir la chaîne, mais si vous avez une transaction longue susceptible de provoquer une chaîne de blocage. Plus d’informations sur DBCC OPENTRAN .

  • Référencez sys.dm_os_waiting_tasks qui se trouve au niveau de la couche thread/tâche de SQL. Plus d’informations sur sys.dm_os_waiting_tasks ici.

Avec les DMV, prendre des captures instantanées au fil du temps fournit des points de données qui vous permettront d’examiner le blocage sur un intervalle de temps spécifié pour identifier le blocage ou les tendances persistants. L’outil d’accès à CSS pour résoudre ces problèmes consiste à utiliser le collecteur de données PSSDiag. Cet outil utilise les « statistiques de performances SQL Server » pour capture instantanée des DMV référencées ci-dessus au fil du temps. Comme cet outil est en perpétuelle évolution, veuillez consulter la dernière version publique de DiagManager sur GitHub.

Collecte des informations de suivi SQL Server

Outre les informations ci-dessus, il est souvent nécessaire de capturer un suivi des activités sur le serveur pour examiner de manière approfondie un problème de blocage sur SQL Server. Par exemple, si une session exécute plusieurs instructions au sein d’une transaction, seule la dernière instruction soumise sera représentée. Toutefois, l’une des commandes précédentes peut être à l’origine de la conservation des verrous. Un suivi vous permet d’afficher toutes les commandes exécutées par une session dans la transaction en cours.

Il existe désormais deux façons de capturer des traces dans SQL Server ; XEvents et suivis du profileur. Le Générateur de profils SQL Server est la technologie de suivi d’origine et le profileur XEvent est la technologie de suivi plus récente qui offre davantage de souplesse. En général, il existe davantage de flexibilité avec XEvents et elles ont moins de charges que le générateur de profils SQL Server. Pour un dépannage plus avancé ou des captures de durée plus longues, XEvents est préférable. Pour une durée de capture plus courte et des événements moins intensifs, le suivi du générateur de profils SQL Server est suffisant. Étant donné que le générateur de profils SQL Server dispose d’une présence plus longue dans le produit SQL Server, il s’agit généralement de l’outil de suivi par défaut utilisé, mais ce n’est pas toujours recommandé.

Trace du générateur de profils SQL Server

Référencer les étapes identifiées pour le Générateur de profils SQL Server ici.

Si vous choisissez de ne pas utiliser de modèle par défaut, gardez à l’esprit que vous souhaitez sélectionner Afficher tous les événements et afficher toutes les colonnes. En outre, si vous utilisez un environnement de production à volume élevé, vous pouvez décider d’utiliser uniquement les événements du tableau 1, car ils sont généralement suffisants pour résoudre la plupart des problèmes de blocage. L’inclusion des événements ajoutés dans le tableau 2 peut faciliter la détermination de la source d’un problème (ou ces événements peuvent être nécessaires pour identifier l’instruction coupable dans une procédure à plusieurs instructions) au coût de l’ajout d’une charge supplémentaire sur le système et de l’augmentation de la taille de sortie du suivi.

  • Tableau 1 : types d’événements

    Titre Événement
    Erreurs et avertissements Exception
    Erreurs et avertissements Attention :
    Audit de sécurité Connexion d’audit
    Audit de sécurité Déconnexion d’audit
    Sessions Connexion existante
    Procédures stockées RPC : démarrage
    TSQL SQL : BatchStarting
  • Tableau 2 : types d’événements supplémentaires

    Titre Événement
    Transactions SQLTransaction
    Transactions SQLTransaction
    Procédures stockées RPC : terminé
    TSQL SQL : BatchCompleted
    Procédures stockées SP : StmtStarting
    Procédures stockées SP : StmtCompleted

Suivi XEvent

Reportez-vous au document qui explique comment utiliser le profileur XEvent. Avec le même compromis que ce qui est mentionné dans la section profileur ci-dessus, nous allons généralement capturer :

  • Contenu

    • Attention :

    • Error_reported

    • Blocked_process_report

    • Exchange_spill

    • Execution_warning

    • Hash_warning

    • Missing_column_statistics

    • Missing_join_predicate

    • Sort_warning

  • Potentiellement

    • Rpc_completed

    • Rpc_starting

    • Sp_cache_remove

    • Sql_batch_completed

    • Sql_batch_starting

    • Sql_statement_recompile

  • Lock

    • Lock_deadlock

    • Lock_escalation

    • Lock_timeout

  • Session

    • Existing_connection

    • Connexion

    • Réussi

Pour configurer le seuil et la fréquence de génération des rapports de processus bloqués, utilisez la commande sp_configure pour configurer l’option de seuil de processus bloqué, qui peut être définie en secondes. Par défaut, aucun rapport de processus bloqué n’est généré.

Identification et résolution des scénarios de blocage courants

En examinant les informations ci-dessus, vous pouvez déterminer la cause de la plupart des problèmes de blocage. Le reste de cet article décrit comment utiliser ces informations pour identifier et résoudre certains scénarios de blocage courants. Cette discussion part du principe que vous avez utilisé les scripts de blocage (référencés précédemment) pour capturer des informations sur les SPID de blocage et que vous avez effectué une trace de XEvent ou de générateur de profils avec les événements décrits ci-dessus.

Affichage de la sortie du script de blocage

  • Examiner la  sys.sysprocesses   sortie pour déterminer les têtes des chaînes de blocage

    Si vous n’avez pas spécifié le mode///Fast pour les scripts de blocage, il y aura une section intitulée SPID à la tête des chaînes de blocage qui répertorie les SPID qui bloquent les autres SPID dans la sortie du script.

    Si vous avez spécifié l’option Fast, vous pouvez toujours déterminer les têtes de blocage en examinant la  sys.sysprocesses   sortie et en suivant la hiérarchie du SPID indiqué dans la colonne bloqué.

  • Examinez la  sys.sysprocesses   sortie pour obtenir des informations sur les SPID à la tête de la chaîne de blocage.

    Il est important d’évaluer les  sys.sysprocesses   champs suivants :

    Statut

    Cette colonne indique l’état d’un SPID particulier. En règle générale, un état de veille indique que le SPID a terminé l’exécution et qu’il attend que l’application envoie une autre requête ou lot. Un exécutable, en cours d’exécution ou un  sos_scheduler_yield   État indique que le SPID est en train de traiter une requête. Le tableau suivant donne des explications succinctes des différentes valeurs d’État.

    Statut Signification
    Arrière-plan Le SPID exécute une tâche en arrière-plan, telle qu’une détection de blocage.
    Dormir Le SPID n’est pas en cours d’exécution. Cela indique généralement que le SPID attend une commande de l’application.
    En cours d’exécution Le SPID est en cours d’exécution sur un planificateur.
    Exécutables Le SPID se trouve dans la file d’attente Runnable d’un planificateur et en attente d’obtenir le temps de planification.
    Sos_scheduler_yield Le SPID était en cours d’exécution, mais il a produit volontairement sa tranche de temps sur le planificateur pour permettre à un autre SPID d’acquérir le temps de planification.
    Suspendu Le SPID attend un événement, comme un verrou ou un loquet.
    Activant Le SPID est en cours de restauration d’une transaction.
    Defwakeup Indique que le SPID attend une ressource qui est en cours de libération. Le waitresource champ doit indiquer la ressource en question.
    • Open_tran

      Ce champ indique le niveau d’imbrication des transactions du SPID. Si cette valeur est supérieure à 0, le SPID se trouve à l’intérieur d’une transaction ouverte et peut contenir des verrous acquis par une instruction au sein de la transaction.

    • Lastwaittype, waittype et waittime

      Le  lastwaittype   champ est une représentation sous forme de chaîne du  waittype   champ, qui est une colonne binaire interne réservée. Si la  waittype   valeur est 0X0000, le SPID n’attend rien pour le moment et la  lastwaittype   valeur indique le dernier  waittype   de l’absence du SPID. Si la  waittype   valeur est différente de zéro, la  lastwaittype   valeur indique le courant  waittype   du SPID.

      Pour plus d’informations sur  sys.dm_os_wait_stats et une brève description des différentes  lastwaittype   et des  waittype   valeurs, consultez la documentation en ligne de SQL Server.

      La  waittime   valeur peut être utilisée pour déterminer si le SPID est en cours d’exécution. Lorsqu’une requête sur la  sys.sysprocesses   table renvoie une valeur dans la  waittime   colonne inférieure à la valeur d'  waittime   une requête précédente de  sys.sysprocesses , cela indique que le verrou précédent a été acquis et libéré et qu’il attend maintenant un nouveau verrou (en supposant que la valeur est différente de zéro waittime ). Cela peut être vérifié en comparant la  waitresource    sys.sysprocesses   sortie entre.

    • Waitresource

      Ce champ indique la ressource qu’un SPID attend. Le tableau suivant répertorie les  waitresource   formats courants et leur signification :

      Ressource Format Exemple
      Table DatabaseID : ObjectID : IndexID ONGLET : 5:261575970:1
      Dans ce cas, l’ID de base de données 5 est l’exemple de base de données pubs et l’ID d’objet 261575970 est la table titles et 1 est l’index clusterisé.
      Page DatabaseID : FileID : PageID PAGE : 5:1:104
      Dans ce cas, l’ID de base de données 5 est pubs, l’ID de fichier 1 est le fichier de données principal et la page 104 est une page appartenant à la table titles.
      Pour identifier l’ID d’objet auquel appartient la page, utilisez la commande DBCC PAGE (dbid, fileid, pageid, output_option) et examinez la m_objId. Par exemple :
      DBCC TRACEON (3604)
      PAGE DBCC (5, 1, 104, 3)
      Clé DatabaseID : Hobt_id (valeur de hachage pour la clé d’index) CLÉ : 5:72057594044284928 (3300a4f361aa)
      Dans ce cas, l’ID de base de données 5 est pubs, Hobt_ID 72057594044284928 correspond à non Clustered index_id 2 pour l’ID d’objet 261575970 (table titles). Utilisez la vue de catalogue sys. partitions pour associer le hobt_id à un ID d’index particulier et à un ID d’objet. Il n’existe aucun moyen de déhacher le hachage de la clé d’index en une valeur de clé d’index spécifique.
      Ligne DatabaseID : FileID : PageID : slot (ligne) RID : 5:1:104:3
      Dans ce cas, l’ID de base de données 5 est pubs, l’ID de fichier 1 est le fichier de données principal, la page 104 est une page appartenant à la table titles et l’emplacement 3 indique la position de la ligne sur la page.
      Compiler DatabaseID : FileID : PageID : slot (ligne) RID : 5:1:104:3
      Dans ce cas, l’ID de base de données 5 est pubs, l’ID de fichier 1 est le fichier de données principal, la page 104 est une page appartenant à la table titles et l’emplacement 3 indique la position de la ligne sur la page.
    • Autres colonnes

      Les autres  sys.sysprocesses   colonnes peuvent également fournir des indications sur la racine d’un problème. Leur utilité varie en fonction des circonstances du problème. Par exemple, vous pouvez déterminer si le problème se produit uniquement à partir de certains clients (nom d’hôte), sur certaines bibliothèques réseau (net_library), lorsque le dernier lot soumis par un SPID était (last_batch), et ainsi de suite.

Examen de la sortie DBCC INPUTBUFFER

Pour tout SPID situé à la tête d’une chaîne de blocage ou avec une valeur différente de zéro waittype , le script de blocage s’exécute DBCC INPUTBUFFER pour déterminer la requête en cours pour ce SPID.

Dans de nombreux cas, il s’agit de la requête qui provoque le blocage d’autres utilisateurs. Toutefois, si le SPID se trouve au sein d’une transaction, les verrous ont pu être acquis par une requête exécutée précédemment, et non par celle en cours. Par conséquent, vous devez également consulter la sortie du générateur de profils pour le SPID, et pas seulement la inputbuffer .

Notes

Étant donné que le script de blocage se compose de plusieurs étapes, il est possible qu’un SPID apparaisse dans la première section en tant que tête d’une chaîne de blocage, mais lors de l’exécution de la INPUTBUFFER requête DBCC, il n’est plus bloqué et le INPUTBUFFER n’est pas capturé. Cela indique que le blocage se résout de lui-même pour ce SPID et qu’il peut s’agir d’un problème ou non. À ce stade, vous pouvez utiliser la version rapide du script de blocage pour vous assurer que vous capturez le inputbuffer avant de disparaître (même s’il n’y a toujours aucune garantie) ou afficher les données du générateur de profils à partir de ce délai afin de déterminer les requêtes que le SPID était en cours d’exécution.

Affichage des données du générateur de profils

L’affichage des données du générateur de profils est utile pour résoudre les problèmes de blocage. La chose la plus importante à réaliser est que vous n’avez pas besoin d’examiner tous les éléments que vous avez capturés ; être sélectif. Le générateur de profils offre des fonctionnalités pour vous aider à afficher efficacement les données capturées. Dans la boîte de dialogue Propriétés (dans le menu fichier , cliquez sur Propriétés), le générateur de profils vous permet de limiter les données affichées en supprimant des colonnes de données ou des événements, en regroupant (tri) par colonne de données et en appliquant des filtres. Vous pouvez rechercher des valeurs spécifiques dans le suivi complet ou uniquement dans une colonne spécifique (dans le menu Edition , cliquez sur Rechercher). Vous pouvez également enregistrer les données du profileur dans une table SQL Server (dans le menu fichier , pointez sur Enregistrer sous , puis cliquez sur table) et exécutez des requêtes SQL sur celle-ci.

Veillez à ne réaliser le filtrage que sur un fichier de suivi précédemment enregistré. Si vous effectuez ces étapes sur une trace active, vous risquez de perdre des données qui ont été capturées depuis le démarrage du suivi. Enregistrer d’abord une trace active dans un fichier ou une table (dans le menu fichier , cliquez sur Enregistrer sous), puis ouvrez-la de nouveau (dans le menu fichier , cliquez sur ouvrir) avant de poursuivre. Lorsque vous travaillez sur un fichier de suivi enregistré, le filtrage ne supprime pas définitivement les données en cours de filtrage, il n’affiche pas toutes les données. Vous pouvez ajouter et supprimer des événements et des colonnes de données selon vos besoins afin de vous aider à affiner vos recherches.

Éléments à Rechercher :

  • Quelles commandes le SPID a-t-il à la tête d’une chaîne de blocage exécutée dans la transaction en cours ? Filtrez les données de suivi d’un SPID particulier qui se trouve à la tête d’une chaîne de blocage (dans le menu fichier, cliquez sur Propriétés, puis, sous l’onglet filtres , spécifiez la valeur SPID ). Vous pouvez ensuite examiner les commandes qu’il a exécutées avant de bloquer les autres SPID. Si vous incluez les événements de transaction, ils peuvent facilement identifier à quel moment une transaction a été démarrée. Dans le cas contraire, vous pouvez rechercher dans la colonne les opérations,,, Text BEGIN SAVE COMMIT ou ROLLBACK TRANSACTION . Utilisez la open_tran valeur de la sysprocesses table pour vous assurer que vous interceptez tous les événements de transaction. Connaître les commandes exécutées et le contexte de transaction vous permettra de déterminer pourquoi un SPID maintient des verrous.

    N’oubliez pas que vous pouvez supprimer des événements et des colonnes de données. Au lieu de consulter à la fois les événements de début et de fin, choisissez-en un. Si les SPID de blocage ne sont pas des procédures stockées, supprimez les SP:Starting SP:Completed événements ou ; les événements SQLBATCH et RPC afficheront l’appel de procédure. Affichez uniquement les événements SP lorsque vous avez besoin de voir ce niveau de détail.

  • Quelle est la durée des requêtes pour les SPID à la tête des chaînes de blocage ?

    Si vous incluez les événements terminés ci-dessus, la colonne Durée indique la durée d’exécution de la requête. Cela peut vous aider à identifier les requêtes de longue durée qui provoquent un blocage. Pour déterminer la raison pour laquelle la requête s’exécute lentement, affichez les colonnes CPU, Read, and Write, ainsi que l’événement du plan d’exécution.

Catégorisation des scénarios de blocage courants

Le tableau ci-dessous mappe les symptômes courants à leurs causes probables. Le nombre indiqué dans la colonne scénario correspond au nombre dans la section scénarios et solutions de blocage courants de cet article ci-dessous. Les Waittype Open_Tran colonnes, et Status font référence à sysprocesses informations. La résolution ? colonne indique si le blocage se résout de lui-même.

Scénario Waittype Open_Tran Statut Résout? Autres symptômes
Non nul >= 0 exécutables Oui, à la fin de la requête. Les colonnes Physical_IO, CPU et/ou MEMUSAGE augmentent au fil du temps. La durée de la requête sera élevée une fois terminé.
0x0000 >0 dormir Non, mais le SPID peut être supprimé. Un signal attention peut apparaître dans le suivi du générateur de profils pour ce SPID, indiquant un dépassement de délai d’attente ou une annulation.
0x0000 >= 0 exécutables Non. Ne sera pas résolu avant que le client n’extraie toutes les lignes ou ferme la connexion. Le SPID peut être supprimé, mais cela peut prendre jusqu’à 30 secondes. Si open_tran = 0 et que le SPID maintient des verrous alors que le niveau d’isolation de transaction est par défaut (READ COMMMITTED), il s’agit probablement d’une cause probable.
4 Variables >= 0 exécutables Non. Ne se résoudra pas tant que le client n’aura pas annulé les requêtes ou fermé les connexions. Les SPID peuvent être supprimés, mais cela peut prendre jusqu’à 30 secondes. La colonne hostname de sysprocesses pour le SPID à la tête d’une chaîne de blocage sera identique à l’un des SPID qu’il bloque.
0x0000 >0 activant Oui. Un signal attention peut apparaître dans le suivi du générateur de profils pour ce SPID, indiquant qu’un dépassement de délai d’attente ou une annulation s’est produite, ou simplement une instruction ROLLBACK a été émise.
0x0000 >0 dormir Pourront. Lorsque Windows NT détermine que la session n’est plus active, la connexion SQL Server est interrompue. La valeur de last_batch dans sysprocesses est très antérieure à l’heure actuelle.

Scénarios de blocage courants et solutions

Les scénarios ci-dessous auront les caractéristiques indiquées dans le tableau ci-dessus. Cette section fournit des informations supplémentaires, le cas échéant, ainsi que des chemins d’accès à la résolution.

  1. Blocage provoqué par une requête exécutée normalement avec un temps d’exécution long

    Résolution :

    La solution à ce type de problème de blocage est de rechercher des méthodes d’optimisation de la requête. En réalité, cette catégorie de problème de blocage peut être un problème de performances et vous obliger à la poursuivre en tant que telle. Pour plus d’informations sur la résolution des problèmes liés à une requête de lenteur spécifique, voir Comment résoudre les problèmes de lenteur de requêtes sur SQL Server 7,0 ou sur les versions ultérieures.

    Pour plus d’informations, consultez les rubriques de procédures relatives à la surveillance des performances et au réglage.

    Si vous disposez d’une requête longue qui bloque d’autres utilisateurs et ne peut pas être optimisée, envisagez de le transférer d’un environnement OLTP vers un système d’aide à la décision.

  2. Blocage provoqué par un SPID en sommeil qui a perdu le suivi du niveau d’imbrication des transactions

    Ce type de blocage peut souvent être identifié par un SPID en sommeil ou en attente d’une commande, mais dont le niveau d’imbrication de la transaction ( @@TRANCOUNT , open_tran from sysprocesses ) est supérieur à zéro. Cela peut se produire si l’application rencontre un dépassement de délai de requête ou émet une instruction Cancel sans avoir également émis le nombre requis d’instructions ROLLBACK et/ou COMMIT. Lorsqu’un SPID reçoit un délai d’expiration de requête ou une annulation, il met fin à la requête et au lot en cours, mais ne restaure pas automatiquement ou ne valide pas la transaction. L’application est responsable de cette opération, car SQL Server ne peut pas supposer qu’une transaction entière doit être annulée en raison de l’annulation d’une seule requête. Le délai d’expiration ou l’annulation de la requête apparaîtra comme un événement de signal d’ATTENTION pour le SPID dans le suivi du générateur de profils.

    Pour illustrer cela, émettez la requête suivante à partir de l’analyseur de requêtes :

    BEGIN TRAN
    SELECT * FROM SYSOBJECTS S1, SYSOBJECTS S2
    
    -- Issue this after canceling query
    SELECT @@TRANCOUNT
    ROLLBACK TRAN
    

    Pendant l’exécution de la requête, cliquez sur le bouton rouge Annuler . Une fois la requête annulée, SELECT @@TRANCOUNT indique que le niveau d’imbrication de la transaction est un. S’il s’agissait d’une DELETE requête ou d’une UPDATE requête, ou si elle avait HOLDLOCK été utilisée sur le SELECT , tous les verrous acquis seraient toujours conservés. Même avec la requête ci-dessus, si une autre requête avait acquis et maintenu des verrous plus tôt dans la transaction, elle se tiendra quand elle SELECT était annulée.

    Résolutions

    • Les applications doivent gérer correctement les niveaux d’imbrication des transactions ou causer un problème de blocage suite à l’annulation de la requête de cette manière. Pour ce faire, plusieurs méthodes sont possibles :

      1. Dans le gestionnaire d’erreurs de l’application cliente, envoyez une IF @@TRANCOUNT > 0 ROLLBACK TRAN erreur suivante, même si l’application cliente ne pense pas qu’une transaction est ouverte. Cette opération est nécessaire, car une procédure stockée appelée pendant le traitement par lots peut avoir démarré une transaction sans la connaissance de l’application cliente. Certaines conditions, telles que l’annulation de la requête, empêchent l’exécution de la procédure au-delà de l’instruction actuelle, de sorte que même si la procédure a une logique pour vérifier IF @@ERROR <> 0 et abandonner la transaction, ce code de restauration n’est pas exécuté dans ces cas.

      2. SET XACT_ABORT ONÀ utiliser pour la connexion, ou dans toutes les procédures stockées qui commencent les transactions et qui ne nettoient pas à la suite d’une erreur. En cas d’erreur d’exécution, ce paramètre annule toutes les transactions ouvertes et renvoie le contrôle au client.

        Notes

        Les instructions T-SQL qui suivent l’instruction à l’origine de l’erreur ne seront pas exécutées.

      3. Si le regroupement de connexions est utilisé dans une application qui ouvre la connexion et exécute un petit nombre de requêtes avant de libérer la connexion au pool, comme une application Web, la désactivation temporaire du regroupement de connexions peut aider à atténuer le problème jusqu’à ce que l’application cliente soit modifiée afin de gérer les erreurs de manière appropriée. Si vous désactivez le regroupement de connexions, le fait de libérer la connexion entraînera une déconnexion physique de la connexion SQL Server, entraînant le rétablissement de toutes les transactions ouvertes par le serveur.

      4. Si le regroupement de connexions est activé et que le serveur de destination est SQL Server 2000, la mise à niveau de l’ordinateur client vers MDAC 2,6 ou version ultérieure peut être bénéfique. Cette version des composants MDAC ajoute du code au pilote ODBC et au fournisseur OLE DB de sorte que la connexion soit réinitialisée avant d’être réutilisée. Cet appel sp_reset_connection interrompt toutes les transactions déclenchées par le serveur (les transactions DTC initiées par l’application cliente ne sont pas affectées), réinitialise la base de données par défaut, définit les options, etc.

        Notes

        La connexion n’est pas réinitialisée tant qu’elle n’est pas réutilisée à partir du pool de connexions, de sorte qu’il est possible qu’un utilisateur puisse ouvrir une transaction, puis libérer la connexion au pool de connexions, mais qu’elle ne puisse pas être réutilisée pendant plusieurs secondes, pendant la durée pendant laquelle la transaction reste ouverte. Si la connexion n’est pas réutilisée, la transaction est abandonnée lorsque le délai d’expiration de la connexion est supprimé du pool de connexions. Par conséquent, il est optimal pour l’application cliente d’abandonner les transactions dans leur gestionnaire d’erreurs ou SET XACT_ABORT ON d’éviter ce délai d’attente.

    • En réalité, cette catégorie de problème de blocage peut également être un problème de performances et vous obliger à la poursuivre en tant que telle. Si la durée d’exécution de la requête peut être réduite, le délai d’expiration de la requête ou l’annulation ne se produisent pas. Il est important que l’application puisse gérer le délai d’expiration ou les scénarios d’annulation si elles se produisent, mais vous pouvez également tirer parti de l’examen des performances de la requête.

  3. Blocage provoqué par un SPID dont l’application cliente correspondante n’a pas récupéré toutes les lignes de résultats jusqu’à la fin

    Après l’envoi d’une requête au serveur, toutes les applications doivent immédiatement extraire toutes les lignes de résultats jusqu’à la fin. Si une application ne récupère pas toutes les lignes de résultats, les verrous peuvent rester sur les tables, bloquant les autres utilisateurs. Si vous utilisez une application qui envoie des instructions SQL au serveur de façon transparente, l’application doit extraire toutes les lignes de résultats. Si ce n’est pas le cas (et si elle ne peut pas être configurée), vous risquez de ne pas pouvoir résoudre le problème de blocage. Pour éviter ce problème, vous pouvez limiter les applications qui se comportent de façon médiocre à une base de données de création de rapports ou d’aide à la décision.

    Résolution :

    L’application doit être réécrite pour extraire toutes les lignes du résultat jusqu’à la fin.

  4. Blocage provoqué par un blocage de client/serveur distribué

    Contrairement à un blocage conventionnel, un blocage distribué n’est pas détectable à l’aide du gestionnaire de verrous du SGBDR. Cela est dû au fait qu’une seule des ressources impliquées dans le blocage est un verrou SQL Server. L’autre côté du blocage se trouve au niveau de l’application cliente, au-delà duquel SQL Server n’a pas de contrôle. Vous trouverez ci-dessous deux exemples de la façon dont cela peut se produire et la façon dont l’application peut l’éviter.

    • Blocage distribué client/serveur avec un seul thread client

      Si le client dispose de plusieurs connexions ouvertes, ainsi qu’un seul thread d’exécution, le blocage distribué suivant peut se produire. Par souci de concision, le terme dbproc utilisé ici fait référence à la structure de connexion du client.

      SPID1------blocked on lock------->SPID2
       /\ (waiting to write results
       | back to client)
       | |
       | | Server side
       | ================================|==================================
       | <-- single thread --> | Client side
       | \/
       dbproc1 <------------------- dbproc2
       (waiting to fetch (effectively blocked on dbproc1, awaiting
       next row) single thread of execution to run)
      

      Dans le cas indiqué ci-dessus, un seul thread d’application cliente a deux connexions ouvertes. Il envoie de manière asynchrone une opération SQL sur dbproc1. Cela signifie qu’il n’attend pas que l’appel soit retourné avant de poursuivre. L’application envoie ensuite une autre opération SQL sur dbproc2 et attend les résultats pour commencer à traiter les données renvoyées. Lorsque les données commencent à retour (quelle que soit la dbproc première réponse, supposons que dbproc1), il traite l’exécution de toutes les données renvoyées sur cette dbproc. Il extrait les résultats de dbproc1 jusqu’à ce que SPID1 soit bloqué sur un verrou détenu par SPID2 (car les deux requêtes sont exécutées de manière asynchrone sur le serveur). À ce stade, dbproc1 attendra indéfiniment d’autres données. SPID2 n’est pas bloqué sur un verrou, mais tente d’envoyer des données à son client, dbproc2. Toutefois, dbproc2 est effectivement bloqué sur dbproc1 au niveau de la couche d’application, car le thread d’exécution unique de l’application est en cours d’utilisation par dbproc1. Il en résulte un blocage que SQL Server ne peut pas détecter ou résoudre car une seule des ressources impliquées est une ressource SQL Server.

    • Blocage distribué client/serveur avec un thread par connexion

      Même si un thread distinct existe pour chaque connexion sur le client, une variante de ce blocage distribué peut se produire comme indiqué dans le code suivant.

      SPID1------blocked on lock-------->SPID2
       /\ (waiting on net write) Server side
       | |
       | |
       | INSERT |SELECT
       | ================================|==================================
       | <-- thread per dbproc --> | Client side
       | \/
       dbproc1 <-----data row------- dbproc2
       (waiting on (blocked on dbproc1, waiting for it
       insert) to read the row from its buffer)
      

      Ce cas est semblable à l’exemple A, sauf dbproc2 et SPID2 exécutent une SELECT instruction avec l’intention d’effectuer un traitement ligne par ligne et de transmettre chaque ligne via une mémoire tampon vers dbproc1 pour une INSERT instruction, UPDATE ou DELETE sur la même table. Finalement, SPID1 (le INSERT , UPDATE ou DELETE ) est bloqué sur un verrou détenu par SPID2 (exécutant le SELECT ). SPID2 écrit une ligne de résultats dans le client dbproc2. Dbproc2 essaie ensuite de transmettre la ligne dans une mémoire tampon vers dbproc1, mais trouve dbproc1 est occupé (il est bloqué en attente sur SPID1 pour terminer l’actuelle INSERT , qui est bloquée sur SPID2). À ce stade, dbproc2 est bloqué au niveau de la couche d’application par dbproc1 dont le SPID (SPID1) est bloqué au niveau de la base de données par SPID2. Là encore, il en résulte un blocage que SQL Server ne peut pas détecter ou résoudre, car seule l’une des ressources impliquées est une ressource SQL Server.

  5. Les deux exemples, A et B, sont des problèmes fondamentaux que les développeurs d’applications doivent connaître. Ils doivent coder les applications pour gérer ces cas de manière appropriée.

    Résolutions

    Deux solutions fiables permettent d’utiliser un délai d’attente de requête ou des connexions liées.

    • Délai d’expiration de la requête

      Lorsqu’un délai d’attente de requête a été fourni, si le blocage distribué se produit, il est rompu lorsque le délai d’expiration se produit. Pour plus d’informations sur l’utilisation d’un délai d’attente de requête, voir la documentation relative à DB-Library ou ODBC.

    • Connexions liées

      Cette fonctionnalité permet à un client disposant de plusieurs connexions de le lier à un seul espace de transaction, de sorte que les connexions ne se bloquent pas mutuellement. Pour plus d’informations, reportez-vous à la rubrique « utilisation de connexions liées » dans la documentation en ligne de SQL Server 7,0.

  6. Blocage provoqué par un SPID dont l’État est Golden ou Rollback

    Une requête de modification de données qui est supprimée, ou annulée en dehors d’une transaction définie par l’utilisateur, sera annulée. Cela peut également se produire en tant qu’effet secondaire de redémarrage de l’ordinateur client et de la déconnexion de sa session réseau. De même, une requête sélectionnée comme victime de blocage sera annulée. Une requête de modification de données ne peut pas être restaurée plus rapidement que les modifications ont été appliquées initialement. Par exemple, si une DELETE INSERT instruction,, ou a UPDATE été exécutée pendant une heure, la restauration peut prendre au moins une heure. Il s’agit du comportement attendu, car les modifications apportées doivent être annulées ou l’intégrité transactionnelle et physique de la base de données sera compromise. Comme cela doit se produire, SQL Server marque le SPID dans un État Golden ou Rollback (ce qui signifie qu’il ne peut pas être supprimé ou sélectionné comme victime du blocage). Cela peut souvent être identifié en observant la sortie de sp_who , qui peut indiquer la commande rollback. La colonne État sys.sysprocesses indique un état de restauration, qui s’affiche également dans sp_who sortie ou dans SQL Server Management Studio Activity Monitor.

    Résolution :

    Vous devez attendre que le SPID termine la restauration des modifications qui ont été apportées.

    Si le serveur est arrêté au milieu de cette opération, la base de données sera en mode de récupération lors du redémarrage et elle sera inaccessible jusqu’à ce que toutes les transactions ouvertes soient traitées. La récupération du démarrage prend essentiellement le même temps par transaction que la récupération à l’exécution et la base de données est inaccessible pendant cette période. Par conséquent, le fait de forcer le serveur à corriger un SPID dans un état de restauration est très peu productif.

    Pour éviter cette situation, n’effectuez pas de lots INSERT UPDATE ou d’opérations volumineux pendant les DELETE heures creuses sur les systèmes OLTP. Si possible, effectuez ces opérations pendant les périodes de faible activité.

  7. Blocage provoqué par une connexion orpheline

    Si l’application cliente s’interrompt ou si la station de travail cliente est redémarrée, la session réseau sur le serveur peut ne pas être annulée immédiatement dans certaines conditions. Du point de vue du serveur, le client semble toujours présent et tous les verrous acquis peuvent toujours être conservés. Pour plus d’informations, consultez la rubrique résolution des problèmes de connexions orphelines dans SQL Server.

    Résolution :

    Si l’application cliente s’est déconnectée sans nettoyer correctement ses ressources, vous pouvez mettre fin au SPID à l’aide de la KILL commande. La KILL commande prend la valeur SPID comme entrée. Par exemple, pour KILL SPID 9, exécutez la commande suivante :

    KILL 9
    

    Notes

    L' KILL exécution de la commande peut prendre jusqu’à 30 secondes, en raison de l’intervalle entre les vérifications de la KILL commande.

Implication des applications dans les problèmes de blocage

Il peut s’avérer très long de se concentrer sur le réglage côté serveur et les problèmes de plateforme en cas de problème de blocage. Toutefois, cela n’entraîne généralement pas de résolution et peut absorber le temps et l’énergie à examiner l’application cliente et les requêtes qu’elle soumet. Quel que soit le niveau de visibilité exposé par l’application en ce qui concerne les appels de base de données, un problème de blocage demande néanmoins fréquemment à la fois d’inspecter les instructions SQL exactes soumises par l’application et le comportement exact de l’application concernant l’annulation de la requête, la gestion de la connexion, l’extraction de toutes les lignes de résultats, et ainsi de suite. Si l’outil de développement n’autorise pas le contrôle explicite sur la gestion de la connexion, l’annulation de la requête, le délai d’expiration de la requête, l’extraction des résultats, etc., les problèmes de blocage peuvent ne pas être résolus. Ce potentiel doit être soigneusement examiné avant la sélection d’un outil de développement d’applications pour SQL Server, en particulier pour les environnements OLTP critiques.

Il est essentiel d’être extrêmement vigilant lors de la phase de conception et de construction de la base de données et de l’application. En particulier, la consommation des ressources, le niveau d’isolation et la longueur du chemin de transaction doivent être évalués pour chaque requête. Chaque requête et transaction doit être aussi simple que possible. Une meilleure discipline de gestion des connexions doit être testée. Si cela n’est pas fait, il est possible que l’application semble avoir des performances acceptables à faible nombre d’utilisateurs, mais que les performances peuvent se dégrader de manière significative à mesure que le nombre d’utilisateurs évolue vers le haut.

Avec une conception d’application et de requête appropriée, SQL Server est capable de prendre en charge de nombreux milliers d’utilisateurs simultanés sur un seul serveur, avec un blocage minime.