Deadlocks - SQL Server 2000

[Sylvia HGDS]

Hallo zusammen,

ich habe mehrere .Net-Anwendungen die via gespeicherter Prozeduren in eine Datenbank (SQL-Server 2000) schreiben.
Dabei scheibt eine der Anwendungen parallel (mehrere Threads). Diese Prozeduren sind so ausgelegt, dass sie einen Insert oder ein Update auf eine Zeile in einer Tabelle durchführen können. Dazu wird innerhalb der Prozedur eine Prüfung (SELECT COUNT(1) FROM ...) durchgeführt.
Wird der Datendurchsatz (Schreiben und Updaten) erhöht, so treten diese Deadlocks (ich nehme an es handelt sich hierbei um Convertierungs-Deadlocks) gehäuft auf.
Die Abfrage, ob dieser DS bereits existiert, habe ich um den Zusatz "READUNCOMMITED" erweitert. Mit dem Ergebnis, dass Deadlocks nicht mehr aufgetreten sind. Als ich ein SELECT * FROM <table> im Enterprise Manager ausgeführt habe, traten sehr viele Deadlocks auf (während der Schreibvorgänge). Die Prozeduren sind in Transaktionen eingeschlossen.
Vielen Dank für all eure Ideen

Gruß Sylvia

[Andreas Kosch]

Hallo,

ich gehe einmal davon aus, dass mit Deadlock tatsächlich der SQL-Fehler 1205 gemeint ist, der auch dann auftreten kann, wenn zwei Sessions gleichzeitig auf nur eine einzige Tabelle zugreifen.

&gt;..Zusatz "READUNCOMMITED" erweitert...

Bei einem DIRTY READ für die SELECT-Abfrage setzt der Server keine S-Locks (Shared-Lock), so dass später eine andere Transaktion für das UPDATE einen X-Lock (Exclusive Lock) erfolgreich anfordern kann.

&gt;.. im Enterprise Manager ausgeführt habe, traten sehr viele Deadlocks auf ...

In diesem Fall hat die Session des Enterprise Managers die Datensätze der Ergebnismenge über ein S-Lock "gesperrt", so dass eine andere Transaktion die fürs UPDATE notwendigen X-Locks nicht mehr setzen kann. Für die Datenbank sind die Tabelle und der Index zwei "veschiedene" Datenbank-Objekte, die separat gesperrt werden müssen. Verschiedene Transaktionen dürfen nur S-Locks gleichzeitig auf das gleiche Datenbank-Objekt setzen.

Für das Vermeiden von Deadlocks gibt es mehrere Ansätze, die im Kern aber von der Datenbankstruktur und der auszuführenden Aktion abhängen: <br>
a) DIRTY READ für SELECTs, es werden keine S-Locks gesetzt <br>
b) Non-clustered Index entfernen. In diesem Fall benötigt der UPDATE-Aufruf nur ein X-Lock für den Datensatz. Allerdings ändert sich der Ausführungsplan, da der SQL Server aufgrund des fehlenden Indizes zum Tabellen-Scan greift. <br>
c) Covered Index (covered Query) verwenden. Wenn alle Ergebnisspalten der SELECT-Abfrage im non-clustered Index vorgefunden werden, benötigt der SELECT-Aufrufe nur einen einzigen S-Lock, so dass kein Deadlock auftreten kann. Da alle Daten direkt aus dem Index gelesen werden, muss der SQL Server nicht mehr auf die Tabelle zugreifen. <br>
d) Im Fehlerfall wiederholen. Wenn der Client den SQL-Fehler 1205 bemerkt, wiederholt er seine Anforderung <br>
e) Zum Isolation Level SERIALIZABLE wechseln und die Aktion in eine Transaktion kapseln<br>
f) Die SELECT-/UPDATE-Aufrufreihenfolge so abstimmen, dass alle Zugriffe immer in der gleichen Reihenfolge ablaufen

[Sylvia HGDS]

Vielen Dank,

ich habe bereits Isolation Level SERIALIZABLE in die Prozedur eingebaut, jedoch spürte ich keine Verbesserung.

Den Punkt C habe ich nicht ganz verstanden. Wie ist er gemeint?

Ist die Vorgehensweise Punkt D Üblich? Verlagert sich das Problem nicht eventl.

[Andreas Kosch]

Hallo,

&gt;c) Covered Index (covered Query) verwenden

Wenn die Tabelle sowohl einen Clustered Index als auch einen Nonclustered Index hat, muss der SQL Server bei Schreibzugrifen gleich zwei verschiedene Objekte (Tabelle und Nonclustered Index) sperren. Wenn allerdings beim SELECT alle angeforderten Spalten bereits im Nonclustered Index enthalten sind, muss der S-Lock beim SELECT nur auf den Index, aber nicht auf die Tabelle gesetzt werden. Das Deadlock-Zeitfenster und die Wahrscheinlichkeit wird somit kleiner (aber verschwindet niemals!).

&gt;Ist die Vorgehensweise Punkt D Üblich?

Ja. Denn der SQL-Fehler 1205 (Deadlock) kann ja auch auftreten, wenn zwei Sessions nur eine einzige Tabelle gleichzeitig lesen/beschreiben wollen. Allerdings ist das Deadlock-Zeitfenster in diesem Fall extrem schmal, so dass eine Wiederholung des Aufrufs mit sehr hoher Wahrscheinlichkeit erfolgreich sein wird.

&gt;...ich habe bereits Isolation Level SERIALIZABLE in die Prozedur eingebaut...

Man darf nicht alle genannten Alternativen gleichzeitig verbauen. Zum Beispiel hat SERIALIZABLE zwangsläufig eine Tabellen/Index-Sperre zur Folge, da innerhalb der eigenen Transaktion sogar das Anlegen eines neuen Datensatzes durch eine fremde Transaktion verhindert werden muss.
<br>
Wenn aber alle (!) Zugriffe auf die Datenbank (egal von welchen Clients) nur mit SERIALIZABLE ablaufen und der Punkt f) eingehalten wird, sollten keine Deadlocks auftreten, die sich nicht durch einfaches Wiederholen beseitigen lassen