„Die Transaktion (Prozess-ID 52) befand sich in einer Deadlocksituation und wurde als Deadlockopfer ausgewählt. Führen Sie die Transaktion erneut aus." Wenn Deine Anwender diese Meldung sehen, ist gerade ein Vorgang mittendrin abgebrochen worden, eine Buchung, eine Belegerfassung, ein Importlauf oder auch nur eine Auswertung. Die Eingabe ist weg, der Frust ist da. Und weil der Fehler scheinbar zufällig auftritt, mal beim Buchen, mal beim Drucken, mal wochenlang gar nicht, wird er gern als Mysterium abgetan.
Kleiner Service für die Comic-Fans vorweg: Mit Deathlok, dem Cyborg aus dem Marvel-Universum, hat der Deadlock nichts zu tun. Völlig abwegig ist die Verwechslung aber auch nicht, denn in beiden Geschichten taucht am Ende ein gnadenloser Vollstrecker auf und terminiert einen Prozess. Unserer trägt nur den deutlich unscheinbareren Namen Deadlock-Monitor, dazu gleich mehr.
Dabei ist ein Deadlock nichts Mysteriöses. Er ist ein völlig logisches Ergebnis aus zwei Abläufen, die sich gegenseitig blockieren. In diesem Beitrag zeige ich Dir, was dabei wirklich passiert, wie Du einen Deadlock mit zwei Query-Fenstern selbst nachstellst (ganz ohne Sage 100, nur mit SQL Server Management Studio) und mit welchen Maßnahmen Du das Problem dauerhaft in den Griff bekommst.
Was ein Deadlock ist, und was er nicht ist
Zuerst die wichtige Abgrenzung: Eine Blockierung ist normal. Wenn Transaktion A eine Zeile sperrt und Transaktion B dieselbe Zeile ändern will, wartet B einfach, bis A fertig ist. Das dauert vielleicht einen Moment, löst sich aber von selbst auf. Sperren sind kein Fehler, sie sind der Mechanismus, mit dem der SQL Server Deine Daten konsistent hält.
Ein Deadlock ist die Sackgasse daraus: Transaktion A hält Sperre 1 und wartet auf Sperre 2. Transaktion B hält Sperre 2 und wartet auf Sperre 1. Keine der beiden kann jemals weitermachen, egal wie lange sie warten. Der SQL Server erkennt solche Zyklen mit einem eigenen Überwachungsprozess, der standardmäßig alle 5 Sekunden nach ihnen sucht. Findet er einen, beendet er eine der beteiligten Transaktionen zwangsweise, in der Regel die, deren Rollback am billigsten ist. Diese Transaktion ist das „Deadlockopfer" und bekommt den Fehler 1205. Die andere läuft weiter, als wäre nichts gewesen.
Das Wichtigste vorab: Der Abbruch ist kein Bug des SQL Servers, sondern seine Rettungsmaßnahme. Ohne sie würden beide Transaktionen für immer stehen und nach und nach alle anderen Anwender mit in den Stau ziehen. Die eigentliche Ursache liegt immer in den Zugriffsmustern der Anwendungen.
Beispiel 1: Der klassische überkreuzte Zugriff
Das stellst Du in fünf Minuten selbst nach. Du brauchst nur eine Testdatenbank und zwei Query-Fenster im Management Studio. Zuerst die Spielwiese:
CREATE TABLE dbo.DemoKonten (
KontoNr INT PRIMARY KEY,
Saldo DECIMAL(18,2) NOT NULL
);
INSERT INTO dbo.DemoKonten (KontoNr, Saldo)
VALUES (1000, 500.00), (2000, 750.00);Jetzt simulieren wir zwei Umbuchungen, die in entgegengesetzter Reihenfolge auf die Konten zugreifen. In Fenster 1 startest Du diese Transaktion (das WAITFOR gibt Dir Zeit, das zweite Fenster zu starten, in echten Anwendungen ist das einfach die Zeit zwischen zwei Statements):
-- Fenster 1: bucht von Konto 1000 nach 2000
BEGIN TRAN;
UPDATE dbo.DemoKonten SET Saldo = Saldo - 100 WHERE KontoNr = 1000;
WAITFOR DELAY '00:00:10';
UPDATE dbo.DemoKonten SET Saldo = Saldo + 100 WHERE KontoNr = 2000;
COMMIT;Und während die 10 Sekunden laufen, in Fenster 2 die Gegenrichtung:
-- Fenster 2: bucht von Konto 2000 nach 1000
BEGIN TRAN;
UPDATE dbo.DemoKonten SET Saldo = Saldo - 50 WHERE KontoNr = 2000;
UPDATE dbo.DemoKonten SET Saldo = Saldo + 50 WHERE KontoNr = 1000;
COMMIT;Was passiert: Fenster 1 sperrt die Zeile von Konto 1000. Fenster 2 sperrt die Zeile von Konto 2000 und bleibt dann beim Zugriff auf Konto 1000 hängen, das gehört ja gerade Fenster 1. Nach Ablauf der 10 Sekunden will Fenster 1 an Konto 2000, das Fenster 2 hält. Der Kreis ist geschlossen, keiner kommt mehr weiter. Wenige Sekunden später schlägt der Deadlock-Monitor zu und eines der beiden Fenster zeigt:
Meldung 1205, Ebene 13, Status 51
Die Transaktion (Prozess-ID xx) befand sich in einer Deadlocksituation
und wurde als Deadlockopfer ausgewählt.
Führen Sie die Transaktion erneut aus.Das andere Fenster läuft sauber durch. Genau dieses Muster steckt hinter den meisten Deadlocks in der Praxis: Zwei Abläufe fassen dieselben Ressourcen in unterschiedlicher Reihenfolge an.
Beispiel 2: Erst lesen, dann schreiben
Das zweite Muster ist heimtückischer, weil beide Sitzungen scheinbar dasselbe Richtige tun. Ein typischer Ablauf in Anwendungen: Erst den aktuellen Stand lesen und prüfen, dann ändern. Führe dieses Skript in beiden Fenstern kurz nacheinander aus:
-- In BEIDEN Fenstern kurz nacheinander starten
BEGIN TRAN;
SELECT Saldo
FROM dbo.DemoKonten WITH (HOLDLOCK)
WHERE KontoNr = 1000;
WAITFOR DELAY '00:00:05';
UPDATE dbo.DemoKonten SET Saldo = Saldo + 10 WHERE KontoNr = 1000;
COMMIT;Beide Sitzungen bekommen für ihr SELECT eine Lesesperre auf dieselbe Zeile, das ist erlaubt, Lesesperren vertragen sich untereinander. Dann wollen beide auf eine Schreibsperre aufwerten. Und dafür muss jeweils die Lesesperre des anderen verschwinden, was nie passieren wird. Wieder ein perfekter Kreis, wieder Fehler 1205. Man nennt das einen Konvertierungs-Deadlock.
Die Lösung für dieses Muster ist ein Klassiker: Wer liest, um gleich danach zu schreiben, sollte das dem SQL Server sagen. Mit WITH (UPDLOCK) statt HOLDLOCK holt sich das SELECT von vornherein eine Update-Sperre. Die zweite Sitzung wartet dann einfach brav, bis die erste fertig ist, aus dem Deadlock wird eine harmlose kurze Blockierung.
Zum Aufräumen danach: DROP TABLE dbo.DemoKonten;
Woher weiß ich, wer sich verklemmt hat?
Der SQL Server protokolliert jeden Deadlock automatisch, Du musst nichts einschalten. Die Extended-Events-Sitzung system_health läuft ab Werk mit und zeichnet zu jedem Deadlock einen kompletten Graphen auf: Welche Sitzungen beteiligt waren, welche Statements liefen, welche Sperren auf welchen Objekten sie hielten und wer das Opfer war. So holst Du die letzten Deadlock-Graphen heraus:
SELECT CAST(event_data AS XML).query('(event/data/value/deadlock)[1]') AS DeadlockGraph
FROM sys.fn_xe_file_target_read_file('system_health*.xel', NULL, NULL, NULL)
WHERE object_name = 'xml_deadlock_report';Im XML siehst Du unter process die beteiligten Statements und unter resource-list die umkämpften Objekte. In Management Studio kannst Du die XML-Datei auch als .xdl speichern und grafisch öffnen, dann zeigt Dir SSMS die beiden Kontrahenten als Diagramm mit dem Opfer durchgestrichen. Für die Frage „wie oft passiert das bei uns überhaupt" reicht ein Blick auf den Leistungsindikator, den auch unser Performance-Script auswertet, dazu gleich mehr.
Maßnahmen: So wirst Du Deadlocks los
Deadlocks komplett auszuschließen ist in einem System mit vielen gleichzeitigen Anwendern unrealistisch. Aber Du kannst sie von „mehrmals täglich" auf „praktisch nie" drücken. Die Maßnahmen, sortiert nach Wirkung:
| Maßnahme | Warum sie wirkt |
|---|---|
| Gleiche Zugriffsreihenfolge | Wenn alle Abläufe Tabellen und Zeilen in derselben Reihenfolge anfassen, kann kein Kreis entstehen. Das ist die einzige Maßnahme, die Deadlocks strukturell unmöglich macht. Relevant vor allem für eigene Entwicklungen und Schnittstellen. |
| Transaktionen kurz halten | Je kürzer eine Transaktion Sperren hält, desto kleiner das Zeitfenster für eine Verklemmung. Keine Benutzereingaben, keine externen Aufrufe, keine langen Berechnungen innerhalb einer offenen Transaktion. |
| Passende Indizes | Fehlt der Index, muss der SQL Server für ein UPDATE die halbe Tabelle durchsuchen und sperrt dabei weit mehr Zeilen als nötig. Jede unnötig gesperrte Zeile ist eine potenzielle Kollisionsfläche. Mehr als einmal haben wir Deadlock-Serien allein durch einen fehlenden Index erklärt. |
| UPDLOCK beim Lesen vor dem Schreiben | Verhindert Konvertierungs-Deadlocks wie in Beispiel 2. Gilt für eigene Prozeduren und Integrationen, die erst prüfen und dann ändern. |
| Aktuelle Statistiken und Wartung | Veraltete Statistiken führen zu schlechten Ausführungsplänen, schlechte Pläne zu Scans, Scans zu breiten Sperren. Regelmäßige Wartung ist auch Deadlock-Vorbeugung. |
| Wiederholungslogik in eigener Software | Fehler 1205 sagt es wörtlich: „Führen Sie die Transaktion erneut aus." Eigene Anwendungen und Schnittstellen sollten den Fehler abfangen und die Transaktion nach kurzer Wartezeit automatisch wiederholen, statt dem Anwender eine Fehlermeldung zu zeigen. |
Ein Wort zu Snapshot-Isolation (RCSI): Damit lesen SELECTs eine Versionskopie statt zu sperren, was Lese-Schreib-Konflikte deutlich entschärft. Bei Standardsoftware wie der Sage 100 ist das aber keine Einstellung zum einfach mal Umlegen. Sie verändert das Sperrverhalten der gesamten Datenbank und gehört vorher mit dem Hersteller beziehungsweise Deinem Sage-Betreuer abgestimmt und getestet.
Und in der Sage 100?
In der Sage 100 kannst Du die Abfragen des Standards natürlich nicht umschreiben. Trotzdem bist Du Deadlocks dort nicht ausgeliefert, denn die drei größten Hebel liegen in Deiner Hand: kurze Sperrzeiten durch einen schnellen Server (RAM, Storage, Wartung), gesunde Indizes und Statistiken durch einen ordentlichen Wartungsplan, und saubere Zugriffsmuster in allem, was Ihr selbst an Schnittstellen, AppDesigner-Anpassungen und Zusatztools betreibt. Ein träger Server verlängert jede Transaktion, und je länger die Sperren stehen, desto öfter verklemmen sie sich. Deadlock-Häufigkeit ist damit auch ein Performance-Symptom.
Genau deshalb misst unser SQL-Performance-Test beides mit: Er liest die Deadlock-Rate seit dem letzten Serverstart aus (als Wert pro Tag, bewertet mit OK, WARNUNG oder KRITISCH) und zeigt Dir mit den Lock-Hotspots, an welchen Sage-Tabellen sich Deine Anwender tatsächlich stauen, in Wartesekunden pro Tag. Damit siehst Du auf einen Blick, ob Du ein Deadlock-Problem hast und wo Du ansetzen musst.
Das Tool kostenlos herunterladen
Das komplette Performance-Script inklusive grafischer Oberfläche bekommst Du kostenlos gegen eine Newsletter-Anmeldung. Was alle Messwerte im Einzelnen bedeuten, erklärt der ausführliche Beitrag zum Tool.
Deadlocks im Griff? Wir helfen.
Wenn bei Dir regelmäßig Zugriffe mit Fehler 1205 abbrechen, egal ob beim Buchen, in der Belegerfassung, beim Drucken oder in einer Schnittstelle, muss das nicht so bleiben. Wir analysieren die Deadlock-Graphen Deines Servers, finden die beteiligten Abläufe und beheben die Ursache, ob sie im Server, in der Wartung oder in einer Schnittstelle steckt. Auch bei allen anderen SQL-Server-Themen rund um die Sage 100 sind wir gern Dein Ansprechpartner.