Optimistisches Concurrency Control als Rettungsanker?

Wer mit Datenbanken arbeitet, verwendet zwangsläufig Transaktionen und muss dabei Trade-Offs berücksichtigen. Optimistisches Concurrency Control ist der Versuch, die Trade-Offs der Datenbank zu mildern. Es lohnt sich, darüber nachzudenken, wenn man den Lesevorgang und den anschließenden Schreibvorgang in zwei unterschiedlichen Datenbanktransaktionen durchführen muss, beispielsweise, weil dazwischen eine langdauernde Benutzerinteraktion stattfindet. 

So funktioniert’s: Wir ergänzen jede Tabelle mit einem zusätzlichen Feld, beispielsweise version int. Die Applikation liest immer auch die Version mit. Und jeder Update wird ergänzt, beispielsweise so:

update test set ... version = version + 1 where version = alte_version; Statt alte_version setzen wir den Wert ein, den wir vorher gelesen haben.

Die Überlegung ist einfach: So können wir sicher stellen, dass wir mit unserem Update-Befehl nicht die Daten verändern, die in der Zwischenzeit eine anderer User verändert hat.

Die Erwartung ist ebenso einfach formuliert: Wir möchten nicht nur keine Updates verlieren, sondern sicher sein, dass es zu keinen Deadlocks und zu keinen blockierten Transaktionen kommt.

Ein weitverbreiteter Irrglaube: Transaktionen der Datenbank kann man nicht umgehen –  Jede Operation in einer Datenbank findet innerhalb einer Transaktion statt – ausnahmslos. Und so wird die Erwartung enttäuscht: In relationalen Datenbanken kann es trotz Optimistischen Concurrency Controls zu blockierten Transaktionen kommen.

Der Artikel untersucht das Optimistische Concurrency Control, illustriert dessen Grenzen mit einem Beispiel und zeigt eine sinnvolle Anwendungsmöglichkeit.

Optimistisches Concurrency Control ist nicht zu verwechseln mit dem Multiversion Concurrency Control (MVCC), das beispielsweise PostgreSQL einsetzt, um ACID-Transaktionen auf Zeilenebene zu verwalten. Wir untersuchen dessen Verhalten im zweiten Beispiel in diesem Artikel.

Optimistisches Concurrency Control – Das Experiment

Wir arbeiten mit PostgreSQL. Das Beispiel lässt sich mit jedem beliebigen relationalen System wiederholen.

Zuerst die Vorbereitungen: Wir arbeiten mit einem Datenbankschema my_database und mit zwei Sessions im PSQL-Client.

Zuerst definieren wir eine einfache Tabelle mit der diskutierten Spalte version. Wir fügen gleich eine Zeile ein:

my_database=> create table test (version int);
CREATE TABLE
my_database=> insert into test values (1);
INSERT 0 1
my_database=>

Der Einfachheit halber belassen wir es dabei und zeigen den Effekt mit dieser einen Tabelle mit einer Zeile und einer Spalte. In einem produktiven Beispiel müssen wir natürlich alle Spalten aufzählen und die vom Update betroffenen Zeilen in der Where-Klausel korrekt auswählen.

Zwei gleichzeitige Sessions mit Optimistischem Concurrency Control

Jetzt haben wir Minimalvoraussetzungen geschaffen, um den Effekt des Optimistischen Concurrency Control zu untersuchen.

Wir haben zwei gleichzeitige Sessions in unterschiedlichen Applikationsprogrammen und zwar diejenige von Alice und diejenige von Bog. Beide arbeiten zur gleichen Zeit mit denselben Daten.

Alice arbeitet mit autocommit, also jedes einzelne Statement wird sofort automatisch committed und sie verwendet Transaction Isolation Level ‘Read Committed’:

my_database=> \echo :AUTOCOMMIT
on
my_database=> SHOW TRANSACTION ISOLATION LEVEL;
transaction_isolation
———————–
read committed
(1 row)
my_database=>

Bob hingegen hat autocommit ausgeschaltet – der Befehl lautet \set AUTOCOMMIT off und auch er verwendet  Read Committed:

