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:

Dieser Beitrag wurde unter php, Datenbanken, Security, webdev veröffentlicht. Setze ein Lesezeichen auf den Permalink.

9 Antworten auf Datenbank-Transaktionen von akademischer Seite: Behind the Scenes

  1. Oliver sagt:

    Interessant ja, aber die meisten „Probleme“ sind eher theoretischer Natur. Ich habe schon Tabellen gesehen, die mehrere hundert tausende von Transaktionen pro Sekunde verarbeiten und ganz selten ein Problem damit erlebt. Innodb arbeitet ja mit Rowlocking und nicht mehr mit Tablelocking wie MyIsam und ist daher auch in meinen Augen die einzig ernst zu nehmende Tabellenengine für Highload Systeme.

    Dass, um auf Dein Beispiel zu kommen, wirklich genug Abfragen auf eine Zeile kommen, um einen Deadlock zu provozieren ist bei heutigen Serversystemen mehr als schwierig – zumal Datenbankdaten sich auch nicht ununterbrochen ändern, sonst gäbe es auch auch bessere Möglichkeiten wie memcache, dass die Ergebnisse alle paar Sekunden in die DB dumpt. Da muss man natürlich abwägen, wie wichtig die Daten sind.

    Bei Eingriffen in die Tabellenstruktur ist der Server natürlich ruck zuck weg. Das hab ich schon mehrfach erleben dürfen, weil dann wirklich die ganze Tabelle gelockt ist. Am besten ist es, den Server dann offline zu nehmen und die Änderungen durchzuführen. Das macht man ja auch nicht so oft.

    1. david sagt:

      Danke für Deinen Kommentar! In der Tat gings mir eher um das (theoretische) Funktionsprinzip als um wirkliche Praxistipps. Bezüglich der Storage-Engines geb ich Dir auch vollkommen recht – war die einzig richtige Entscheidung, InnoDB ab 5.3 zum Standard zu erheben. Ist mir eh unbegreiflich, wie sich MyISAM ohne Foreign Key-Support und Transaktionen solang behaupten konnte. Schlussendlich möchte ich noch meine große Anerkennung für die Entwickler von DBMS bekunden ;)

      1. Oliver sagt:

        Ich fand’s interessant – 80% aller über Google (fehl-)geleiteter werden wohl in der Mitte aufgegeben haben. :-)

  2. Mario sagt:

    Wunderbarer Artikel, sowas wünsch ich mir häufiger. Mir fehlt leider an vielen Stellen der theoretische Background weswegen ich trotz fortgeschrittenem Alter nochmal ein Studium erwäge.

    Möchte abschließend noch auf autocommit hinweisen, das sollte man hier auch noch erwähnen: http://php.net/manual/de/pdo.transactions.php

  3. tim sagt:

    sehr guter Beitrag, was passiert wenn eine Transaktion begonnen wird, jedoch nie durch einen Systemfehler nie geschlossen wird? zu welchem Zeitpunkt führt mysql automatisch einen Rollback durch? – erst wenn die Verbindung abbricht?

    1. david sagt:

      Hi Tim,

      wenn du eine Transaktion nie explizit comittest, wird auf der Datenbankseite auch keine Änderung vorgenommen.

      Beim Codebeispiel unten würde also der Datenbestand unverändert bleiben (auch wenn kein Fehler passiert), weil du einfach kein commit hast.


      $conn->exec("START TRANSACTION");
      $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("UPDATE konto SET betrag = betrag + 3600 WHERE kontonr = 2");
      $conn = null;

      print "fertig";

      Bezogen auf PDO und autocommit liest du dir am besten mal diese Seite durch.

      Wenn das Script endet oder die Verbindung im Begriff ist, geschlossen zu werden und Sie eine Transaktion ausstehen haben, wird PDO automatisch einen Rollback durchführen. Dies ist eine Sicherheitsmaßnahme, um Inkonsistenzen in dem Fall, wo das Script unerwartet beendet wird, zu vermeiden – wenn Sie die Transaktion nicht explizit ausgeführt haben, wird angenommen, dass etwas schiefgegangen ist, deswegen wird zur Sicherheit ein Rollback durchgeführt.

  4. stietze sagt:

    Ein sehr informativer Artikel. Vielen, vielen Dank dafür!

  5. Seb sagt:

    danke für diesen guten Artikel.

  6. noname sagt:

    Mit einer von PDO abgeleiteten Klasse, kann man das Setzen des Transaktions Isolations Levels zum Beispiel durch Überschreiben der Methode „beginTransaction“ direkt beim Aufbau einer Transaktion integrieren. Das würde auch sicherstellen, dass die Reihenfolge immer korrekt ist.

Schreibe einen Kommentar zu Seb Antworten abbrechen

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert