Ich habe eine gespeicherte Prozedur, die eine Verknüpfung von TableB zu TableA :
Gleichzeitig werden Zeilen in einer Transaktion in TableA und dann in TableB eingefügt.
Diese Situation führt gelegentlich zu Deadlocks, da die gespeicherte Prozedur Zeilen aus TableB extrahiert, während die Einfügung Zeilen zu TableA hinzufügt und dann jeder den anderen zulassen lässt geh von der anderen Tabelle:
%Vor% Logic erfordert, dass INSERT zuerst Zeilen zu A und dann zu B hinzufügt, obwohl mir persönlich die Reihenfolge egal ist, in der SQL Server diese ausführt beitreten - solange es sich anschließt.
Die übliche Empfehlung zum Beheben von Deadlocks besteht darin, sicherzustellen, dass alle Ressourcen in der gleichen Reihenfolge zugreifen. Aber in diesem Fall sagt der Optimierer von SQL Server mir, dass die umgekehrte Reihenfolge "besser" ist. Ich kann eine andere Join-Reihenfolge erzwingen und eine schlechtere Abfrage durchführen.
Aber sollte ich?
Soll ich den Optimierer jetzt und für immer mit einer Join-Reihenfolge überschreiben, die ich verwenden soll?
Oder sollte ich nur den Fehler nativer Fehler 1205 abfangen und die select-Anweisung erneut einreichen?
Die Frage ist nicht, wie viel schlechter die Abfrage ausgeführt werden könnte, wenn ich das Optimierungsprogramm überschreibe und etwas nicht optimal mache. Die Frage ist: Ist es besser, den Versuch automatisch zu wiederholen, anstatt schlechtere Abfragen auszuführen?
Ist es besser, Deadlocks automatisch erneut zu versuchen. Der Grund dafür ist, dass Sie diesen Deadlock beheben können, nur um später einen anderen zu treffen. Das Verhalten kann sich zwischen SQL-Versionen ändern, wenn sich die Größe der Tabellen ändert, wenn sich die Server-Hardwarespezifikationen ändern und wenn sich die Last auf dem Server ändert. Wenn der Deadlock häufig ist, sollten Sie aktive Schritte ergreifen, um ihn zu beseitigen (ein Index ist normalerweise die Antwort), aber bei seltenen Deadlocks (etwa alle 10 Minuten) kann ein erneuter Versuch in der Anwendung den Deadlock maskieren. Sie können Lesevorgänge mit oder wiederholen, da die Schreibvorgänge natürlich von einer ordnungsgemäßen begin transaction / commit-Transaktion umgeben sind, um alle Schreiboperationen unteilbar zu halten und sie somit ohne Probleme wiederholen zu können.
Eine weitere Möglichkeit, die Sie in Betracht ziehen sollten, ist das Read-Committed-Snapshot . Wenn dies aktiviert ist, wird SELECT keine Sperren annehmen, aber konsistente Lesevorgänge erzielen.
Um Deadlocks zu vermeiden, ist eine der gebräuchlichsten Empfehlungen "Sperren in der gleichen Reihenfolge" oder "Zugriff auf Objekte in der gleichen Reihenfolge". Das ist eindeutig sinnvoll, aber ist es immer machbar? Ist es immer möglich? Ich stoße immer wieder auf Fälle, in denen ich diesem Rat nicht folgen kann.
Wenn ich ein Objekt in einer Eltern-Tabelle und einem oder mehreren Kind-Tabellen speichere, kann ich diesem Rat überhaupt nicht folgen. Beim Einfügen muss ich zuerst meine Elternzeile einfügen. Beim Löschen muss ich es in umgekehrter Reihenfolge tun.
Wenn ich Befehle verwende, die mehrere Tabellen oder mehrere Zeilen in einer Tabelle berühren, habe ich normalerweise keine Kontrolle, in welcher Reihenfolge Sperren erworben werden (angenommen, dass ich keine Hinweise verwende).
In vielen Fällen verhindert das Sperren von Sperren in derselben Reihenfolge nicht alle Deadlocks. Wir brauchen also irgendwie Deadlocks - wir können nicht davon ausgehen, dass wir sie alle eliminieren können. Sofern wir nicht alle Zugriffe mit Service Broker oder sp_getapplock serialisieren.
Wenn wir nach Deadlocks versuchen, überschreiben wir sehr wahrscheinlich die Änderungen anderer Prozesse. Wir müssen uns darüber im Klaren sein, dass wahrscheinlich jemand anders die Daten geändert hat, die wir ändern wollten. Insbesondere, wenn alle Leser unter Snapshot-Isolation laufen, können Leser nicht in Deadlocks involviert sein, was bedeutet, dass alle an einem Deadlock beteiligten Parteien Writer sind, modifiziert oder versucht haben, dieselben Daten zu modifizieren. Wenn wir die Ausnahme nur abfangen und automatisch erneut versuchen, können wir die Änderungen anderer Personen überschreiben.
Das nennt man verlorene Updates, und das ist normalerweise falsch. In der Regel ist es nach einem Deadlock der richtige Zeitpunkt, auf einer viel höheren Ebene zu versuchen - wählen Sie die Daten erneut aus und entscheiden Sie, ob auf die gleiche Weise gespeichert werden soll, wie die ursprüngliche Speicherentscheidung getroffen wurde.
Wenn beispielsweise ein Benutzer die Schaltfläche Speichern gedrückt hat und die Sicherungstransaktion als Deadlock-Opfer ausgewählt wurde, kann es sinnvoll sein, die Daten nach dem Deadlock erneut auf dem Bildschirm anzuzeigen.
Trapping und Rerunning können funktionieren, aber sind Sie sicher, dass SELECT immer das Deadlock-Opfer ist? Wenn die Einfügung das Deadlock-Opfer ist, müssen Sie beim erneuten Versuch viel vorsichtiger sein.
Die einfachste Lösung in diesem Fall ist meiner Meinung nach NOLOCK oder READUNCOMMITTED (selbe Sache). Die Leute haben berechtigte Bedenken wegen Dirty Reads, aber wir haben NOLOCK seit Jahren überall für höhere Parallelität laufen lassen und hatten nie ein Problem.
Ich würde auch ein wenig mehr in Lock-Semantik forschen. Zum Beispiel glaube ich, wenn Sie Transaktionsisolationsstufe auf Snapshot setzen (erfordert 2005 oder später), gehen Ihre Probleme weg.
Tags und Links sql-server error-handling deadlock sql-server-2000 database-deadlocks