INF : Compréhension et résolution des problèmes de blocage de SQL Server

Résumé

Dans cet article, le terme « connexion » fait référence à une seule session de session de la base de données. Chaque connexion s’affiche sous la forme d’un ID de Session (SPID). On parle souvent de ces SPID comme de traitements, bien qu’il ne soit pas un contexte de processus distinct dans le sens habituel du terme. Chaque SPID résulte plutôt 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 client 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 seule application client sur un seul ordinateur client et plusieurs connexions de plusieurs applications clientes ou de plusieurs ordinateurs clients. Une connexion peut bloquer une autre connexion, qu’elles proviennent de la même application ou d’applications distinctes sur deux ordinateurs clients différents.

Plus d'informations

Le blocage est une caractéristique inévitable de tout système de gestion de base de données relationnelle (SGBDR) basé sur le verrou de la concurrence. Dans SQL Server, le blocage se produit lorsqu’un SPID possède un verrou sur une ressource spécifique et d’un deuxième SPID tente d’obtenir un type de verrou en conflit sur la même ressource. En règle générale, la période pour laquelle le premier SPID ne maintient le verrou est très faible. Lorsqu’il le libère le verrou, la deuxième connexion est libre de son propre verrou sur la ressource et de poursuivre le traitement. Ce comportement est normal et peut se produire plusieurs fois tout au long de la journée sans effet notable sur les performances du système.

Le contexte de transaction et de la durée d’une requête déterminent la durée de maintien du verrou 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 sans indicateurs de verrou), les verrous pour les instructions SELECT seulement tiendra sur une ressource au moment où qu'il est effectivement en cours de lecture, mais pas pour 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 la cohérence des données et pour permettre à la requête à être restaurée si nécessaire.

Pour les requêtes exécutées dans une transaction, la durée pour laquelle les verrous sont déterminés par le type de requête, le niveau d’isolement de la transaction et de verrouiller ou non indications sont utilisées dans la requête. Pour obtenir une description du verrouillage, des indicateurs de verrou et niveaux d’isolement des transactions, consultez les rubriques suivantes dans la documentation en ligne de SQL Server :

  • Verrouillage dans le moteur de base de données

  • Verrouillage de personnalisation et de gestion des versions de ligne

  • Modes de verrouillage

  • Compatibilité de verrouillage

  • Niveaux d’isolement fondé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 augmentent au point où il existe un effet négatif sur les performances du système, il est généralement dû à l’une des raisons suivantes :

  • Un SPID maintient des verrous sur un jeu de ressources pendant une longue période de temps avant de les introduire. Ce type de blocage résout de lui-même avec le 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 résout pas lui-même et empêche indéfiniment l’accès aux ressources affectées.

Dans le premier scénario ci-dessus, le blocage se résout de lui-même avec le temps lorsque le SPID libère les verrous. Toutefois, la situation peut être très fluide comme autre cause de SPID blocages sur différentes ressources au fil du temps, la création d’une cible de déplacement. Pour cette raison, ces situations peuvent être difficiles à résoudre à l’aide de SQL Server Enterprise Manager ou les requêtes SQL individuelles. Le deuxième scénario implique un état cohérent qui peut être plus facile à diagnostiquer.

Collecte des informations de blocage

Pour remédier à la difficulté de résoudre les problèmes de blocage, un administrateur de base de données peut utiliser les scripts SQL qui surveillent en permanence l’état de verrouillage et de blocage sur SQL Server. Ces scripts peuvent fournir des instantanés d’instances spécifiques au fil du temps, entraînant une image globale du problème. Pour obtenir une description de la procédure pour surveiller le blocage de scripts SQL, consultez les articles suivants dans la Base de connaissances Microsoft :

271509 comment surveiller le blocage dans SQL Server 2005 et SQL Server 2000

