Datenbank-Transaktionen von akademischer Seite: Behind the Scenes

Disclaimer

An diejenigen mit ordentlichen Vorkenntnissen auf dem Gebiet der Transaktionen: Bitte nicht Abschrecken lassen. Es geht nach der kurzgehaltenen Einführung noch ordentlich in die Tiefe.

Was sind Transaktionen

Nach Definition ist eine Transaktion eine logische Arbeitseinheit, die entweder ganz oder garnicht durchgeführt wird. Bei einem Fehler wird die Datenbank also in den Zustand vor Ausführung der Transaktion versetzt, als ob nie etwas geschehen wäre.

Beispiel: Wo könnte man Transaktionen mehr benötigen als auf einem Gebiet, auf dem Fehler richtig weh tun? Die Bankenwelt! Man stelle sich folgendes ultrasimples Datenmodell vor:

CREATE TABLE  `konto` (
`kontonr` INT NOT NULL PRIMARY KEY ,
`betrag` INT NOT NULL DEFAULT  '0',
`kundenid` INT NOT NULL
) ENGINE = INNODB;

CREATE TABLE  `ueberweisung` (
`ueberweisungsid` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`from_kontonr` INT NOT NULL ,
`to_kontonr` INT NOT NULL ,
`betrag` INT NOT NULL
) ENGINE = INNODB;

Man beachte InnoDB als Engine, da MyISAM keine Transaktionen unterstützt. Folgendes Codebeispiel (bitte wirklich genauer anschauen & verstehen, ist wichtig für den Ablauf):

$conn = new PDO("mysql:host=localhost;dbname=konto", "root", "");
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

try
{
	$conn->exec("START TRANSACTION");
	
	$conn->exec('INSERT INTO ueberweisung (from_kontonr,to_kontonr,betrag) VALUES (1,2,400)');
	$conn->exec("UPDATE konto SET betrag = betrag - 400 WHERE kontonr = 1");
	
	//etwas geht schief...
	$conn->exec("Korruptes SQL Statement, das einen Datenbankfehler symbolisiert");
	
	$conn->exec("UPDATE konto SET betrag = betrag + 400 WHERE kontonr = 2");
	
	$conn->exec("COMMIT"); //Transaktion abgeschlossen
}
catch (Exception $e)
{
	print "Error: ".$e->getMessage();
	$conn->exec("ROLLBACK"); //Zurück zum Zustand vor "START TRANSACTION"
}

Hierbei verwende ich bewusst die expliziten SQL-Kommandos START TRANSACTION, COMMIT und ROLLBACK und nicht das in PDO bereits verzahnte Transaktions-Handling. Dazu später mehr. Ohne Transaktionen würde in obigem Fall das Geld von erstem Konto abgebucht werden, ohne jemals auf dem Zielkonto anzukommen. Man stelle sich anstelle von meinem korrupten SQL-Statement einen beliebigen Fehler vor (Stromausfall, Headcrash, …).

Okay, haben wir den Sinn von Transaktionen geklärt.

Savepoints

Das Ganze lässt sich nun noch etwas feingranularer gestalten:

$conn->exec("START TRANSACTION");

try
{
	$conn->exec('INSERT INTO ueberweisung (from_kontonr,to_kontonr,betrag) VALUES (1,2,400)');
	$conn->exec("UPDATE konto SET betrag = betrag - 400 WHERE kontonr = 1");
	$conn->exec("UPDATE konto SET betrag = betrag + 400 WHERE kontonr = 2");
	$conn->exec("SAVEPOINT ueberweisung1");
}
catch (Exception $e)
{
	print "Error in Überweisung 1: ".$e->getMessage();
	$conn->exec("ROLLBACK");
}

try
{
	$conn->exec('INSERT INTO ueberweisung (from_kontonr,to_kontonr,betrag) VALUES (3,4,3600)');
	$conn->exec("UPDATE konto SET betrag = betrag - 3600 WHERE kontonr = 3");
	
	$conn->exec("Hier passiert was blödes");
	
	$conn->exec("UPDATE konto SET betrag = betrag + 3600 WHERE kontonr = 4");
}
catch (Exception $e)
{
	print "Error in Überweisung 2: ".$e->getMessage();
	$conn->exec("ROLLBACK TO ueberweisung1");
}

