Deadlock verursacht durch SELECT JOIN-Anweisung mit SQL Server

Question

Bei der Ausführung einer SELECT-Anweisung mit einem JOIN von zwei Tabellen scheint SQL Server beide Tabellen der Anweisung einzeln zu sperren. Zum Beispiel bei einer Abfrage wie so:

SELECT ...
FROM
    table1
    LEFT JOIN table2
        ON table1.id = table2.id
    WHERE ...

Ich habe herausgefunden, dass die Reihenfolge der Schlösser von der WHERE-Bedingung abhängt. Der Abfrageoptimierer versucht, einen Ausführungsplan zu erstellen, der nur so viele Zeilen wie nötig liest. Wenn also die WHERE-Bedingung eine Spalte von Tabelle1 enthält enthält, holt er zuerst die Ergebniszeilen aus Tabelle1 und dann die entsprechenden Zeilen aus Tabelle2. Wenn die Spalte aus Tabelle2 stammt, wird der Plan in umgekehrter rund. Komplexere Bedingungen oder die Verwendung von Indizes können einen Einfluss haben auf die Entscheidung des Abfrageoptimierers auswirken.

Wenn die von einer Anweisung gelesenen Daten später in der Transaktion aktualisiert werden sollen mit UPDATE-Anweisungen aktualisiert werden sollen, ist nicht gewährleistet, daß die Reihenfolge der UPDATE Anweisungen mit der Reihenfolge übereinstimmt, in der die Daten aus den beiden Tabellen gelesen wurden. Wenn eine andere Transaktion versucht, Daten zu lesen, während eine Transaktion die Tabellen aktualisiert, kann es zu einem Deadlock kommen, wenn die SELECT-Anweisung in zwischen den UPDATE-Anweisungen ausgeführt wird, da weder die SELECT-Anweisung die Sperre auf die erste Tabelle erhalten kann die Sperre auf die erste Tabelle erhalten kann, noch kann die UPDATE-Anweisung die Sperre auf die zweite Tabelle erhalten. 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 Typenhierarchie dar und werden immer gemeinsam geladen. Es ist also ist es sinnvoll, ein Objekt über ein SELECT mit JOIN zu laden. Das Laden beider Tabellen Tabellen einzeln zu laden, würde dem Abfrageoptimierer keine Chance geben, den besten Ausführungsplan zu finden. Da aber UPDATE-Anweisungen nur jeweils eine Tabelle aktualisieren können kann dies zu Deadlocks führen, wenn ein Objekt geladen wird, während das Objekt von einer anderen Transaktion aktualisiert wird. Aktualisierungen von Objekten verursachen oft UPDATEs auf beiden Tabellen, wenn Eigenschaften des Objekts, die zu verschiedenen Typen der Typenhierarchie gehören, aktualisiert werden.

Ich habe versucht, der SELECT-Anweisung Sperrhinweise hinzuzufügen, aber das ändert das Problem nicht. ändert das Problem nicht. Es verursacht nur die Blockierung in den SELECT-Anweisungen, wenn beide Anweisungen versuchen, die Tabellen zu sperren, und eine SELECT-Anweisung die Sperre in der umgekehrten Reihenfolge wie die andere Anweisung. Vielleicht wäre es möglich Daten für Aktualisierungen immer mit der gleichen Anweisung zu laden, um die Sperren in der in der gleichen Reihenfolge. Das würde einen Deadlock zwischen zwei Transaktionen verhindern, die die Daten aktualisieren wollen, aber es würde nicht verhindern, dass eine Transaktion, die nur Daten liest Daten liest, die unterschiedliche WHERE-Bedingungen haben muss.

Die einzige Möglichkeit, dies zu umgehen, scheint zu sein, dass Lesungen nicht gesperrt werden können. bekommen. Mit SQL Server 2005 kann dies mit SNAPSHOT ISOLATION erreicht werden. Die einzige Möglichkeit für SQL Server 2000 wäre die Verwendung der Isolationsebene READ UNCOMMITED Ebene.

Ich würde gerne wissen, ob es eine andere Möglichkeit gibt, den SQL Server zu verhindern daran zu hindern, diese Deadlocks zu verursachen?

Answer 1

Bei einer isolierten Momentaufnahme, bei der die Leser die Schreiber nicht blockieren, wird dies nicht passieren. Abgesehen davon gibt es keine Möglichkeit, solche Dinge zu verhindern. Ich habe hier eine Menge Repro-Skripte geschrieben: Reproduzieren von Deadlocks mit nur einer Tabelle

Edita:

Ich habe keinen Zugriff auf SQL 2000, aber ich würde versuchen, den Zugriff auf das Objekt mit sp_getapplock zu serialisieren, so dass Lesen und Ändern nie gleichzeitig ablaufen. Wenn Sie sp_getapplock nicht verwenden können, sollten Sie einen eigenen Mutex einrichten.

Answer 2

Eine andere Möglichkeit, dies zu beheben, besteht darin, die select... from... join in mehrere select-Anweisungen aufzuteilen. Setzen Sie die Isolationsebene auf read committed. Verwenden Sie eine Tabellenvariable, um die Daten aus dem Select zu den anderen zu leiten. Verwenden Sie distinct, um Einfügungen in diese Tabellenvariablen herauszufiltern.

Also, wenn ich zwei Tabellen A, B. Ich bin einfügen/aktualisieren in A und dann B. Wo als die Sql-Abfrage-Optimierer bevorzugt B zuerst lesen und A. Ich werde die einzelnen Select in 2 Selects aufteilen. Zuerst lese ich B. Dann gebe ich diese Daten an die nächste Select-Anweisung weiter, die A liest.

Hier kommt es nicht zu einer Verklemmung, da die Lesesperren auf Tabelle B freigegeben werden, sobald die erste Anweisung ausgeführt wird.

PS: Ich hatte dieses Problem auch schon und es hat sehr gut funktioniert. Viel besser als meine Force-Order-Antwort.

Answer 3

Ich hatte das gleiche Problem. Die Verwendung des Abfragehinweises FORCE ORDER wird dieses Problem beheben. Der Nachteil ist, dass Sie nicht in der Lage sein werden, den besten Plan zu nutzen, den der Abfrageoptimierer für Ihre Abfrage hat, aber dies wird die Blockierung verhindern.

Wenn Sie also eine Abfrage FROM tabelle1 LEFT JOIN tabelle2 haben und Ihre WHERE-Klausel nur Spalten aus tabelle2 enthält, wird der Ausführungsplan normalerweise zuerst die Zeilen aus tabelle2 auswählen und dann die Zeilen aus tabelle1 nachschlagen (dies stammt vom Benutzer "Bill the Lizard"). Bei einer kleinen Ergebnismenge aus table2 müssen nur wenige Zeilen aus table1 geholt werden. Mit FORCE ORDER müssen zuerst alle Zeilen aus Tabelle1 geholt werden, da sie keine WHERE-Klausel hat, dann werden die Zeilen aus Tabelle2 verbunden und das Ergebnis wird mit Hilfe der WHERE-Klausel gefiltert. Das verschlechtert die Leistung.

Wenn Sie aber wissen, dass dies nicht der Fall sein wird, verwenden Sie dies. Möglicherweise möchten Sie die Abfrage manuell optimieren.

Die Syntax lautet

SELECT ...
FROM
    table1
    LEFT JOIN table2
        ON table1.id = table2.id
    WHERE ...
OPTION (FORCE ORDER)