SQL Server blocages entre select/update ou plusieurs sélectionne
L'ensemble de la documentation sur les blocages SQL Server parle du scénario dans lequel l'opération 1 serrures ressource puis les tentatives d'accès à des ressources de B et de fonctionnement 2 serrures ressource B et les tentatives d'accès à des ressources A.
Cependant, j'ai assez souvent voir des blocages entre une sélection et une mise à jour ou même entre plusieurs sélectionne dans certains de nos occupé applications. Je trouve que certains points de détail de la trace de blocage de sortie assez impénétrable, mais je voudrais vraiment comme à comprendre ce qui peut provoquer un blocage entre deux opérations. Sûrement, si une sélection a un verrou en lecture de la mise à jour doit juste attendre avant d'obtenir un verrou exclusif et vice-versa?
Ce qui se passe sur SQL Server 2005 n'est pas que je pense que cela fait une différence.
Vous devez vous connecter pour publier un commentaire.
Une fois, j'ai bookmarké un bon article sur les Advanced SQL Server verrouillage à SQL-Server-Performance.com. Cet article va au-delà de la classique de la situation de blocage que vous avez mentionné et peut vous donner un aperçu de votre problème.
Cela peut se produire en raison d'un select prend un lock-out, deux index différents, en attendant une mise à jour prend un lock-out, les mêmes indices dans l'ordre inverse. L'sélectionnez besoin de deux indices, car le premier indice ne couvre pas toutes les colonnes nécessaires à l'accès; la mise à jour doit à deux indices, parce que si vous mettez à jour un index de colonne de clé vous avez besoin de prendre un verrou sur elle.
http://blogs.msdn.com/bartd/archive/2006/09/25/770928.aspx a un fantastique explication. Des corrections suggérées comprennent l'ajout d'un index qui couvre toutes les colonnes de la sélectionner besoins, de commutation à isolation snapshot, ou explicitement, en forçant le sélectionner pour attraper un verrou de mise à jour qu'il ne serait pas normalement besoin.
Je suis surpris que personne n'a mentionné le
WITH (UPDLOCK)indicateur de verrou. Il est très utile si vous avez des blocages impliquant, par exemple deux ou sélectionnez-insérer des paires running en parallèle.Dans SQL Server, si vous émettez la sélectionne avec
WITH (UPDLOCK), le second select attendre jusqu'à ce que la première sélection est terminé. Sinon, ils obtiennent des verrous partagés, et quand ils en même temps essayer de mettre à niveau vers les verrous exclusifs, ils blocage.Rolf KristensenexpliquéWITH (UPDLOCK)parfaitement dans sa réponse.Ma conjecture est que le select-déclaration acquiert une lecture de verrouillage, lorsque vous venez avec la mise à jour de l'instruction, qu'il doit ensuite mettre à niveau vers une écriture de verrouillage.
La mise à niveau vers une écriture-lock exige que tous les autres lire les serrures sont supprimés (Leur sélectionnez-les transactions se termine). Mais si un autre processus a déjà eu la brillante idée de mettre à niveau à une réduction de verrouillage, puis soudainement, vous avez deux processus attendre les uns les autres à la libération de la lecture de verrouillage, de sorte qu'ils peuvent obtenir la radiation de la serrure.
Si à l'aide de select-pour-mise à jour (UPDLOCK) puis il va acquérir une écriture de verrouillage depuis le début, et puis vous n'avez pas l'impasse de la question.
Serrures simples requêtes peuvent se produire comme ils le verrouillage des lignes simples, pas l'ensemble de la table:
La requête de mise à jour obtient un verrou de mise à jour sur quelques lignes dans une table, et la requête select obtient un verrou en lecture sur d'autres lignes dans la table. La requête de mise à jour, puis tente d'obtenir un verrou de mise à jour sur les lignes qui sont en lecture verrouillé, et la requête select tente d'obtenir un verrou en lecture sur les lignes de mise à jour verrouillé.
On peut faire encore plus compliqué avec escalading les verrous, c'est à dire la base de données décide qu'il y a trop de lignes simples verrouillé par une transaction de sorte qu'il devrait être dégénéré, verrouillage d'une section de la table ou de la totalité de la table. Cela signifie que le verrouillage peut affecter les lignes qui ne sont pas directement impliqués dans la requête.
Lire correctement sur les transactions et les niveaux d'isolation: pour un peu dense mais assez complète et neutre sur le plan technologique, voir Principes de Traitement des Transactions. Il a secoué mon monde (et m'a donné quelques maux de tête!).
Je ne suis pas sûr de ce que vous êtes d'avoir des ennuis avec, ou ce que le niveau d'isolation que vous utilisez. Mais pensez à ceci: pour tous les moteur de base de données sait, si vous ne les lit dans une transaction, comment peut-il dire si oui ou non vous allez faire une écriture plus tard? Haut niveaux d'isolement besoin d'être bloqué à chaque fois qu'une lecture est en fait, peut-être sur l'ensemble de la table pour se protéger contre les lectures fantômes, puisque les données peuvent affecter une écriture plus tard.
Voudriez-vous que la base de données d'attendre arbitrairement long pour un verrou exclusif sur vos données? Jetez un oeil à vos niveaux d'isolement dans l'ensemble, et si vous êtes inutilement l'exécution d'une série de lectures isolé de la transaction. Il n'est pas toujours facile de déterminer comment sale lit que vous pouvez tolérer, mais...
Vous devriez lire sur l'isolation de la transaction:
http://msdn.microsoft.com/en-us/library/ms173763.aspx