$conn->exec("COMMIT");

Nach der erfolgreichen Überweisung im ersten Block wird ein Savepoint definiert (SAVEPOINT ueberweisung1). Der Fehler in der zweiten Überweisung führt nun nicht zu einem kompletten Rollback, die erste Überweisung bleibt bestehen. Natürlich würde man in der Praxis jede Überweisung in eine eigene Transaktion verpacken, dann würde mein Beispiel aber nicht mehr klappen ;).

Die Probleme von Transaktionen

Okay, bis hierher war ja alles cool. Nun stelle man sich aber mal Mehrbenutzerbetrieb vor. Verschiedene Transaktionen laufen parallel auf der Datenbank und operieren mit den gleichen Daten. Dazu ist hier exemplarisch ein Vorgang so dargestellt, wie er Datenbank-intern ablaufen könnte. Herr Müller mit Kontonummer 1 kauft sich ein neues Fahrrad für 400 Euro und bekommt parallel in der selben Mikrosekunde sein Gehalt von 2000€ überwiesen

Schlecht, oder? Wenn der Betriebssystem-Scheduler fies ist und genau so die Prozesse wechselt, ist das Gehalt weg. Aber die Datenbanksystem-Entwickler sind ja nicht auf der Wurstpelle dahergeschwommen. Daher:

ACID to the rescue

ACID steht für Atomicity, Consistency, Isolation und Durability.

• Atomar bedeutet in diesem Zusammenhang, dass Transaktionen als kleinstmögliche Einheit gesehen werden. Das war das mit dem „entweder alles oder garnichts“ aus der Definition.
• Kosistent bedeutet, dass die Datenbank von einem konsistenten in einen anderen konsistenten Zustand überführt wird.
• Isolation bedeutet, dass sich die Transaktionen nicht gegenseitig beeinflussen dürfen. Jede Transaktion soll denken, sie sei die einzige
• Dauerhaftigkeit bedeutet, dass die Daten nach einem Commit dauerhaft gespeichert sein sollen

Die Probleme der Isolation

Nun unterscheidet man 3 große Probleme bei parallel laufenden Transaktionen (Mehrbenutzerbetrieb). Eines davon habe ich weiter oben bereits auf dem Bild angesprochen: Lost Update.

Lost Update

Eine andere Transaktion operiert auf den gleichen Daten und resetted eine durchgeführte Änderung.

Ablauf beim Lost Update:

Dirty Read

Dirty Read bezeichnet das Lesen von noch nicht freigegebenen (comitteden) Daten. In folgendem Beispiel soll Mitarbeiter 2 doppelt soviel Gehalt bekommt wie Mitarbeiter 1. Gleichzeitig läuft noch eine andere Transaktion, die das Gehalt von Mitarbeiter 1 um 400€ erhöht – aber abgebrochen wird.

Durch das Lesen der „schmutzigen“ Gehaltserhöhung von Mitarbeiter 1 bekommt Mitarbeiter 2 nun mehr, als ihm eigentlich zusteht. Die Gehaltserhöhung kam nämlich durch den rollback nie zum Tragen.

Ablauf beim Dirty Read:

Non Repeatable Read

Eine Bank (mit bisher nur 2 Kunden) möchte ermitteln, wieviel Geld alle Kunden gemeinsam auf ihren Konten haben. Parallel dazu finden aber zwei Abbuchungen statt.

Nun ist also der alte Betrag von Konto 1 und der neue Betrag von Konto 2 in die Gesamtsumme eingegangen. Deswegen auch non repeatable: Führe ich die Summen-Abfrage ein zweites mal aus, kommt was anderes heraus.

Ablauf beim Non Repeatable Read:

Was nun? Sperren!

Das sind ja keine guten Voraussetzungen für saubere Transaktionen. Doch Sperren machens möglich.

