Vorwort
Ich hatte mal wieder das Vergnügen zu analysieren, warum sich auf einem MSSQL Server drei unterschiedliche Applikationen im Sekundentakt mit Deadlocks blockiert haben. Die Literatur dazu ist vielfältig, hat mir im Speziellen aber wenig geholfen. Daher fasse ich hier mal in einfachen Worten zusammen, wie ich die Deadlocks aufgelöst habe. Das Ganze hat keinen Anspruch auf Vollständigkeit oder korrektes „Wording“.
Voranalyse
Deadlocks können immer auftreten und stellen normalerweise kein Problem dar (fehlertolerante Anwendungen vorausgesetzt). Zu Problemen kommt es bei gehäuften Auftreten. Die Erkennung und Behandlung von Deadlocks kostet einfach Zeit. Im „schlimmsten“ Produktivfall dauerte es über 10 Sekunden bis ein Deadlock in der Anwendung aufschlug.
Wenn gleichzeitig mehrere Deadlocks auftreten, muss man diese nacheinander „auflösen“. Wenn man „Glück“ hat, lösen sich „Folgedeadlocks“ gleich mit auf. Um das „richtige“ Deadlock zu finden unterteile ich die Deadlocks in zwei Klassen:
- Ablauf-Deadlock: der klassische Fall. Ein Begin Transation und Commit sind nötig. Diese gehe ich meistens erst im zweiten Schritt an.
- Zugriffs-Deadlock: Diese Deadlocks werden dadurch verursacht wie dar SQL-Server auf die Daten zugreift. Immer wenn ein Entwickler sagt „Der gleiche Code produziert unter Oracle keine Deadlocks“, hat man so einen Kandidaten. Meistens kann man diese ohne größere Eingriffe in die Applikation auflösen.
Zuverlässiger erkennt man diese Zugriffs-Deadlocks jedoch an dem beteiligten QueryPlan. Dazu muss man wissen, dass der SQL-Server jedes Statement mit einer eigenen Transaction „sichert“ um konsistent auf die Datenbestände zuzugreifen. Als Konsequenz daraus folgt: man kann nicht auf Daten zugreifen (Delete, Update, Insert) ohne Locks zu erzeugen. Daraus folgt wiederum, Statements die viele IO-Ops haben (lesen, schreiben), erzeugen auch viele Locks, die Chance auf ein Deadlock steigen.
Konkret: Es wird folgendes Statement ohne explizite Transaktion von mehreren Prozessen konkurrierend ausgeführt.
UPDATE test_table SET date_value= @Date WHERE name= @Name
In der Theorie (und bei Oracle) darf hier nie ein Deadlock auftreten. Im schlimmsten Fall aktualisieren alle Prozesse die gleichen Rows und der „letzte“ Prozess gibt „die neue Warheit“ vor. Unter MS-SQL hagelt es Deadlocks. Das Statement kann nicht in eine atomare Operation umgesetzt werden. Erst müssen die zu verändernden Rows gefunden und anschließend die Änderung durchgeführt werden. Um einen konsistenten Zugriff auf die Daten zu bekommen benutzt der SQL-Server Shared Locks. Wenn die Spalte „name“ nicht indiziert ist, wird es einen FullTableScan geben. Ein SharedLock „wandert“ durch die Tabelle (und in Abhängigkeit des verwendeten Isolationslevels bleiben die Locks bestehen), dort wo die Where-Clause matched gibt es ein UpdateLock und wenn es dumm kommt, blockieren sich dabei zwei Prozesse zu Tode.
Zusammenfassend formuliert: jede IO-Operation erzeugt ein Lock. Damit hat man gleich zwei Gründe diese zu vermeiden. Das bedeutet aber auch, dass die gleichen Maßnahmen zur Zugriffsoptimierung auch bei Deadlock-Problemen helfen können.
Statement tuning
Der erste Ansatz zielt auf die oben beschriebene IO-Ops/Lock-Problematik ab. Man lässt sich via Error Trace die Statements raus, die an den Deadlocks beteiligt sind und ermittelt sich die Kandidaten mit der höchsten IO-Last (entweder über die Cached Plans-Statistik oder über eine einfache Plananalyse). Diese Statements tuned man anschließend mit passenden Indizes.
Dummerweise macht einem der QueryOptimiser bei kleinen Tabellen einen Strich durch die Rechnung. Der Optimizer versucht (vereinfacht gesagt) die IO-Ops runter zu bekommen. Bei kleinen Tabellen kann es sinnvoller sein einen Full Table Scan zu machen, anstatt einen Index-Seek und Key-Lookup zu machen. Das hat mir schon ein paar mal den Tag versaut.
Ein Beispiel: Eine kleine Tabelle (nur eine Page) wird zur Kommunikation zwischen Prozessen benutzt. Ein Index ist so eingerichtet, das jeder Prozess direkt auf die ihm zugeordneten Rows zugreifen kann. Beim Auslesen der „zu verarbeitenden Werte“ müssen alle Spalten selektiert werden. Aus diesem Grund war der QueryOptimizer auf normalem Weg nicht dazu zu bringen, den Index für die Where-Bedingung zu benutzten. Ein Umstellen der Abfrage nach der Art „SELECT PrimaryKey FROM Table WHERE“ mit anschließendem „SELECT * FROM Table WHERE PrimaryKey =“, um die Nutzung des Index „sanft“ zu „erzwingen“, war nicht möglich. Es blieb nur die Möglichkeit der Applikationen eine View unter zu jubeln, die mittels WITH(INDEX ( ))-QueryHint die Nutzung des Index erzwingt. In Folge dieser Optimierung ging die IO-Last um 20% hoch und die Ausführungszeit des Statements stieg auch um wenige MilliSekunden. Der QueryOptimizer hatte also Recht, den Index nicht zu nutzen ist performanter. Mit Index-Nutzung treten jedoch keine Deadlocks mehr auf (vorher gab es mehre Deadlock alle 10 Sekunden), der Gesamtdurchsatz über die Tabelle stieg dadurch um den Faktor 10. Die höhere IO-Last war damit zu ignorieren.
Eine weitere Möglichkeit der Optimierung ist die Separierung der Indizes. Das ganze zielt in die gleich Richtung wie der Ansatz mit dem Index-Erzwingen. Man führt mehr Indizes als eigentlich nötig um zu verhindern das ein Lese-Lock auf einem Index gelegt wird, der für den Schreibzugriff benötigt wird bzw. umgekehrt. Eine Änderung im Clustered-Index/RawTable soll sich nicht auf eine LeseOperation durchschlagen. Das ganze funktioniert nur wenn die beiden konkurrierenden Statements unterschiedliche Where-Clauses verwenden oder man eine Indexnutzung erzwingt.
Zugriffe und Abläufe optimieren.
Auch wenn die Entwickler es gerne leugnen, verneinen, ignorieren und unzählige Frameworks einführen und nutzen um es zu kaschieren, am Ende entscheiden sie mit ihrer Art des Zugriffs, wie gut der Datenzugriff erfolgen kann. Wenn man also alle Scan – Operationen beseitigt hat, Indizes separiert und optimiert hat und immer noch Deadlocks auftreten, ist es an der Zeit mit dem Entwickler zu Reden und die Abläufe und Datenzugriffe in der Applikation zu optimieren.
Relativ einfach aufzulösen sind Konstrukte der folgenden Art: Lese N Einträge aus einer Tabelle, markiere sie, verarbeite sie und quittiere die Verarbeitung. Im schlimmsten Fall alles in einer großen Transaktion. Es kann performanter sein, nur einen Datensatz zu lesen und zu markieren (inklusive COMMIT) und anschließend in einer neuen Transaktion die Verarbeitung durchzuführen und zu Quittieren. Da man immer nur einen Datensatz sperrt, ist die Chance eine Deadlocks geringer und der Durchsatz kann steigen.
In die Kategorie „schwer zu detektieren aber einfach zu lösen“ fällt die Optimierung der Lock-Reihenfolge bzw. in welcher Reihenfolge wird welche Resource blockiert. Dies kann durch einfaches umsortieren der Statements erfolgen oder mit LockHints (UPDLOCK, XLOCK). Es kann sinnvoll sein möglichst früh ein Update- oder Exclusive-Lock zu holen um spätere Verklemmungen oder gar Deadlocks zu verhindern. Einmal gefunden lassen sich diese Änderung meist ohne großen Aufwand in der Applikation umsetzten.
Richtig kompliziert wird es erst bei Zugriffsoptimierung.
Eins vorweg: eine Zugriffs-Optimierung kann man auch ohne Zutun der Entwickler umsetzten. Mittels View oder „QueryPlan-Forcing“ kann man der Applikation immer die Zugriffs-Variante unterschieben, die man für „optimaler“ hält. Allerdings muss man diese Optimierung bei jedem Software-Update erneut vornehmen. Sobald der Entwickler sich dieser Optimierung bewusst ist (und sie auch versteht) laufen die Updates wesentlich entspannter.
Im Kern geht es auch in dieser Kategorie darum den Zugriff auf Daten zu separieren. Dabei unterteilt man Vertikale und Horizontale Partitionierung.
Einen Nachteil hat die gezielte optimieren jedoch. Man muss aufpassen, dass man eine Seite nicht übervorteilt. Wenn man die Zugriffe einer Applikation stark optimiert hat (über indizies oder Zugriffsanweisungen), kann es passieren, das die konkurrierende Applikation „verhungert“. Also immer beide Seite Betrachten und optimieren.