my_database=> \echo :AUTOCOMMIT
off
my_database=> SHOW TRANSACTION ISOLATION LEVEL;
transaction_isolation
———————–
read committed
(1 row)
my_database=*>

Zusammenfassend:

• Beide Sessions verwenden das voreingestellte Transaction Isolation Level von PostgreSQL: Read Committed.
• Der Unterschied der Sessions liegt im Commit-Verhalten.

Die Grafik zeigt zuerst das Beispiel, das wir anschließend Zeile für Zeile unter die Lupe nehmen. Der Einfachheit halber betrachten wir eine Tabelle test mit genau einer Zeile und genau einer Spalte.

Gehen wir das Beispiel jetzt Schritt für Schritt durch.

Bob’s Transaktion dauert lange, er führt beispielsweise lange Analysen durch und ganz wichtig – commit erfolgt erst ganz am Schluss nach mehreren Datenbankoperationen. Das mag konstruiert erscheinen, ist in der Praxis oft anzutreffen. Zuerst liest Bob den Inhalt der Tabelle – wir konzentrieren uns nur auf die Spalte ‘Version’, weil diese ja fürs Optimistische Concurrency Controll maßgebend ist, alle anderen Spalten hängen vom jeweiligen Anwendungsfall ab:

my_database=*> select * from test;
version
———
1
(1 row)
my_database=*>

Achtung – kein Commit bei Bob.

Alice kann ungehindert die Daten in derselben Tabelle verändern. Erinnere: Alice verwendet autocommit:

my_database=> update test set version=version+1 where version=1;
UPDATE 1
my_database=>

Das funktioniert wie erwartet. Immerhin hat Bob bisher ja ‘nur’ gelesen.

Bob wiederholt den Lesevorgang:

my_database=*> select * from test;
version
———
2
(1 row)
my_database=*>

Das ist korrekt – Read Committed liefert ja immer immer die letzten, durch commit bestätigten Werte. Die anderen Transaction Isolation Leven werden wir später beleuchten.

Jetzt will Bob die Werte verändern:

my_database=*> update test set version=version+1 where version=2;
UPDATE 1
my_database=*> select * from test;
version
———
3
(1 row)
my_database=*>

Beachte – noch immer kein Commit in Bob’s Transaktion. Zuerst wird der Wert verändert und danach gelesen. Postgres geht davon aus, dass Bob in seiner Transaktion mit den selbst veränderten Werten weiterarbeiten will und liefert diese aus, obwohl sie noch nicht bestätigt wurden. Auch hierzu setzt Postgres auf MVCC.

Jetzt wird Alice wieder aktiv – sie arbeitet noch immer mit autocommit:

Der Effekt des Optimistischen Concurrency Control

Alice führt jetzt eine Veränderung an den Daten mit Hilfe von Optimistischem Concurrency Control durch:

Alice liest zuerst die Werte:

my_database=> select * from test;
version
———
2
(1 row)
my_database=>

Es ist korrekt dass Alice für die Version den Wert 2 erhält: Auch ihre Transaktion funktioniert mit Read Committed und Bob hat die Veränderungen seiner Transaktion ja noch nicht bestätigt. Postgres setzt auch hierzu MVCC ein: lesende und schreibende Transaktionen blockieren einander nicht gegenseitig.

Alice berechnet jetzt eine Veränderung an den Daten und will mit Hilfe der Where-Klausel und dem Versionsfeld sicherstellen, keine Daten anderer Transaktionen zu verändern.

Lassen wir Alice eine extreme Erhöhung des Werts vornehmen – so wird der Effekt des Testbeispiels besser sichtbar – Alice erhöht die Version um 100 und erwartet als Ergebnis 102:

my_database=> update test set version=version+100
where version=2;

Hier bleibt die Transaktion stehen – das funktioniert wie erwartet, denn Bob hat ja noch immer keinen commit gemacht und hält jetzt Schreibsperren auf der Zeile, die Alice verändern möchte. Zwei Transaktionen dürfen nicht gleichzeitig dieselbe Zeile schreiben.