Les scripts dans cet article effectue les tâches ci-dessous. Si possible, la méthode d’obtention de ces informations à partir de SQL Server Management Studio est donnée.

  1. Identifier le SPID (ID de Session) à la tête de la chaîne de blocage et l’instruction SQL.
    Outre les scripts décrits dans l’article de la Base de connaissances mentionné précédemment, vous pouvez identifier la tête de la chaîne de blocage à l’aide de fonctionnalités qui sont fournies par le biais de SQL Server Management Studio. Pour ce faire, utilisez une des méthodes suivantes :

    • Cliquez droit sur l’objet serveur, développez les rapports, développez les Rapports Standard, puis cliquez sur activité – toutes les Transactions de blocage. Cet état indique les opérations à la tête de la chaîne de blocage. Si vous développez la transaction, le rapport affichera les transactions qui sont bloquées par la transaction de tête. Ce rapport affiche également la « déclaration de SQL de blocage » et la « déclaration de SQL bloqué ».

    • Utilisez DBCC INPUTBUFFER(<spid>) pour rechercher la dernière instruction envoyée par un SPID.

  2. Trouver le niveau d’imbrication des transactions et l’état de processus du SPID bloquant.
    Le niveau d’imbrication des transactions d’un SPID est disponible dans la variable globale @@TRANCOUNT. Toutefois, il peut être déterminé à partir de l’extérieur du SPID en interrogeant la table sysprocesses comme suit :

    SELECT open_tran FROM master.sys.sysprocesses WHERE SPID=<blocking SPID number>
    go

    La valeur retournée est la valeur de @@TRANCOUNT pour le SPID. Cela montre le niveau d’imbrication des transactions pour le SPID bloquant, ce qui à son tour explique pourquoi celui-ci maintient les verrous. Par exemple, si la valeur est supérieure à zéro, le SPID est en cours de transaction (auquel cas il est normal qu’il maintienne certains verrous qu’il a obtenus, selon le niveau d’isolement de transaction).

    Vous pouvez également vérifier s’il existe une transaction ouverte à long terme dans la base de données à l’aide de DBCC OPENTRAN
    database_name.

Collecte des informations de Trace SQL Server Profiler

Outre les informations ci-dessus, il est souvent nécessaire de capturer une trace du Générateur de profils des activités sur le serveur pour une investigation approfondie sur un problème de blocage sur SQL Server. Si un SPID exécute plusieurs instructions dans une transaction, uniquement de la dernière statementthat a été envoyé s’affichent dans le rapport, la mémoire tampon d’entrée ou la sortie de moniteur d’activité. Toutefois, une des commandes antérieures peut être à l’origine sont toujours en cours des verrouillages. Une trace du Générateur de profils vous permettra d’afficher toutes les commandes exécutées par un SPID dans la transaction en cours. Les étapes suivantes vous aident à configurer SQL Server Profiler pour capturer une trace.

  1. Ouvrez SQL Server Profiler.

  2. Dans le menu fichier , pointez sur Nouveau, puis cliquez sur suivi.

  3. Sous l’onglet Général , spécifiez un nom de trace et un nom de fichier pour capturer les données.

    Important Le fichier de trace doit être écrit sur un disque rapide local ou partagé. Évitez de suivi à un lecteur de disque ou de réseau lent. Assurez-vous également que le serveur traite la trace est activée.

  4. Dans l’onglet Sélection des événements , cliquez sur Afficher tous les événements et les cases à cocher Afficher toutes les colonnes .

  5. Dans l’onglet Sélection des événements , ajoutez les types d’événements qui sont répertoriés dans le tableau 1 à votre trace.

    En outre, vous pouvez inclure les types d’événements supplémentaires répertoriés dans le tableau 2 pour plus d’informations. Si vous exécutez dans un environnement de production à volume élevé, vous pouvez décider d’utiliser uniquement les événements dans le tableau 1, comme elles sont généralement suffisantes pour résoudre les problèmes de la plupart des problèmes de blocage. Inclure les événements supplémentaires dans le tableau 2 peut faciliter déterminer rapidement la source du problème (ou ces événements peuvent être nécessaires pour identifier l’instruction dans une procédure de plusieurs instruction coupable). Toutefois, y compris les événements dans le tableau 2 également ajouter à la charge sur le système et augmenter la taille de sortie de trace.

Tableau 1 : Types d’événements

Titre

Événement

Erreurs et avertissements

Exception

Erreurs et avertissements

Attention

Audit de sécurité

Ouverture de session d’audit

Audit de sécurité

Fermeture de session d’audit

Sessions

Connexion existante

Procédures stockées

RPC:Starting

TSQL

SQL:BatchStarting


Tableau 2 : Types d’événement supplémentaires

Titre

Événement

Transactions

DTCTransaction

Transactions

SQLTransaction

Procédures stockées

RPC:Completed

TSQL

SQL:BatchCompleted

Procédures stockées

SP:StmtStarting

Procédures stockées

SP:StmtCompleted


Pour plus d’informations sur le SQL Server Profiler, consultez la documentation en ligne de SQL Server.

Identification et résolution de 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 est une discussion sur la manière d’utiliser ces informations pour identifier et résoudre certains scénarios de blocage courants. Cette discussion suppose que vous avez utilisé les scripts de blocage dans l’article 271509 (mentionnés plus haut) pour capturer des informations sur les SPID bloquants et avez apporté une trace du Générateur de profils avec les événements décrits ci-dessus.

Affichage de la sortie du Script de blocage

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

Si vous n’avez pas spécifié le mode rapide pour les scripts de blocage, il y aura une section nommée « SPID à la tête de chaînes de blocage » qui répertorie les SPID bloquant d’autres SPID dans la sortie du script.

SPIDs at the head of blocking chains

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

