Partager via

Résoudre les problèmes bloquants provoqués par l’escalade de verrous dans SQL Server

  • Article

Résumé

L’escalade de verrous est le processus de conversion de nombreux verrous affinés (tels que les verrous de ligne ou de page) en verrous de table. Microsoft SQL Server détermine dynamiquement quand effectuer l’escalade des verrous. Lorsqu’il prend cette décision, SQL Server considère le nombre de verrous conservés sur une analyse particulière, le nombre de verrous détenus par l’ensemble de la transaction et la mémoire utilisée pour les verrous dans l’ensemble du système. En règle générale, le comportement par défaut de SQL Server entraîne l’escalade des verrous uniquement aux moments où il améliore les performances ou lorsque vous devez réduire la mémoire excessive du verrou système à un niveau plus raisonnable. Toutefois, certaines conceptions d’application ou de requête peuvent déclencher l’escalade des verrous à un moment où cette action n’est pas souhaitable, et le verrou de table réaffecté peut bloquer d’autres utilisateurs. Cet article explique comment déterminer si l’escalade de verrous provoque un blocage et comment traiter l’escalade de verrous indésirable.

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

Déterminer si l’escalade de verrous provoque le blocage

L’escalade de verrous ne provoque pas la plupart des problèmes de blocage. Pour déterminer si l’escalade de verrous se produit à ou près du moment où vous rencontrez des problèmes de blocage, démarrez une session Événements étendus qui inclut l’événement lock_escalation . Si vous ne voyez lock_escalation aucun événement, l’escalade de verrous ne se produit pas sur votre serveur et les informations contenues dans cet article ne s’appliquent pas à votre situation.

Si l’escalade de verrous se produit, vérifiez que le verrou de table réaffecté bloque d’autres utilisateurs.

Pour plus d’informations sur la façon d’identifier le bloqueur principal et la ressource de verrouillage détenue par le bloqueur principal et qui bloquent d’autres ID de processus serveur (SPID), consultez INF : Comprendre et résoudre les problèmes de blocage sql Server.

Si le verrou qui bloque d’autres utilisateurs est autre qu’un verrou TAB (au niveau de la table) qui a un mode de verrouillage S (partagé) ou X (exclusif), l’escalade de verrous n’est pas le problème. En particulier, si le verrou TAB est un verrou d’intention (par exemple, un mode de verrouillage d’IS, D’IU ou IX), cela n’est pas dû à l’escalade de verrous. Si vos problèmes de blocage ne sont pas provoqués par l’escalade de verrous, consultez l’INF : Comprendre et résoudre les problèmes de blocage de SQL Server.

Empêcher l’escalade de verrous

La méthode la plus simple et la plus sûre pour empêcher l’escalade des verrous consiste à réduire l’encombrement des transactions et à réduire l’encombrement des requêtes coûteuses afin que les seuils d’escalade de verrous ne soient pas dépassés. Il existe plusieurs méthodes pour atteindre cet objectif, notamment les stratégies suivantes :

  • Divisez les opérations de traitement par lots volumineuses en plusieurs opérations plus petites. Par exemple, vous exécutez la requête suivante pour supprimer 100 000 anciens enregistrements d’une table d’audit, puis vous déterminez que la requête a provoqué une escalade de verrous qui a bloqué d’autres utilisateurs :

    DELETE FROM LogMessages WHERE LogDate < '20020102';

    En supprimant ces enregistrements quelques centaines à la fois, vous pouvez réduire considérablement le nombre de verrous qui s’accumulent par transaction. Cela empêche l’escalade des verrous. Par exemple, vous exécutez la requête suivante :

    DECLARE @done bit = 0;
WHILE (@done = 0)
BEGIN
    DELETE TOP(1000) FROM LogMessages WHERE LogDate < '20020102';
    IF @@rowcount < 1000 SET @done = 1;