Die Transaktion steht ziemlich lange still. Ich habe die Zeit nicht gemessen und auch nicht nachgelesen, wie lange es dauert, bis ein Timeout auftritt. Sicher kann ich berichten, dass ich den berühmten Kaffe trinken war und sich bis zu meiner Rückkehr nichts bewegt hat – mehrere Minuten waren es sicher – untragbar für einen produktiven Mehrnutzerbetrieb.

Die Situation kann aufgelöst werden, indem Bob endlich commit sagt:

my_database=*> commit;
COMMIT
my_database=>

Jetzt geht auch Alices Transaktion zu Ende. Erwartungsgemäss wird der Update nicht durchgeführt – immerhin wurde ja der Wert in Version von Bob verändert und Alice findet keine Zeile mehr mit version=2. Das anschließende Select-Statement findet den Wert, den Bob hinterlassen hat.

my_database=> update test set version=version+100
where version=2;
UPDATE 0
my_database=> select version from test;
version
———
3
(1 row)
my_database=>

Die Arbeit von Alice wurde also in der Datenbank nicht festgehalten. Alice muss ihre Transaktion nochmals durchführen – ein gut geschriebenes Applikationsprogramm wird sie dabei unterstützen und in einer verständlichen Systemmitteilung auch den Grund erklären, so dass Alice versteht, warum sie doppelt Arbeiten muss. Je nach Anwendungsfall wird das Programm die Transaktion vielleicht automatisch wiederholen können. Ob in unserem Beispiel Alice das Endergebnis verstehen würde? Immerhin erwartet sie 102 und erhält aber 3…

MVVC und Snapshot Isolation

In unserem Beispiel führten wir bisher mit der Spalte Version eine Art selbstgebautes Concurrency Control ein – diese wird auch Optimistisches Concurrency Control genannt.

PostgreSQL verwendet MVCC – Multiversion Concurrency Control. Damit wird garantiert, dass lesende Transaktionen keine schreibenden behindern und umgekehrt. Dazu führt Postgres verschiedene Versionen (Snapshots)  ein- und derselben Zeile und merkt sich, welcher Transaktion welche Version der Zeile ausgeliefert wurde.

Die folgende Grafik zeigt das Verhalten von PostgreSQL mit Hilfe desselben Beispiels, doch diesmal ohne die Where-Klausel, also mit reinem MVCC ohne den von der Applikation ergänzten Zusatz des Optimistischen Concurrency Control.

Die Session-Einstellungen für Alice und Bob lassen wir unverändert: Alice verwendet autocommit, Bob nicht und beide arbeiten mit dem Transaction Isolation Level ‘Read Committed’.

Diesmal hat unsere Tabelle eine einige Spalte zahl int und eine einzige Zeile.

Die allerletzte Zeile zeigt den einzigen Unterschied: Alice erwartete zwar den Wert 102 – immerhin sah sie den Wert 2, bevor sie sich entschloss, diesen um 100 zu erhöhen. Nach dem Commit ihrer Transaktion, sieht sie aber den Wert 103.

Aus Sicht der Datenbank ist der Wert 103 jedoch der korrekte: Die Datenbank hat einen “Lost Update” verhindert, und die Erhöhung der Transaktion von Bob mit einbezogen.

Ob Alice als Person das auch so sieht, steht auf einem anderen Blatt geschrieben.

Die Grenzen des MVCC – der Einsatz des Optimistischen Concurrency Control

Unser bisheriges Beispiel zeigte, dass MVCC ausreichend ist und der Aufwand des Optimistischen Concurrency Control überflüssig.

Jetzt verändern wir das Beispiel und wir arbeiten mit einer neuen Tabelle:

create table test (stadt varchar(20), land varchar(20));

Der Einfachheit halber bleiben wir bei einer einzelnen Zeile:

Der Ablauf ist gleich, wie im vorherigen Beispiel. Doch diesmal werden andere Felder verändert. Aus Sicht der Datenbank ist alles in Ordnung: nur bestätigte Werte werden ausgeliefert und keine Transaktion überschreibt die Werte einer anderen Transaktion.