Examinez la sortie sys.sysprocesses pour plus d’informations sur les SPID à la tête de la chaîne de blocage.

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

État

Cette colonne affiche l’état d’un SPID spécifique. En règle générale, un état de veille indique que le SPID a terminé son exécution et attend que l’application envoie une autre requête ou le lot. Un état exécutable, en cours d’exécutionou sos_scheduler_yield indique que le SPID traite actuellement une requête. Le tableau suivant donne de brèves explications sur les différentes valeurs d’état.

État

Signification

Arrière-plan

Le SPID exécute une tâche d’arrière-plan, telles que la détection de blocage.

En veille

Le SPID n’est pas en cours d’exécution. Cela indique généralement que le SPID attend une commande à partir de l’application.

En cours d’exécution

Le SPID est en cours d’exécution sur un planificateur.

Exécutable

Le SPID est dans la file d’attente exécutable d’un planificateur et en attente pour obtenir l’heure du planificateur.

Sos_scheduler_yield

Le SPID était en cours d’exécution, mais il a volontairement résultant de sa tranche de temps sur le planificateur pour autoriser un autre SPID acquérir le temps du planificateur.

Suspendu

Le SPID est en attente d’un événement, comme un verrou ou d’un loquet.

Retour arrière

Le SPID est lors de la restauration d’une transaction.

Defwakeup

Indique que le SPID est en attente d’une ressource qui est en train d’être libérée. Le champ du Grant 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 est dans une transaction ouverte et peut contenir des verrous acquis par n’importe quelle instruction au sein de la transaction.

Lastwaittype, waittype et waittime

Le champ lastwaittype est une représentation sous forme de chaîne du champ waittype , qui est une colonne binaire interne réservée. Si le type d’attente est 0 x 0000, le SPID n’attend pas de quoi que ce soit, et la valeur lastwaittype indique le dernier type d’attente ayant le SPID. Si le type d’attente n’est pas égale à zéro, la valeur de lastwaittype indique l' actuel type d’attente du SPID.

Pour une brève description des différentes lastwaittype et des valeurs de type d’attente , consultez l’article suivant dans la base de connaissances Microsoft :

Description 822101 des colonnes waittype et lastwaittype de la table master.dbo.sysprocesses dans SQL Server 2000 et SQL Server 2005

Pour plus d’informations sur sys.dm_os_wait_stats, consultez la documentation en ligne de SQL Server.

La valeur de temps d’attente peut être utilisée pour déterminer si l’exécution du SPID progresse. Lorsqu’une requête sur la table sys.sysprocesses renvoie une valeur dans la colonne temps d’attente est inférieure à la valeur de temps d’attente à partir d’une requête antérieure de sys.sysprocesses, cela indique que le verrou antérieur a été acquis et publié et est qu’il attend désormais un nouveau verrou (en supposant qu’autre que zéro). Cela peut être vérifié en comparant la waitresource entre sys.sysprocesses sortie.

Grant

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

Ressources

Format

Exemple

Table

DatabaseID:ObjectID:IndexID

ONGLET : 5:261575970:1
Dans ce cas, la base de données ID 5 est la base de données pubs et ID d’objet 261575970 est la table titles et 1 est l’index ordonné en clusters.

Page

DatabaseID:FileID:PageID

PAGE : 5:1:104 DE
Dans ce cas, la base de données ID 5 est pubs, ID de fichier 1 est le fichier de données principal et page 104 est un page appartenant à la table titles .

Pour identifier l’id de l’objet auquel appartient la page, utilisez la commande DBCC PAGE (dbid, fileid, pageid, output_option) et consultez la m_objId. Par exemple :

DBCC TRACEON ( 3604 )
DBCC PAGE ( 5 , 1 , 104 , 3 )

Clé

DatabaseID:Hobt_id (valeur de hachage de la clé d’index)

CLÉ : 5:72057594044284928 (3300a4f361aa)

Dans ce cas, la base de données ID 5 Pubs, 72057594044284928 de Hobt_ID correspond à non ordonné en clusters index_id 2 pour id d’objet 261575970 (tabletitles ). La vue de catalogue sys.partitions permet d’associer le hobt_id à un id d’index particulier et l’id de l’objet. Il n’y a aucun moyen de hachage le hachage de clé d’index à une valeur de clé d’index spécifique.

Ligne

DatabaseID:FileID:PageID:Slot(row)

RID : 5:1:104:3

Dans ce cas, la base de données ID 5 est pubs ID de fichier 1 est le fichier de données principal, page 104 est un page appartenant à la table titles et emplacement 3 indique la position de la ligne sur la page.

Compilation

DatabaseID:ObjectID [[COMPILE]]

