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

Résumé

L’escalade de verrous est le processus de conversion de nombreux verrous affinés (tels que des 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 prend en compte le nombre de verrous qui sont 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ù elle améliore les performances ou lorsque vous devez réduire la mémoire de verrouillage système excessive à un niveau plus raisonnable. Toutefois, certaines conceptions d’application ou de requête peuvent déclencher une escalade de verrous à un moment où cette action n’est pas souhaitable et où le verrou de table escaladé peut bloquer d’autres utilisateurs. Cet article explique comment déterminer si l’escalade de verrous est à l’origine du blocage et comment gérer l’escalade de verrous indésirables.

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

Déterminer si l’escalade de verrous est à l’origine du 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 au moment où vous rencontrez des problèmes de blocage ou à proximité, démarrez une session d’événements étendus qui inclut l’événement lock_escalation . Si vous ne voyez aucun lock_escalation é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 remonté bloque d’autres utilisateurs.

Pour plus d’informations sur la façon d’identifier le bloqueur principal et la ressource de verrou qui est détenue par le bloqueur principal et qui bloque d’autres ID de processus serveur (SPID), consultez INF : Présentation et résolution des problèmes de blocage SQL Server.

Si le verrou qui bloque d’autres utilisateurs est autre chose 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 intentionnel (par exemple, un mode de verrouillage IS, IU ou IX), cela n’est pas dû à l’escalade de verrous. Si vos problèmes de blocage ne sont pas dus à l’escalade de verrous, consultez les étapes de résolution des problèmes de blocage inf : comprendre et résoudre SQL Server les problèmes de blocage.

Empêcher l’escalade de verrous

La méthode la plus simple et la plus sûre pour empêcher l’escalade de verrous consiste à maintenir des transactions courtes et à réduire l’encombrement des verrous 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 plus de 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êchera l’escalade de 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. Des analyses volumineuses ou de nombreuses recherches de signets peuvent augmenter le risque d’escalade de verrous. En outre, celles-ci augmentent le risque d’interblocages et affectent négativement l’accès concurrentiel 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 éventuellement de nouveaux index non cluster pour améliorer les performances des requêtes. Pour plus d’informations, consultez SQL Server Guide de conception et d’architecture d’index.

  L’un des objectifs de cette optimisation est de faire en sorte que les recherches d’index retournent le moins de lignes possible afin de réduire le coût des recherches de signets (optimiser la sélectivité de l’index pour la requête). Si SQL Server estime qu’un opérateur logique Bookmark Lookup 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 de transaction d’une partie de la requête à « lecture reproductible » pour une partie de la requête. Cela signifie que ce qui peut ressembler à une SELECT instruction au niveau d’isolation « read committed » peut acquérir plusieurs milliers de verrous de clé (sur l’index cluster et un index non cluster). Cela peut entraîner le dépassement des seuils d’escalade de verrous. Cela est particulièrement important si vous constatez que le verrou escaladé est un verrou de table partagé, bien qu’ils ne soient généralement pas vus au niveau d’isolation « read committed » par défaut. Si une clause Bookmark Lookup WITH PREFETCH est à l’origine de 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 sous 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 qui ont été utilisées 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 tous les éléments dans la liste « select column ».

  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 est toujours réaffecté à un verrou de table, et jamais à un verrou de page. En outre, si une tentative d’escalade de verrou échoue parce qu’un autre SPID contient un verrou TAB incompatible, la requête qui a tenté l’escalade ne se bloque pas en attendant un verrou TAB. Au lieu de cela, il continue d’acquérir des verrous à son niveau d’origine plus granulaire (ligne, clé ou page), effectuant régulièrement des tentatives d’escalade supplémentaires. Par conséquent, 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 remontée. Un verrou IX (intention exclusive) au niveau de la table ne verrouille aucune ligne ou page, mais il n’est toujours pas compatible avec un verrou TAB S (partagé) ou X (exclusif). Par exemple, supposons que vous devez exécuter un travail par lots qui modifie de nombreuses lignes dans la table mytable et qui a provoqué un 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 démarrage du nouveau travail plusieurs minutes avant l’heure de début du 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 conserve un verrou IX sur mytable pendant une heure. Cela empêche l’escalade de verrous sur la table pendant cette période. Ce lot ne modifie pas les données ou ne bloque pas 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é les verrous de page ou de ligne à l’aide de ALTER INDEX).