Inhaltlich jedoch ist alles schief gelaufen und die Datenbank enthält jetzt sinnlose Daten – die ewige Stadt liegt ja nicht in der Schweiz.

select for update – die Datenbank arbeiten lassen

Bisher gingen wir davon aus, dass wir gute Gründe haben, den Lese- und Schreibvorgang bei Alice in zwei verschiedenen Datenbanktransaktionen ablaufen zu lassen. Die Datenbank geht immer davon aus, dass wir als Anwenderinnen und Anwender wissen, was wir tun, und unterstützt uns dabei.

Und selbstverständlich bietet uns eine moderne Datenbank wie PostgreSQL auch die notwendigen Bordmittel, ohne den Overhead des Optimistischen Concurrency Controls zu arbeiten.

Das folgende Beispiel zeigt, wie es geht:

Was wurde verändert:

• Alice schaltet autocommit aus. Bob hatte ja autocommit immer ausgeschaltet.
• Jede schreibwillige Transaktion liest die später zu schreibenden Daten mit select for update.

Mit select for update versieht die Datenbank die betroffenen Daten schon beim Lesen mit einer Schreibsperre.

So wird erreicht, dass Alice die von Bob veränderten Daten nicht zu sehen bekommt, bis Bob diese bestätigt hat und so wird Alice auch auf inhaltlich korrekten und konsistenten Daten arbeiten.

Auswirkungen fürs Applikationsprogramm

Select for update und der anschließende Update müssen in ein- und derselben Transaktion stattfinden. Auch wenn dazwischen ein Benutzer eine lange Denkpause einlegt. Die betroffenen Daten bleiben solange gesperrt für Veränderungen anderer Transaktionen, können aber noch gelesen werden.

Eine Transaktion ist immer an eine Datenbanksession gebunden. Handelt es sich beim Applikationsprogramm um eine Webanwendung, dann muss die Datenbanksession korrekt den jeweiligen Requests und Responses zugeordnet werden, was wiederum bedeutet, dass die Datenbanksession im zugehörenden Serverseitigen Web-Sessionobjekt gehalten werden muss.

Ist das nicht möglich, dann ist Optimistisches Concurrency Control ein Ausweg, der jedoch Disziplin seitens der Entwickler erfordert, die konsequent die Spalte ‘Version’ nachführen müssen.

Gesperrte Einheiten

Viele moderne relationale Datenbanken arbeiten standardmäßig mit Row-Level-Locking. Ist das nicht der Fall, dann werden größere Einheiten gesperrt, beispielsweise eine Datenbank-Seite oder die ganze Tabelle.

Beim Page-Level-Locking werden sämtliche Zeilen gesperrt, die sich auf derselben Datenbank-Seite befinden, wie die von der Transaktion tatsächlich verarbeitete Zeile. Wieviele Zeilen das sind, und welche, können wir nicht wissen. In dem Fall kann es aus Sicht der Benutzer zu unerklärlichen Wartezeiten und unerklärlichen Deadlocks kommen.

Table-Level-Locking ist sehr ungünstig für einen Mehrnutzerbetrieb und wird meistens nur für Wartungsarbeiten verwendet.

Andere Transaction Isolation Level

Viele relationale Datenbanken verwenden Read Committed als das Standard-Transaction Isolation Level. Den Effekt haben wir im Beispiel gesehen: Bob’s Transaktion erhält die Werte gezeigt, die Alice verändert hat. Alice jedoch kann keine Werte verändern, solange Bob’s Transaktion die Schreibsperren nicht freigibt. Dieses Verhalten ist oft wünschenswert.

Mit Repeatable Read könnte Bob erreichen, dass seine Transaktion bei wiederholtem Lesen derselben Daten jeweils dieselben Werte gezeigt erhält – eigene Änderungen inklusive. Bob könnte jedoch keine Änderungen vornehmen, wenn Alice inzwischen Änderungen an denselben Daten vorgenommen hat.

Zusätzlich würde Bob bei wiederholtem Lesen die Effekte von Insert- und Delete-Statements bemerken, die Alice inzwischen vorgenommen hat: Würde Alice Zeilen eingefügt haben, die auf die Where-Klauseln von Bob’s wiederholtem Select antworten, dann würde Bob diese zu sehen bekommen. Man spricht in diesem Zusammenhang von Phantomen.

