Locks incompréhensibles sur des SELECT

Version imprimable

Bonjour,

J'ai un client qui me sollicite car il a depuis une semaine maintenant des blocages d'une application qu'il ne s'explique pas.

L'application en question est une application Web, donc pise à part quelques traitements particuliers, aucun verrou ne reste actif plus de quelques dixièmes de secondes (genre on n'a pas besoin que Ginette sorte de la fiche article pour pouvoir passer une commande dessus).

Il n'y a eu aucune modification ni de code, ni de paramétrage, ni de process depuis des mois.

C'est la première fois que ce genre de locks semble se produire.

Connaissant l'application en question, je sais qu'il y a, parfois, des traitements qui font des hold lock sur des requêtes de type SELECT, le temps d'effectuer des opérations en rapport avec le jeu de données renvoyé. Ca a déjà causé des problèmes chez un autre client.
=> Je me suis donc naturellement orienté vers ce point. Sans succès. Visiblement il y a de tels traitements chez ce client, mais ces derniers travaillent sur des tables vides, donc leur exécution est instantanée. On voit dans les logs qu'à chaque fois ça se termine immédiatement.

En analysant les logs applicatifs, pas moyen de déterminer un traitement en particulier : deux plantages coups sur coups ont fait ressortir l'utilisation d'un module en particulier sur une fiche client en particulier. Mais depuis, de nouveaux blocages se sont produits et aucune trace ni du client, ni du module suspecté.

Côté SQL Server, on a lancé une requête pour déterminer la liste des traitements bloqués.

Et là je n'arrive pas à m'expliquer le problème.

Vendredi, nous avons analysé une première liste, où la racine d'une chaîne de blocage de plusieurs requête partait d'un simple SELECT. Pas de présente du moindre HINT de verrou dans la requête. Exécutée manuellement après coup, la requête dure quelques centièmes de seconde. Il y a quelques amélioration d'index suggérées par SSMS, mais rien de significatif.
=> Premièrement, comment un SELECT, même pas très rapide, peut-il être à l'origine de blocages d'autres SELECT ?

Ce matin, nouveau blocage, nouveaux fichiers, nouvelle liste de requête bloquées.

Sur 75 lignes il y a 73 SELECT (sans jamais de hint de lock), un "FETCH API_CURSOR" sans détails, et un "INSERT".

Le INSERT n'est à l'origine d'aucun LOCK et travaille sur une table hors application. Aucune autre requête dans la liste de tente d'accéder à cette table.
Le FETCH API_CURSOR n'est à l'origine aucun lock non plus. Est-ce possible d'avoir une idée de la requête correspondant au curseur ?

Sur les 73 SELECT, 67 ne sont bloqués par rien... Comment un SELECT peut-il se retrouvé locké... par rien ?
Le FETCH API_CURSOR est locké par un SELECT.

Je m'attarde sur le premier cas de lock.

Voici la première requête, numéro 121 du fichier :
Code:

1 2 (@P1 bigint)SELECT ID,F7000,F7007,F7046,F7058,F7060 FROM CRM_AU WHERE ID_KM_176=@P1 AND LosKZ=0 AND (((((F7046=1) AND (F7060=0)))) OR (((F7000 IN(11,12,23,24)))) OR (((F7007 IN(8,2,3,4,15,9,5))))) ORDER BY ID_KM_176,ID
Elle travaille exclusivement sur la table CRM_AU.

Elle est bloquée par la requête numéro 387 que voici :
Code:

1 2 (@P1 bigint)SELECT Kontakt,Status,ID,F7159 FROM CRM_MA WHERE ID_KM=@P1 AND LosKZ=0 AND (((((Kontakt=217) AND (Status=0)))) OR (((Kontakt=217))) OR (((Kontakt=217) AND (F7159=1)))) ORDER BY ID_KM,ID
Elle travaille exclusivement sur la table CRM_AU.
Cette requête n'est pas bloquée par une autre.
Il n'y a aucune clé étrangère entre les deux tables, ni directement, ni indirectement.

Première question : même si c'était des modifications de données, comment une requête dans une table peut-être bloquer une requête sur une autre table, du moment que ces deux tables sont indépendantes ?

Dans le fichier, toutes les requête sont marqués "suspended", sauf la 387 qui est "running".
Si je la lance, elle dure quelques centièmes de secondes. Dans le fichier, c'est l'avant dernière à avoir été lancée... Après les autres requêtes qu'elle soit-disant bloque...

Seconde question : comment une requête peut-être être bloquée par une autre requête qui n'est pas encore démarrée ?