END;

  • Réduisez l’empreinte de verrou de la requête en rendant la requête aussi efficace que possible. Les analyses volumineuses ou de nombreuses recherches de signets peuvent augmenter le risque d’escalade de verrous. En outre, ces blocages augmentent la probabilité d’interblocages et affectent négativement la concurrence et les performances. Après avoir identifié que la requête qui provoque l’escalade de verrous, recherchez les opportunités de créer de nouveaux index ou d’ajouter des colonnes à un index existant pour supprimer les analyses d’index ou de table et optimiser l’efficacité des recherches d’index. Passez en revue le plan d’exécution et créez potentiellement de nouveaux index non cluster pour améliorer les performances des requêtes. Pour plus d’informations, consultez le Guide d'architecture et de conception de l'index SQL Server.

    L’un des objectifs de cette optimisation est de faire en sorte que les recherches d’index retournent autant de lignes que possible pour réduire le coût des recherches de signet (optimiser la sélectivité de l’index pour la requête). Si SQL Server estime qu’un opérateur logique de recherche de signet retourne de nombreuses lignes, il peut utiliser une PREFETCH clause pour effectuer la recherche de signets. Si SQL Server utilise PREFETCH pour une recherche de signet, il doit augmenter le niveau d’isolation des transactions d’une partie de la requête en « lecture reproductible » pour une partie de la requête. Cela signifie que ce qui peut ressembler à une SELECT instruction à un niveau d’isolation « en lecture validée » peut acquérir plusieurs milliers de verrous de clé (sur l’index cluster et un index non cluster). Cela peut entraîner une telle requête à dépasser les seuils d’escalade de verrous. Cela est particulièrement important si vous constatez que le verrou réaffecté est un verrou de table partagé, bien que ceux-ci ne soient généralement pas vus au niveau d’isolation « en lecture validée » par défaut. Si une clause Bookmark Lookup WITH PREFETCH provoque l’escalade, envisagez d’ajouter des colonnes à l’index non cluster qui apparaît dans la recherche d’index ou l’opérateur logique Analyse d’index en dessous de l’opérateur logique Bookmark Lookup dans le plan de requête. Il peut être possible de créer un index de couverture (index qui inclut toutes les colonnes d’une table utilisée dans la requête), ou au moins un index qui couvre les colonnes utilisées pour les critères de jointure ou dans la clause WHERE s’il n’est pas pratique d’inclure tout dans la liste « sélectionner la colonne ».

    Une jointure de boucle imbriquée peut également utiliser PREFETCH, ce qui entraîne le même comportement de verrouillage.

  • L’escalade de verrous ne peut pas se produire si un autre SPID contient actuellement un verrou de table incompatible. L’escalade de verrous passe toujours à un verrou de table, et jamais à un verrou de page. En outre, si une tentative d’escalade de verrou échoue, car un autre SPID contient un verrou TAB incompatible, la requête qui a essayé l’escalade ne bloque pas lors de l’attente d’un verrou TAB. Elle continue plutôt d’acquérir des verrous à son niveau d’origine, plus précis (ligne, clé ou page), en effectuant régulièrement des tentatives d’escalade supplémentaires. Ainsi, une méthode pour empêcher l'escalade de verrous sur une table particulière consiste à acquérir et à conserver un verrou sur une autre connexion qui n'est pas compatible avec le type de verrou escaladé. Un verrou IX (exclusion intentionnelle) au niveau de la table ne verrouille aucune ligne ni aucune page, mais il n'est quand même pas compatible avec un verrou TAB S (partagé) ou X (exclusif) remonté. Par exemple, supposons que vous devez exécuter un travail de traitement par lots qui modifie de nombreuses lignes dans la table mytable et qui a provoqué le blocage en raison de l’escalade de verrous. 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 l’heure de début du travail par lots :

    BEGIN TRAN;
SELECT * FROM mytable WITH (UPDLOCK, HOLDLOCK) WHERE 1 = 0;
WAITFOR DELAY '1:00:00';
COMMIT TRAN;

    Cette requête acquiert et contient un verrou IX sur mytable pendant une heure. Cela empêche l’escalade des verrous sur la table pendant cette période. Ce lot ne modifie pas de données ni ne bloque d’autres requêtes (sauf si l’autre requête force un verrou de table à l’aide de l’indicateur TABLOCK ou si un administrateur a désactivé des verrous de page ou de lignes à l’aide de ALTER INDEX).

  • Éliminez l’escalade des verrous causée par l’absence de SARGabilité, un terme de base de données relationnelle utilisé pour décrire si une requête peut utiliser des index pour les prédicats et les colonnes de jointure. Pour plus d’informations sur SARGability, consultez Considérations relatives aux requêtes du Guide de conception. Par exemple, une requête assez simple qui ne semble pas demander de nombreuses lignes , ou peut-être une seule ligne, peut encore finir par analyser une table/un index entier. Cela peut se produire s’il existe une fonction ou un calcul dans le côté gauche d’une clause WHERE. Ces exemples qui n’ont pas de SARGabilité incluent des conversions implicites ou explicites de type de données, la fonction système ISNULL(), une fonction définie par l’utilisateur avec la colonne passée en tant que paramètre ou un calcul sur la colonne, comme WHERE CONVERT(INT, column1) = @a ou WHERE Column1*Column2 = 5. Dans ce cas, la requête ne peut pas RECHERCHER l’index existant, même s’il contient les colonnes appropriées, car toutes les valeurs de colonne doivent être récupérées en premier et transmises à la fonction. Cela entraîne une analyse de l’ensemble de la table ou de l’index et entraîne l’acquisition d’un grand nombre de verrous. Dans de telles circonstances, SQL Server peut atteindre le seuil d’escalade du nombre de verrous. La solution consiste à éviter d’utiliser des fonctions sur des colonnes dans la clause WHERE, ce qui garantit des conditions SARGables.

Désactiver l’escalade de verrous

