Null SQL-deadlock durch design - jede Codierung Muster?
Bin ich der Begegnung mit sehr selten noch nervig SQL-deadlocks auf .NET 2.0 webapp läuft auf der Oberseite des MS SQL Server 2005. In der Vergangenheit haben wir den Umgang mit dem SQL-deadlocks in die sehr empirische Weise - im Grunde das optimieren der Abfragen, bis es funktioniert.
Doch, ich fand diese Vorgehensweise sehr unbefriedigend: zeitaufwendig und unzuverlässig. Ich würde sehr vorziehen zu Folgen deterministischen Abfrage-Muster, die dafür sorgen würde von design, dass keine SQL-deadlock wird anzutreffen sein - immer.
Beispielsweise in C# - Multithread-Programmierung, eine einfache design-Regel wie müssen die Schlösser genommen werden, die nach Ihrer lexikographischen Ordnung sorgt dafür, dass kein Stillstand wird immer passieren.
Gibt es irgendwelche SQL-coding-Muster garantiert deadlock-Beweis?
- Dies ist mit Abstand einer der am meisten frustrierenden Dinge, die ich jemals erlebt habe in meiner Programmier-Karriere. Ich habe in 5+ Jahren brechen meine Zähne zu versuchen, um Datenbanken zu Verhalten, wie imperative Programmiersprachen. +1 für die gute Frage. Ich wünschte nur, es war eine befriedigende Antwort.
Du musst angemeldet sein, um einen Kommentar abzugeben.
Schreiben deadlock-Beweis-code ist wirklich schwer. Selbst wenn Sie Zugriff auf die Tabellen in der Reihenfolge, die Sie möglicherweise noch deadlocks [1]. Ich schrieb ein post auf meinem blog, welcher durch einige Ansätze, die Ihnen helfen, vermeiden und beheben von deadlock-Situationen.
Wenn Sie sicherstellen möchten, dass zwei Aussagen/Transaktionen wird nie deadlock, die Sie möglicherweise in der Lage, es zu erreichen, ist durch die Beobachtung, die Schlösser jede Anweisung verbraucht mit der sp_lock system gespeicherten Prozedur. Um dies zu tun, müssen Sie entweder sehr schnell, oder verwenden Sie eine offene Transaktion mit einem Hinweis holdlock.
Hinweise:
Writing deadlock-proof code is really hard: Nicht das du meinst sagen zuimpossibleda der Ausführungsplan der Datenbank ändern kann im Laufe seiner Lebensdauer?Null deadlocks ist im Grunde ein unglaublich kostspieliges problem im Allgemeinen Fall, denn Sie müssen wissen, alle Tabellen/obj, die Sie zum Lesen und ändern für jede ausgeführte Transaktion (dies beinhaltet Wählt). Die Allgemeine Philosophie ist aufgerufen, bestellt strict two-phase locking (nicht zu verwechseln mit dem two-phase-commit) (http://en.wikipedia.org/wiki/Two_phase_locking ; sogar 2PL nicht Garantie keine deadlocks)
Sehr wenige DBMS implementiert strict-2PL wegen der massiven Leistungseinbruch, so ein Ding verursacht (es gibt kein kostenloses Mittagessen), während alle Ihre Transaktionen warten, um für selbst einfache SELECT-Anweisungen ausgeführt werden.
Jedenfalls, wenn dies ist etwas, das Sie wirklich interessiert, werfen Sie einen Blick auf
SET ISOLATION LEVELim SQL-Server. Können Sie zwicken, wie es notwendig ist. http://en.wikipedia.org/wiki/Isolation_levelWeitere Infos, siehe wikipedia auf Serialisierbarkeit: http://en.wikipedia.org/wiki/Serializability
Dass gesagt-eine große Analogie ist, wie source-code-Revisionen: check-in früh und Häufig. Halten Sie Ihre Transaktionen klein (in # der SQL-Anweisungen Anzahl der Zeilen, die modifiziert) und schnelle (wall-clock-Zeit hilft, Vermeidung von Kollisionen mit anderen). Es kann nett und ordentlich zu tun, eine MENGE Dinge, die in einer einzigen Transaktion -- und im Allgemeinen bin ich einverstanden mit, dass die Philosophie -- aber, wenn Sie erleben eine Menge von deadlocks, Sie brechen die trans-bis in kleinere und dann überprüfen Sie Ihren status in der Anwendung, wie Sie entlang umziehen. TRAN 1 - OK J/N? Wenn Y, senden TRAN 2 - OK J/N? etc. etc
Nebenbei, in meinen vielen Jahren als DBA und auch ein Entwickler (von multiuser-DB-Anwendungen Messung von tausenden von gleichzeitigen Benutzern) ich habe nie gefunden, deadlocks zu solch einem massiven problem, dass ich brauchte, spezielle Kenntnis davon (oder zum ändern der Isolationsstufen, wohl oder übel, etc).
Es gibt keine Magische Allzweck-Lösung für dieses problem, das in der Praxis funktionieren. Sie können push-Parallelität der Applikation, sondern diese können sehr Komplex sein, vor allem, wenn Sie brauchen, um die Koordinierung mit anderen Programmen ausführen in separaten Speicher Leerzeichen.
Allgemeine Antworten zu reduzieren deadlock Möglichkeiten:
Basic-query-Optimierung (richtigen index zu verwenden) hotspot avoidanant design, halten Sie Transaktionen für den kürzest möglichen Zeiten...usw.
Wenn möglich, stellen Sie vernünftige Abfrage-timeouts, so dass, wenn ein deadlock auftreten sollte, ist es self-clearing nach der timeout-Zeitraum abläuft.
Deadlocks in MSSQL sind oft wegen seiner default read concurrency-Modell so seine sehr wichtig, nicht zu abhängig sind - davon ausgehen, Oracle Stil MVCC in allen Ausführungen. Verwenden Sie die snapshot-isolation oder, wenn möglich, die LESE-UNGEBUNDEN-isolation-level.
Glaube ich, dass die folgenden nützlich Lesen/schreiben Muster ist dead lock Beweis gegeben einige Einschränkungen:
Einschränkungen:
Write-Zyklus:
innerhalb der einzelnen update-Gruppe.
durch die schreiben zum ersten Datensatz).
Read-Zyklus:
Vorteile:
Ich brauche dieses, um zu arbeiten, also bitte kommentieren/korrigieren!!
Als Sie sagte, immer den Zugriff auf Tabellen in der gleichen Reihenfolge, ist ein sehr guter Weg, um deadlocks zu vermeiden. Darüber hinaus verkürzen Sie Ihre Geschäfte so viel wie möglich.
Anderen coolen trick ist, kombinieren Sie 2 sql-Anweisungen in einem Wann immer Sie können. Einzelne Aussagen sind immer transactional. Beispielsweise verwenden Sie "AKTUALISIEREN ... AUSWÄHLEN" oder "INSERT ... SELECT", verwenden Sie "@@ERROR" und "@@ROWCOUNT" anstelle von "SELECT COUNT", oder "WENN (VORHANDEN ...)"
Schließlich, stellen Sie sicher, dass Ihre Berufung-code verarbeiten kann, deadlocks durch Umbuchung der Abfrage eine konfigurierbare Anzahl von Zeiten. Manchmal passiert es einfach, es ist normales Verhalten und Ihre Anwendung muss in der Lage sein, damit umzugehen.
Neben der konsequenten Reihenfolge der lock-Akquisition - ein anderer Weg ist die explizite Verwendung der locking und isolation-Tipps zur Reduzierung der Zeit/Ressourcen verschwendet unabsichtlich den Erwerb von sperren wie shared-Vorsatz beim Lesen.
Etwas, dass keiner hat erwähnt (überraschend) ist, dass auf dem SQL-server betrifft viele locking-Probleme beseitigt werden können mit dem richtigen Belag Indizes für eine DB-Abfrage Arbeitsbelastung. Warum? Da kann es erheblich reduzieren die Anzahl von bookmark-lookups in einer Tabelle gruppierter index (vorausgesetzt, es ist kein Haufen), wodurch Konflikte und locking.
Wenn Sie genug haben, das design, die Kontrolle über Ihre app, beschränken Sie Ihre updates /inserts für bestimmte gespeicherte Prozeduren, und entfernen Sie die update - /insert-Berechtigungen aus der Datenbank verwendeten Rollen durch die app erst ausdrücklich erlauben, updates über diese gespeicherten Prozeduren).
Isolieren Sie Ihre Datenbank-verbindungen zu einer bestimmten Klasse in Ihrer app (jede Verbindung muss von dieser Klasse) und geben Sie an, "Abfrage, nur" verbindungen legen Sie die Isolationsstufe zu "dirty read" ... das entspricht einem (nolock) auf jeder join.
Diese Weise isolieren Sie die Aktivitäten, die kann Ursache Schleusen, die bestimmte gespeicherte Prozeduren) und nehmen "einfach liest" des "locking-loop".
Schnelle Antwort ist Nein, es gibt keine sichere Technik.
Sehe ich nicht, wie man machen kann, keine Anwendung deadlock Beweis im Allgemeinen als ein design-Prinzip, wenn es keine nicht-triviale Durchsatz. Wenn Sie präventiv sperren Sie alle Ressourcen, die Sie könnten potenziell müssen in einem Prozess, in der gleichen Reihenfolge, auch wenn Sie nicht am Ende brauchen Sie, riskieren Sie mehr kostspielige Angelegenheit, wo der zweite Prozess warten, um zu erwerben die erste Schleuse, die es braucht, und Ihre Verfügbarkeit beeinflusst wird. Und als die Anzahl der Ressourcen in Ihrem system wächst, sogar trivial Prozesse haben, sperren Sie alle in der gleichen, um zu verhindern, dass deadlocks.
Den besten Weg zur Lösung SQL-deadlock-Probleme, wie die meisten performance und Verfügbarkeit Problemen ist der Blick auf die Arbeitsbelastung in den profiler und verstehen das Verhalten.
Nicht eine direkte Antwort auf deine Frage, aber ein Denkanstoß:
http://en.wikipedia.org/wiki/Dining_philosophers_problem
Den "Dining philosophers problem" ist ein altes Gedanken-experiment für die Prüfung der deadlock-problem. Darüber zu Lesen, es könnte helfen, Sie finden eine Lösung für Ihre besonderen Umstände.