SQL Server-Deadlock-Fix: Force join-Reihenfolge, oder automatisch wiederholen?
ich habe eine gespeicherte Prozedur, führt eine Verknüpfung der TableB
zu TableA
:
SELECT <--- Nested <--- TableA
Loop <--
|
---TableB
Zur gleichen Zeit, in einer Transaktion, Zeilen eingefügt werden TableA
, und dann in TableB
.
Diese situation ist gelegentlich verursachen deadlocks, wie die gespeicherte Prozedur wählen Sie packt Zeilen aus TableB, während die insert fügt Zeilen zu TableA, und jeder will den anderen gehen zu lassen die andere Tabelle:
INSERT SELECT
========= ========
Lock A Lock B
Insert A Select B
Want B Want A
....deadlock...
Logik erfordert die INSERT
ersten Zeilen hinzufügen Eine, und dann zu B, während ich persönlich kümmern sich nicht die Reihenfolge, in der SQL Server führt die join - solange es kommt.
Der gemeinsamen Empfehlung für die Behebung von deadlocks ist, um zu gewährleisten, dass jeder Zugriff auf Ressourcen in der gleichen Reihenfolge. Aber in diesem Fall SQL Server-Optimierer ist mir, dass die umgekehrte Reihenfolge ist "besser". ich kann anderen Kraft join-Reihenfolge, und haben eine schlechtere ausführen der Abfrage.
Aber sollte ich?
Soll ich überschreiben der Optimierer, jetzt und für immer, mit einer join-Reihenfolge, die ich möchte es zu benutzen?
Oder sollte ich einfach trap Fehler systemeigene Fehler 1205, und erneut die select-Anweisung?
Die Frage ist nicht, wie viel schlimmer die Abfrage durchführen kann, wenn ich das überschreiben der optimizer und für es zu tun, etwas, was nicht optimal ist. Die Frage ist: ist es besser, sich automatisch wiederholen, anstatt schlimmer Abfragen?
Du musst angemeldet sein, um einen Kommentar abzugeben.
Ist es besser, sich automatisch wiederholen deadlocks. Der Grund dafür ist, dass Sie möglicherweise beheben diese Sackgasse, die nur zu schlagen, ein anderes später. Das Verhalten kann sich ändern, zwischen den SQL-Versionen, wenn die Größe der Tabellen ändert, wenn die server-hardware-Spezifikationen ändern, und auch wenn die Last auf dem server ändert. Wenn der deadlock ist Häufig, Sie sollten aktive Schritte, um Sie zu beseitigen (ein index ist in der Regel die Antwort), aber für seltene deadlocks (sagen wir alle 10 Minuten oder so), wiederholen Sie in der Anwendung kann die Maske der deadlock. Sie können versuchen liest oder schreibt, da die Schreibvorgänge sind, natürlich, umgeben von richtigen begin transaction/commit transaction zu halten, werden alle Schreibvorgänge atomar und daher in der Lage, zu wiederholen, w/o Probleme.
Einen anderen Weg zu prüfen, ist das einschalten read committed-snapshot. Wenn diese Funktion aktiviert ist, WÄHLEN Sie einfach keine sperren, noch yield consistent".
Um deadlocks zu vermeiden, eine der häufigsten Empfehlungen ist es, "sperren in der gleichen Reihenfolge" oder "access-Objekte in der gleichen Reihenfolge". Klar das macht auch Sinn, aber es ist immer machbar? Ist es immer möglich? Ich halte die Begegnung mit Fällen, wenn ich nicht Folgen diesem Rat.
Wenn ich Speichere ein Objekt in einem übergeordneten Tabelle und einem oder mehr Kind lieben, kann ich nicht Folgen diesem Rat. Beim einfügen, habe ich einfügen müssen meine Eltern erste Reihe. Beim löschen, ich habe zu tun, es in das Gegenteil um.
Wenn ich Befehle verwenden, berühren Sie mehrere Tabellen oder mehrere Zeilen in einer Tabelle, dann in der Regel ich habe keine Kontrolle der Reihenfolge, in der die sperren erworben werden, (vorausgesetzt, ich bin nicht mit hinweisen).
So, in vielen Fällen versuchen, zu erwerben Schlösser in der gleichen Reihenfolge nicht verhindern, dass alle deadlocks. Also, wir brauchen eine Art von Umgang mit deadlocks trotzdem - wir können nicht davon ausgehen, dass wir beseitigen Sie alle. Es sei denn, natürlich, wir serialisieren alle Zugriff mithilfe von Service Broker oder sp_getapplock.
Wenn wir wiederholen nach deadlocks, sind wir sehr wahrscheinlich zu überschreiben andere Prozesse " verpasst. Wir müssen uns bewusst sein, dass sehr wahrscheinlich noch jemand anders geändert, die Daten, die wir bestimmt ändern. Vor allem, wenn alle Leserinnen und Leser, laufen Sie unter snapshot-isolation, dann der Leser nicht beteiligt werden deadlocks, das bedeutet, dass alle beteiligten in einem deadlock sind Schriftsteller, geändert oder zu ändern versucht wurde, die gleichen Daten. Wenn wir nur die Ausnahme abzufangen und automatisch wiederholen, wir überschreiben kann jemand anderes ändert.
Dies wird als lost-updates, und dies ist in der Regel falsch. In der Regel das richtige zu tun, nachdem ein deadlock ist zu wiederholen, auf einem viel höheren level - re-wählen Sie die Daten und entscheiden, ob zu speichern in der gleichen Weise die ursprüngliche Entscheidung für speichern gemacht wurde.
Zum Beispiel, wenn ein Benutzer geschoben, ein Speichern-button und speichern Sie die Transaktion wurde als Deadlockopfer gewählt, ist es wahrscheinlich eine gute Idee, re-Anzeige der Daten auf dem Bildschirm, die nach dem deadlock.
Trapping und erneutes ausführen der arbeiten kann, aber sind Sie sicher, dass die Option ist immer die deadlock-Opfer? Wenn das insert ist die deadlock-Opfer, Sie müssen viel vorsichtiger sein, über den Vorgang wiederholen.
Die einfachste Lösung in diesem Fall, denke ich, ist es, NOLOCK oder READUNCOMMITTED (gleiche) wählen Sie Ihre. Die Menschen haben berechtigte Bedenken, dirty reads, aber wir haben NOLOCK alle über dem Platz für eine höhere Parallelität, die für Jahre und hatte noch nie ein problem.
Ich würde auch ein wenig mehr Forschung in Schloss Semantik. Zum Beispiel, ich glaube, wenn Sie die set transaction isolation level snapshot (erfordert, die 2005 oder später) Ihre Probleme Weg.
SELECT
- Anweisung kann nicht erstellen Sie eine deadlock-situation. Sie müssen zumindest zwei mehrfach-statement-Transaktionen. Sie nicht werden, DML, aber Sie müssen warten, sperren sich gegenseitig auf die Ressourcen und das bedeutet, dass Sie beide müssen mindestens zwei Ressourcen. Wenn es wirklich nur eine einzigeSELECT
Anweisung, nicht gewickelt, in jeder größeren Transaktion, dann könnte es nicht eine echte Sackgasse, es könnte nur sein das I/O-system kämpfen zu halten oder einige andere seltsame server-Problem.SELECT
- Anweisung wird immer gewählt werden, als die Opfer, weil Sie als weniger "wichtig" Transaktion. Ich bin eigentlich überrascht, dass Sie sah noch 1 InstanzUPDATE
Opfer zu sein, aber dann ist das Ding über deadlocks, Sie sind sehr unberechenbar, und Sie müssen bereit sein, für Sie irgendwie.UPDATE
getötet wurde: ich kann nicht sagen, was der andere Prozess war, zu tun. Vermutlich war es nicht einfach, wählen Sie.sp_lock sp_who sp_who2 select * from sys.dm_tran_locks
?