Für den Transfer von 300 Rupien von Asha zu Ben sind zwei Aktualisierungen erforderlich: von einem Konto abziehen und zum anderen addieren. Wenn das System zwischen ihnen zusammenbricht, verschwindet das Geld. SQL-Transaktionen machen dies unmöglich, indem beide Schritte als eine Einheit behandelt werden.

In diesem Leitfaden gehen wir Commits, Rollbacks und Fehler in einer kleinen Kontentabelle durch. Wir haben diese Beispiele in SQLite ausgeführt und die angezeigten Salden stammen aus diesem Durchlauf. Die grundlegenden Befehle sind in allen Datenbanken ähnlich, das Sperr- und Isolationsverhalten variiert jedoch stark. Daher weisen wir Sie darauf hin, wo Sie die Dokumentation Ihrer eigenen Datenbank nachschlagen können.

Was ist eine Transaktion?

Eine Transaktion ist eine Folge einer oder mehrerer SQL-Anweisungen, die von der Datenbank als eine einzige logische Operation behandelt werden. Hier ist unsere Tabelle:

CREATE TABLE accounts (
  id INT PRIMARY KEY,
  owner TEXT,
  balance INT CHECK (balance >= 0)
);

INSERT INTO accounts VALUES (1, 'Asha', 1000), (2, 'Ben', 200);

Die CHECK-Einschränkung ist eine Regel, die besagt, dass ein Saldo niemals unter Null fallen kann, die wir später verwenden werden. Beachten Sie, dass die Syntax zum Starten einer Transaktion unterschiedlich ist: SQLite und PostgreSQL akzeptieren BEGIN, MySQL verfügt auch über START TRANSACTION und SQL Server verwendet BEGIN TRANSACTION.

Außerhalb einer expliziten Transaktion verwenden die meisten Datenbanken Autocommit, wobei jede Anweisung gespeichert wird, sobald sie abgeschlossen ist. Oracle ist eine bemerkenswerte Ausnahme, da eine Sitzung dort in einer Transaktion verbleibt, bis Sie einen Commit durchführen.

COMMIT: Machen Sie es dauerhaft

BEGIN;
UPDATE accounts SET balance = balance - 300 WHERE id = 1;
UPDATE accounts SET balance = balance + 300 WHERE id = 2;
COMMIT;

-- Asha | 700
-- Ben  | 500

Bis zur Ausführung von COMMIT sind die Änderungen vorläufig. Nach erfolgreicher Rückkehr garantiert die Datenbank, dass sie gespeichert werden. Die Summe beider Konten blieb bei 1200, und das ist der springende Punkt der Übung.

ROLLBACK: Alles abbrechen

ROLLBACK macht alles seit Beginn der Transaktion rückgängig:

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
SELECT owner, balance FROM accounts;
-- Asha | 600   (visible inside this transaction)
-- Ben  | 500

ROLLBACK;
SELECT owner, balance FROM accounts;
-- Asha | 700   (back to where it was)
-- Ben  | 500

Ein Rollback erfolgt auch automatisch, wenn die Verbindung unterbrochen wird oder der Server abstürzt, bevor Sie einen Commit durchführen, sodass eine halbfertige Transaktion nicht überlebt.

Wenn eine Anweisung fehlschlägt

Hier ist ein realistischerer Fall. Asha versucht, 5000 zu senden, die auf dem Konto nicht vorhanden sind. Die Gutschrift an Ben erfolgt zuerst und dann erfolgt die Belastung, was gegen die CHECK-Regel verstößt:

BEGIN;
UPDATE accounts SET balance = balance + 5000 WHERE id = 2;
UPDATE accounts SET balance = balance - 5000 WHERE id = 1;
-- error: CHECK constraint failed: balance >= 0

ROLLBACK;

-- Asha | 700
-- Ben  | 500

Ohne die Transaktion hätte Ben die 5000 behalten und die Bücher wären falsch. Beachten Sie ein wichtiges Detail: Eine fehlgeschlagene Anweisung bricht die Transaktion nicht immer von selbst ab. In SQLite und MySQL können beispielsweise frühere Anweisungen ausstehend bleiben, sodass Ihr Code das Rollback durchführen muss. PostgreSQL markiert stattdessen die gesamte Transaktion als abgebrochen und lehnt weitere Anweisungen ab, bis Sie ein Rollback durchführen.

Koppeln Sie im Anwendungscode immer die Transaktion mit der Fehlerbehandlung. Hier ist die Idee in Python, und unser Leitfaden zur Python-Ausnahmebehandlung erklärt die Syntax:

try:
    conn.execute("BEGIN")
    conn.execute(debit_sql, values)
    conn.execute(credit_sql, values)
    conn.execute("COMMIT")
except Exception:
    conn.execute("ROLLBACK")
    raise

Speicherpunkte

Ein Sicherungspunkt markiert eine Position innerhalb einer Transaktion, sodass Sie nur einen Teil der Arbeit rückgängig machen können:

BEGIN;
UPDATE accounts SET balance = balance - 50 WHERE id = 1;
SAVEPOINT bonus;
UPDATE accounts SET balance = balance + 999 WHERE id = 2;
ROLLBACK TO bonus;
UPDATE accounts SET balance = balance + 50 WHERE id = 2;
COMMIT;

-- Asha | 650
-- Ben  | 550

Der 999-Bonus wurde rückgängig gemacht, während der Rest der Transaktion weitergeführt und festgeschrieben wurde. Dies ist nützlich für optionale Schritte in einem langen Prozess.