Mit Serializable wird das Verhalten von Repeatable Read noch verschärft. Da könnte Alice keine Änderungen vornehmen, wenn Bob die Daten gelesen hat. Mit diesem Transaction Isolation Level treten oft ungewünschte Blockaden und gar Deadlocks auf, gerade dann, wenn Transaktionen, wie diejenige von Bob vorhanden sind.

Read Uncommitted ist die vierte Möglichkeit. Je nach Datenbanksystem werden dabei tatsächlich Sperren umgangen und noch unbestätigte und damit möglicherweise inkonsistente Werte geliefert.

Setzt man mit PostgreSQL die Transaktion von Alice auf Read Uncommitted und führt unsere Beispiele durch, dann wird man dasselbe Verhalten beobachten, wie mit Read Committed. PostgreSQL behandelt Read Uncommitted gleich wie Read Committed und verhindert damit einen ungewollt inkonsistenten Zustand der Daten.

Fazit

Es führt kein Weg daran vorbei: Ein Applikationsprogramm muss vorsehen, dass eine Transaktion wiederholt werden muss.

Der Einsatz von Optimistischem Concurrency Control ist eine Notlösung, wenn eine Transaktion zwischen dem Lesen und anschließenden Schreiben nicht aufrecht erhalten werden kann.

Verlässlicher ist es, sich auf die Werkzeuge der Datenbank zu verlassen und mit select for update zu arbeiten. Dank MVCC ist in modernen Datenbanken das Transaction Serialisation Level ‘Read Committed’ die beste Wahl für Anwendungen mit User-Interaktionen.

Problematisch wird es, wenn komplexe Transaktionen Daten wiederholt lesen, Berechnungen vornehmen und Daten schreiben aufgrund der Ergebnisse der Berechnungen. Bob’s Transaktion simuliert dieses Verhalten. Solche Transaktionen benötigen unter Umständen Repeatable Read oder gar Serializable als Isolation Level und behindern damit den reibungslosen Mehrnutzerbetrieb.

Empfehlungen für Transaktionen in Relationalen Datenbanken

Allgemeine Empfehlungen zum Transaktionshandling möchte ich nur sehr oberflächlich abgeben:

• Kenne deine Datenbank;
• Kenne deine Applikation und alle anderen Applikationen auf denselben Datenbanktabellen;
• Halte alles so einfach, wie möglich;
• Entkopple Abhängigkeiten in den Daten;
• Verwende Standard-Verfahren der Datenbank;
• Halte alle Transaktionen kurz;
• Lagere Analysen aus in Data Warehouses oder Date Lakehouses;
• Erwäge NoSQL-Datenbanken.

NoSQL Datenbanken

Auch in nicht-relationalen Datenbanken finden alle Operationen innerhalb einer Transaktion statt. Doch anders als bei relationalen Datenbanken finden wir in NoSQL-Datenbanken viele unterschiedliche Ausprägungen des Transaktionsverhaltens vor.

Durch die nicht-relationale Datenorganisation werden auch Abhängigkeiten verringert – mit dem Preis, dass Redundanz eingeführt wird und Daten inhaltlich inkonsistent werden können. Die Verantwortung über die inhaltliche Datenintegrität bleibt in den Händen der Entwicklerinnen und Entwickler der Applikationen.

In unserem Beispiel blieb die eine Session stehen, weil beide Sessions ja als READ COMMITTED-Transaktionen liefen. Mit einer geeigneten NoSQL-Datenbank können wir mit der gezeigten Art des Optimistischen Concurrency Control arbeiten, ohne dass es zu blockierten Sessions kommt.

Doch auch bei NoSQL-Datenbanken gilt: Man kenne die Datenbank – denn diese ‘Freiheit’ kommt mit einem Trade-Off, den man kennen sollte, bevor man ihn eingeht. Welches der Trade-Off ist hängt ab von vom Datenbankprodukt und von der eigenen Applikation.