Deadlock verursacht durch SELECT-JOIN-Anweisung mit SQL-Server
Beim ausführen einer SELECT-Anweisung mit einem JOIN von zwei Tabellen, SQL-Server scheint
sperren Sie beide Tabellen von der Anweisung einzeln. Zum Beispiel, indem Sie eine Abfrage wie
diese:
SELECT ...
FROM
table1
LEFT JOIN table2
ON table1.id = table2.id
WHERE ...Fand ich heraus, dass die Reihenfolge der sperren hängt von der WHERE-Bedingung. Die
Abfrageoptimierer versucht, produzieren einen Ausführungsplan, der liest nur so viel
Zeilen wie nötig. Also, wenn die WHERE-Bedingung enthält eine Spalte von Tabelle1
es wird zunächst das Ergebnis Zeilen aus Tabelle1 und dann bekommen Sie die entsprechenden
Zeilen aus Tabelle2. Wenn die Spalte aus Tabelle2 wird es tun das andere Weg
Runde. Komplexere Bedingungen oder die Verwendung von Indizes kann haben einen Effekt an
die Entscheidung des abfrageoptimierers zu.
Wenn die Daten gelesen, indem eine Anweisung aktualisiert werden sollte, später in der Transaktion
mit UPDATE-Anweisungen es ist nicht garantiert, dass die Reihenfolge der AKTUALISIERUNG
Aussagen, die mit der Bestellung übereinstimmt, die verwendet wurde, um Daten zu Lesen und aus den 2 Tabellen.
Wenn eine andere Transaktion versucht, Daten zu Lesen, während eine Transaktion ist die Aktualisierung der
Tabellen es kann einen deadlock verursachen, wenn die SELECT-Anweisung ausgeführt wird
zwischen den UPDATE-Anweisungen, weil weder der WÄHLEN Sie können die Sperre auf
die erste Tabelle noch kann das UPDATE bekommen, die Sperre für die zweite Tabelle. Für
Beispiel:
T1: SELECT ... FROM ... JOIN ...
T1: UPDATE table1 SET ... WHERE id = ?
T2: SELECT ... FROM ... JOIN ... (locks table2, then blocked by lock on table1)
T1: UPDATE table2 SET ... WHERE id = ?Beide Tabellen stellen eine Typ-Hierarchie und sind immer zusammen verladen. So ist es
Sinn macht das laden eines Objekts mit einem SELECT mit einem JOIN. Beide Tabellen laden
einzeln würde nicht den Abfrageoptimierer eine chance, den besten zu finden
Ausführungsplan. Aber da die UPDATE-Anweisungen können nur aktualisieren einer Tabelle zu einem
Zeit dies kann Ursachen von deadlocks, wenn ein Objekt geladen wird, während das Objekt
aktualisiert wird die von einer anderen Transaktion. Updates von Objekten, die Häufig die Ursache für UPDATEs auf
sowohl Tabellen als Eigenschaften dem Objekt, gehören zu verschiedenen Arten der
Typ-Hierarchie aktualisiert werden.
Ich habe versucht, hinzufügen von sperrhinweisen zu der SELECT-Anweisung, aber das funktioniert nicht
am problem ändern. Es führt genau die Sackgasse, in die SELECT-Anweisungen, wenn
beide Aussagen versuchen zu sperren, die Tabellen und eine SELECT-Anweisung ruft die Sperre
in der entgegengesetzten Reihenfolge der anderen Aussage. Vielleicht wäre es möglich
laden von Daten für die updates immer mit der gleichen Aussage zu zwingen, die sperren zu sein
in der gleichen Reihenfolge. Das würde verhindern, dass ein deadlock zwischen zwei Transaktionen, die
die Daten aktualisieren möchten, aber nicht verhindern, dass eine Transaktion nur liest
Daten zu deadlock, die Bedürfnisse und die unterschiedlichen WHERE-Bedingungen.
Nur Arbeit-a-Runde, so dass diese so weit zu sein scheint, dass liest erhalten möglicherweise nicht sperren
an alle. Mit SQL Server 2005-diese kann getan werden, mithilfe der SNAPSHOT-ISOLATION. Die
einzige Möglichkeit, SQL Server 2000, würde das LESEN UNGEBUNDEN-isolation
Ebene.
Ich würde gerne wissen, ob es eine andere Möglichkeit zu verhindern, dass der SQL-Server
verursacht diese deadlocks?
- snapshot-isolation-level?
- Wenn das ist eine Frage, die Antwort ist Nein. Es passiert mit allen Isolationsstufen, vielleicht mit Ausnahme der READ UNCOMMITTED, dass ich nicht testen, weil ich nicht will, dass Transaktionen, die Lesen können, die Hälfte aktualisierten Daten.
Du musst angemeldet sein, um einen Kommentar abzugeben.
Wird nie passieren, die unter der momentaufnahmeisolation, wenn die Leser blockieren keine Schreiber. Andere als die, es gibt keine Möglichkeit solche Sachen zu unterbinden. Ich schrieb eine Menge von repro-Skripten hier: Reproduktion von deadlocks mit nur einer Tabelle
Edit:
Ich habe keinen Zugriff auf SQL 2000, aber ich würde versuchen zu serialisieren Zugriff auf das Objekt durch die Verwendung von sp_getapplock, so dass das Lesen und Modifikationen, die nie gleichzeitig laufen. Wenn Sie nicht verwenden können sp_getapplock, roll-out Ihre eigenen mutex.
Einen anderen Weg um dies zu beheben, teilen Sie die select... from... mitmachen in mehrere select-Anweisungen. Festlegen der Isolationsstufe read committed. Tabelle variable zum weiterleiten von Daten aus wählen Sie für den Beitritt zur anderen. Verwenden Sie verschiedene filtern unten Einfügungen in diese Tabelle Variablen.
Also, wenn ich habe zwei Tabellen A, B. ich bin inserting/updating in A und dann B. Wo, wie die sql-query-optimizer vorzieht zu Lesen, B erste und A. ich werde teilen Sie die einzelnen select-into-2 wählt. Zunächst werde ich Lesen B. Dann pass auf diese Daten, um die nächste select-Anweisung, die liest A.
Hier deadlock wird nicht passieren, da die lese-sperren auf die Tabelle B wird veröffentlicht, sobald 1. statement ist getan.
PS ich habe angesichts dieses Problems und dies funktionierte sehr gut. Viel besser als meine Kraft, um Antwort.
War ich vor dem gleichen Problem. Mit Abfragehinweis FORCE ORDER wird dieses Problem lösen. Der Nachteil ist, dass Sie nicht in der Lage, zu nutzen, die beste plan, die Abfrage-Optimierer hat die für eine Abfrage, aber dies wird verhindern, dass die deadlock.
So (dies ist aus "Bill, die Eidechse" - Benutzer) wenn Sie eine Abfrage AUS Tabelle1 LEFT JOIN Tabelle2 und Ihre WHERE-Klausel enthält nur die Spalten aus Tabelle2 der Ausführungsplan wird in der Regel zunächst wählen Sie die Zeilen aus Tabelle2 und dann suchen Sie die Zeilen aus Tabelle1. Mit einem kleinen ResultSet aus Tabelle2 nur ein paar Zeilen aus Tabelle1 haben, abgerufen werden. Mit KRAFT, UM zunächst alle Zeilen aus Tabelle1 müssen geholt werden, da es keine WHERE-Klausel, dann die Zeilen aus Tabelle2 verknüpft und das Ergebnis ist eine Filterung mit der WHERE-Klausel. Was die Leistung verringert.
Aber wenn Sie wissen, dies wird nicht der Fall sein, verwenden Sie diese. Möchten Sie vielleicht optimieren Sie die Abfrage manuell.
Die syntax ist