ONGLET : 5:834102012 [[COMPILE]], que ce n’est pas un verrou de table, mais plutôt une compilation verrouille sur une procédure stockée. ID de la base de données 5 est pubs, ID d’objet 834102012 est une procédure stockée usp_myprocedure. Voir 263889 de l’Article de la Base de connaissances pour plus d’informations sur le blocage provoqué par des verrous de compilation.

Autres colonnes

Les autres colonnes sys.sysprocesses apportent une vision de la racine d’un problème. Leur utilité dépend des circonstances du problème. Par exemple, vous pouvez déterminer si le problème se produit uniquement à partir de certains clients (hostname) ou sur certaines bibliothèques réseau (net_library), lorsque le dernier lot envoyé par un SPID (last_batch) et ainsi de suite.

Examinez la sortie de DBCC INPUTBUFFER.

Pour tous les SPID à la tête d’une chaîne de blocage ou à un type d’attente différent de zéro, le script de blocage 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 pose les verrous qui bloquent les autres utilisateurs. Toutefois, si le SPID est actif dans une transaction, les verrous peuvent acquises par une requête exécutée précédemment, pas celle en cours. Par conséquent, vous devez également examiner la sortie du Générateur de profils pour ce SPID et pas seulement le processus inputbuffer.

Remarque Étant donné que le script de blocage consiste en plusieurs étapes, il est possible qu’un SPID apparaisse dans la première section comme la tête d’une chaîne de blocage, mais au moment de que l’exécution de la requête DBCC INPUTBUFFER, il ne bloque plus l’et le processus INPUTBUFFER n’est pas capturé. Cela indique que le blocage se résout de lui-même pour ce SPID et qu’il peut ou ne peut pas être un problème. À ce stade, vous pouvez utiliser la version rapide du script de blocage pour tenter de capturer le processus inputbuffer avant qu’il efface (bien qu’il n’existe encore aucune garantie), ou afficher les données du profileur à partir de cette période pour déterminer les requêtes que le SPID était en cours d’exécution.

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

Un examen efficace des données du Générateur de profils est primordial dans la résolution des problèmes de blocage. La chose la plus importante à comprendre est que vous n’avez pas à examiner toutes les données capturées ; Soyez sélectif. Générateur de profils fournit des fonctionnalités pour vous aider efficacement à afficher les données capturées. Dans la boîte de dialogue Propriétés (dans le menu fichier , cliquez sur Propriétés), Profiler vous permet de limiter les données affichées en supprimant des colonnes de données ou des événements, en groupant (triant) par les colonnes de données et en appliquant des filtres. Vous pouvez rechercher toute la trace ou uniquement une colonne spécifique (dans le menu Edition , cliquez sur Rechercher). Vous pouvez également enregistrer les données du Générateur de profils dans une table SQL Server (dans le menu fichier , pointez sur Enregistrer sous , puis sur Table) et pour exécuter des requêtes SQL.

Veillez à effectuer un filtrage uniquement sur un fichier de trace 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 de la trace. Enregistrer une trace active dans un fichier ou la table (dans le menu fichier , cliquez sur Enregistrer sous), puis le rouvrez (dans le menu fichier , cliquez sur Ouvrir) avant de continuer. Lorsque vous travaillez sur un fichier de trace enregistré, le filtrage ne supprime pas définitivement les données sont filtrées, il ne sont simplement pas affichées toutes les données. Vous pouvez ajouter et supprimer des événements et des colonnes de données selon les besoins afin de cibler vos recherches.

Ce que vous recherchez :

  • Quelles commandes a été le SPID à la tête d’une chaîne de blocage au sein de la transaction en cours d’exécution ?
    Filtrer les données de trace pour un SPID spécifique qui est à la tête d’une chaîne de blocage (dans le menu fichier , cliquez sur Propriétés, puis sur l’onglet filtres , spécifiez la valeur du SPID). Vous pouvez examiner les commandes exécutées avant l’heure qu’il bloque les autres SPID. Si vous incluez les événements Transaction, ils identifieront facilement lors du démarrage d’une transaction. Dans le cas contraire, vous pouvez rechercher la colonne texte BEGIN, SAVE, COMMIT ou ROLLBACK, les opérations de TRANSACTION. Utilisez la valeur open_tran à partir de la table sysprocesses pour vous assurer de capturer tous les événements de la transaction. Connaître les commandes exécutées et le contexte de transaction vous permettra de déterminer la raison pour laquelle un SPID maintient des verrous.

    N’oubliez pas, vous pouvez supprimer des événements et colonnes de données. Plutôt que de rechercher à partir de ces deux événements terminés, sélectionnez une. Si les SPID bloquants ne sont pas des procédures stockées, supprimer le
    SP : démarrage de ou de SP : terminé événements ; les événements SQLnouvelle et RPC affiche l’appel de procédure. La permet d’afficher uniquement les événements SP lorsque vous avez besoin de ce niveau de détail.

  • Quelle est la durée des requêtes pour le SPID à la tête des chaînes de blocage ?
    Si vous incluez les événements terminés ci-dessus, la colonne durée affiche la durée d’exécution de requête. Cela peut vous aider à identifier les requêtes d’exécution longue qui provoquent les blocages. Pour déterminer pourquoi la requête s’exécute lentement, affichez l' UC, lectureet écrit des colonnes, ainsi que l’événement de Plan d’exécution .

