J'ai une procédure stockée qui effectue une jointure de en même temps, dans une transaction , les lignes sont insérées dans Cette situation provoque occasionnellement des blocages, car la procédure stockée saisit des rangées de Logique nécessite l'insertion La recommandation commune pour la fixation des blocages est de garantir que tout le monde accède aux ressources dans le même ordre. Mais dans ce cas, l'optimiseur de SQL Server me dit que l'ordre inverse est "meilleur". Je peux forcer une autre commande de joindre et avoir une requête pire performante. mais devrais-je? Dois-je remplacer l'optimiseur, maintenant et pour toujours, avec un ordre de jointure que je le veux à utiliser? ou devrais-je simplement piéger une erreur La question n'est pas la pire pire de la requête pourrait effectuer quand je remplace l'optimiseur et pour cela pour faire quelque chose de non-optimal. La question est la suivante: est-il préférable de réessayer automatiquement, plutôt que de courir pire des requêtes? tableb à Tablea : Tablea , puis dans tableb . pour d'abord ajouter des lignes à A , puis à B , tandis que je me soucie personnellement de la commande dans laquelle SQL Server effectue sa jointure - tant qu'elle se joint.
3 Réponses :
Le piégeage et la réconfort peuvent fonctionner, mais êtes-vous sûr que le SELECT est toujours la victime de l'impasse? Si l'insert est la victime de l'impasse, vous devrez être beaucoup plus prudent de réessayer.
La solution la plus facile dans ce cas, je pense, est à Nolock ou à Readungmited (même chose) de votre sélection. Les gens ont des préoccupations justifiables sur les lectures sales, mais nous avons couru Nolock partout à la place pour une concurrence plus élevée pendant des années et n'ont jamais eu de problème.
Je ferais aussi un peu plus de recherches sur la sémantique de verrouillage. Par exemple, je crois que si vous définissez le niveau d'isolement de la transaction en instantané (nécessite 2005 ou ultérieure), vos problèmes disparaissent.
SQL Server Roule la transaction avec le moins de ressources détenues. L'insert est une série transaction de peut-être une douzaine d'insertions. Le Select est une sélection isolée (enveloppée dans une procédure stockée)
@Ian Boyd: Un seul Sélectionnez l'instruction Impossible de créer une situation d'impasse. Vous devez avoir au moins deux transactions multi-sorties. Ils n'ont pas tous les deux besoin d'être DML, mais ils doivent tous deux attendre des serrures sur les ressources de chacun et cela signifie qu'ils doivent tous les deux utiliser au moins deux ressources. Si ce n'est vraiment qu'un seul Sélectionnez l'instruction , non emballées dans une transaction plus importante, ce n'est peut-être pas une véritable impasse, il peut être simplement du système d'E / S qui luttant pour suivre ou un autre serveur impair problème.
@Aronner. Une seule sélection peut Cause une impasse avec un autre processus ( blogs.msdn.com/bartd/archive/2006/09/25/770928.aspx )
Et dans 30 jours, j'ai enregistré 140 victimes d'impasse. 138 d'entre eux étaient sur une seule déclaration de sélection. 1 était sur une autre déclaration de sélection. Et 1 était sur une mise à jour multi-instruction transaction. Donc, pour répondre à votre question ("Êtes-vous sûr que le choix est toujours la victime de l'impasse?"): Je suis sûr. 99.2857% sûr.
@Ian Boyd: Intéressant, je n'ai jamais vécu cela, appris quelque chose aujourd'hui. Il semble nécessiter une combinaison de circonstances hautement spécifique en matière d'indexation et de structure de la requête. Bien sûr, la relève SELECT sera toujours choisie comme la victime, car elle est considérée comme une transaction moins "importante". Je suis en fait surpris que vous aviez vu même une instance de update étant la victime, mais c'est la chose à propos des blocages, ils sont extrêmement imprévisibles et vous devez être préparé pour eux en quelque sorte.
@AarNaugeur: Pour le Mise à jour qui a été tué: Je ne peux pas dire ce que l'autre processus faisait. Vraisemblablement, ce n'était pas un simple sélection.
@IanBoyd Comment obtenir plus d'informations sur Deadlock ? sp_lock sp_who sp_who2 Sélectionnez * à partir de sys.dm_tran_locks ?
est-il préférable de réessayer automatiquement des blocages. La raison étant que vous pouvez corriger cette impasse , uniquement pour en frapper une autre plus tard. Le comportement peut changer entre les versions SQL si la taille des tableaux change, si les spécifications matérielles du serveur changent, et même si la charge sur le serveur change. Si l'impasse est fréquente, vous devez prendre des mesures actives pour l'éliminer (un indice est généralement la réponse), mais pour des blocages rares (disons toutes les 10 minutes environ), réessayez dans l'application peut masquer l'impasse. Vous pouvez réessayer les lectures ou écrit, car les écritures sont entourées, bien sûr entourées d'une transaction transactionnelle / validation appropriée pour garder toutes les opérations d'écriture atomique et donc capable de les réessayer sans problèmes.
Un autre avenue à envisager est d'allumer Lire l'instantané engagé . Lorsque cela est activé, sélectionnez simplement ne prenez pas de serrures, mais rendez-vous des lectures cohérentes.
Remus, réessayer les lectures est parfaitement logique, mais réessayer automatiquement les écrit après des impasses conduisant à des mises à jour perdues. Lorsque vous écrivez et devenez une victime d'impasse, il est probable que les données que vous allez toucher ont été modifiées par quelqu'un d'autre. Nous ne devrions pas réécrire automatiquement dans de tels cas. Nous devrions relire des données éventuellement modifiées et envisagez de nouveau si nous voulons économiser. Logique?
@ALEXKUZNETSOV: C'est absurde si une transaction est écrite correctement (c'est-à-dire atomiquement), comment la réessayer peut-être peut-être une mise à jour perdue? Je donne ceci +1, c'est certainement la bonne réponse. Vous ne pouvez pas arrêter chaque blocage, il s'agit simplement d'une partie du bruit de fond avec une sémantique acide.
@Alex, @aaro: Vous avez les deux raison. Par «Réessayez», je veux dire en effet «lire le statut actuel, appliquer des modifications, rédiger un nouveau statut». Pour les applications de traitement automatisées, il s'agit d'un motif très facile à atteindre. Toutefois, pour les applications interactives de l'utilisateur, cela peut être plus difficile et souvent l'action appropriée consiste à repousser la "écriture" en reliant l'état actuel et à réafficher l'affichage à l'utilisateur. Il / elle peut confirmer que les changements appliqués Créez un sens dans le nouvel état / contexte, et je pense que c'est ce que Alex avait en tête. L'action correcte dépend donc du cas au cas.
J'ai commenté le commentaire d'Alex sur l'autre réponse: mais je vais répéter. Réessayer une mise à jour est la même que l'utilisateur cliquant à l'origine sur le bouton "Enregistrer" 200ms plus tard. S'ils venaient d'attendre un peu un peu, il n'y aurait pas eu d'impasse.
Pour éviter les blocages, l'une des recommandations les plus courantes est "d'acquérir des verrous dans le même ordre" ou "d'accès d'accès dans le même ordre". Clairement cela fait un sens parfait, mais est-ce toujours réalisable? Est-ce toujours possible? Je continue à rencontrer des cas lorsque je ne peux pas suivre ce conseil.
Si je stocke un objet dans une table parent et un ou plusieurs enfants, je ne peux pas suivre ce conseil. Lors de l'insertion, je dois d'abord insérer ma ligne mère d'abord. Lors de la suppression, je dois le faire dans l'ordre inverse.
Si j'utilise des commandes qui touchent plusieurs tables ou plusieurs lignes dans une table, alors je n'ai généralement aucun contrôle dans lequel les serrures d'ordre sont acquises (supposant que je n'utilise pas de notes).
Donc, dans de nombreux cas, essayer d'acquérir des verrous dans le même ordre n'empêche pas toutes les impasses. Nous avons donc besoin de quelque peu de manutention de toute façon - nous ne pouvons pas supposer que nous pouvons tous les éliminer. Sauf si, bien sûr, nous sérialisons tous les accès à l'aide d'un courtier de service ou sp_getaplock.
Lorsque nous réessayons après des impasses, nous sommes très susceptibles d'écraser les changements d'autres processus. Nous devons être conscients que très probablement quelqu'un d'autre a modifié les données que nous voulions modifier. Surtout si tous les lecteurs sont exécutés sous isolement d'instantané, les lecteurs ne peuvent pas être impliqués dans des blocages, ce qui signifie que toutes les parties impliquées dans une impasse sont des écrivains, modifiés ou tentés de modifier les mêmes données. Si nous attrapions simplement l'exception et réessayons automatiquement, nous pouvons écraser les changements de quelqu'un d'autre.
Ceci s'appelle des mises à jour perdues, ce qui est généralement faux. En règle générale, la bonne chose à faire après une impasse est de réessayer sur un niveau beaucoup plus élevé - ré-sélectionner les données et décider de savoir s'il faut économiser de la même manière que la décision initiale de sauvegarde a été prise.
Par exemple, si un utilisateur a poussé un bouton Enregistrer et que la transaction de sauvegarde a été choisie comme une victime d'impasse, il peut être judicieux de réafficher les données à l'écran après l'impasse.
+1 Ceci est vrai dans les applications interactives: Si une écriture avait une impression, il est très probable que l'état qui soit mis à jour avait changé a changé, car c'est exactement la ressource que l'impasse est survenue. Ma réponse a été influencée par mes antécédents dans le traitement de la file d'attente, où le «niveau supérieur» est contenu dans la transaction qui retourne.
@ALEXKUZNETSOV: Je ne suis pas tellement d'accord sur le péril de réessayer une mise à jour. Si l'utilisateur est arrivé à cliquer sur le bouton 200ms plus tard, plutôt que plus tôt, l'effet aurait été identique.
Si votre demande est déjà conçue pour soutenir une concurrence optimiste, il est logique de traiter une impasse comme un conflit. Si l'application aurait des modifications écrasées de toute façon, vous pourriez aussi bien réessayer la mise à jour.
@Ian: Si l'utilisateur est arrivé à cliquer sur la touche 200ms plus tard, plutôt que plus tôt, le système peut détecter une mise à jour perdue potentielle et rebondir la modification.
@ALEX: Vous voulez dire mon système? Où la dernière personne à cliquer sur sauvegarder gagne?
@Ian. Je ne connais pas vos besoins. Pourtant, dans de nombreux cas, la «dernière personne à cliquer sur Save Wins» n'est pas acceptable.
@Alex. Parfois verrouillé est recherché. Mais surtout nous nous comportons comme tout le monde pense qu'il se comporte. Comme si vous avez enregistré un autre fichier.