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.
- Vertikale Partitionierung: Eine große Tabellen (viele Rows) wird in mehre kleine Tabellen zerlegt. Ziel ist, das die Partitionen so angelegt sind, dass die Applikation nur selten mehrere Partitionen selektieren muss. Ein Beispiel für so etwas sind Auftragsdaten. In einem System gibt es meist 3 Arten von Auftragsdaten: Die Vorschau, die aktiven Aufträge und die archivierten Aufträge. Die Vorschau und die aktiven Aufträge halten sich mengenmäßig meist in Grenzen, die Archivdaten explodieren hingegen meistens. Die BuisinessLogic wird aber fast immer nur die aktiven Aufträge selektieren. Selbst der Benutzer wird sich hauptsächlich um die aktiven Aufträge kümmern und nur selten auf die Archivdaten zugreifen. Die Folge: Es existieren unnötige große Indizies, die Datenzugriffe erfolgen über viel zu große Datenmengen, die Latenz geht hoch, die Chance für Deadlocks steigen. Sinniger ist es die Archivdaten von den aktiven Daten zu trennen (zwei oder drei Tabellen) und die Datenbestände ganz bewusst in der BL zu überführen (delete aus der QuellTabelle – Insert in die FolgeTabelle) und nur im Sonderfall alle Daten zu Selektieren.
- Horizontale Partitionierung: Gerade moderne Frameworks (z.B. das EntityFramework) verleiten dazu Daten aus unterschiedlichen logischen Kontexten physikalisch in einer Entität abzulegen (Schlagwort: Vererbung und Discriminator). Die Folge sind „sehr Breite“ Tabellen.
Ein Beispiel: Eine (kleine, Hobby) Autovermietung führt in einer Tabelle die Basis-Fahrzeugdaten (Baujahr, Typ, Modell …), Lebensdaten (letzter Kilometerstand, letzte Reparatur, aktueller Benutzer …) als auch (weil es so Fancy ist) die aktuellen Telemetriedaten (GPS – Speed, Coords, …). Das sind eine Reihe von Zugriffen aus unterschiedlichen Kontexten mit vollkommen unterschiedlicher Zugriffs-Art und Frequentierung.- Die Basis-Daten werden einmal Geschrieben, defacto nie geändert und maximal gelöscht, dafür aber sehr häufig in großen Blöcken gelesen (Auflistung der Fahrzeuge)
- Die Lebensdaten werden regelmäßig aktualisiert und gezielt (über Fahrzeugkennung – indiziert) ausgelesen.
- Die Telemetriedaten werden permanent (scheiß auf Datenvolumen) geschrieben und nur im Sonderfall ausgelesen.
Das kann man (bei großen Tabellen) kaum unter einen Hut bringen. Sinnvoller ist es die Daten in unterschiedliche Tabellen Auszulagern und über „1 zu 1“-Beziehung zu verknüpfen. So schlagen die häufigen Updates nicht auf die häufigen Reads durch (und umgekehrt).
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.