Catégorisation de scénarios de blocage courants

Le tableau ci-dessous associe des problèmes courants à leur cause probable. Le nombre indiqué dans la colonne scénario correspond au nombre dans la section « Blocage de scénarios et résolutions courantes » de cet article. Les colonnes Waittype, Open_Tranet l’état Voir informations de sysprocesses . Le résout ? colonne indique ou non le blocage se résout de lui-même.

Scénario

Type d’attente

Open_Tran

État

Résout ?

Autres problèmes

1

Non nul

> = 0

exécutable

Oui, lorsque la requête est terminée.

Les colonnes Physical_IO, CPU et/ou Memusage augmenteront avec le temps. Durée de la requête sera élevée terminé.

2

0x0000

> 0

en veille

Non, mais le SPID peut être supprimé.

Un signal d’avertissement peut être vu dans la trace du Générateur de profils pour ce SPID, indiquant un délai d’expiration de la requête ou l’annuler s’est produite.

3

0x0000

> = 0

exécutable

Non. Ne résoudra pas jusqu'à ce que le client extrait toutes les lignes ou ferme la connexion. Le SPID peut être supprimé, mais il peut prendre jusqu'à 30 secondes.

Si open_tran = 0 et que le SPID maintient des verrous tandis que le niveau d’isolation des transactions est la valeur par défaut (lecture COMMMITTED), il s’agit d’une cause probable.

4

Varie

> = 0

exécutable

Non. Ne résoudra pas jusqu'à ce que le client annule les requêtes ou ferme les connexions. SPID peuvent être supprimés, mais peuvent prendre jusqu'à 30 secondes.

La colonne hostname dans sysprocesses pour le SPID à la tête d’une chaîne de blocage sera identique à un des SPID qu’il bloque.

5

0x0000

> 0

retour arrière

Oui.

Un signal d’avertissement peut être vu dans la trace du Générateur de profils pour ce SPID, indiquant un délai d’expiration de la requête ou Annuler s’est produite, ou simplement une instruction rollback a été délivrée.

6

0x0000

> 0

en veille

Par la suite. Lorsque Windows NT détermine que la session n’est plus active, la connexion SQL Server sera rompue.

La valeur last_batch dans sysprocesses est nettement antérieure à l’heure actuelle.

Résolutions et des scénarios de blocage courants