Dans le log, j'ai quatre types de wait_type :

LCK_M_S : 7 requêtes
CXPACKET : 66 requêtes
SOS_SCHEDULER_YIELD : 1 requête, la 387 qui est la seule à tourner
ASYNC_NETWORK_IO : 1 requête, c'est l'INSERT, qui vient d'un traitement lancé depuis un autre serveur

Pour CXPACKET, je trouve ça :

Citation:

Producer Consumer Issue under Parallelism, where Consumer threads report CXPACKET wait type while waiting for data from Producer threads. This is something which is non-actionable because this happen because of parallelism.
Excessive Parallelism for small Queries which can be control by changing MAXDOP and /or Cost Threshold for Parallelism values. This is something which is actionable that can be control by changing these values.
Uneven task distribution between parallel threads. This situation may occur when some of the parallel threads will complete assigned task prior to other tasks. These threads will report CXPACKET wait type until other threads will complete assigned task as well. This is something which generally occurs because of outdated statistics. We can reduce this situation by updating statistics. This is again something which is actionable.

Je n'ai pas vérifié le MAXDOP (pas d'accès au serveur) mais à mon avis il est à sa valeur par défaut. Je doute qu'il y ait plus de 4 ou 6 cœurs sur le serveur : on est sur une "petite" application et une "petite base" (entre 10 et 20 Go).
Pour les statistiques, il y a un plan de maintenance qui les recalcule tous les week-end. Donc lundi 11h je doute qu'elles soient déjà pourries (ou alors le plan de maintenance est totalement bidon).

Pour LCK_M_S, ça semble être le lock classique qui se produit lorsqu'on tente d'accéder à une ligne verrouillée par un verrou quelconque.

Pour SOS_SCHEDULER_YIELD, j'ai l'impression que ça indique que le serveur BDD est soit surchargé, soit qu'il tente d'utiliser des CPU qu'il n'a pas le droit d'utiliser (sur une édition Express par exemple).
Là on doit être sur une standard, et comme je disais, y'a pas 32 CPU... tout au plus 4 ou 6. On doit pas dépasser la limite.

Qu'est-ce qui à votre avis pourrait poser ce genre de comportement ?

Autre question : j'ai remarqué que la croissante automatique de la base était configurée... à 1 Mo ! J'ai donc immédiatement conseillé de corriger ça au plus vite. Cependant, si c'était ça la source du problème on serait plutôt bloqué par des wait relatifs aux IO, non ? Quel genre de wait type se déclenche quand des lignes sont en cours de réorganisation dans base par manque de place dans une page ?

12/12/2022, 11h13
SQLpro

La problématique des ressources peut être la cause de blocages sans visibilité de verrous logique. Il faut donc veiller :
1) à n'avoir aucune croissance pendant la production sur les fichiers de données et surtout du JT => les dimensionner très large (pour 3 à 5 ans de données)
2) même topo pour la tempdb surtout pour les fichiers de données.

Si VM, erreur fréquente :
1) mémoire en balooning
2) coeurs flottant
Faire de la réservation sur l'intégralité des ressources pour ces deux paramètres.
Voir le guide de config VMWare / SQL Server (82 pages...)
https://www.vmware.com/content/dam/d...PubCID=2470763

Au niveau instance :
régler le parallélisme =
1) max dop à 1/4 - 1 si plus de 16 coeurs, sinon 1/2 -1
2) cost treshold for parallelims => entre 12 et 100 (12, 25, 50 ou 100 pour petite, moyenne, grosse, très grosse base)
3) optimize for adhoc workload à 1

S'assurer que les derniers SP ou CU ont été passés

A +
12/12/2022, 11h46
StringBuilder

Merci SQLpro pour ta réponse complète.

Je vais faire remonter ces points :ccool:
13/12/2022, 17h30
StringBuilder

Bonjour,

Nous avons commencé à appliquer un certain nombre de recommandations et vérifications.

Pour le moment sans succès : l'application à figé 4 fois en une heure en début d'après-midi.

Nous commençons à avoir une piste (cas qui plante systématiquement) sans pour le moment que nous ne comprenions quoi que ce soit : on tente de modifier une donnée, et dans les requête bloquées y'a que des SELECT, aucun UPDATE... Très bizarre...
Je dois continuer à creuser pour identifier tout le cheminement applicatif lors de cette action.

