Cause d'un processus victime d'une impasse

Question

J'ai un processus avec un Select qui prend beaucoup de temps pour se terminer, de l'ordre de 5 à 10 minutes.
Je suis actuellement ne pas utiliser NOLOCK en tant qu'indication pour le moteur de base de données MS SQL.
Parallèlement, un autre processus effectue des mises à jour et des insertions dans la même base de données et les mêmes tables.
Le premier processus a démarré et s'est récemment terminé prématurément par un message

SQLEXCEPTION : La transaction était bloquée sur les ressources de verrouillage avec un autre processus et a été choisie comme victime du blocage.

Ce premier processus s'exécute sur d'autres sites dans des conditions identiques, mais avec des bases de données plus petites et, par conséquent, l'instruction select en question prend beaucoup moins de temps (de l'ordre de 30 secondes environ). Dans ces autres sites, je n'obtiens pas le message de blocage. Je n'ai pas non plus reçu ce message sur le site qui rencontre le problème au départ, mais je suppose qu'au fur et à mesure que la base de données s'est développée, j'ai dû franchir un certain seuil. Voici mes questions :

1. Le temps nécessaire à l'exécution d'une transaction pourrait-il rendre le processus associé plus susceptible d'être signalé comme victime d'une impasse.
2. Si j'exécute la sélection avec un indice NOLOCK, le problème sera-t-il résolu ?
3. Je soupçonne qu'un champ de type datetime vérifié dans le cadre de la clause WHERE de l'instruction de sélection est à l'origine de la lenteur de la recherche. Puis-je créer un index basé sur ce champ ? Est-ce conseillé ?

Answer 1

Q1 : Le temps nécessaire à l'exécution d'une transaction peut-il faire en sorte que le processus associé soit plus susceptible d'être signalé comme victime d'une impasse ?

Non. Le SELECT est la victime parce qu'il n'avait que des données en lecture, et la transaction a donc un coût moins élevé Il est donc choisi comme victime :

Par défaut, le fichier Databas session exécutant la transaction qui est le moins coûteux à annuler . L'utilisateur peut également spécifier la priorité des sessions en cas de dans une situation de blocage en utilisant l'option SET DEADLOCK_PRIORITY s DEADLOCK_PRIORITY peut avoir la valeur LOW, NORMAL ou HIGH, ou encore peut prendre la valeur d'un entier compris entre -10 et 10.

Q2. Si j'exécute la sélection avec un indice NOLOCK, le problème sera-t-il résolu ?

Non. Pour plusieurs raisons :

• vous devez d'abord essayer d'éliminer l'impasse de manière appropriée, en recherchant la cause fondamentale.
• les "dirty reads" sont lectures incohérentes .
• la bonne façon de spécifier les lectures non conformes est d'utiliser l'option niveaux d'isolation des transactions
• il existe une bien meilleure solution : lire l'instantané engagé .

Q3. Je soupçonne qu'un champ de type datetime vérifié dans le cadre de la clause WHERE de l'instruction de sélection est à l'origine de la lenteur de la recherche. Puis-je créer un index basé sur ce champ ? Est-ce conseillé ?

Probablement. La cause du blocage est très probablement une base de données mal indexée. Les requêtes de 10 minutes sont acceptables dans des conditions si étroites que je suis sûr à 100 % que votre cas est le suivant no acceptable.

Avec 99% de confiance, je déclare que votre blocage est dû à un scan de table important qui entre en conflit avec les mises à jour. Commencez par capturer le graphique de l'impasse pour en analyser la cause. Vous devrez très probablement optimiser le schéma de votre base de données. Avant de procéder à toute modification, lisez cette rubrique Conception d'index et les sous-articles.

Answer 2

Voici comment ce problème de blocage s'est produit et comment il a été résolu. Il s'agit d'une base de données relativement active, avec 130 000 transactions quotidiennes. Les index des tables de cette base de données étaient à l'origine regroupés. Le client nous a demandé de rendre les index non clusterisés. Dès que nous l'avons fait, le blocage a commencé. Lorsque nous avons rétabli les index en tant que clusters, le blocage a cessé.

Answer 3

Les réponses données ici valent la peine d'être essayées, mais vous devriez également revoir votre code. En particulier, lisez la réponse de Polyfun ici : Comment se débarrasser d'un blocage dans une application SQL Server 2005 et C# ?

Il explique le problème de la concurrence et comment l'utilisation de "with (updlock)" dans vos requêtes peut corriger votre situation de blocage - en fonction de ce que fait exactement votre code. Si votre code suit ce modèle, il s'agit probablement d'une meilleure solution, avant d'avoir recours aux dirty reads, etc.

Answer 4

Bien que @ Remus Rusanu est déjà une excellente réponse, au cas où l'on souhaiterait avoir un meilleur aperçu de ce qui suit Blocage du serveur SQL les causes et les stratégies de dépistage, je vous suggère de lire Brad McGehee 's Comment repérer les blocages à l'aide de SQL Server 2005 Profiler