• Lesesperre: Möchte eine Transaktion einen Wert lesen, setzt sie eine Lesesperre auf ihn. Nun darf keine andere Transaktion diesen Wert schreiben, bis die erste Transaktion nicht die Sperre (durch den commit) wieder freigegeben hat. Paralleles Lesen ist allerdings möglich, d.h. mehrere Transaktionen dürfen eine Lesesperre auf den selben Wert setzen.
• Schreibsperre: Beim Schreiben setzt die Transaktion (logischerweise) eine Schreibsperre auf den Wert. Das bedeutet, dass der nun weder von anderen Transaktionen gelesen, noch geschrieben werden darf. Erst beim commit ist das dann wieder möglich.

Also alles gut jetzt? Nein, nicht ganz. Wenn eine Transaktion viele Sperren gesetzt hat und sehr lange ausgeführt wird, müssen alle anderen Transaktionen, die auch gern eine Sperre auf die Daten hätten, bis zum commit (=Freigabe der Sperren) warten. Auch Deadlocks sind möglich:

Transaktion 1 hat einen WriteLock auf Wert A und möchte Wert B beschreiben, auf den allerdings Transaktion 2 einen WriteLock hat. Transaktion 2 möchte Wert A beschreiben, der ja von Transaktion 1 gelocked ist. Blöd, oder? In beiden angesprochenen Fällen (Deadlock und übermäßige Wartezeit) muss das DBMS eine Transaktion abbrechen, um das Problem zu lösen. Wie das nun intern aber genau geregelt ist, ist von System zu System verschieden.

Und noch ein Problem: Das Phantom!

Mit Sperren kann man also Probleme mit Deadlocks und lange laufenden Transaktionen bekommen. Was von Sperren ebenfalls nicht gelöst werden kann, ist das sogenannte Phantom-Problem. Eine Bank ist großzügig und möchte auf alle Konten einen Bonus von 313373€ verteilen. Während dieser Prozess läuft, wird ein neues Konto in die Datenbank eingetragen.

Führt nun also dazu, dass der Bonus ungerecht verteilt wird, weil in der Zwischenzeit ein Konto mehr angelegt wurde. Mit Sperren haben wir keine Chance, dieses Problem zu lösen, da alles mit rechten Dingen zugeht. Hier muss das Datenbanksystem intern selbst Buch führen, um solche Vorgänge zu vermeiden.

Das Isolations-Level

So, das ganze theoretische Vorwissen war nötig, um das Innodb-Transaktions-Isolationslevel zu verstehen.

In der MySQL-Konfigurationsdatei my.ini lässt sich im Abschnitt mysqld folgende Einstellung tätigen:

transaction-isolation = {READ-UNCOMMITTED | READ-COMMITTED | REPEATABLE-READ | SERIALIZABLE}

Dabei entsprechen die verschiedenen Level genau den oben besprochenen Problemen der Isolation. Klar ist, dass Sperren und andere Vorsichtsmaßnahmen Performance kosten. Wenn es also in meiner Anwendung von vornherein ausgeschlossen ist, dass es zu Non Repeatable Read-Problemen kommen kann, dann kann ich dem Datenbanksystem eine gewisse Last abnehmen und damit mehr Performance erreichen. Aber kurz zur Erklärung der Optionen:

• SERIALIZABLE: Ausgeschlossen: lost-update, dirty read, non repeatable read, phantom / Möglich: –
• REPEATABLE-READ (im übrigen die Standardeinstellung von mysql): Ausgeschlossen: lost-update, dirty read, non repeatable read / Möglich: phantom
• READ-COMMITTED: Ausgeschlossen: lost-update, dirty read / Möglich: non repeatable read, phantom
• READ-UNCOMMITTED: Ausgeschlossen: lost-update / Möglich: dirty read, non repeatable read, phantom. Eine Transaktion hat hier Zugriff auf noch nicht festgeschriebene Daten. In MySQL äußert sich das so, dass keine Lesesperren (ReadLock) gesetzt werden, wenn eine Select-Abfrage durchgeführt wird.

Lost Update ist dabei also immer ausgeschlossen. Man kann wie man sieht nun sehr gut Einfluss auf die interne Handhabung der genannten Probleme nehmen und so einiges an Performance gewinnen, wenn man gewisse Probleme anwendungsbedingt nicht fürchten muss.

