Comment résoudre les problèmes de blocage qui sont provoqués par escalade du verrouillage dans SQL Server

Traductions disponibles Traductions disponibles
Numéro d'article: 323630 - Voir les produits auxquels s'applique cet article
Agrandir tout | Réduire tout

Sommaire

Résumé

Promotion du verrouillage est le processus de conversion de plusieurs verrous affinées (tels que les verrous ligne ou page) en verrous de la table. Microsoft SQL Server dynamiquement détermine à quel moment escalade du verrouillage effectuer. Lorsque vous effectuez cette décision, SQL Server prend en compte le nombre de verrous sont maintenus sur une analyse particulière, le nombre de verrous sont détenus par la transaction complète et la mémoire utilisé pour les verrous dans le système dans son ensemble. En général, par défaut SQL Server entraîne escalade du verrouillage se produise uniquement à ces points, où il serait améliorer les performances ou lorsque vous devez réduire mémoire de verrouillage excessif système à un niveau plus raisonnable. Toutefois, certains modèles d'application ou une requête peuvent déclencher escalade du verrouillage à un moment lorsqu'il n'est pas souhaitable, et le verrouillage de table escalated peut bloquer d'autres utilisateurs. Cet article explique comment déterminer si escalade du verrouillage pose blocage et comment traitent escalade verrouillage indésirable.

Plus d'informations

Comment faire pour déterminer si au verrouillage est provoqué le blocage

Promotion de verrous n'entraîne pas la plupart des problèmes de blocage. Pour déterminer si escalade du verrouillage se produit autour de la fois lorsque vous rencontrez des problèmes de blocage, démarrez une trace du Générateur de profils qui inclut l'événement verrouillé : au . Si vous ne voyez pas les événements verrouillé : au , escalade du verrouillage se produit pas sur votre serveur et les informations contenues dans cet article ne s'applique pas à votre situation.

Si l'escalade du verrouillage se produit, vérifiez que le verrou de table escalated ne bloque les autres utilisateurs.