• Éliminez l’escalade de verrous causée par l’absence de SARGability, 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 la sargabilité, consultez Considérations relatives aux requêtes du Guide de conception. Par exemple, une requête assez simple qui ne semble pas demander beaucoup de lignes (ou peut-être une seule ligne) peut encore finir par analyser une table ou 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 ne sont pas sargables incluent les conversions de type de données implicites ou explicites, 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 si elle contient les colonnes appropriées, car toutes les valeurs de colonne doivent d’abord être récupérées et passées à la fonction. Cela conduit à une analyse de l’ensemble de la table ou de l’index et entraîne l’acquisition d’un grand nombre de verrous. Dans ce cas, SQL Server pouvez 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 des verrous dans SQL Server, nous ne le recommandons pas. 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 de verrous au niveau de la table. Voir ALTER TABLE ... SET (LOCK_ESCALATION = DISABLE) (en anglais). Pour déterminer la table à cibler, examinez les requêtes T-SQL. Si ce n’est pas possible, utilisez les é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 de ObjectID2 SQL Profiler.
• Niveau de l’instance : Vous pouvez désactiver l’escalade des verrous en activant les indicateurs de trace 1211 ou 1224, ou les deux pour le instance. Toutefois, ces indicateurs de trace désactivent toutes les escalades de verrous globalement dans le instance de SQL Server. L’escalade de verrous est utile dans SQL Server en optimisant l’efficacité des requêtes qui sont autrement ralenties par la surcharge liée à l’acquisition et à la libération de plusieurs milliers de verrous. L’escalade des verrous permet également de réduire la mémoire nécessaire pour effectuer le suivi des verrous. La mémoire que SQL Server pouvez allouer dynamiquement pour les structures de verrou est finie. Par conséquent, si vous désactivez l’escalade de verrous et que la mémoire de verrou devient suffisamment grande, 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
Le SQL Server ne peut pas obtenir de ressource LOCK pour l’instant. Réexécutez votre instruction lorsqu’il y a moins d’utilisateurs actifs ou demandez à l’administrateur système d’case activée la configuration du verrouillage SQL Server et de la mémoire.

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 la DBCC TRACEON (1211, -1) requête ou DBCC TRACEON (1224, -1) , l’indicateur de trace prend effet immédiatement.
Toutefois, si vous n’ajoutez pas -T1211 ou -T1224 comme paramètre de démarrage, 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 toute escalade de verrous à venir, 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 de verrous.

Seuils d’escalade de verrous

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

• Le seuil de mémoire est atteint : un seuil de mémoire de 40 % de la mémoire de verrouillage est atteint. Lorsque la mémoire de verrouillage dépasse 24 % du pool de mémoires tampons, une escalade de verrous peut être déclenchée. La mémoire de verrouillage est limitée à 60 % du pool de mémoires tampons visible. Le seuil d’escalade des verrous est défini à 40 % de la mémoire de verrouillage. Il s’agit de 40 % des 60 % du pool de mémoires tampons, ou de 24 %. Si la mémoire de verrouillage 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 des erreurs sont générées.

• Un seuil de verrouillage 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 IntegerData colonnes et IntegerData2 .

Recommandations

Les méthodes décrites dans la section Empêcher l’escalade de verrous sont de meilleures options que la désactivation de l’escalade au niveau de la table ou 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 en cours d’examen.

Voir aussi

• Comprendre et résoudre les problèmes de blocage de SQL Server
• Résoudre les problèmes de lenteur des requêtes sur SQL Server