SQL Server In-Memory OLTP – Isolation Level Beispiele
Wie schon mal von mir erwähnt, erlebe ich bei den Teilnehmern meiner SQL Server In-Memory OLTP Workshops die meisten Schwierigkeiten bei der Verwendung der “neuen” Isolation Level. Zwar klingen für viele Entwickler die Level SNAPSHOT, REPEATABLE READ und SERIALIZABLE vertraut, nur schon bei den SQL Server “disk-based” Tabellen haben die wenigsten diese aktiv genutzt. Standard ist häufig READ COMMITED und das ist auch gut so. Die Tatsache, dass In-Memory OLTP aber ohne Sperren auskommt, fühlt sich für die meisten Entwickler erstmal befremdlich an.
Zwar haben wohl die meisten Interessierten schon mal die Microsoft PowerPoints mit einer Erklärung des Version Stores gesehen und sich gefreut, wie schön da Versionen von Zeilen nebeneinander koexistieren können. Nur wie sieht das in der Praxis für den Entwickler aus?
Der deutsche Begriff für die Technologie In-Memory OLTP “Arbeitsspeicheroptimierung” trifft es irgendwie nicht ganz. Zwar wird der Arbeitsspeicher wesentlich optimierter genutzt, dennoch klingt das im Deutschen schon unspektakulär, oder?
In-Memory OLTP wurde entwickelt, um äußerst schnelle Lösungen zu ermöglichen. Auch wenn man mittels altbekanntem T-SQL mit dieser Engine arbeitet und diese auch hybride Lösungen mit den nun “disk-based” Tabellen (also den guten alten 8kb-lastigen Strukturen) ermöglicht, so ist es doch eine durch und durch neue Engine. Und vieles was die gute alte Engine an Schlamperei bei der Programmierung einfach mal geschluckt hat, funktioniert nun nicht mehr. Dafür ist In-Memory OLTP einfach zu spezialisiert auf Geschwindigkeit. Spontane Flexibilität wird da schwierig.
Zur Erinnerung: Isolation Level bestimmen in der Welt der pessimistischen Level wie lange ein Shared Lock auf einem Datensatz oder gar auf einen Bereich aufrecht erhalten wird. Wenn ein Datensatz exklusiv gesperrt ist, dann kann er nicht gelesen und erst recht nicht geändert werden. Also, es kommt in einer Transaktion ein SELECT daher und verhindert durch den verwendeten Isolation Level, dass andere Transaktionen Daten ändern oder ergänzen können. Die Transaktion mit der Änderung (UPDATE, DELETE usw.) bleibt einfach hängen (Blocking genannt…), da ja kein rankommen an die Daten ist. Ist ja ein Shared Lock drauf.
In der optimistischen Welt der In-Memory OLTP Engine ist das alles anders. Und ja, auch schon früher konnte man mit optimistischer Isolation arbeiten, nur haben das die wenigsten Entwickler genutzt. Die Wahl hat man nicht mehr, wenn man wenn es nun “ein wenig” schneller haben möchte.
Durch den Version Store von In-Memory OLTP können nun mehrere Versionen einer Datenzeilen parallel existieren und jede Transaktion sieht die für sich gültigen Zeilen. Liest sich doch ganz einfach, oder? Wie ist das jetzt aber bei Änderungen? Und wenn jeder seine “Wahrheit” sieht, wer hat dann am Ende recht?
Abgesehen von zwei Ausnahmen (Wobei es sich eigentlich nur um eine handelt…), lässt der SQL Server erstmal alle Änderungen zu. Erst beim COMMIT einer Transaktion wird geprüft, ob es Abhängigkeiten gibt, welche einen Fehler auslösen müssen. Und, um es nun noch ein wenig interessanter zu gestalten, im Gegensatz zum pessimistischen Modell, können nun SELECTs im Nachhinein ungültig sein.
Gehen wir ein paar Beispiele einfach mal durch.
Hier sehen wir, dass ein einfaches SELECT problemlos läuft. Nichts anderes haben wir erwartet, oder?
Was ist das? Sobald wir eine explizite Transaktion aufmachen und ein SELECT ausführen, gibt es einen Fehler.
Accessing memory optimized tables using the READ COMMITTED isolation level is supported only for autocommit transactions. It is not supported for explicit or implicit transactions. Provide a supported isolation level for the memory optimized table using a table hint, such as WITH (SNAPSHOT).
Wir lernen aus der Fehlermeldung, dass wir einen Isolation Level benötigen, wenn wir mit transaktionalen Klammern arbeiten. In den folgenden Beispielen setze ich daher einen Table Hint (WITH SNAPSHOT).
Und schon funktioniert unsere einfache Abfrage.
Kommen wir zu spannenderen Dingen. Wir öffnen eine weitere Session und führen ebenfalls ein SELECT in einer Transaktion aus. Wichtig hierbei, wir schließen diese noch nicht.
Probieren wir mal ein einfaches UPDATE.
Ups, ja klar. Ein UPDATE muss ja auch lesen was es so löschen möchte. Also brauchen wir auch hier einen Isolation Level.
Und schon funktioniert das UPDATE in Session 63. Als wir versuchen die selbe Zeile zu ändern, laufen wir in einen Fehler. In der pessimistischen Welt wären wir hier hängen geblieben.
The current transaction attempted to update a record that has been updated since this transaction started. The transaction was aborted.
Wir lernen daraus, dass die SQL Server In-Memory OLTP Engine aufpasst, dass wir nichts überschreiben, was in einer anderen Session bereits geändert wurde.
Bei einem DELETE hätten wir übrigens den gleichen Fehler erhalten.
So, kommen wir zu einem INSERT.
Erst machen wir in 63 ein INSERT mit der ID 4 und dann ein INSERT in 62 mit der ID 4. Und schon haben wir zwei Zeilen mit der identischen ID. Autsch.
Nun kommt es darauf an, wer zuerst ein COMMIT ausführt.
Und wir sehen, dass es nur einen geben konnte (Sorry…). Dennoch gab es einen Moment in der Vergangenheit, in dem mindestens zwei Wahrheiten existierten. Damit sollte der Entwickler umgehen lernen.
Und wo wir gerade bei zwei Wahrheiten sind. Können wir unsere zwei Versionen einer Zeile jeweils auch ändern?
Nein, das schlägt auch gleich fehl.
Wie schaut es noch generell mit DELETES aus?
Wir haben wieder zwei Sessions. Beide zeigen das gleiche Resultset.
Nach einem DELETE in 63 verschwindet dort unsere ID 4. In Session 62 ist diese aber noch dabei, da diese auf eine alte Version der Zeile zeigt.
Das bleibt so lange, wie die Session 62 ihre Transaktion nicht schließt. Ein COMMIT in 63 hilft da nicht weiter.
Erst das Beenden der Transaktion in 62 zeigt auch hier die letzte wahrhaftige Wahrheit. Auch dessen sollte sich ein Entwickler bewusst sein.
Nun kommen wir zu den wirklich interessanten Dingen. Steigern wir den Isolation Level und gehen auf REPEATABLEREAD. Bitte dran denken, dass die Verwendung der höheren Level unter Last auch messbar wird!
Wir starten wieder von vorne. In 63 öffnen wir eine Transaktion und lesen unsere Tabelle. In 62 führen wir ein UPDATE durch. Die Transaktion sparen wir uns, damit entfällt auch die Notwendigkeit für einen Table Hint.
Nun haben wir in 63 die alte Version und in 62 sehen wir schon die neue Version.
Unsere Transaktion liest im Step 21 aber fleißig wiederholt unsere Tabelle. Und bekommt den alten Stand geliefert.
Und nun wird es unangenehm. Ein COMMIT in der Session 63 führt zu einem Fehler. Aber wir haben doch nur gelesen?! Warum?
Weil die SQL Server In-Memory OLTP Engine ERST beim COMMIT eine Überprüfung durchführt, ob die ISOLATION LEVEL denn auch eingehalten worden sind. Wie schon erwähnt, kann also ein SELECT im Nachhinein fehlschlagen. Der Entwickler hat auf Basis der Daten gearbeitet, muss sich aber bewusst sein, dass er evtl. schon in einer alten Realität agierte und ihm sein Code um die Ohren fliegt.
Meldung 41305, Ebene 16, Status 0, Zeile 96
The current transaction failed to commit due to a repeatable read validation failure.
Wie schaut es mit INSERTs und REPEATABLEREAD aus? Probieren wir das einfach mal aus.
In 63 öffnen wir die Transaktion und schauen uns die Daten an. Dann schreiben wir in 62 eine neue Zeile, welche dort auch sichtbar ist.
In Step 25 sehen wir, dass uns in 63 weiterhin die alte Version gezeigt wird.
Erst ein Schließen der Transaktion führt dazu, dass wir den aktuellen Stand sehen. INSERTs führen also NICHT zu Fehlern bei REPEATABLEREAD. Dafür können neue Zeilen unvermittelt erscheinen.
Auf zum nächsten Level. Alles noch mal, nun mit SERIALIZABLE.
In 63 sehen wir unsere Daten und in 62 fügen wir eine Zeile hinzu. In Step 29 schauen wir mal wieder nach unseren Daten und sehen die Zeile nicht.
Erst das COMMIT in 63 zeigt, was dieser Isolation Level bewirkt. Er verhindert, dass im Lesebereich, der war hier jetzt ganz grob die ganze Tabelle, alte Versionen gelesen werden können und führt nach Ende der Transaktion zu einem Fehler.
Wichtig, nicht das INSERT schlägt fehl, sondern das SELECT, welches in der Vergangenheit einen ungültigen Stand der Daten las. Nochmals, In-Memory OLTP prüft erst beim COMMIT, ob die Isolation Level eingehalten wurden!!!
Meldung 41325, Ebene 16, Status 0, Zeile 128
The current transaction failed to commit due to a serializable validation failure.
Diese noch recht einfachen Beispiele zeigen schon sehr deutlich, dass existierender T-SQL Code in aller Regel gründlich überarbeitet werden muss für die Nutzung von In-Memory OLTP. Ja, das bedeutet Aufwand. Nur dieser ist meiner Erfahrung nach mehr als gerechtfertigt, wenn man den deutlichen Performance Gewinn für seine Lösung sieht.
Ich habe bewusst unterwegs ein paar technische Details übersprungen, damit sich das Thema halbwegs zügig liest. Dieser Post ist nicht als Schulungsersatz für die Verwendung von In-Memory OLTP gedacht, sondern um bewusst zu machen, dass es da Arbeit gibt.
Für viele Entwickler (und Entwicklerinnen…) mag sich nach dem Lesen dieses Posts In-Memory OLTP nun noch ein wenig schräger anfühlen als schon vorher, nur gerade aus den gezeigten Mechaniken zieht diese Engine ihre unglaubliche Geschwindigkeit. Probiert sie einfach mal aus!