Mittels folgender Query kann ich auch aus dem Programm selbst das Verhalten anpassen:

SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}

Im MySQL-Handbuch steht dazu:

Standardmäßig wird die Isolationsebene immer für die nächste (noch nicht begonnene) Transaktion eingestellt. Mit dem Schlüsselwort GLOBAL stellt die Anweisung die Standard-Transaktionsebene global für alle neuen Verbindungen ein, die ab diesem Punkt aufgebaut werden (aber nicht für die schon bestehenden Verbindungen).

Leider muss das Isolations-Level auf diese Art und Weise mit einer harten Abfrage geändert werden, da PHP (weder mit PDO noch MySQLi) einen Wrapper dafür anbietet. In Java kann man sowas beispielsweise ganz smooth erledigen (mit JDBC):

con.setTransactionIsolation(TRANSACTION_SERIALIZABLE);
con.getDefaultTransactionIsolation();
con.supportsTransactions(); 
con.supportsTransactionIsolationLevel( )

Um damit mal ein paar Funktionen aufzulisten.

Transaktionshandling mit PDO

Wenn man sich auf die „Basics“ beschränkt, kann man auch mit PDO gut mit Transaktionen umgehen:

try 
{
	$conn->exec("SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE"); //kein wrapper dafür in pdo
	$conn->beginTransaction();
  
	$conn->exec('INSERT INTO ueberweisung (from_kontonr,to_kontonr,betrag) VALUES (3,4,3600)');
	$conn->exec("UPDATE konto SET betrag = betrag - 3600 WHERE kontonr = 3");
	
	$conn->exec("Hier passiert was blödes");
	
	$conn->exec("UPDATE konto SET betrag = betrag + 3600 WHERE kontonr = 4");
	
	$conn->commit();
} 
catch (Exception $e) 
{
	print "Error: ".$e->getMessage();
	$conn->rollBack();
}

Würde ich auch vorziehen, verglichen mit den direkten Queries von „START TRANSACTION“ etc.

Implicit Commit

Es gibt (speziell bei strukturverändernden Operationen) sogenannte implicit commits. Bedeutet, dass automatisch ein Commit ausgelöst wird, auch wenn im Code kein „COMMIT;“ steht. Eine kleine Auswahl (vollständig auf der verlinkten MySQL-manpage dazu):

CREATE DATABASE
ALTER DATABASE
DROP DATABASE
CREATE TABLE
ALTER TABLE
DROP TABLE
RENAME TABLE
TRUNCATE TABLE
CREATE INDEX
DROP INDEX

Heißt im konkreten Fall am Beispiel:

try 
{
	$conn->beginTransaction();
	
	$conn->exec('INSERT INTO ueberweisung (from_kontonr,to_kontonr,betrag) VALUES (1,2,3600)');
	$conn->exec("UPDATE konto SET betrag = betrag - 3600 WHERE kontonr = 1");
	
	$conn->exec("ALTER TABLE  `konto` ADD UNIQUE (`kundenid`)"); //implicit commit
	
	$conn->exec("Hier passiert was blödes");
	
	$conn->exec("UPDATE konto SET betrag = betrag + 3600 WHERE kontonr = 2");
	
	$conn->commit();
} 
catch (Exception $e) 
{
	print "Error: ".$e->getMessage();
	$conn->rollBack();
}

Die 3600€ kommen nie auf Konto 2 an, werden aber abgebucht. Der total zusammenhanglos dazwischenstehende ALTER TABLE Befehl führt wie beschrieben einen commit aus.

Finished

Ich hoffe, ich bin nicht der einzige der sowas tierisch interessant findet und es hat jemand bis hier ausgehalten. Fragen und Ergänzungen wie immer gern in den Kommentaren. Ach, und vielen Dank an Frau Prof. Störl für die exzellenten Anregungen und das ein oder andere Bild aus ihrem Script ;).

Weitere Posts:

• Lasst die Datenbank ihren Job machen!
• Parallel processing in PHP
• Vagrant Tutorial
• #1062 – Duplicate entry ‚2147483647‘ for key ‚PRIMARY‘
• MySQL Inputvalidierung mit Triggern