ACID-Eigenschaften erklärt

Konsistenz wird häufig missverstanden. Die Datenbank erzwingt nur die von Ihnen deklarierten Regeln, wie z. B. Einschränkungen und Fremdschlüssel. Es verhindert nicht, dass Ihr Code den falschen Betrag überweist. Haltbarkeit wird normalerweise mit einem Write-Ahead-Protokoll erreicht, bei dem Änderungen sicher auf der Festplatte aufgezeichnet werden, bevor der Commit bestätigt wird.

Was passiert bei gleichzeitigen Transaktionen?

Wir haben zwei Verbindungen zu einer SQLite-Datei geöffnet. Der erste hat eine Transaktion gestartet und eine Zeile von 1 auf 2 aktualisiert, ohne noch einen Commit durchzuführen. Folgendes sah die zweite Verbindung:

Connection B reads:   1                     -- no uncommitted data
Connection B writes:  database is locked    -- it has to wait
(A commits)
Connection B reads:   2                     -- now it sees the change

SQLite lässt jeweils nur einen Schreiber zu, was einfacher ist als bei Serverdatenbanken, bei denen viele Transaktionen gleichzeitig verschiedene Zeilen schreiben können. Das Prinzip ist jedoch dasselbe: Andere Sitzungen sollten unter normalen Einstellungen keine nicht festgeschriebenen Änderungen sehen, und widersprüchliche Schreibvorgänge müssen warten. In unserem Test haben wir die zweite Verbindung so eingestellt, dass sie sofort abgebrochen wird, anstatt zu warten, sodass der Fehler sofort auftrat.

Anomalien lesen

Wenn sich Transaktionen überschneiden, können bestimmte Probleme auftreten. Der SQL-Standard definiert diese im Hinblick darauf, was eine Transaktion beobachten kann:

Ein weiteres Problem ist das verlorene Update. Zwei Sitzungen lesen einen Saldo von 100, jede addiert 10 und jede schreibt 110. Ein Update ist praktisch verschwunden, obwohl die Antwort 120 lauten sollte.

SQL-Isolationsstufen

Mit Isolationsstufen können Sie Strenge gegen Parallelität eintauschen. Folgendes erlaubt der SQL-Standard auf jeder Ebene:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Die genaue Syntax und das Verhalten hängen von Ihrer Datenbank ab. Auch die Standardeinstellungen unterscheiden sich: PostgreSQL und SQL Server verwenden Read Committed, während InnoDB von MySQL wiederholbares Lesen verwendet. Einige Datenbanken bieten stärkere Garantien, als der Standard erfordert, oder behandeln eine Ebene als eine andere. In PostgreSQL verhält sich beispielsweise read uncommitted wie read commit. Lesen Sie immer die Dokumentation Ihrer Datenbank, bevor Sie sich auf ein bestimmtes Verhalten verlassen.

Deadlocks

Ein Deadlock tritt auf, wenn zwei Transaktionen jeweils eine Sperre halten, die die andere benötigt. Transaktion A sperrt Zeile 1 und möchte Zeile 2, während Transaktion B Zeile 2 sperrt und Zeile 1 möchte. Keiner von beiden kann fortfahren.

Datenbanken wie PostgreSQL, MySQL und SQL Server erkennen dies und brechen eine Transaktion mit einem Fehler ab. Das ist normales Verhalten und Ihre Bewerbung sollte darauf vorbereitet sein. Um Deadlocks zu reduzieren, aktualisieren Sie die Zeilen immer in einer konsistenten Reihenfolge, z. B. mit der niedrigsten Konto-ID zuerst, halten Sie die Transaktionen kurz und versuchen Sie es erneut, wenn jemand als Opfer ausgewählt wird.

Best Practices

• Halten Sie Transaktionen kurz. Lange Transaktionen halten Sperren und blockieren andere Benutzer.
• Warten Sie niemals auf einen Benutzer oder eine langsame API innerhalb einer Transaktion. Erledigen Sie diese Arbeit, bevor Sie beginnen, oder danach.
• Fehler immer behandeln. Jeder Pfad sollte mit einem Commit oder einem Rollback enden.
• Fügen Sie Einschränkungen hinzu. Sie geben der Datenbank echte Regeln vor, die durchgesetzt werden müssen. Gutes Tischdesign hilft auch.
• Verwenden Sie gute Indizes. Schnellere Suchvorgänge bedeuten, dass Sperren kürzer gehalten werden.
• Seien Sie bei Wiederholungsversuchen vorsichtig. Versuchen Sie es bei Deadlocks noch einmal, aber stellen Sie sicher, dass ein Vorgang nicht zweimal angewendet werden kann.
• Testen Sie die Parallelität absichtlich. Viele dieser Fehler treten nur unter Last auf, sodass grundlegende Integrationstests allein sie möglicherweise nicht aufdecken.

Fazit

Transaktionen verwandeln mehrere riskante Schritte in einen sicheren Vorgang, und ACID beschreibt die Garantien, die diesen Vorgang vertrauenswürdig machen. Wenn eine Geschäftsaktion mehr als eine Zeile oder Tabelle betrifft, fragen Sie sich, was passieren würde, wenn sie auf halbem Weg gestoppt würde. Wenn Ihnen die Antwort Sorgen bereitet, schließen Sie sie in eine Transaktion ein. Öffnen Sie zum Üben zwei Sitzungen in einer lokalen Datenbank und versuchen Sie, die Anomalien selbst zu erzeugen.

Häufige Fragen

Lerne weiter