Les scénarios ci-dessous aura les caractéristiques répertoriées dans le tableau ci-dessus. Cette section fournit des détails supplémentaires, le cas échéant, ainsi que des voies de résolution.

  1. Blocage provoqué par un normalement en cours d’exécution une requête avec un temps d’exécution

    Résolution :
    La solution à ce type de problème de blocage consiste à rechercher les moyens d’optimiser la requête. En fait, cette catégorie de problème peut simplement être un problème de performances et nécessitent d’être traitée en tant que tel. Pour plus d’informations sur le dépannage d’une requête de lenteur d’exécution spécifique, consultez l’article suivant de la Base de connaissances Microsoft :

    243589 comment résoudre les problèmes de lenteur d’exécution des requêtes sur SQL Server 7.0 ou versions ultérieures

    Pour la résolution des problèmes de performances globales de l’application, consultez l’article suivant de la Base de connaissances :

    224587 Comment : résoudre les problèmes de Performance d’applications sous SQL Server

    Pour plus d’informations, consultez la rubrique documentation en ligne de SQL Server 2008 analyse des performances et réglage des rubriques Comment sur le site Web MSDN suivant :

    http://msdn.microsoft.com/en-us/library/ms187830.aspxSi vous avez une requête d’exécution longue qui bloque d’autres utilisateurs et ne peut pas être optimisée, envisagez de déplacer d’un environnement OLTP vers un système de support de décision.

  2. Blocage provoqué par un SPID en veille qui a perdu la trace de niveau d’imbrication de la Transaction

    Ce type de blocage peut souvent être identifié par un SPID en veille ou en attente d’une commande, mais dont niveau d’imbrication des transactions (@@TRANCOUNT, open_tran de sysprocesses) est supérieur à zéro. Cela peut se produire si l’application rencontre un dépassement de délai ou émet une annulation sans exécuter également le nombre requis d’instructions ROLLBACK et/ou COMMIT. Lorsqu’un SPID reçoit un délai d’expiration de la requête ou l’annuler, il va mettre fin à la requête en cours et le traitement par lots, mais ne pas automatiquement Annuler et valider la transaction. L’application est responsable de ce problème, SQL Server ne peut pas supposer qu’un ensemble de la transaction doit être restaurée en raison d’une requête unique en cours d’annulation. Le délai d’expiration de la requête ou l’annuler s’affiche sous la forme d’un événement de signal ATTENTION pour le SPID dans la trace du Générateur de profils.

    Pour illustrer cela, tapez la requête simple 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 la croix rouge bouton Annuler . Après l’annulation de la requête, sélectionnez @@TRANCOUNT indique que le niveau d’imbrication des transactions. Cela avait été une suppression ou une requête mise à jour, ou si HOLDLOCK avait été utilisé dans la sélection, tous les verrous obtenus seraient maintenus. Même avec la requête ci-dessus, si une autre requête avait acquis et détenus de verrous plus haut dans la transaction, ils seraient encore maintenus lorsque l’instruction SELECT ci-dessus a été annulée.

    Résolutions :

    • Les applications doivent gérer correctement les niveaux d’imbrication des transactions, ou elles peuvent provoquer un problème de blocage après l’annulation de la requête de cette manière. Pour ce faire de plusieurs façons :

      1. Dans le Gestionnaire d’erreurs de l’application cliente, soumettre une instruction IF @@TRANCOUNT > 0 TRAN de restauration suite à une erreur, même si l’application cliente qu’aucune transaction n’est ouvrir. Cela est nécessaire, car une procédure stockée appelée au cours du lot peut avoir démarré une transaction sans connaissances de l’application client. Notez que certaines conditions, par exemple l’annulation de la requête, empêchent la procédure de s’exécuter au-delà de l’instruction en cours, même que si la procédure a une logique différente de @@ERROR IF 0 de vérifier et d’abandon de la transaction, ce code de restauration ne sera pas être exécuté dans ces demandes de devis.

      2. Utilisez SET XACT_ABORT ON pour la connexion, ou dans toute procédure stockée qui démarre les transactions et ne purge pas après une erreur. En cas d’erreur lors de l’exécution, ce paramètre sera abandonner toutes les transactions ouvertes et retourner le contrôle au client. Notez que les instructions T-SQL qui suit l’instruction qui a provoqué 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 basée sur le Web, la désactivation temporaire du groupement de connexions aidera peut-être à réduire le problème jusqu'à ce que l’application cliente est modifiée pour gérer les erreurs de manière appropriée. En désactivant le regroupement de connexion, libérer la connexion provoquera une fermeture physique de la connexion SQL Server, ce qui entraîne l’annulation de toutes les transactions ouvertes de serveur.

      4. Si le regroupement de connexions est activé et que le serveur de destination est SQL Server 2000, il peut être avantageux de mise à niveau de l’ordinateur client vers MDAC 2.6 ou version ultérieure. Cette version des composants MDAC ajoute du code pour le pilote ODBC et le fournisseur OLE DB afin que la connexion soit « réinitialisée » avant qu’elle soit réutilisée. Cet appel à sp_reset_connection annule toute transaction exécutée par le serveur (les transactions DTC démarrées par l’application cliente ne sont pas affectées), rétablit la base de données de la valeur par défaut, définir les options et ainsi de suite. Notez que la connexion n’est pas réinitialisée tant qu’il est réutilisé du regroupement de connexion, il est donc possible qu’un utilisateur peut ouvrir une transaction et puis libérer la connexion au pool de connexions, mais il ne peut pas être réutilisé pendant quelques secondes, durant lesquelles le transaction restent ouverte. Si la connexion n’est pas réutilisée, la transaction est abandonnée lorsque la connexion arrive à expiration et est supprimée du pool de connexions. Ainsi, il est idéal pour l’application cliente d’annuler les transactions dans son gestionnaire d’erreurs ou d’utiliser SET XACT_ABORT ON pour éviter ce délai potentiel.

    • En fait, cette catégorie de problème peut également être un problème de performances et nécessitent d’être traitée en tant que tel. Si le temps d’exécution requête peut être réduit, le dépassement de délai ou l’annulation ne se produisent pas. Il est important que l’application soit en mesure de gérer le délai d’attente ou l’annulation le cas échéant, mais vous pouvez également tirer profit de l’examen des performances de la requête.

  3. Blocage provoqué par un SPID dont l’Application cliente correspondante n’a pas extrait toutes les lignes du résultat jusqu'à la fin

    Après l’envoi d’une requête au serveur, toutes les applications doivent immédiatement extraire toutes les lignes du résultat jusqu'à la fin. Si une application n’extrait pas toutes les lignes de résultat, des verrous peuvent rester sur les tables, bloquant d’autres utilisateurs. Si vous utilisez une application qui envoie des instructions SQL au serveur en toute transparence, l’application doit extraire toutes les lignes de résultat. Si elle ne le fait pas (et s’il ne peut pas être configuré pour cela), vous ne pourrez pas résoudre le problème de blocage. Pour éviter ce problème, vous pouvez restreindre les applications fautives à une base de données d’aide à la décision ou de déclaration.

    Résolution :

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

  4. Blocage provoqué par un blocage distribué Client/serveur

    Contrairement à un blocage conventionnel, un blocage distribué n’est pas détectable à l’aide du Gestionnaire de verrous SGBDR. Cela est dû au fait que seule des ressources impliquées dans le blocage est un verrou de SQL Server. L’autre côté de l’interblocage est au niveau de l’application client, sur laquelle SQL Server n’a aucun contrôle. Voici deux exemples de comment cela peut se produire et comment l’application peut l’éviter.

    1. Client/serveur blocage distribué avec un seul Thread Client
      Si le client a ouvert plusieurs connexions et un seul thread d’exécution, le blocage distribué ci-dessous 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 illustré ci-dessus, un thread d’application cliente a ouvert deux connexions. Il soumet une opération SQL sur dbproc1 de manière asynchrone. Cela signifie qu’il n’attend pas l’appel avant de continuer. L’application puis envoie une autre opération SQL sur dbproc2 et attend les résultats avant de commencer à traiter les données retournées. Lorsque les données commencent à fidéliser (quel que soit le dbproc répond en premier - supposent il s’agit de dbproc1), elle traite toutes les données renvoyées sur ce dbproc. Il extrait les résultats de dbproc1 jusqu'à ce que SPID1 soit bloqué sur un verrou maintenu par SPID2 (parce que les deux requêtes sont exécutées sur le serveur). À ce stade, dbproc1 attend indéfiniment de plus de données. SPID2 n’est pas bloqué sur un verrou mais tente d’envoyer des données à son client dbproc2. Toutefois, dbproc2 est bloqué sur dbproc1 au niveau de la couche d’application car le thread unique d’exécution de l’application est utilisé par dbproc1. Cela provoque un blocage que SQL Server ne peut pas détecter ni résoudre parce qu’une des ressources concernées est une ressource SQL Server.

    2. Client/serveur blocage distribué avec un Thread par connexion

      Même si un thread distinct existe pour chaque connexion sur le client, une variation de ce blocage distribué peut se produire comme indiqué par le texte 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 que dbproc2 et SPID2 exécutent une instruction SELECT dans l’intention d’effectuer une ligne au moment de traitement et de traitement de chaque ligne via une mémoire tampon à dbproc1 pour une insertion, une mise à jour, ou supprimer l’instruction sur la même table. Finalement, SPID1 (en effectuant l’INSERT, UPDATE ou DELETE) se bloque sur un verrou maintenu par SPID2 (en effectuant la sélection). SPID2 écrit une ligne de résultat au client dbproc2. Dbproc2 tente alors de passer la ligne dans une mémoire tampon à dbproc1, mais dbproc1 est occupé (il est bloqué en attente de SPID1 qui doit finir l’instruction INSERT en cours, qui est bloquée sur SPID2). À ce stade, dbproc2 est bloqué au niveau de la couche application par dbproc1, dont le SPID (SPID1) est bloqué au niveau de la base de données par SPID2. Là encore, cela provoque un blocage que SQL Server ne peut pas détecter ni résoudre parce qu’une des ressources concernées est une ressource SQL Server.

    Ces deux exemples A et B sont les problèmes fondamentaux que les développeurs d’applications doivent respecter. Ils doivent coder leurs applications pour gérer ces situations de façon appropriée.

    Résolutions :

    Il existe deux solutions de fiables d’utiliser un délai d’expiration de la requête ou les connexions associées.

    • Délai d’expiration de la requête
      Lorsqu’un délai d’expiration de la requête a été fourni, si le blocage distribué se produit, il sera débloqué lorsque délai est dépassé. Consultez la DB-Library ou la documentation ODBC pour plus d’informations sur l’utilisation d’un délai d’expiration de la requête.

    • Connexions liées
      Cette fonctionnalité permet à un client ayant plusieurs connexions de les lier dans un espace de transaction unique, afin que les connexions ne bloquent pas les uns des autres. Pour plus d’informations, consultez la rubrique « Utilisation de connexions liées » dans la documentation en ligne de SQL Server 7.0.

  5. Blocage provoqué par un SPID en « Or », ou de restaurer, état

    Une requête de modification de données qui est mis à mort ou annulée en dehors d’une transaction définie par l’utilisateur, sera annulée. Cela peut également survenir comme un effet secondaire de l’ordinateur redémarre et que sa session réseau est déconnectée. De même, une requête sélectionnée comme victime du blocage sera annulée. Une requête de modification de données souvent ne peut pas être annulée plus rapidement que les modifications ont été appliquées au départ. Par exemple, si une instruction DELETE, INSERT ou UPDATE est exécutée pendant une heure, il pourrait prendre au moins une heure à restaurer. Ce comportement est normal, car les modifications apportées doivent être totalement annulées ou intégrité transactionnelle et physique dans la base de données serait compromise. Car cela doit se produire, SQL Server marque le SPID dans un état « or » ou rollback (qui signifie qu’il ne peut pas être mis à mort ni sélectionné comme victime du blocage). Ceci peut souvent être identifié en observant la sortie de sp_whoqui peut indiquer la commande ROLLBACK. La colonne statut de sys.sysprocesses indiquera un état ROLLBACK, qui apparaîtra également dans la sortie sp_who ou Moniteur d’activité SQL Server Management Studio.
    Résolution :

    Vous devez attendre que le SPID finir d’annuler les modifications apportées.

    Si le serveur est arrêté au cours de cette opération, la base de données sera en mode de récupération au redémarrage et elle restera inaccessible jusqu'à ce que toutes les transactions sont traitées. Récupération au démarrage prend essentiellement le même intervalle de temps par transaction que la récupération de l’exécution et la base de données est inaccessible pendant cette période. Par conséquent, forcer le serveur à résoudre un SPID en état de restauration sera souvent contre-productive.

    Pour éviter cette situation, ne pas effectuer le gros lot INSERT, UPDATE, ou supprimer des opérations pendant les heures de disponibilité sur les systèmes OLTP. Si possible, effectuez ces opérations pendant les périodes de faible activité.

  6. Blocage provoqué par une connexion orpheline

    Si les interruptions d’application client ou la station de travail cliente est redémarrée, la session réseau sur le serveur ne peut pas être annulée immédiatement dans certaines conditions. Du point de vue du serveur, le client semble être toujours présent et tous les verrous obtenus seront maintenus. Pour plus d’informations, cliquez sur le numéro ci-dessous pour afficher l’article correspondant dans la Base de connaissances Microsoft :

    137983 comment résoudre les problèmes de connexions orphelines dans SQL Server


    Résolution :

    Si l’application cliente s’est déconnectée sans purger ses ressources de façon appropriée, vous pouvez supprimer le SPID à l’aide de la commande KILL. La commande KILL utilise la valeur du SPID comme entrée. Par exemple, pour supprimer SPID 9, tapez la commande suivante :

    KILL 9


    Remarque La commande KILL peut prendre jusqu'à 30 secondes pour s’exécuter, en raison de l’intervalle entre les vérifications de la commande KILL.

