Ich habe zwei sehr einfache Tabellen, nennen wir sie [UserData1] und [UserData2]. Beide haben die Spalte [UserId] als Primärschlüssel. Ich führe zwei Arten von Abfragen für diese beiden Tabellen durch. Eine ist eine SELECT-Anweisung, die kombinierte Daten für einen bestimmten Benutzer zurückgibt:
%Vor%Die andere ist eine Transaktion, die Benutzerdaten in beiden Tabellen für einen bestimmten Benutzer aktualisiert:
%Vor%Das Problem hierbei ist, dass die Reihenfolge des Erlangens von gemeinsam genutzten Tabellensperren in der SELECT-Anweisung unbestimmt ist, was zu einer klassischen Deadlock-Situation führen kann (und tatsächlich führt), wenn SQL Server beschließt, [UserData2] vor [UserData1] zu sperren. Was wäre der beste Weg, in diesem Fall Deadlocks zu vermeiden?
Verschmelzen Sie diese Tabellen in eine Tabelle, oder? Ich wünschte es wäre so leicht. Angenommen, es gibt einen Grund, sie getrennt zu halten.
READ UNCOMMITTED / NOLOCK Hinweis? Angenommen, schmutzige Lesevorgänge können nicht toleriert werden.
SNAPSHOT Isolationsstufe? Dies würde das Problem lösen, aber ich bin mir nicht sicher über den damit verbundenen Aufwand.
Die Frage läuft also so weit ab: Gibt es eine Möglichkeit, die Reihenfolge zu garantieren, in der Sperren für verknüpfte Tabellen erworben werden?
Zuerst dachte ich, dies könnte mit FORCE ORDER query thin erreicht werden, aber dann habe ich durch Experimente herausgefunden, dass es nicht unbedingt die Reihenfolge durchsetzt, in der die Tabellen gesperrt sind. Eine andere Lösung in diesem speziellen Fall wäre, separate SELECT-Abfragen für jede Tabelle zu erstellen und dann zwei einreihige Recordsets in der Anwendungsschicht zu kombinieren, aber für den Fall, dass ich jemals eine Abfrage für mehrere Benutzer erstellen muss, würde ich trotzdem alle bevorzugen Ergebnisse in einem Recordset.
UPDATE:
Dies ist der Auszug aus der Deadlock-Ablaufverfolgung:
%Vor%Anscheinend hat der Prozess, der die SELECT-Anweisung ausführt, trotz DES FORCE ORDER-Hinweises eine Sperre für die [UserData2] -Tabelle vor [UserData1] erhalten.
Mit READ COMMITTED sollte die Auswahl nicht am Deadlock teilnehmen, da sie immer nur eine Sperre gleichzeitig erhalten soll. Die Sperre kann sofort nach dem Lesen der gesperrten Zeile ausgelöst werden.
Ich empfehle Ihnen wärmstens, die Snapshot-Isolation zu aktivieren. Es wird das Problem lösen. Machen Sie sich mit den 3 Gemeinkosten vertraut: erhöhte Zeilengröße, Tempdb-Schreibvorgänge und geringer Leseaufwand. Die meiste Zeit sind sie nicht sinnvoll.
Als erstes (glaube ich) ist Ihre where-Klausel in der ersten Abfrage redundant. Sie haben die gleiche Sache in der Join und es ist besser in der Join, weil Sie eine vollständige äußere Join tun.
Im Hinblick auf die Vermeidung von Deadlocks hat es wahrscheinlich damit zu tun, wie die erste Abfrage in Bezug auf das Fallenlassen der Lesesperre gehandhabt wird. Wenn die Anwendung nur die Daten liest und dies nicht Teil einer Benutzertransaktion ist, kann die zweite Abfrage nach Abschluss des Lesevorgangs abgeschlossen werden, und Sie erhalten keinen Deadlock.
Erhalten Sie in Ihrer Umgebung einen Deadlock oder raten Sie einfach, dass Sie einen Deadlock bekommen. Wenn Sie das Deadlock-Diagramm veröffentlichen, können wir sehen, was die tatsächliche Sperre ist.
Tags und Links sql-server