En revanche, j'ai une question : le DBA a modifié la taille des bases et le taux d'accroissement des fichiers.
Cependant, si la base est très fragmentée (185 Go actuellement avec un accroissement à 1 Mo, je vous laisse imaginer le désastre...) comment retrouver une base avec des fichiers sans fragments ?
Ils sont sur une baie de disques SSD. Est-ce que la fragmentation peut avoir un impact réellement perceptible ?
13/12/2022, 18h10
Waldar

Citation:

Envoyé par StringBuilder

Nous commençons à avoir une piste (cas qui plante systématiquement) sans pour le moment que nous ne comprenions quoi que ce soit : on tente de modifier une donnée, et dans les requête bloquées y'a que des SELECT, aucun UPDATE... Très bizarre...

Le SELECT pose un verrou avec le mode de transaction par défaut de SQL-Server.
Ouvre une session, fais begin transaction select * from MaTable, ouvre une autre session, même chose, tant que la transaction de la session 1 n'est pas finie la session 2 ne renvoie rien.
La solution "sale mais qui marche" select * from MaTable (nolock);
13/12/2022, 18h15
SQLpro

Citation:

Envoyé par Waldar

...
La solution "sale mais qui marche" select * from MaTable (nolock);

ou bien passer en verrouillage optimiste... SQL Server est le seul SGBDR à faire les deux !

A +
14/12/2022, 13h40
Zabriskir

Citation:

Envoyé par Waldar

Le SELECT pose un verrou avec le mode de transaction par défaut de SQL-Server.
Ouvre une session, fais begin transaction select * from MaTable, ouvre une autre session, même chose, tant que la transaction de la session 1 n'est pas finie la session 2 ne renvoie rien.

Hello,
Vous êtes sûr de ce que vous avancez 🤔 ?
Le select pose un shared lock qui ne bloque pas un autre select (en mode d'isolation par défaut).
@+

On n'est clairement pas dans des cas de lock explicables par le langage SQL lui-même (ou alors j'ai raté ma vocation de bonne soeur jésuite).

En effet, mettons deux tables :
Code:

1 2 3 4 5 6 7 8 9 10 create table TableA ( id int identity primary key, label varchar(20) ); create table TableB ( id int identity primary key, label varchar(20) );
Absolument aucun rapport entre les deux tables : pas de clés externes, pas de trigger, pas de colonne calculée, même pas de vue qui tape sur les deux, rien, nada.

Pourtant, on voit dans la liste des requêtes en attente ce genre de choses :
Code:

1 2 3 4 id blocked by started wait type sql statement 1 2 14:00:00 CXPACKET select label from tableA where id = 1 2 14:35:00 CXPACKET select label from tableB where id = 1
Comment un select sur tableB peut-il être locké par un select sur tableA dans ces conditions ?
D'autant que la plupart des requêtes qui sont bloquées sont des requêtes triviales du genre recherche sur une clé primaire ou au moins un index de deux ou trois colonnes max.

J'ai passé deux heures ce matin à tenter de trouver ce qui pouvait expliquer, indépendament des waits, pourquoi une modification d'une colonne sur une ligne pouvait tout faire partir en live.
=> J'ai trouvé un unique traitement qui doit générer deux SELECT relativement rapides (tapent tous deux sur un index, et normalement sur un faible volume de données : 10 lignes grand max) et mettent à jour une colonne dans une autre table. Rien de folichon. Lorsque je confronte la liste des wait, je n'ai pas l'impression que ce traitement soit en train de tourner au moment où tout fige. En même temps ça ne m'étonne pas, c'est censé être séquentiel... A noter que quand ça freeze, le UPDATE ne fait pas partie des requêtes bloquées.

La colonne mise à jour n'est pas indexée. On passe un int de 0 à 1, donc pas de changement de taille de la ligne dans le fichier de données (ceci dit, y'a un varbinary qui doit grossir un peu, mais vu qu'il n'est pas lisible, je ne saurais pas dire, dans tous les cas il fait moins de 1 Ko).

Bref, incompréhensible.