Bien qu’il soit possible de désactiver l’escalade de verrous dans SQL Server, nous ne l’avons pas recommandé. Utilisez plutôt les stratégies de prévention décrites dans la section Empêcher l’escalade de verrous.

  • Niveau de la table : vous pouvez désactiver l’escalade des verrous au niveau de la table. Consultez l’article ALTER TABLE ... SET (LOCK_ESCALATION = DISABLE). Pour déterminer la table à cibler, examinez les requêtes T-SQL. Si cela n’est pas possible, utilisez des événements étendus, activez l’événement lock_escalation et examinez la colonne object_id . Vous pouvez également utiliser l’événement Lock :Escalation et examiner la colonne à l’aide ObjectID2 de SQL Profiler.
  • Niveau d’instance : vous pouvez désactiver l’escalade des verrous en activant l’un des indicateurs de trace 1211 ou 1224 ou les deux pour l’instance. Toutefois, ces indicateurs de trace désactivent toutes les escalades de verrous globalement dans l’instance de SQL Server. L’escalade de verrous sert un but utile dans SQL Server en optimisant l’efficacité des requêtes qui sont autrement ralenties par la surcharge d’acquisition et de libération de plusieurs milliers de verrous. Une escalade de verrous permet également de réduire la mémoire nécessaire pour effectuer le suivi des verrous. La mémoire que SQL Server peut allouer dynamiquement pour les structures de verrou est finie. Par conséquent, si vous désactivez l’escalade des verrous et que la mémoire de verrou augmente suffisamment, toute tentative d’allocation de verrous supplémentaires pour n’importe quelle requête peut échouer et générer l’entrée d’erreur suivante :

Erreur : 1204, Gravité : 19, État : 1
Sql Server ne peut pas obtenir une ressource LOCK pour l’instant. Réexécutez votre instruction lorsqu’il y a moins d’utilisateurs actifs ou demandez à l’administrateur système de vérifier la configuration du verrou et de la mémoire SQL Server.

Note

Lorsqu’une erreur 1204 se produit, elle arrête le traitement de l’instruction actuelle et provoque une restauration de la transaction active. La restauration elle-même peut bloquer les utilisateurs ou provoquer une longue durée de récupération de base de données si vous redémarrez le service SQL Server.

Vous pouvez ajouter ces indicateurs de trace (-T1211 ou -T1224) à l’aide de Gestionnaire de configuration SQL Server. Vous devez redémarrer le service SQL Server pour qu’un nouveau paramètre de démarrage prenne effet. Si vous exécutez le DBCC TRACEON (1211, -1) ou DBCC TRACEON (1224, -1) la requête, l’indicateur de trace prend effet immédiatement.
Toutefois, si vous n’ajoutez pas le paramètre de démarrage -T1211 ou -T1224, l’effet d’une DBCC TRACEON commande est perdu lorsque le service SQL Server est redémarré. L’activation de l’indicateur de trace empêche les futures escalades de verrous, mais elle n’inverse pas les escalades de verrous qui se sont déjà produites dans une transaction active.

Si vous utilisez un indicateur de verrou, tel que ROWLOCK, cela modifie uniquement le plan de verrouillage initial. Les indicateurs de verrouillage n’empêchent pas l’escalade des verrous.

Seuils d’escalade de verrous

L’escalade de verrous peut se produire dans l’une des conditions suivantes :

  • Seuil de mémoire atteint : un seuil de mémoire de 40 pour cent de la mémoire de verrou est atteint. Lorsque la mémoire de verrou dépasse 24 % du pool de mémoires tampons, une escalade de verrous peut être déclenchée. La mémoire de verrou est limitée à 60 % du pool de mémoires tampons visible. Le seuil d’escalade de verrous est défini à 40 % de la mémoire de verrou. Il s’agit de 40 % de 60 % du pool de mémoires tampons, ou de 24 %. Si la mémoire de verrou dépasse la limite de 60 % (cela est beaucoup plus probable si l’escalade de verrous est désactivée), toutes les tentatives d’allocation de verrous supplémentaires échouent et 1204 les erreurs sont générées.

  • Un seuil de verrou est atteint : une fois le seuil de mémoire vérifié, le nombre de verrous acquis sur la table ou l’index actuel est évalué. Si le nombre dépasse 5 000, une escalade de verrous est déclenchée.

Pour comprendre le seuil atteint, utilisez les événements étendus, activez l’événement lock_escalation et examinez les colonnes escalated_lock_count et escalation_cause . Vous pouvez également utiliser l’événement Lock :Escalation et examiner la EventSubClass valeur, où « 0 - LOCK_THRESHOLD » indique que l’instruction a dépassé le seuil de verrouillage, et « 1 - MEMORY_THRESHOLD » indique que l’instruction a dépassé le seuil de mémoire. Examinez également les colonnes et IntegerData2 les IntegerData colonnes.

Recommandations

Les méthodes décrites dans la section Empêcher l’escalade de verrous sont de meilleures options que de désactiver l’escalade au niveau de la table ou de l’instance. En outre, les méthodes préventives produisent généralement de meilleures performances pour la requête que la désactivation de l’escalade de verrous. Microsoft vous recommande d’activer cet indicateur de trace uniquement pour atténuer les blocages graves provoqués par l’escalade de verrous, tandis que d’autres options, telles que celles décrites dans cet article, sont examinées.

Voir aussi