MS SQLServer 7 / ISAPI / deadlock

[Guido Franke]

Bei einer Isapianwendung taucht hin und wieder folgender Fehler auf:

Your transaction (process ID #9) was deadlocked with another process and has been chosen as the deadlock victim. Rerun your transaction

Unter welchen Bedingungen kann den im Normalfall ein solcher Deadlock enstehen?

Zur Anwendung:
Der Datebzugriff erfolgt mit TADOQuery. Im Datenmodul gibt es zwei ADOConnection Objekte und zwei TADOQuery (jeweils verbunden). Bei jedem Request wird
ADOConnection1 geöffnet und am Ende geschlossen.
Bei einer Funktion wird eine Transaktion gestartet und nur in diesem Fall wird zusätzlich die zweite ADOConnection geöffnet und nach beenden der Transaktion geschlossen.
Der Deadlock taucht allerdings in einer Funktion auf, in der auf eine Tabelle (update) zugegriffen wird, die gar nicht in der genannten Transaktion betroffen ist, allerdings ist das eine Tabelle die recht häufig frequentiert wird (lesen, hinzufügen, update).

Hat jemand Vorschläge was da falsch läuft???

[Andreas Kosch]

Hallo,

ein Deadlock kann beim SQL Server 7/2000 in mindestens 2 Fällen auftreten:

Fall 1: <br>
Zwei Benutzer (Transaktionen) führen ein UPDATE/INSERT auf <b>zwei Tabellen</b> aus, wobei nicht die gleiche Reihenfolge eingehalten wird. Beispiel: Benutzer A schreibt zuerst in die TabelleA und will dann in die TabelleB schreiben, allerdings hat Benutzer B zuerst die TabelleB mit seinem Schreibzugriff gesperrt und will nun in die TabelleA schreiben, die jedoch vom Benutzer A gesperrt wurde. Beim Einlesen eines Datensatzes setzt der SQL Server ein S-Lock (Shared Lock), während beim Schreiben ein X-Lock (Exclusive Lock) gesetzt wird. Ein X-Lock ist jedoch nur dann erfolgreich, wenn keine andere Transaktion ein S-Lock gesetzt hat. Somit blockieren sich Benutzer A und B gegenseitig - die typische Deadlock-Situation liegt vor.

Fall 2:<br>
Zwei Benutzer (Transaktionen) führen ein SELECT/UPDATE/INSERT auf <b>eine Tabelle</b> aus, die einen <i>non-clustered Index</i> enthält. Der Benutzer A aktualisiert nur eine Spalte eines Datensatzes, während der Benutzer B eine Abfrage nach einem Wert einer indizierten Spalte startet. Jede Transaktion der beiden Clients betrifft nur einen einzigen Datensatz in einer einzigen Tabelle - und doch können Deadlocks auftreten. Die Deadlock-Gefahr besteht, weil beide Benutzer gemeinsam auf den Non-clustered Index zugreifen, allerdings mit unterschiedlichen Anforderungen. Ein Non-clustered Index speichert seine Daten unabhängig von der Tabelle in einen eigenen Datenbankobjekt, somit besteht diese Tabelle aus 2 Objekten, die vom SQL Server für das Locking als zwei völlig unabhängige Objekte betrachtet werden.

Während man beim Fall 1 einen Deadlock im Programm vermeiden kann (immer die gleiche Reihenfolge der Zugriffe einhalten), sieht die Situation im Fall 2 schlechter aus. Hier gibt es verschiedene Lösungsstrategien für dieses Problem: <br>
- DIRTY READ für SELECTs, es werden keine S-Locks gesetzt <br>
- 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>
- 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>
- Im Fehlerfall den Aufruf wiederholen. Wenn der Client den SQL-Fehler 1205 bemerkt, wiederholt er seine Anforderung.

P.S: Falls der Fall 2 vorliegt, würde ich zuerst den Aufruf im Fall des Fehlers 1205 sofort wiederholen (das Deadlock-Zeitfenster ist beim SQL Server 7/2000 nur Bruchteile einer Millisekunden gross, so dass die Wahrscheinlichkeit eines nochmaligen Deadlocks gering ist).
&#10

[Guido Franke]

Danke erst mal für die Tips. Leider ist das Problem immer noch.
Es sieht immer wie eine Serie von deadlocks aus, manchmal gefolgt von Timeouts wobei nicht mehr als 3 gleichzeitige Zugriffe auf die Anwendung existieren. Hier ein Logauszug.
12:30:52 AM; Funktion001 Your transaction (process ID #8) was deadlocked with another process and has been chosen as the deadlock victim. Rerun your transaction
12:30:56 AM; Funktion001 Your transaction (process ID #8) was deadlocked with another process and has been chosen as the deadlock victim. Rerun your transaction
12:30:58 AM; Funktion001 Your transaction (process ID #8) was deadlocked with another process and has been chosen as the deadlock victim. Rerun your transaction
12:31:05 AM; Funktion001 Your transaction (process ID #9) was deadlocked with another process and has been chosen as the deadlock victim. Rerun your transaction
12:31:11 AM; Funktion001 Your transaction (process ID #8) was deadlocked with another process and has been chosen as the deadlock victim. Rerun your transaction
12:31:47 AM; Funktion001 Timeout expired
12:32:36 AM; Funktion001 Timeout expired
12:33:18 AM; Funktion001 Timeout expired
12:34:00 AM; Funktion001 Timeout expired
12:34:43 AM; Funktion001 Timeout expired

In der Funktion001 wird ein update auf eine Tabelle gemacht (TADOQuery/ExecSQL).
- Wenn dieser Fehler kommt, sollte doch der deadlock aufgehoben sein, oder? In der Anwendung sieht es allerdings so aus, dass der deadlock erhalten bleibt. Nach meinen Beobachtungen geht dieser Serie von deadlocks eine oder mehrere Transaktionen auf andere Tabellen voraus, die aber alle ohne Fehler mit CommitTrans abgeschlossen wurden.

Wie kann ich denn genau erfahren, wobei der deadlock ensteht

[Andreas Kosch]

Hallo,

ein LOCK_TIMEOUT-Fehler setzt eine Transaktion nicht automatisch über ROLLBACK zurück. Sobald der Fehlerwert 1222 vorgefunden wird, sollte das Client selbst einen ROLLBACK auslösen.

Es ist wichtig, den Auslöser des Deadlocks zu ermitteln. Über die Spalte <b>open_tran</b> der Systemtabelle <b>sysprocesses</b> kann man dazu die Anzahl der offenen Transaktionen abrufen, wobei die Spalte <b>spid</b> den Wert zurückliefert, über den mit der Abfrage <b>dbcc inputbuffer</b> der Text dieser SQL-Anweisung ermittelt werden kann:
<pre>
SELECT spid, open_tran FROM master..sysprocesses WHERE open_tran > 0
dbcc inputbuffer(spid-Wert)
</pre&gt

[Guido Franke]

Hi,
für den Fehlerfall habe ich die dbcc Abfrage in die ISAPI DLL eingebaut. Folgendes Ergebnis kam:<br>
EventType; Parameters; EventInfo<br>
RPC ; 4 ; sp_executesql<br>
RPC ; 0 ;<br>
<br>
Also es gab 2 Transaktionen. Ja und welche waren das nun?? Was sagen mir diese Werte?

Einen "lustigen" Effekt gab es noch dabei. Für die Ausgabe habe ich in dem Webmodul eine 2. ADOConnection angelegt und als DefaultDatabase master angegeben. Nach dem diese Connection genutzt wurde, war auch für das andere Connectionobject die master DB die aktuelle ;-)

[Andreas Kosch]

Hallo,

die Abfrage dbcc inputbuffer(spid-Wert) würde ich aus dem <i>Query Analyzer</i> heraus von Hand absetzen, wenn die ISAPI einen Deadlock verursacht. Als Parameter muss man ja die spid verwenden, die die vorherige Abfrage von sysprocesses zurückgeliefert hat

[Guido Franke]

Hi,

das Problem der deadlocks und timeouts war erst behoben, nachdem ich KeepConnection von ADOConnection auf false gesetzt habe und bei einigen Abfragen den Cursor auf clUseServer gesetzt habe. Die ADOConnection öffne und schließe ich jetzt praktisch bei jedem Datenbankzugriff (vorher hatte ich die Connection bei Beginn eines Request geöffnet, diverse Datenbankzugriffe darüber gemacht und am Ende des Request geschlossen).

Bis dan