De leur côté, ils peuvent avoir trouvé un autre traitement qui se déclenche quand cette colonne change de valeur (tout du moins, quand elle devient égale à une autre, mais c'est bien le cas ici). Ils sont partis dessus tête baissée, mais vu que c'est une tâche planifiée, je ne vois pas comment elle pourrait se déclencher en même temps que la modification de la ligne...

Par contre, pour en revenir au CXPACKET, le BDA chez eux semble s'y intéresser. Il cherche à augmenter le coût de déclenchement du multithreading. Je suis moyennement convaincu... En effet, Ca me semble très étrange que SQL Server multi-thread un SELECT qui tape sur une unique ligne avec pour filtre la clé primaire. Ca doit cacher autrechose non ?

Et là, hier un sysadmin nous a partagé son écran pendant qu'il regardait ce qu'il se passait sur l'hyperviseur et les baies de disque. D'après lui, tout est en ordre.
Par contre j'ai vu un truc qui m'a choqué : certaines VM ont leur CPUs candencés à 30 MHz. Oui, 30 MHz... Même pas aussi rapide que le bon vieux 386 sur lequel j'ai découvert l'informatique !

Et là je suis pris d'un doute affreux (j'ai jamais eu de retour quand au memory ballooning ni la réservation des coeurs) : ne serions-nous pas dans un scénario où l'hyperviseur ralenti certains coeurs émulés pour pouvoir affecter plus de coeurs virtuels que de coeurs physiques ? Ne serions-nous pas dans un cas où VMWare estime qu'avec un coeur à 2.9 GHz et tous les autres à 30 MHz c'est bien suffisant ? Et que du coup dès qu'une requête tombe sur un coeur en mode années 80... ben ça fait tout tomber ? Car j'imagine que si un thread sur le coeur à 2,9 envoie des données sur un thread qui tourne sur un coeur à 30 MHz, c'est l'ensemble qui se retrouve à 30 MHz... Et SQL Server... ben il est pas prévu pour tourner là dessus... Même la version 6 je suis pas sûr qu'elle pouvait tourner à cette vitesse...

Suis-je totalement sur une mauvaise piste, ou si ça pourrait en effet expliquer tous nos malheurs ?

Autre piste : si un INSERT (ou UPDATE à la limite) nécessite une réorganisation des pages dans le fichier de données (avec ou sans accroissement) est-ce que la requête d'insertion ou de mise à jour serait visible dans la liste des requêtes en attente ?
Pour le moment je suis parti du principe que oui, mais on sait jamais... C'est tellement farfelu ce qu'on a sous les yeux...

En effet, vous parlez de transactions qui peuvent en bloquer certaines... et si le rapport des requêtes en attente est mal foutu, on pourrait avoir en effet ce genre de chose :
Code:

1 2 3 4 begin transaction; select id from tableA where id = 1; select id from tableB where id = 1;
Et pendant que la seconde requête tourne, on lance ceci :
Code:

1 2 3 begin transaction; select id from tableA where id = 1;
=> Est-ce qu'on verrait le dernier select bloqué par le second ? (cas qu'on a sous les yeux et qui nous semble inexplicable)
=> Après, je ne m'explique pas comment une requête bidon qui retourne parfois aucune ligne en tapant sur un index (qui ont été recalculés/statistiques reconstruites) peut prendre 35 minutes... (sauf si elle tourne à 30 MHz, et même là je suis pas sûr...)

Aussi, je suis certain que les requêtes de type consultation se font hors transaction : donc impossible d'avoir plusieurs SELECT dans une même transaction...

15/12/2022, 17h03
SQLpro

Citation:

Envoyé par StringBuilder

...
Et là je suis pris d'un doute affreux (j'ai jamais eu de retour quand au memory ballooning ni la réservation des coeurs) : ne serions-nous pas dans un scénario où l'hyperviseur ralenti certains coeurs émulés pour pouvoir affecter plus de coeurs virtuels que de coeurs physiques ? Ne serions-nous pas dans un cas où VMWare estime qu'avec un coeur à 2.9 GHz et tous les autres à 30 MHz c'est bien suffisant ? Et que du coup dès qu'une requête tombe sur un coeur en mode années 80... ben ça fait tout tomber ? Car j'imagine que si un thread sur le coeur à 2,9 envoie des données sur un thread qui tourne sur un coeur à 30 MHz, c'est l'ensemble qui se retrouve à 30 MHz... Et SQL Server... ben il est pas prévu pour tourner là dessus... Même la version 6 je suis pas sûr qu'elle pouvait tourner à cette vitesse...

Oui, oui, oui !Déjà dit dans mon post !!!

Citation:

Suis-je totalement sur une mauvaise piste, ou si ça pourrait en effet expliquer tous nos malheurs ?

Autre piste : si un INSERT (ou UPDATE à la limite) nécessite une réorganisation des pages dans le fichier de données (avec ou sans accroissement) est-ce que la requête d'insertion ou de mise à jour serait visible dans la liste des requêtes en attente ?

Les attentes c'est vraiment des attentes. Il passe son temps à attendre une ressources... Dans le cas du parallélisme (et non du multithreading) tous les processus attendent, sauf le dernier... Il est donc normal d'avoir un cumul d'attente élevé... Mais il est vrai que trop de // tue le //.

A +