Rôle de l’application dans des problèmes de blocage

Il peut y avoir tendance à se concentrer sur les problèmes de réglage et de plate-forme côté serveur lors d’un problème de blocage. Toutefois, cela n’entraîne généralement pas une résolution et peut absorber de temps et énergie mieux dirigées à l’examen de l’application cliente et les requêtes qu’elle envoie. Quel que soit le niveau de visibilité que l’application expose en ce qui concerne les réalisation des appels de base de données, un problème de blocage néanmoins fréquemment exige l’examen des instructions SQL exactes envoyées par l’application et l’application exacte comportement en ce qui concerne l’annulation de requêtes, 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 de contrôle explicite sur la gestion des connexions, l’annulation de requêtes, délai d’expiration de la requête, l’extraction de résultats et ainsi de suite, les problèmes de blocage ne trouveront pas de solutions. Ce potentiel doit être examiné attentivement avant de sélectionner un outil de développement d’applications pour SQL Server, en particulier pour les environnements OLTP critiques pour l’entreprise.

Il est essentiel d’être extrêmement vigilant lors de la phase de conception et de construction de la base de données et l’application. En particulier, la consommation de ressources, niveau d’isolement et longueur du chemin de transaction doivent être évalués pour chaque requête. Chaque requête et chaque transaction doivent être aussi léger que possible. Discipline de gestion de connexion correcte doit être exercée. Si ce n’est pas fait, il est possible que l’application d’avoir des performances acceptables au faible nombre d’utilisateurs, mais les performances se dégradent considérablement le nombre d’utilisateurs vers le haut.

Avec une application correcte et de création de requête, Microsoft SQL Server est capable de prendre en charge plusieurs milliers d’utilisateurs simultanés sur un seul serveur, avec un blocage minimal.

Besoin d’aide ?

Développez vos compétences
Découvrez des formations
Accédez aux nouvelles fonctionnalités en avant-première
Rejoindre Microsoft Insider

Ces informations vous ont-elles été utiles ?

Nous vous remercions pour vos commentaires.

Merci pour vos commentaires. Il serait vraisemblablement utile pour vous de contacter l’un de nos agents du support Office.

×