Pour plus d'informations sur la façon d'identifier le bloqueur de fenêtres têtes d'impression et identifier la ressource de verrou détenue par le bloqueur de fenêtres têtes d'impression qui bloque d'autres processus serveur ID (SPID), cliquez sur le numéro ci-dessous pour afficher l'article correspondant dans la Base de connaissances Microsoft :
224453 Compréhension et la résolution SQL Server 7.0 ou 2000 problèmes de blocage
Si le verrou qui bloque les autres utilisateurs est autre chose qu'un verrou de (niveau table) tabulation avec un mode de verrouillage de S (partagé), ou X (exclusif), escalade du verrouillage n'est pas le problème. En particulier, si le verrou TAB est un verrou intention (comme un mode de verrouillage D'EST, IU ou IX), il n'est pas le résultat de la promotion de verrous. Si vos problèmes de blocage sont non imputable à escalade du verrouillage, consultez l'article Q224453 pour la résolution des étapes.

Comment faire pour empêcher au verrouillage

Le moyen plus simple et plus sûr pour empêcher la promotion de verrous consiste à conserver transactions courte et permet de réduire l'encombrement de verrouillage de requêtes coûteuses et par les seuils de promotion de verrouillage sont dépassés pas. Il existe plusieurs manières pour obtenir cet objectif, dont sont répertoriés :
  • Rompre des opérations par lots de Grand dans plusieurs opérations plus petites. Par exemple, supposons que vous avez exécuté la requête suivante supprimer les anciens enregistrements plusieurs milliers à partir d'une table d'audit, et vous avez trouvé une promotion de verrous bloqué d'autres utilisateurs qu'il a déclenchés :
    DELETE FROM LogMessages WHERE LogDate < '2/1/2002'						
    par supprimer ces enregistrements quelques centaines à la fois, vous pouvez considérablement réduire le nombre de verrous qui s'accumulent par transaction et empêcher l'escalade du verrouillage. Par exemple :
    SET ROWCOUNT 500
    delete_more:
         DELETE FROM LogMessages WHERE LogDate < '2/1/2002'
    IF @@ROWCOUNT > 0 GOTO delete_more
    SET ROWCOUNT 0
  • Réduire encombrement de verrouillage de la requête en Création la requête aussi efficace que possible. Analyses de grandes ou grand nombre de recherches signet peut augmenter le risque d'escalade du verrouillage ; en outre, il augmente le risque de blocages et généralement affecte les affecter simultanéité d'accès aux données et les performances. Une fois que vous trouvez la requête qui provoque l'escalade du verrouillage, cherche l'apparence des opportunités pour créer de nouveaux index ou pour ajouter des colonnes à un index existant pour supprimer les analyses index ou d'une table et maximiser l'efficacité des index. Envisagez de coller la requête dans une fenêtre de requête l'Analyseur de requêtes permet d'effectuer une analyse automatique d'index sur celui-ci. Pour cela, dans le menu requête , cliquez sur Assistant Paramétrage d'index dans SQL Server 2000, ou cliquez sur effectuer une analyse de index dans SQL Server 7.0.

    Un objectif de cette optimisation est faire index cherche renvoyer aussi peu de lignes que possible pour réduire le coût de recherches signet (optimiser la sélectivité de l'index de la requête spécifique). Si SQL Server estime qu'un opérateur logique recherche de signet peut-être renvoyer autant de lignes, il peut utiliser une récupération pour effectuer la recherche de signet. Si SQL Server utilise de récupération pour une recherche de signet, il doit Augmentez le niveau d'isolation transaction d'une partie de la requête en lecture répétable pour une partie de la requête. Cela signifie que ce qui peut ressembler à une instruction SELECT à un niveau d'isolation read-committed peut acquérir plusieurs milliers de clé verrous (sur les l'index cluster et un index non cluster), qui peuvent provoquer une telle requête à dépasser les seuils de promotion de verrou. Ceci est particulièrement important si vous constatez que le verrou escalated est un verrou de table partagée, qui, cependant pas généralement apparaît au niveau d'isolation read-committed par défaut. Si une clause de recherche de signet avec PRÉ-récupération provoque la promotion, envisagez d'ajouter des colonnes supplémentaires à l'index non cluster qui apparaît dans l'index de recherche ou de l'analyse d'index opérateur logique ci-dessous l'opérateur logique recherche de signet dans le plan la requête. Il peut être possible de créer un index de couverture (un index qui inclut toutes les colonnes dans une table qui ont été utilisés dans la requête), ou au moins un index qui couvre les colonnes qui ont été utilisés pour les critères de jointure ou dans la clause WHERE Si incluez tous les éléments dans la liste de sélection de colonnes est peu commode.

    Une jointure de boucle imbriqués peut également utiliser pré récupération, et ainsi, le même comportement de verrouillage.

    Pour plus d'informations, cliquez sur le numéro ci-dessous pour afficher l'article correspondant dans la Base de connaissances Microsoft :
    260652 Jointure de boucle imbriquée qui utilise un « recherche de signet.. .WITH PRÉ-récupération » peut contenir plus de verrous
  • Promotion de verrous ne peut pas se produire si un SPID différent actuellement maintient un verrou de table incompatible. Promotion de verrous toujours réaffectent pour un verrou de table et jamais verrous de page. En outre, si un verrou promotion échoue, car un autre SPID MAINTIENT d'un verrou incompatible TAB, la requête qui a tenté d'escalade n'empêche pas en attendant un verrou de tabulation. Au lieu de cela, il continue à acquérir des verrous à son niveau d'origine, plus granulaire (ligne, clé ou page), tente d'effectuer régulièrement escalade supplémentaire. Par conséquent, une méthode pour empêcher l'escalade de verrou sur une table spécifique consiste à acquérir et pour y placer un verrou sur une autre connexion qui est incompatible avec le type de verrou escalated. Un verrou IX (intention exclusive) au niveau table ne verrouille pas les lignes ou des pages, mais il est toujours pas compatible avec un « S » escalated (partagé) ou X () TAB exclusif. Par exemple, supposons que vous devez exécuter un travail par lots qui modifie un grand nombre de lignes dans la table mytable et qui a provoqué le blocage qui se produit because of escalade du verrouillage. Si ce travail se termine toujours en moins d'une heure, vous pouvez créer un travail Transact-SQL qui contient le code suivant et planifier le nouveau travail pour démarrer plusieurs minutes avant heure de début le travail par lots :
    BEGIN TRAN
    SELECT * FROM mytable (UPDLOCK, HOLDLOCK) WHERE 1=0
    WAITFOR DELAY '1:00:00'
    COMMIT TRAN				
    cette requête acquiert et détient un verrou IX de mytable pendant une heure, ce qui empêche la promotion verrouillage de la table pendant cette période. Ce lot ne modifier les données ni bloquer autres requêtes (sauf si l'autre requête force un verrou de table avec le conseil TABLOCK ou si un administrateur a désactivé verrous de page ou une ligne en utilisant un sp_indexoption procédure stockée).
En outre, vous pouvez désactiver escalade du verrouillage en activant l'indicateur de suivi 1211. Toutefois, cet indicateur de suivi désactive tous les escalade verrouillage globalement dans l'instance de SQL Server. Escalade du verrouillage joue un rôle très utile dans SQL Server à optimiser l'efficacité des requêtes qui sinon ralenti vers le bas par la surcharge d'acquisition et au relâchement plusieurs milliers de verrous. Verrouiller escalade également permet de réduire la mémoire nécessaire pour effectuer le suivi des verrous. La mémoire que SQL Server peut allouer dynamique pour les structures verrou est limitée, de sorte que si vous désactivez escalade du verrouillage et de la mémoire verrouillage augmente assez grande, tentatives à allouer verrous supplémentaires pour une requête peuvent échouer et l'erreur suivante se produit :

Erreur : 1204, gravité : 19, état: 1
Le serveur SQL ne peut pas obtenir une ressource de verrouillage pour l'instant. Réexécutez le relevé de compte lorsqu'il y a moins utilisateurs actifs ou demandez à l'administrateur système pour vérifier la configuration du verrouillage et de la mémoire SQL Server.
note Lorsqu'une erreur « 1204 » se produit, il arrête le traitement de l'instruction en cours et entraîne une restauration de la transaction active. La restauration lui-même peut empêcher les utilisateurs ou entraîner un temps de récupération de base de données long si vous redémarrez le service SQL Server.

L'utilisation d'un indicateur de verrouillage comme verrouiller la ligne modifie uniquement le plan de verrou initiale. Indicateurs de verrou n'empêchent pas escalade du verrouillage.

Les autres méthodes d'empêcher l'escalade du verrouillage décrits plus haut dans cet article sont meilleures options que l'activation de l'indicateur de trace. En outre, les autres méthodes entraîner généralement de meilleures performances pour la requête de désactivation escalade verrouillage de l'instance de toute. Microsoft vous recommande d'activer cet indicateur de suivi uniquement réduire le blocage grave est provoqué par l'escalade de verrouillage tout en d'autres options, telles que les nous l'avons expliqué précédemment dans cet article est faisant. Pour activer un indicateur de suivi afin qu'il est activée chaque fois que SQL Server est démarré, ajouter en tant que paramètre démarrage du serveur.

Pour ajouter un paramètre de démarrage de serveur, cliquez avec le bouton droit sur le serveur dans SQL Enterprise Manager, cliquez sur Propriétés , puis sous l'onglet Général , cliquez sur Paramètres de démarrage et puis ajoutez le paramètre suivant (exactement comme indiqué) :
-T1211
Vous devez ainsi le service SQL Server pour un nouveau paramètre de démarrage soient prises en compte. Si vous exécutez la requête suivante dans l'Analyseur de requêtes l'indicateur de trace prend effet immédiatement :
DBCC TRACEON (1211, -1)				
Toutefois, si vous n'ajoutez pas le -T1211 paramètre de démarrage, l'effet d'une commande traceon est perdue lorsque le service SQL Server est succèdent. L'activation de l'indicateur de trace empêche toute Indexation verrou futures, mais il ne pas les Indexation verrou qui s'est déjà produits dans une transaction active.

Propriétés

Numéro d'article: 323630 - Dernière mise à jour: jeudi 22 février 2007 - Version: 10.3
Les informations contenues dans cet article s'appliquent au(x) produit(s) suivant(s):
  • Microsoft SQL Server 2000 Standard
  • Microsoft SQL Server 7.0 Standard
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Express Edition
  • Microsoft SQL Server 2005 Workgroup Edition
Mots-clés : 
kbmt kbinfo KB323630 KbMtfr
Traduction automatique
IMPORTANT : Cet article est issu du système de traduction automatique mis au point par Microsoft (http://support.microsoft.com/gp/mtdetails). Un certain nombre d?articles obtenus par traduction automatique sont en effet mis à votre disposition en complément des articles traduits en langue française par des traducteurs professionnels. Cela vous permet d?avoir accès, dans votre propre langue, à l?ensemble des articles de la base de connaissances rédigés originellement en langue anglaise. Les articles traduits automatiquement ne sont pas toujours parfaits et peuvent comporter des erreurs de vocabulaire, de syntaxe ou de grammaire (probablement semblables aux erreurs que ferait une personne étrangère s?exprimant dans votre langue !). Néanmoins, mis à part ces imperfections, ces articles devraient suffire à vous orienter et à vous aider à résoudre votre problème. Microsoft s?efforce aussi continuellement de faire évoluer son système de traduction automatique.
La version anglaise de cet article est la suivante: 323630
L'INFORMATION CONTENUE DANS CE DOCUMENT EST FOURNIE PAR MICROSOFT SANS GARANTIE D'AUCUNE SORTE, EXPLICITE OU IMPLICITE. L'UTILISATEUR ASSUME LE RISQUE DE L'UTILISATION DU CONTENU DE CE DOCUMENT. CE DOCUMENT NE PEUT ETRE REVENDU OU CEDE EN ECHANGE D'UN QUELCONQUE PROFIT.

Envoyer des commentaires

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com