Photo de James Sutton sur Unsplash
remarque: le problème décrit ci-dessous s’applique à toutes les requêtes SELECT, pas seulement à celles ornées d’indices NOLOCK. Le fait qu’il s’applique aux requêtes NOLOCK a été une énorme surprise pour moi, d’où le titre.
beaucoup de gens n’aiment pas NOLOCK (c’est-à-dire le niveau d’isolement non validé en lecture) car il peut renvoyer des données inexactes., J’ai vu beaucoup d’arguments avertissant les développeurs de récupérer des lectures non validées en raison de la façon dont ils peuvent renvoyer des données sales, des lectures fantômes et des lectures non répétables .
je connais tous ces problèmes ci-dessus, mais il y a un problème dont je n’ai jamais entendu parler jusqu’à récemment: NOLOCK peut bloquer l’exécution d’autres requêtes.
regardez la vidéo de cette semaine sur YouTube
prenons du recul et comprenons pourquoi J’ai si souvent utilisé NOLOCK dans le passé. Une instance assez typique lorsque j’utilise NOLOCK est lorsque je veux laisser une requête s’exécuter pendant la nuit pour renvoyer un grand ensemble de données., Je suis d’accord avec certaines incohérences dans les données (de lectures sales,etc…). Ma principale préoccupation est que je ne veux pas que la requête longue durée gêne les autres processus.
j’ai toujours pensé que NOLOCK était une solution parfaite pour ce scénario car il ne verrouille jamais les données qu’il lit – les résultats ne sont peut-être pas parfaits, mais au moins la requête n’aura d’impact négatif sur aucun autre processus sur le serveur.
c’est là que ma compréhension de NOLOCK était fausse: bien que NOLOCK ne verrouille pas les données au niveau des lignes, il supprimera un verrou de stabilité de schéma.,
un verrou schema s tability (Sch-s) empêche la structure d’une table de changer pendant l’exécution de la requête. Toutes les instructions SELECT, y compris celles du niveau d’isolation read uncommitted/NOLOCK, suppriment un verrou Sch-s. Cela a du sens car nous ne voudrions pas commencer à lire les données d’une table, puis faire modifier la structure des colonnes à mi-chemin de la récupération des données.
cependant, cela signifie également que certaines opérations peuvent être bloquées par un verrou Sch-s. Par exemple, toute commande demandant un verrou de modification de schéma (SCH-M) est bloquée dans ce scénario.,
quelles commandes demandent des verrous Sch-m?
des choses comme une reconstruction d’index ou une table sp_recompile. Ce sont les types de commandes exécutées dans mes travaux de maintenance nocturnes que j’essayais d’éviter de blesser en utilisant NOLOCK en premier lieu!
pour réitérer, je pensais que l’utilisation de l’indice NOLOCK était un excellent moyen d’empêcher le blocage lors de requêtes longues., Cependant, il s’avère que mes requêtes NOLOCK bloquaient en fait mes travaux d’index nocturne (toutes les requêtes SELECT bloquent dans cet exemple, mais je trouve que le NOLOCK est particulièrement TROMPEUR), Ce qui a également bloqué d’autres instructions SELECT!
jetons un coup d’œil à cela en action. Ici, j’ai une requête qui crée une base de données, une table, puis exécute une requête longue avec NOLOCK:
maintenant, pendant que ce milliard de lignes lues se produit, nous pouvons vérifier que la requête a sorti un verrou Sch-s en regardant sys.,dm_tran_locks:
SELECT *FROM sys.dm_tran_locksWHERE resource_type = 'OBJECT'
Alors que c’est en cours d’exécution, si nous essayons de reconstruire un index, que la reconstruction est bloqué (a ATTENDRE):
USE GOALTER INDEX IX_Id ON dbo.Test REBUILD
Notre reconstruction d’index de requête restera bloquée jusqu’à ce que notre milliard de lignes NOLOCK SÉLECTIONNEZ requête se termine en cours d’exécution (ou est tué). Cela signifie que la requête que j’avais l’intention d’être complètement discrète bloque maintenant l’exécution de mon travail de maintenance d’index nocturne.,
pire encore, Toutes les autres requêtes qui tentent de s’exécuter après la requête de reconstruction (ou toutes les autres commandes qui demandent un verrou Sch-M) vont également être bloquées! Si j’essaie de lancer un simple COUNT(*) de la requête:
USE GOSELECT COUNT(*) FROM dbo.Test
Bloqué! Cela signifie que non seulement ma requête NOLOCK initiale fait attendre mes travaux de maintenance de reconstruction d’index, mais le verrou Sch-M placé par le travail de maintenance de reconstruction provoque le blocage de toutes les requêtes ultérieures sur cette table et leur obligation d’attendre également., Je viens de faire dérailler la rapidité de mon travail de maintenance et des requêtes ultérieures avec une déclaration de blocage nolock!
Solutions
malheureusement, c’est un problème difficile et il n’y a pas de remède unique.
Solution #1: N’exécutez pas de requêtes longues
je pourrais éviter d’exécuter de longues requêtes la nuit lorsqu’elles pourraient se heurter à mes travaux de maintenance d’index., Cela empêcherait ces tâches de maintenance d’index et les requêtes suivantes d’être retardées, mais cela signifie que ma requête initiale de sélection d’un milliard de lignes devrait alors s’exécuter plus tôt, ce qui aurait un impact négatif sur les performances du serveur à une heure potentiellement plus chargée de la journée.,
Solution # 2: Utilisez WAIT_AT_LOW_PRIORITY
à partir de 2014, je pourrais faire une reconstruction d’index en ligne avec le jeu D’options WAIT_AT_LOW_PRIORITY:
ALTER INDEX IX_Id ON dbo.Test REBUILD WITH (ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 1 MINUTES , ABORT_AFTER_WAIT = BLOCKERS)))
cette requête donne essentiellement toutes les requêtes de sélection bloquantes en cours d’exécution 1 minute pour terminer l’exécution. Alternativement, nous aurions également pu définir ABORT_AFTER_WAIT = SELF et la requête de reconstruction se tuerait, permettant à la ligne NOLOCK billion SELECT de terminer l’exécution et n’empêchant aucune autre requête de s’exécuter.,
Ce n’est pas une excellente solution car cela signifie que la requête longue est tuée ou que la reconstruction de l’index est tuée.
Solution #3: reconstruire si aucun Sch-s, réorganiser sinon
Une solution programmatique peut être écrite qui tente de reconstruire l’index, mais retombe pour réorganiser si elle sait qu’elle devra attendre un verrou Sch-M.
j’ai créé la plaque de chaudière ci-dessous comme point de départ, mais le ciel est la limite avec ce que vous pouvez en faire (par exemple, créez une boucle WHILE pour vérifier le verrouillage toutes les X secondes, créez un délai d’attente pour le moment où le script doit cesser d’essayer de reconstruire et simplement réorganiser à la place, etc…)
Cette solution est ma préférée car:
- les requêtes Ad hoc de longue durée ne sont pas tuées (tout ce temps passé à traiter ne se perd pas)
- Les autres requêtes select ne sont pas bloquées par la tentative de verrouillage Sch-M par REBUILD
- la maintenance de L’Index se produit toujours, même
