SQL COMMIT, ROLLBACK und SAVEPOINT: Transaktionen sicher steuern

COMMIT und ROLLBACK sind SQL-Befehle zur Steuerung von Transaktionen. Mit COMMIT werden die innerhalb der aktuellen Transaktion vorgenommenen Änderungen dauerhaft gespeichert. ROLLBACK verwirft dagegen noch nicht bestätigte Änderungen und stellt den vorherigen Zustand der Datenbank wieder her. Zusammen ermöglichen diese Befehle eine kontrollierte Transaktionsverarbeitung und tragen dazu bei, die Datenintegrität zu bewahren, indem vollständige Änderungssätze entweder gespeichert oder bei einem Fehler zuverlässig zurückgenommen werden können.

Dieses Tutorial erläutert die grundlegende Syntax für MySQL, PostgreSQL, Oracle und SQL Server. Zusätzlich werden Savepoints, Verfahren zur Fehlerbehandlung, das Transaktionsverhalten in Stored Procedures, Retry- und Batch-Muster sowie Abfragen zur Diagnose von Transaktionsproblemen in produktiven Umgebungen behandelt. Die Beispiele lassen sich mit Standardinstallationen der jeweiligen Datenbanksysteme verwenden.

Wenn ein konkretes Transaktionsproblem untersucht werden soll, enthält der Abschnitt zur Diagnose von Transaktionsproblemen in produktiven Umgebungen datenbankspezifische Abfragen. Für die Entwicklung neuer Transaktionslogik bietet der Abschnitt zu praxisnahen Transaktionsmustern außerdem Vorlagen für Wiederholungsversuche und Batch-Verarbeitung.

Wichtigste Erkenntnisse

• COMMIT speichert die aktuelle Transaktion dauerhaft, während ROLLBACK nicht bestätigte Änderungen verwirft. Sobald ein COMMIT erfolgreich abgeschlossen wurde, lassen sich die Änderungen mit gewöhnlichen Transaktionsbefehlen nicht mehr rückgängig machen. Eine Wiederherstellung erfordert anschließend ein Backup oder eine datenbankspezifische Point-in-Time-Recovery-Funktion.
• DDL-Anweisungen führen in MySQL und Oracle zu impliziten Commits. Nicht bestätigte DML-Änderungen, die beispielsweise vor einem ALTER TABLE oder DROP TABLE ausgeführt wurden, werden dadurch dauerhaft gespeichert und können nicht mehr zurückgerollt werden. DDL und DML sollten bei diesen Datenbanksystemen deshalb in getrennten Transaktionseinheiten verarbeitet werden.
• Das standardmäßige Autocommit-Verhalten unterscheidet sich zwischen den Datenbanksystemen. MySQL und SQL Server verwenden üblicherweise Autocommit, Oracle SQL*Plus normalerweise nicht. PostgreSQL besitzt keine serverweite Autocommit-Einstellung, sondern steuert dieses Verhalten über den jeweiligen Client.
• Mit SAVEPOINT lassen sich nur bestimmte Teile einer laufenden Transaktion zurücknehmen. Da mehrfach verwendete Savepoint-Namen je nach Datenbanksystem unterschiedlich behandelt werden, sollte für jeden Kontrollpunkt ein eindeutiger Name verwendet werden.
• Auch die Fehlerbehandlung ist datenbankspezifisch. SQL Server verwendet häufig TRY/CATCH zusammen mit IF @@TRANCOUNT > 0 und XACT_STATE(). PostgreSQL setzt auf BEGIN ... EXCEPTION-Subtransaktionen, die bei einer Exception automatisch zurückgesetzt werden. Oracle verwendet in PL/SQL beispielsweise EXCEPTION WHEN OTHERS THEN ROLLBACK.
• Identity- und Sequence-Werte werden bei einem Rollback in den großen Datenbanksystemen nicht zurückgesetzt. Lücken in AUTO_INCREMENT-, IDENTITY– oder PostgreSQL-Sequence-Werten sind nach Rollbacks deshalb normal.
• Die Fehlersuche bei Transaktionen in produktiven Systemen beginnt häufig mit der Suche nach lang laufenden oder als idle in transaction markierten Sitzungen. Dafür stehen unter anderem pg_stat_activity, sys.dm_tran_active_transactions, information_schema.innodb_trx und v$transaction zur Verfügung.

Voraussetzungen

• Zugriff auf mindestens eine Installation von MySQL 8.0+, PostgreSQL 13+, Oracle 19c+ oder SQL Server 2019+.
• Grundkenntnisse der SQL-Befehle SELECT, INSERT, UPDATE und DELETE.
• Ein Datenbank-Client wie mysql, psql, sqlplus oder sqlcmd.
• Falls noch kein Datenbanksystem ausgewählt wurde, sollten SQLite, MySQL und PostgreSQL zunächst miteinander verglichen werden.

Was ist eine SQL-Transaktion?

Eine SQL-Transaktion ist eine zusammengehörige Arbeitseinheit, die von der Datenbank atomar verarbeitet wird. Entweder werden sämtliche Anweisungen innerhalb der Transaktion erfolgreich ausgeführt und gemeinsam bestätigt, oder keine der Änderungen bleibt erhalten. Eine Transaktion bildet damit gleichzeitig eine Wiederherstellungsgrenze. Schlägt ein Vorgang während der Verarbeitung fehl, kann das Datenbanksystem die Daten auf den Zustand vor Beginn der Transaktion zurücksetzen, ohne dass eine manuelle Bereinigung erforderlich ist.

Der Lebenszyklus einer Transaktion lässt sich über drei zentrale Kontrollpunkte darstellen:

BEGIN -> ANWEISUNGEN AUSFÜHREN -> COMMIT (speichern) | ROLLBACK (verwerfen)

Transaktionen sollten die ACID-Eigenschaften erfüllen, damit Daten auch bei Fehlern, Wiederholungsversuchen und parallelen Zugriffen konsistent und zuverlässig bleiben.

ACID-Eigenschaften und ihre Bedeutung

ACID beschreibt die Eigenschaften, durch die transaktionale Datenbanksysteme sowohl bei normaler Verarbeitung als auch bei Fehlern einen korrekten Datenzustand gewährleisten.

Autocommit-Modus und explizite Transaktionssteuerung

Im Autocommit-Modus wird jede einzelne SQL-Anweisung als eigenständige Transaktion verarbeitet. Bei einer expliziten Transaktionssteuerung werden dagegen mehrere Anweisungen innerhalb einer gemeinsamen Transaktionsgrenze zusammengefasst.

Was ist COMMIT in SQL?

COMMIT kennzeichnet den Zeitpunkt, an dem die innerhalb einer Transaktion vorgenommenen Änderungen zu einem dauerhaften Bestandteil des Datenbankzustands werden. Vor dem Ausführen des Befehls können die Änderungen der aktiven Transaktion noch zurückgenommen werden. Sobald COMMIT erfolgreich abgeschlossen wurde, bleiben die Änderungen bestehen, werden entsprechend den Parallelitätsregeln der Datenbank sichtbar und können mit einem gewöhnlichen Rollback nicht mehr rückgängig gemacht werden.

COMMIT-Syntax bei verschiedenen Datenbanksystemen

Das Schlüsselwort COMMIT ist bei den wichtigsten Datenbanksystemen weitgehend einheitlich. Unterschiede bestehen vor allem darin, wie eine Transaktion gestartet wird und wie sich Anweisungen außerhalb einer expliziten Transaktion verhalten. Die folgende Tabelle vergleicht die Syntax zum Starten und Bestätigen von Transaktionen.

Nachdem eine Transaktion geöffnet wurde, folgt ein grundlegender Commit unabhängig vom Datenbanksystem demselben allgemeinen Muster:

BEGIN;
UPDATE customer SET state = 'TX' WHERE customer_id = 4;
COMMIT;

In MySQL kann BEGIN als Alias für START TRANSACTION verwendet werden. Bei SQL Server wird anstelle von BEGIN; der Befehl BEGIN TRANSACTION; verwendet. In Oracle entfällt die BEGIN-Anweisung vollständig, da die erste DML-Anweisung die Transaktion implizit startet.

Ein weiterer wichtiger Punkt ist das Verhalten von COMMIT, wenn aktuell keine Transaktion geöffnet ist. MySQL und Oracle führen den Befehl ohne Fehler aus. PostgreSQL gibt die Warnung WARNING: there is no transaction in progress aus. SQL Server meldet dagegen einen Fehler, wenn zuvor kein entsprechendes BEGIN TRANSACTION ausgeführt wurde. Anwendungen, die am Ende eines Codepfads grundsätzlich ein COMMIT ausführen, sollten dieses Verhalten deshalb für jedes unterstützte Datenbanksystem testen.

Wann wird COMMIT automatisch ausgeführt?

Ist Autocommit aktiviert, wird jede erfolgreich ausgeführte SQL-Anweisung als eigenständige Transaktion behandelt und unmittelbar bestätigt. Ein expliziter COMMIT-Befehl ist dann nicht erforderlich. Dieses Verhalten ist bei vielen Datenbank-Clients die Standardeinstellung.

MySQL aktiviert Autocommit normalerweise standardmäßig. Zusätzlich führen MySQL und Oracle bei DDL-Anweisungen wie CREATE, ALTER und DROP implizite Commits aus.

Wird bei MySQL oder Oracle innerhalb einer laufenden Transaktion eine DDL-Anweisung ausgeführt, werden zuvor noch nicht bestätigte DML-Änderungen automatisch gespeichert. Diese früheren Änderungen können anschließend nicht mehr mit der normalen Transaktionssteuerung zurückgenommen werden.

Bevor das Transaktionsverhalten einer Datenbanksitzung geändert wird, sollte die Übersicht zu Autocommit und expliziter Transaktionssteuerung herangezogen werden, um die Client- und Datenbankstandards den passenden Befehlen zuzuordnen.

Was ist ROLLBACK in SQL?

ROLLBACK stellt den Wiederherstellungsweg bereit, wenn eine Transaktion nicht erfolgreich abgeschlossen werden kann. Ursachen können beispielsweise ein Validierungsfehler, eine Constraint-Verletzung, ein Deadlock oder ein anderer Fehler sein. Durch ROLLBACK werden die Änderungen der Datenbank auf den Zustand vor Beginn der Transaktion zurückgesetzt. Anschließend kann die Sitzung den Vorgang erneut versuchen, den Fehler protokollieren oder mit anderen Arbeiten fortfahren.

ROLLBACK-Syntax bei verschiedenen Datenbanksystemen

Auch ROLLBACK ist zwischen den Datenbanksystemen weitgehend einheitlich. SQL Server unterstützt zusätzlich die Form ROLLBACK TRANSACTION, wobei ROLLBACK als Kurzform verwendet werden kann. Für den Start der Transaktion gelten dieselben Regeln wie im vorherigen Abschnitt.

BEGIN;
UPDATE customer SET state = 'TX' WHERE customer_id = 4;
ROLLBACK;

Ein vollständiger ROLLBACK beendet die Transaktion bei allen hier betrachteten Datenbanksystemen und bringt die Sitzung wieder in ihren normalen Autocommit- beziehungsweise impliziten Transaktionsmodus zurück. PostgreSQL besitzt eine zusätzliche Besonderheit: Tritt innerhalb einer Transaktion ein unbehandelter Fehler auf, wechselt die Transaktion in einen abgebrochenen Zustand. Bis ein ROLLBACK ausgeführt wurde, weist die Sitzung weitere Befehle mit ERROR: current transaction is aborted, commands ignored until end of transaction block zurück. Dieses Verhalten fällt häufig auf, wenn nach einem fehlgeschlagenen Statement ein SELECT ausgeführt wird, bevor die Transaktion zurückgesetzt wurde.

Vollständiger Rollback und partieller Rollback

Ein gewöhnlicher ROLLBACK nimmt sämtliche Änderungen zurück, die seit Beginn der Transaktion durchgeführt wurden. Mit ROLLBACK TO SAVEPOINT werden dagegen nur die Änderungen verworfen, die nach einem bestimmten Savepoint erfolgt sind. Beide Varianten decken damit unterschiedliche Wiederherstellungsbereiche innerhalb desselben Transaktionsmodells ab.

Wird ROLLBACK ohne weitere Argumente verwendet, erfolgt ein vollständiger Rollback und die Transaktion wird beendet. ROLLBACK TO SAVEPOINT sp_name führt hingegen einen partiellen Rollback durch und hält die Transaktion weiterhin geöffnet. Danach können weitere SQL-Anweisungen ausgeführt werden, bevor später ein COMMIT oder ein weiterer ROLLBACK folgt.

Der Unterschied ist nicht nur syntaktischer Natur. Bei einem vollständigen Rollback werden alle Änderungen seit BEGIN verworfen, die von der Transaktion gehaltenen Sperren werden freigegeben und die Arbeitseinheit wird beendet. Beim Zurücksetzen auf einen Savepoint werden lediglich die Änderungen nach dem ausgewählten Kontrollpunkt verworfen. Frühere Änderungen bleiben Bestandteil der laufenden Transaktion und die Transaktion kann weiterverarbeitet werden.

BEGIN;
UPDATE customer SET state = 'New York' WHERE customer_id = 1;
SAVEPOINT after_first_update;
UPDATE customer SET state = 'Texas' WHERE customer_id = 2;
-- Die zweite Änderung soll zurückgenommen werden
ROLLBACK TO SAVEPOINT after_first_update;
-- Die Transaktion ist weiterhin geöffnet. Die erste Änderung ist noch nicht bestätigt.
UPDATE customer SET state = 'Karnataka' WHERE customer_id = 3;
COMMIT;

Nach dem Commit dieser Transaktion besitzt Kunde 1 den Status New York, Kunde 3 den Status Karnataka und Kunde 2 bleibt unverändert. Vollständiger und partieller Rollback ergänzen sich somit und stellen keine voneinander unabhängigen Alternativen dar.

Der Abschnitt zu SAVEPOINT erläutert die Syntax und das Verhalten solcher Transaktionskontrollpunkte ausführlicher und zeigt, wie ein partieller Rollback bei mehrstufigen Transaktionen eingesetzt werden kann.

Was ist ein SAVEPOINT in SQL?

Ein SAVEPOINT erstellt einen Kontrollpunkt innerhalb einer laufenden Transaktion. Zu diesem Punkt kann später zurückgekehrt werden, ohne die komplette Transaktion zu beenden. Das ist besonders hilfreich, wenn eine Transaktion mehrere Verarbeitungsschritte enthält und bereits erfolgreich geprüfte Schritte erhalten bleiben sollen, obwohl ein späterer Schritt zurückgenommen werden muss. Ohne Savepoints besteht im Wesentlichen nur die Wahl, sämtliche Änderungen zu bestätigen oder die komplette Transaktion zurückzusetzen. Savepoints ermöglichen eine gezieltere Steuerung.

Der Lebenszyklus eines Savepoints

Ein Savepoint wird zunächst angelegt, kann später das Ziel eines Rollbacks sein und wird anschließend entweder ausdrücklich freigegeben oder beim Abschluss der Transaktion automatisch entfernt. Ein vollständiger Ablauf kann folgendermaßen aussehen:

BEGIN;
UPDATE customer SET state = 'New York' WHERE customer_id = 3;

SAVEPOINT after_customer_3_update;

UPDATE customer SET state = 'Florida' WHERE customer_id = 2;

-- Die zweite Änderung soll nicht bestehen bleiben
ROLLBACK TO SAVEPOINT after_customer_3_update;
-- Kunde 3 bleibt auf New York geändert. Kunde 2 bleibt unverändert.

-- Savepoint freigeben, damit kein zusätzlicher Tracking-Speicher benötigt wird
RELEASE SAVEPOINT after_customer_3_update;

UPDATE customer SET state = 'Karnataka' WHERE customer_id = 1;
COMMIT;

Mehrere Details in diesem Ablauf sind besonders wichtig, weil sie beim Einsatz von Savepoints häufig zu Missverständnissen führen.

RELEASE SAVEPOINT bestätigt keine Datenänderungen. Der Befehl entfernt lediglich den jeweiligen Kontrollpunkt. Die Änderungen innerhalb der Transaktion bleiben unbestätigt und können weiterhin durch ein späteres COMMIT oder ROLLBACK beeinflusst werden. Das Wort „Release“ bezieht sich somit ausschließlich auf die Savepoint-Markierung und nicht darauf, die zugehörigen Datenänderungen dauerhaft zu speichern.

Doppelt verwendete Savepoint-Namen werden von den Datenbanksystemen unterschiedlich behandelt. PostgreSQL verschiebt die Markierung auf die neue Position, wodurch der ältere Savepoint nicht mehr erreichbar ist. MySQL ersetzt den vorherigen Savepoint stillschweigend. In Oracle wird ein älterer Savepoint mit demselben Namen unzugänglich. SQL Server erlaubt identische Namen und verwendet beim Rollback den zuletzt angelegten Savepoint mit diesem Namen. Für plattformübergreifende Anwendungen ist es daher sinnvoll, innerhalb einer Transaktion keine Savepoint-Namen mehrfach zu verwenden.

Savepoints verschwinden in bestimmten Situationen auch automatisch. Ein COMMIT entfernt alle Savepoints der Transaktion. Ein ROLLBACK TO SAVEPOINT name entfernt alle Savepoints, die nach dem ausgewählten Kontrollpunkt angelegt wurden, lässt den ausgewählten Savepoint selbst jedoch bestehen. Wäre beispielsweise nach SAVEPOINT after_customer_3_update noch ein SAVEPOINT sp_b erstellt worden, würde ein Rollback auf after_customer_3_update den Savepoint sp_b automatisch entfernen.

Savepoints benötigen außerdem Ressourcen, da das Datenbanksystem ausreichend Undo-Informationen behalten muss, um zu ihnen zurückkehren zu können. Bei lang laufenden Batch-Prozessen mit Tausenden Datensätzen kann sich deshalb eine große Menge an Undo-Daten ansammeln, wenn für jede Iteration ein Savepoint angelegt, aber nicht wieder freigegeben wird. Jeder Savepoint sollte entfernt werden, sobald der dadurch geschützte Abschnitt erfolgreich abgeschlossen wurde.

SQL COMMIT und ROLLBACK: Beispiele

Die folgenden Beispiele verwenden eine einzelne Tabelle namens customer mit vier Ausgangsdatensätzen. Dadurch lässt sich der Tabellenzustand vor und nach COMMIT, ROLLBACK und einem Rollback auf einen Savepoint vergleichen.

Zum Start einer Transaktion verwenden die Beispiele BEGIN;. Dies ist die Standardsyntax in PostgreSQL und wird ebenfalls von MySQL akzeptiert. In MySQL kann alternativ START TRANSACTION; genutzt werden. Bei SQL Server wird BEGIN; durch BEGIN TRANSACTION; und COMMIT; durch COMMIT TRANSACTION; ersetzt.

Einfaches COMMIT-Beispiel

Zunächst wird die Tabelle erstellt.

CREATE TABLE customer (
  customer_id INT PRIMARY KEY,
  customer_name VARCHAR(100),
  state VARCHAR(100),
  country VARCHAR(100)
);

Anschließend werden vier Datensätze eingefügt.

INSERT INTO customer (customer_id, customer_name, state, country) VALUES
(1, 'Akash', 'Delhi', 'India'),
(2, 'Amit', 'Hyderabad', 'India'),
(3, 'Jason', 'California', 'USA'),
(4, 'John', 'Texas', 'USA');

Nun wird ein Datensatz gelöscht und die Transaktion anschließend bestätigt.

BEGIN;
DELETE FROM customer WHERE state = 'Texas';
COMMIT;

Nach dem Commit kann der aktuelle Tabellenzustand abgefragt werden.

SELECT * FROM customer;

+-------------+---------------+------------+---------+
| CUSTOMER ID | CUSTOMER NAME | STATE      | COUNTRY |
+-------------+---------------+------------+---------+
| 1           | Akash         | Delhi      | India   |
| 2           | Amit          | Hyderabad  | India   |
| 3           | Jason         | California | USA     |
+-------------+---------------+------------+---------+

Einfaches ROLLBACK-Beispiel

Vor dem Test des Rollback-Verhaltens wird die Tabelle zunächst zurückgesetzt.

TRUNCATE TABLE customer;

Anschließend werden die vier Datensätze erneut eingefügt.

INSERT INTO customer (customer_id, customer_name, state, country) VALUES
(1, 'Akash', 'Delhi', 'India'),
(2, 'Amit', 'Hyderabad', 'India'),
(3, 'Jason', 'California', 'USA'),
(4, 'John', 'Texas', 'USA');

Nun wird ein Datensatz gelöscht und die Transaktion danach zurückgerollt.

BEGIN;
DELETE FROM customer WHERE state = 'Texas';
ROLLBACK;

Danach wird der Zustand der Tabelle erneut abgefragt.

SELECT * FROM customer;

+-------------+---------------+------------+---------+
| CUSTOMER ID | CUSTOMER NAME | STATE      | COUNTRY |
+-------------+---------------+------------+---------+
| 1           | Akash         | Delhi      | India   |
| 2           | Amit          | Hyderabad  | India   |
| 3           | Jason         | California | USA     |
| 4           | John          | Texas      | USA     |
+-------------+---------------+------------+---------+

SAVEPOINT zusammen mit ROLLBACK verwenden

Mit einem Savepoint können frühere, bereits validierte Änderungen bestehen bleiben, während lediglich später ausgeführte Änderungen derselben Transaktion verworfen werden.

BEGIN;
UPDATE customer SET state = 'Karnataka' WHERE customer_id = 1;
SAVEPOINT sp1;
UPDATE customer SET state = 'Nevada' WHERE customer_id = 4;
ROLLBACK TO SAVEPOINT sp1;
COMMIT;

Die erste Änderung an customer_id = 1 bleibt erhalten, da sie vor dem Anlegen von sp1 durchgeführt wurde. Die spätere Änderung an customer_id = 4 wird durch ROLLBACK TO SAVEPOINT sp1 zurückgenommen. Der abschließende COMMIT speichert alle noch in der Transaktion vorhandenen Änderungen dauerhaft.

SELECT * FROM customer;

+-------------+---------------+------------+---------+
| CUSTOMER ID | CUSTOMER NAME | STATE      | COUNTRY |
+-------------+---------------+------------+---------+
| 1           | Akash         | Karnataka  | India   |
| 2           | Amit          | Hyderabad  | India   |
| 3           | Jason         | California | USA     |
| 4           | John          | Texas      | USA     |
+-------------+---------------+------------+---------+

ROLLBACK bei Fehlern mit TRY/CATCH in SQL Server

Mit TRY/CATCH lässt sich in SQL Server sicherstellen, dass bei einem Fehler innerhalb einer Transaktion ein definierter Rollback-Pfad ausgeführt wird.

BEGIN TRY
    BEGIN TRANSACTION;

    UPDATE customer
    SET state = 'WA'
    WHERE customer_id = 4;

    INSERT INTO customer (customer_id, customer_name, state, country)
    VALUES (4, 'Duplicate Id', 'NA', 'USA');

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
    THROW;
END CATCH;

Der Versuch, den bereits vorhandenen Primärschlüssel customer_id = 4 erneut einzufügen, führt dazu, dass die Verarbeitung in den CATCH-Block wechselt. In sqlcmd sieht die Ausgabe beispielsweise folgendermaßen aus:

Msg 2627, Level 14, State 1, Line 7
Violation of PRIMARY KEY constraint 'PK__customer'. Cannot insert duplicate key in object 'dbo.customer'. The duplicate key value is (4).

Das zuvor ausgeführte UPDATE, das state = 'WA' gesetzt hatte, wird gemeinsam mit dem fehlgeschlagenen INSERT zurückgenommen. Der Tabellenzustand entspricht anschließend wieder dem Zustand vor BEGIN TRANSACTION. Die Bedingung IF @@TRANCOUNT > 0 vor ROLLBACK TRANSACTION ist wichtig, weil bestimmte Fehlerklassen, darunter Verbindungsfehler und einige Fehler ab Severity 17, die Transaktion bereits automatisch zurückrollen können, bevor der CATCH-Block ausgeführt wird. Ein weiterer Rollback ohne aktive Transaktion würde einen zusätzlichen Fehler erzeugen und könnte den ursprünglichen Fehler verdecken.

SQL Server stellt außerdem SET XACT_ABORT ON bereit. Damit kann bei zahlreichen Laufzeitfehlern automatisch ein Rollback erzwungen werden, die andernfalls möglicherweise eine offene Transaktion zurücklassen würden.

ROLLBACK bei Fehlern mit Exception-Behandlung in PostgreSQL und Oracle

PostgreSQL und Oracle verwenden unterschiedliche Mechanismen zur Behandlung von Exceptions. Beide Ansätze ermöglichen jedoch ein kontrolliertes Verhalten bei Fehlern innerhalb von Transaktionen.

DO $$
BEGIN
    UPDATE customer SET state = 'WA' WHERE customer_id = 4;
    INSERT INTO customer (customer_id, customer_name, state, country)
    VALUES (4, 'Duplicate Id', 'NA', 'USA');
EXCEPTION
    WHEN OTHERS THEN
        RAISE;
END;
$$;

Ausgabe in PostgreSQL:

ERROR:  duplicate key value violates unique constraint "customer_pkey"
DETAIL:  Key (customer_id)=(4) already exists.
CONTEXT:  PL/pgSQL function inline_code_block line 4 at SQL statement

Das UPDATE und das fehlgeschlagene INSERT werden innerhalb derselben BEGIN ... EXCEPTION-Subtransaktion ausgeführt. Sobald die Exception ausgelöst wird, setzt PL/pgSQL diese Subtransaktion automatisch zurück. Dadurch behält Kunde 4 seinen vorherigen Zustand.

BEGIN
    UPDATE customer SET state = 'WA' WHERE customer_id = 4;
    INSERT INTO customer (customer_id, customer_name, state, country)
    VALUES (4, 'Duplicate Id', 'NA', 'USA');
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END;
/

Ausgabe in Oracle:

ORA-00001: unique constraint (SCHEMA.SYS_C0011234) violated
ORA-06512: at line 4

Der ROLLBACK im Exception-Handler nimmt sowohl das vorherige UPDATE als auch das fehlgeschlagene INSERT zurück. Dabei wird die gesamte aktuell aktive Sitzungstransaktion zurückgesetzt, einschließlich aller Arbeiten, die bereits vor diesem anonymen Block ausgeführt wurden. Dieses Muster sollte deshalb nur eingesetzt werden, wenn die Prozedur oder der Block die vollständige Transaktionsgrenze kontrollieren soll.

Praxisnahe Transaktionsmuster

Einfache Lehrbeispiele zeigen häufig eine Transaktion mit einer festen Abfolge von Anweisungen. In produktiven Anwendungen werden Transaktionen dagegen oft mit Wiederholungslogik, Batch-Verarbeitung oder Audit-Protokollierung kombiniert. Diese Kombinationen erfordern zusätzliche Vorgehensweisen.

Idempotente Wiederholungsversuche bei Deadlocks. Transaktionen, die unter hoher Parallelität ausgeführt werden, sollten so aufgebaut sein, dass sie gefahrlos erneut gestartet werden können. Eine Retry-Logik kann den datenbankspezifischen Deadlock-Fehler erkennen und die Transaktion nach einer kurzen Verzögerung erneut ausführen.

# Pseudocode in einer beliebigen Host-Sprache. BEGIN/COMMIT durch die
# Transaktionsbefehle des verwendeten Treibers ersetzen.
attempt = 0
while attempt < 3:
    try:
        BEGIN
        UPDATE inventory SET qty = qty - 1 WHERE sku = 'A100' AND qty >= 1
        INSERT INTO orders (sku, customer_id) VALUES ('A100', 42)
        COMMIT
        break
    except DeadlockError:
        ROLLBACK
        attempt += 1
        sleep(2 ** attempt * 0.05)  # 50ms, 100ms, 200ms

Beide Anweisungen innerhalb der Transaktion müssen einen erneuten Ausführungsversuch sicher verkraften. Das UPDATE prüft qty >= 1, sodass kein zusätzlicher Abzug erfolgt, wenn diese Voraussetzung nicht mehr erfüllt ist. Das INSERT könnte dagegen einen doppelten Bestelldatensatz erzeugen. Deshalb sollte die Tabelle eine eindeutige Request-ID besitzen oder eine idempotente Insert-Technik wie INSERT ... ON CONFLICT DO NOTHING in PostgreSQL beziehungsweise INSERT ... ON DUPLICATE KEY UPDATE in MySQL verwenden.

Batch-Verarbeitung mit einem Savepoint pro Datensatz. Wenn eine größere Menge von Datensätzen verarbeitet wird und ein einzelner fehlerhafter Datensatz nicht den gesamten Batch abbrechen soll, kann für jede Iteration ein eigener Savepoint erzeugt werden.

# Pseudocode. Die Transaktion wird einmal geöffnet und einmal beendet;
# Savepoints isolieren Fehler einzelner Datensätze innerhalb der Transaktion.
BEGIN

for record in batch:
    SAVEPOINT sp_record
    try:
        INSERT INTO orders (...) VALUES (record values)
    except Exception:
        ROLLBACK TO SAVEPOINT sp_record
        log_failure(record)
    RELEASE SAVEPOINT sp_record

COMMIT

Dieses Muster wird häufig bei ETL-Importen eingesetzt, bei denen einzelne Datensätze fehlschlagen dürfen. Erfolgreiche Datensätze werden gemeinsam bestätigt, während fehlerhafte Einträge einzeln zurückgenommen und für eine spätere Prüfung protokolliert werden. Nach der Verarbeitung jedes Datensatzes sollte der Savepoint freigegeben werden, damit die Menge der gespeicherten Undo-Informationen nicht mit der Batch-Größe wächst.

Audit-Protokollierung, die einen Rollback überlebt. Audit-Einträge sollen häufig erhalten bleiben, selbst wenn die übergeordnete Transaktion zurückgesetzt wird. Die geeignete Vorgehensweise hängt vom jeweiligen Datenbanksystem ab:

• Oracle: Die Prozedur zum Schreiben des Audit-Eintrags kann mit PRAGMA AUTONOMOUS_TRANSACTION versehen werden. Die autonome Transaktion kann unabhängig bestätigt werden.
• PostgreSQL: Der Audit-Eintrag kann über dblink an eine separate Verbindung gesendet werden, die einen eigenen Transaktionskontext besitzt.
• SQL Server: Der Audit-Eintrag kann über eine Stored Procedure geschrieben werden, die beispielsweise über einen Service Broker aufgerufen wird, oder die Anwendung nutzt dafür eine separate Verbindung.
• MySQL: Das Schreiben erfolgt über eine separate Verbindung. Standard-SQL in MySQL besitzt kein direktes Gegenstück zu autonomen Transaktionen.

Unabhängig von der konkreten Umsetzung gilt bei allen vier Datenbanksystemen dasselbe Prinzip: Der Audit-Schreibvorgang muss außerhalb der Transaktionsgrenze der übergeordneten Operation stattfinden.

COMMIT und ROLLBACK in Stored Procedures

Das Verhalten von Transaktionen innerhalb gespeicherter Prozeduren unterscheidet sich je nach Datenbanksystem. Transaktionsbefehle können erlaubt, eingeschränkt oder vom umgebenden Ausführungskontext abhängig sein. Das Verhalten einer Prozedur sollte deshalb mit denselben Client- und Laufzeiteinstellungen getestet werden, die auch in der Zielumgebung verwendet werden.

Verhalten in Stored Procedures von SQL Server

Stored Procedures in SQL Server arbeiten im Transaktionskontext des Aufrufers, sofern sie nicht selbst eine Transaktion beginnen. Das folgende defensive Muster kann sowohl dann eingesetzt werden, wenn bereits eine Transaktion existiert, als auch dann, wenn noch keine geöffnet ist.

CREATE OR ALTER PROCEDURE dbo.UpdateCustomerState
    @CustomerId INT,
    @State NVARCHAR(100)
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        BEGIN TRANSACTION;
        UPDATE customer SET state = @State WHERE customer_id = @CustomerId;
        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;
        THROW;
    END CATCH;
END;

Drei Techniken aus diesem Beispiel kommen besonders häufig in produktiven T-SQL-Prozeduren vor.

@@TRANCOUNT ist ein Zähler auf Sitzungsebene. Jedes BEGIN TRANSACTION erhöht ihn, während ein COMMIT ihn reduziert. Die Bedingung IF @@TRANCOUNT > 0 vor ROLLBACK TRANSACTION schützt vor Situationen, in denen ein Laufzeit- oder Verbindungsfehler die Transaktion bereits zurückgesetzt hat, bevor die Ausführung den CATCH-Block erreicht. Ein erneuter Rollback ohne aktive Transaktion würde einen weiteren Fehler erzeugen und könnte das ursprüngliche Problem verdecken.

THROW reicht den ursprünglichen Fehler nach dem Rollback wieder an den Aufrufer weiter. Fehlernummer, Severity, Status und Meldung bleiben dabei erhalten. Zum erneuten Auslösen eines vorhandenen Fehlers sollte THROW gegenüber RAISERROR bevorzugt werden, da RAISERROR die Fehler-Metadaten verändert und die Fehlersuche erschweren kann.

SET NOCOUNT ON verhindert, dass SQL Server nach jeder Anweisung Meldungen über die Anzahl betroffener Zeilen sendet. Für die eigentliche Transaktionssteuerung ist dies nicht zwingend erforderlich, in Stored Procedures jedoch üblich, da solche zusätzlichen Meldungen Anwendungen stören können, die den Ergebnisstrom der Datenbank auswerten.

Muss eine Prozedur sicher aufgerufen werden können, wenn der Aufrufer bereits eine laufende Transaktion besitzt, kann BEGIN TRANSACTION durch SAVE TRANSACTION sp_name ersetzt werden. Der Rollback kann dann gezielt mit ROLLBACK TRANSACTION sp_name auf diesen Kontrollpunkt zurückgehen. Dadurch nimmt die Prozedur nur ihre eigenen Änderungen zurück, ohne die offene Transaktion des Aufrufers zu beenden.

Verhalten in Oracle PL/SQL

Stored Procedures in Oracle PL/SQL dürfen COMMIT und ROLLBACK direkt ausführen. Diese Befehle beeinflussen den aktuellen Transaktionskontext der jeweiligen Sitzung.

CREATE OR REPLACE PROCEDURE update_customer_state (
    p_customer_id IN NUMBER,
    p_state IN VARCHAR2
) AS
BEGIN
    UPDATE customer
    SET state = p_state
    WHERE customer_id = p_customer_id;
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END;
/

Ein COMMIT oder ROLLBACK auf Prozedurebene beeinflusst in Oracle die aktuelle Sitzungstransaktion. Eine solche Prozedur sollte deshalb nur dort verwendet werden, wo diese Transaktionsgrenze ausdrücklich beabsichtigt ist.

Verhalten in PostgreSQL-Funktionen und -Prozeduren

PostgreSQL-Prozeduren unterstützen seit PostgreSQL 11 COMMIT und ROLLBACK. Ein Block mit einer EXCEPTION-Klausel darf jedoch die umgebende Transaktion nicht beenden. PostgreSQL-Funktionen können überhaupt keine Befehle zur Transaktionssteuerung ausführen. Welches Muster verwendet wird, hängt daher davon ab, ob eine explizite Transaktionssteuerung oder eine Exception-Behandlung benötigt wird.

Das erste Muster verwendet eine explizite Transaktionssteuerung ohne Exception-Block:

CREATE OR REPLACE PROCEDURE update_customer_state(
    p_customer_id INT,
    p_state TEXT
)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE customer
    SET state = p_state
    WHERE customer_id = p_customer_id;
    COMMIT;
END;
$$;

Das zweite Muster behandelt Exceptions ohne explizite Transaktionsbefehle. Wird eine Exception abgefangen, setzt PL/pgSQL die implizite Subtransaktion automatisch zurück. Ein eigener ROLLBACK-Befehl ist daher nicht erforderlich.

CREATE OR REPLACE PROCEDURE update_customer_state(
    p_customer_id INT,
    p_state TEXT
)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE customer
    SET state = p_state
    WHERE customer_id = p_customer_id;
EXCEPTION
    WHEN OTHERS THEN
        RAISE NOTICE 'Update failed: %', SQLERRM;
        RAISE;
END;
$$;

Ein PL/pgSQL-Block mit einer EXCEPTION-Klausel kann weder COMMIT noch ROLLBACK ausführen. Ein entsprechender Versuch führt zur Laufzeit zu 2D000: invalid_transaction_termination. Innerhalb eines Blocks muss daher je nach gewünschtem Verhalten zwischen expliziter Transaktionssteuerung und Exception-Behandlung gewählt werden.

Transaktionsprobleme in produktiven Umgebungen diagnostizieren

Probleme mit Transaktionen zeigen sich in produktiven Systemen meist auf eine von drei Arten: Eine Abfrage bleibt blockiert, erwartete bestätigte Änderungen fehlen oder Änderungen sind weiterhin vorhanden, obwohl sie eigentlich zurückgerollt werden sollten. Für jedes dieser Symptome ist ein anderer Diagnoseweg erforderlich.

Lang laufende oder blockierte Transaktionen finden

Wenn eine Abfrage scheinbar hängt, wartet sie häufig auf eine Sperre, die von einer anderen Transaktion auf einer benötigten Zeile oder Tabelle gehalten wird. Zunächst sollte die blockierende Transaktion identifiziert werden. Anschließend kann entschieden werden, ob auf deren Abschluss gewartet, die Sitzung beendet oder die Anwendung korrigiert werden soll, die die Transaktion offen gelassen hat.

PostgreSQL:

SELECT pid,
       state,
       xact_start,
       now() - xact_start AS duration,
       query
FROM pg_stat_activity
WHERE state IN ('active', 'idle in transaction')
  AND xact_start IS NOT NULL
ORDER BY xact_start;

Sitzungen, die über längere Zeit als idle in transaction angezeigt werden, sind häufig die Ursache von Blockierungen. Obwohl aktuell keine Abfrage ausgeführt wird, bleibt die Transaktion offen und kann weiterhin Sperren halten. Dies weist häufig darauf hin, dass eine Anwendung eine Transaktion geöffnet und anschließend andere Arbeiten durchgeführt hat. Eine solche Sitzung kann mit SELECT pg_terminate_backend(pid); beendet werden.

SQL Server:

SELECT s.session_id,
       s.login_name,
       t.transaction_id,
       t.transaction_begin_time,
       DATEDIFF(SECOND, t.transaction_begin_time, GETDATE()) AS duration_seconds,
       r.command,
       r.status
FROM sys.dm_tran_active_transactions t
JOIN sys.dm_tran_session_transactions st ON st.transaction_id = t.transaction_id
JOIN sys.dm_exec_sessions s ON s.session_id = st.session_id
LEFT JOIN sys.dm_exec_requests r ON r.session_id = s.session_id
ORDER BY t.transaction_begin_time;

Die Sitzung kann mit KILL <session_id>; beendet werden.

MySQL:

SELECT trx_id,
       trx_state,
       trx_started,
       TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS duration_seconds,
       trx_mysql_thread_id,
       trx_query
FROM information_schema.innodb_trx
ORDER BY trx_started;

Die betreffende Sitzung kann mit KILL <trx_mysql_thread_id>; beendet werden.

Oracle:

SELECT s.sid,
       s.serial#,
       s.username,
       t.start_time,
       t.used_ublk
FROM v$transaction t
JOIN v$session s ON s.saddr = t.ses_addr
ORDER BY t.start_time;

Die Sitzung kann mit ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE; beendet werden. Ohne IMMEDIATE markiert Oracle die Sitzung lediglich zur Beendigung und wartet, bis sie wieder reagiert. Bei einer inaktiven Sitzung kann dieser Vorgang mehrere Minuten dauern.

Eine nicht mehr bestätigbare oder abgebrochene Transaktion erkennen

Eine Transaktion gilt als nicht mehr bestätigbar, wenn ein Fehler aufgetreten ist und ein COMMIT nicht mehr möglich ist. Die verschiedenen Datenbanksysteme zeigen diesen Zustand unterschiedlich an.

PostgreSQL lehnt in diesem Zustand sämtliche Befehle außer ROLLBACK ab und meldet ERROR: current transaction is aborted, commands ignored until end of transaction block. Die Transaktion muss zurückgesetzt werden. Soll eine Anwendung nach einer riskanten Operation innerhalb derselben Transaktion weiterarbeiten können, ohne sämtliche früheren Änderungen zu verwerfen, sollte vor der riskanten Anweisung ein Savepoint angelegt werden. Anschließend kann bei Bedarf ROLLBACK TO SAVEPOINT genutzt werden.

SQL Server stellt den Zustand einer Transaktion über XACT_STATE() bereit. Ein Rückgabewert von -1 bedeutet, dass die Transaktion noch existiert, aber nicht mehr bestätigt werden kann. Ein CATCH-Block in einer produktiven Anwendung kann diesen Zustand folgendermaßen prüfen:

BEGIN CATCH
    IF XACT_STATE() = -1
        ROLLBACK TRANSACTION;
    ELSE IF XACT_STATE() = 1
        COMMIT TRANSACTION;
    THROW;
END CATCH;

MySQL und Oracle stellen kein unmittelbar vergleichbares Statuskennzeichen für eine nicht mehr bestätigbare Transaktion bereit. In MySQL kann innerhalb einer Stored Procedure beispielsweise DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK; definiert werden, um Fehler abzufangen und automatisch zurückzurollen. In Oracle kann innerhalb eines PL/SQL-Blocks EXCEPTION WHEN OTHERS THEN ROLLBACK; RAISE; verwendet werden. Beide Verfahren entsprechen den zuvor beschriebenen Mustern für Stored Procedures.

Prüfen, ob Autocommit Änderungen automatisch gespeichert hat

Wenn ein ROLLBACK scheinbar keine Wirkung zeigt und die Änderungen weiterhin vorhanden sind, ist Autocommit eine wahrscheinliche Ursache. Die aktuelle Einstellung lässt sich je nach Datenbanksystem folgendermaßen prüfen:

Ist Autocommit aktiviert und wird ein einzelnes UPDATE ausgeführt, bevor anschließend ROLLBACK aufgerufen wird, wurde das UPDATE bereits beim erfolgreichen Abschluss bestätigt. Der nachfolgende Rollback hat deshalb keine ausstehenden Änderungen mehr, die verworfen werden könnten. Um dies zu vermeiden, muss die Änderung entweder in eine explizite Transaktion eingebunden oder Autocommit zu Beginn der Sitzung deaktiviert werden.

Dieselben Diagnoseabfragen lassen sich auch bei verwalteten Datenbankdiensten einsetzen. Verwaltungsoberflächen solcher Dienste stellen häufig Kennzahlen zu lang laufenden Abfragen und zur Anzahl von Transaktionen auf Datenbankebene bereit. Slow-Query-Logging für PostgreSQL und MySQL kann über die üblichen Datenbankparameter konfiguriert werden. Bei PostgreSQL-Anwendungen mit transaktionsbewusstem Connection Pooling erhält ein Session-Modus den Transaktionszustand über eine gepoolte Verbindung hinweg. Ein Transaction-Modus bewahrt denselben Sitzungszustand dagegen nicht, sodass Verfahren mit Advisory Locks, Prepared Statements oder SET LOCAL dort abweichend funktionieren können.

Wichtige Unterschiede zwischen den Datenbanksystemen

Die grundlegenden Konzepte der Transaktionssteuerung orientieren sich zwar an gemeinsamen SQL-Prinzipien, Unterschiede bestehen jedoch bei Autocommit, der Behandlung von DDL und der Unterstützung verschachtelter Transaktionen. Diese Abweichungen beeinflussen die Gestaltung sicherer Transaktionsgrenzen, insbesondere wenn eine Anwendung mehrere Datenbanksysteme unterstützen soll.

MySQL und MariaDB

MySQL und MariaDB verwenden normalerweise Autocommit. Ein einzelnes UPDATE oder INSERT, das in einem Client ausgeführt wird, wird daher unmittelbar nach erfolgreicher Ausführung bestätigt. Für Transaktionen mit mehreren Anweisungen muss entweder Autocommit mit SET autocommit = 0 für die Sitzung deaktiviert oder die Verarbeitung ausdrücklich mit START TRANSACTION begonnen werden.

DDL-Anweisungen wie CREATE TABLE, ALTER TABLE, DROP TABLE und TRUNCATE TABLE führen zu impliziten Commits. Nicht bestätigte DML-Änderungen werden dadurch dauerhaft gespeichert. Migrationsskripte, die Schema- und Datenänderungen innerhalb einer vermeintlich gemeinsamen Transaktion kombinieren, können deshalb unerwartete Ergebnisse liefern. DDL und DML sollten in getrennten Transaktionseinheiten ausgeführt werden.

Die Unterstützung von Transaktionen hängt außerdem von der verwendeten Storage Engine ab. InnoDB unterstützt Transaktionen, MyISAM dagegen nicht. Ein START TRANSACTION für eine MyISAM-Tabelle führt nicht zwingend zu einer Fehlermeldung, ROLLBACK kann Änderungen an MyISAM-Datensätzen jedoch nicht zurücknehmen. Vor dem Einsatz von Transaktionen sollte deshalb mit SHOW CREATE TABLE customer\G geprüft werden, ob die Tabelle ENGINE=InnoDB verwendet.

Werte von AUTO_INCREMENT werden bei einem Rollback nicht zurückgesetzt. Wenn ein eingefügter Datensatz einen Wert verbraucht und die zugehörige Transaktion anschließend zurückgerollt wird, bleibt dieser Wert trotzdem verbraucht. Lücken in AUTO_INCREMENT-Spalten sind daher normal.

PostgreSQL

PostgreSQL besitzt keine serverweite Einstellung für Autocommit. Autocommit wird durch Clients und Datenbanktreiber gesteuert. Der psql-Client und viele Treiber verwenden standardmäßig Autocommit, wodurch jede Anweisung als eigene Transaktion ausgeführt wird, sofern nicht zuvor mit BEGIN eine explizite Transaktion geöffnet wurde. In psql lässt sich Autocommit mit \set AUTOCOMMIT off deaktivieren.

DDL ist in PostgreSQL transaktional. Befehle wie CREATE TABLE, ALTER TABLE, DROP TABLE und zahlreiche weitere Schemaänderungen können innerhalb von BEGIN ... COMMIT ausgeführt und gemeinsam mit DML wieder zurückgenommen werden. Ausnahmen sind Operationen, die globalen Zustand verändern oder außerhalb des normalen MVCC-Systems arbeiten. Dazu gehören unter anderem CREATE DATABASE, DROP DATABASE, CREATE INDEX CONCURRENTLY, VACUUM und REINDEX CONCURRENTLY.

Tritt innerhalb einer PostgreSQL-Transaktion ein Fehler auf, wechselt die Transaktion in einen abgebrochenen Zustand. Bis die Transaktion beendet wird, akzeptiert die Sitzung lediglich ROLLBACK oder ROLLBACK TO SAVEPOINT, sofern vor der fehlerhaften Anweisung ein geeigneter Savepoint vorhanden ist. Dadurch verhindert PostgreSQL, dass nach einem Fehler mit einem inkonsistenten Zwischenzustand weitergearbeitet wird. Die PostgreSQL-Dokumentation zur Transaktionsverwaltung beschreibt dieses Wiederherstellungsmodell ausführlicher. Riskante Anweisungen können hinter Savepoints platziert werden, wenn eine Transaktion nach einem Fehler weiterlaufen soll.

Auch PostgreSQL-Sequences sind nicht transaktional. Ein Aufruf von SELECT nextval('customer_id_seq') verbraucht den entsprechenden Wert, selbst wenn die umgebende Transaktion anschließend zurückgerollt wird. Von einer lückenlosen Folge bei sequence-generierten IDs sollte daher nicht ausgegangen werden.

Oracle

Oracle SQL*Plus arbeitet normalerweise mit deaktiviertem Autocommit. Eine Sitzung arbeitet transaktional, wobei die erste DML-Anweisung die Transaktion implizit eröffnet. Für eine normale Oracle-SQL-Transaktion ist daher kein BEGIN oder START TRANSACTION erforderlich. SET TRANSACTION wird stattdessen genutzt, um Eigenschaften wie den Isolationsgrad oder einen Read-only-Modus festzulegen.

DDL löst in Oracle sowohl vor als auch nach der jeweiligen DDL-Anweisung einen impliziten Commit aus. Besonders relevant ist der Commit vor der Anweisung. Befinden sich noch nicht bestätigte DML-Änderungen in der Sitzung und wird beispielsweise ALTER TABLE ausgeführt, bestätigt Oracle zunächst die bestehenden DML-Änderungen, führt anschließend die Schemaänderung aus und bestätigt danach auch die DDL-Anweisung. Die früheren Datenänderungen sind zu diesem Zeitpunkt bereits dauerhaft gespeichert. Migrations- und Testskripte sollten DDL und DML deshalb nicht innerhalb derselben logischen Transaktion vermischen.

Oracle unterstützt autonome Transaktionen über die PL/SQL-Direktive PRAGMA AUTONOMOUS_TRANSACTION. Eine autonome Transaktion kann unabhängig von der aufrufenden Transaktion bestätigt oder zurückgesetzt werden. Dies eignet sich beispielsweise für Audit-Daten, die auch dann erhalten bleiben müssen, wenn die übergeordnete Transaktion fehlschlägt. Da autonome Transaktionen die Isolationsgrenze der Elterntransaktion umgehen, sollten sie bewusst eingesetzt werden.

Oracle-Sequences sind ebenfalls nicht transaktional. customer_id_seq.NEXTVAL verbraucht den jeweiligen Wert unabhängig davon, ob die umgebende Transaktion bestätigt oder zurückgesetzt wird. Zusammen mit der üblichen Einstellung CACHE 20 sind Lücken in Sequence-Werten daher normal.

SQL Server (T-SQL)

SQL Server verwendet standardmäßig Autocommit. Jede einzelne Anweisung bildet eine eigene Transaktion, sofern sie nicht in BEGIN TRANSACTION ... COMMIT TRANSACTION eingeschlossen wird. Der alternative Modus für implizite Transaktionen lässt sich mit SET IMPLICIT_TRANSACTIONS ON aktivieren. In diesem Modus öffnet DML eine Transaktion, die solange bestehen bleibt, bis ausdrücklich ein Commit oder Rollback ausgeführt wird.

DDL ist in SQL Server teilweise transaktional. Zahlreiche DDL-Anweisungen wie CREATE TABLE, ALTER TABLE und DROP TABLE können innerhalb einer expliziten Transaktion zurückgerollt werden. Ausnahmen sind Vorgänge, die das Dateisystem oder systemweite Metadaten auf eine Weise verändern, die vom Datenbanksystem nicht rückgängig gemacht werden kann. Dazu gehören beispielsweise CREATE DATABASE, BACKUP, RESTORE und bestimmte ALTER DATABASE-Anweisungen.

SQL Server unterstützt keine echten verschachtelten Transaktionen. Ein BEGIN TRANSACTION innerhalb einer bereits offenen Transaktion erhöht lediglich den Zähler @@TRANCOUNT, erzeugt jedoch keine unabhängige innere Transaktion. Erst der äußerste COMMIT bestätigt die Änderungen tatsächlich. Ein ROLLBACK nimmt dagegen die gesamte äußerste Transaktion zurück, unabhängig davon, wie hoch der Verschachtelungszähler ist. Deshalb prüft defensiver T-SQL-Code häufig vor einem Rollback mit IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION, ob überhaupt noch eine aktive Transaktion vorhanden ist.

Von IDENTITY-Spalten erzeugte Werte werden durch einen Rollback nicht zurückgesetzt. Ein fehlgeschlagenes Insert kann daher Lücken in der Identity-Folge hinterlassen. Mit DBCC CHECKIDENT können Identity-Werte geprüft oder neu gesetzt werden, wobei Lücken grundsätzlich ein normales Verhalten darstellen.

XACT_ABORT und XACT_STATE() sind zwei zentrale Werkzeuge für defensive T-SQL-Transaktionen. SET XACT_ABORT ON sorgt dafür, dass die Transaktion bei Laufzeitfehlern ab Severity 16 automatisch zurückgerollt wird. Dadurch wird eine Fehlerklasse vermieden, bei der TRY/CATCH andernfalls eine nicht mehr bestätigbare Transaktion geöffnet lassen könnte. XACT_STATE() liefert -1, wenn eine Transaktion weiterhin existiert, aber nicht mehr bestätigt werden kann. Produktive Stored Procedures sollten diesen Zustand daher prüfen, bevor innerhalb eines CATCH-Blocks ein COMMIT versucht wird.

Häufige Fehler und wie sie vermieden werden

• Wenn Autocommit unbeabsichtigt aktiviert bleibt, kann jede einzelne Anweisung dauerhaft gespeichert werden, bevor spätere Validierungsschritte abgeschlossen sind. Bei mehrstufigen Vorgängen sollte die Transaktion deshalb ausdrücklich mit dem jeweiligen Befehl des Datenbanksystems gestartet werden, beispielsweise BEGIN, START TRANSACTION oder BEGIN TRANSACTION. Auf Sitzungsvorgaben, die sich zwischen Umgebungen unterscheiden können, sollte nicht vertraut werden.
• Die Annahme, dass DDL in MySQL oder Oracle zurückgerollt werden kann, kann unerwartete permanente Datenänderungen verursachen, weil DDL vorherige DML implizit bestätigt. Schema- und Datenänderungen sollten deshalb in getrennten Migrationsdateien oder Transaktionseinheiten ausgeführt werden. ALTER TABLE sollte bei diesen Datenbanksystemen nicht zwischen nicht bestätigten DML-Anweisungen stehen.
• Eine Kombination aus impliziten und expliziten Transaktionsmustern innerhalb desselben Skripts kann zu uneinheitlichen Rollback-Grenzen führen. Pro Skript sollte ein konsistenter Ansatz gewählt werden: Entweder wird Autocommit deaktiviert und DML entsprechend verarbeitet, oder Autocommit bleibt aktiv und mehrstufige Abläufe werden vollständig in explizite BEGIN ... COMMIT-Blöcke eingeschlossen.
• Zu lange offen gehaltene Transaktionen verlängern Sperrzeiten, blockieren andere Sitzungen und erhöhen das Risiko von Timeouts. Sitzungsspezifische Zeitlimits können beispielsweise mit SET statement_timeout = '30s' in PostgreSQL, SET SESSION MAX_EXECUTION_TIME = 30000 in MySQL oder SET LOCK_TIMEOUT 30000 in SQL Server konfiguriert werden. Lang laufende Sitzungen lassen sich unter PostgreSQL beispielsweise mit SELECT * FROM pg_stat_activity WHERE state = 'idle in transaction' und unter SQL Server mit SELECT * FROM sys.dm_tran_active_transactions finden.
• Wenn Deadlocks nicht mit Wiederholungsversuchen behandelt werden, können parallele Batch-Prozesse sporadisch fehlschlagen. Die Transaktion sollte von einer Retry-Logik umgeben werden, die den jeweiligen Deadlock-Fehler erkennt. Dazu gehören Fehler 1213 bei MySQL, 40P01 bei PostgreSQL, 1205 bei SQL Server und ORA-00060 bei Oracle. Danach kann die Transaktion mit exponentiellem Backoff erneut ausgeführt werden. Voraussetzung ist, dass die Transaktion idempotent aufgebaut ist.
• Nicht getestete Rollback-Pfade können Fehler in der Fehlerbehandlung bis zum produktiven Betrieb unentdeckt lassen. In Tests kann gezielt eine fehlschlagende Anweisung wie eine Verletzung des Primärschlüssels innerhalb der Transaktion ausgelöst werden. Anschließend sollte geprüft werden, ob der Datenbankzustand nach dem Rollback dem Zustand vor der Transaktion entspricht.
• Die mehrfache Verwendung identischer Savepoint-Namen erzeugt datenbankspezifisches Verhalten, das schwer nachvollziehbar sein kann. Für jeden Kontrollpunkt sollten daher eindeutige Namen wie sp_iteration_1 und sp_iteration_2 oder ein Zähler verwendet werden.
• Ein COMMIT ohne aktive Transaktion kann je nach Datenbanksystem stillschweigend erfolgreich sein, eine Warnung erzeugen oder zu einem Fehler führen. Anwendungen, die mehrere Datenbanksysteme unterstützen, sollten deshalb vor dem Commit den Transaktionszustand prüfen. PostgreSQL bietet hierfür pg_current_xact_id_if_assigned(), SQL Server XACT_STATE() und MySQL @@in_transaction.

Häufig gestellte Fragen

Was ist der Unterschied zwischen COMMIT und ROLLBACK in SQL?

COMMIT speichert die Änderungen der aktuellen Transaktion dauerhaft. ROLLBACK verwirft noch nicht bestätigte Änderungen und stellt den zuletzt bestätigten Datenzustand wieder her.

Was passiert, wenn weder COMMIT noch ROLLBACK ausgeführt wird?

Das Verhalten hängt vom Datenbanksystem und den Einstellungen der Sitzung ab. Ist Autocommit aktiviert, werden einzelne Anweisungen automatisch bestätigt. Explizite Transaktionen werden bei einem unerwarteten Ende der Sitzung üblicherweise zurückgerollt, sofern sie zuvor nicht abgeschlossen wurden.

Was ist ein SAVEPOINT in SQL und wann sollte er verwendet werden?

Ein SAVEPOINT markiert einen Zwischenpunkt innerhalb einer aktiven Transaktion. Er eignet sich, wenn ein späterer Teil einer mehrstufigen Verarbeitung möglicherweise zurückgenommen werden muss, ohne zuvor bereits validierte Änderungen zu verwerfen.

Kann ein COMMIT in SQL zurückgerollt werden?

Nein. Sobald eine Transaktion bestätigt wurde, kann sie mit den gewöhnlichen Befehlen zur Transaktionssteuerung nicht mehr zurückgenommen werden. Eine Wiederherstellung nach einem Commit benötigt Backups oder datenbankspezifische Wiederherstellungsmechanismen außerhalb von COMMIT und ROLLBACK.

Wie behandelt SQL Server einen automatischen ROLLBACK bei Fehlern?

SQL Server rollt mit den Standardeinstellungen nicht jeden Fehler automatisch zurück. Riskante Anweisungen sollten deshalb innerhalb von TRY/CATCH ausgeführt werden. Im CATCH-Block kann ROLLBACK TRANSACTION verwendet und mit IF @@TRANCOUNT > 0 abgesichert werden. Für ein strengeres Verhalten sorgt SET XACT_ABORT ON, das bei den meisten Laufzeitfehlern ab Severity 16 automatisch einen Rollback auslöst. Mit XACT_STATE() lässt sich außerdem eine nicht mehr bestätigbare Transaktion erkennen, wenn die Funktion -1 zurückgibt.

Ist die Syntax von COMMIT und ROLLBACK in Oracle und MySQL identisch?

Die grundlegende Syntax von COMMIT und ROLLBACK ist bei beiden Systemen vergleichbar. Unterschiede bestehen vor allem im Standardverhalten. MySQL verwendet normalerweise Autocommit, während Oracle SQL*Plus üblicherweise mit deaktiviertem Autocommit arbeitet.

Können COMMIT und ROLLBACK innerhalb einer Stored Procedure verwendet werden?

Ja, allerdings unterscheiden sich Unterstützung und Verhalten zwischen den Datenbanksystemen. Stored Procedures in SQL Server arbeiten innerhalb des Transaktionskontexts des Aufrufers und verwenden häufig TRY/CATCH mit IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION. Oracle-PL/SQL-Prozeduren dürfen COMMIT und ROLLBACK direkt ausführen, wobei ein Commit auf Prozedurebene die gesamte aktuelle Sitzungstransaktion beeinflusst. PostgreSQL-Prozeduren unterstützen seit Version 11 COMMIT und ROLLBACK, PostgreSQL-Funktionen dagegen nicht. Zusätzlich darf ein PostgreSQL-Block mit einer EXCEPTION-Klausel keine Befehle zur Transaktionssteuerung ausführen.

Was ist der Unterschied zwischen ROLLBACK und ROLLBACK TO SAVEPOINT?

ROLLBACK ohne weitere Argumente nimmt sämtliche Änderungen der aktiven Transaktion zurück und beendet diese. ROLLBACK TO SAVEPOINT verwirft nur die Änderungen, die nach dem angegebenen Savepoint ausgeführt wurden, und lässt die Transaktion weiterhin geöffnet.

Fazit

Dieses Tutorial hat SQL-Transaktionen und die ACID-Eigenschaften, die Syntax und das Verhalten von COMMIT, ROLLBACK und SAVEPOINT, die Fehlerbehandlung mit TRY/CATCH und Exception-Blöcken, die Transaktionssteuerung in Stored Procedures der vier großen Datenbanksysteme, retry-sichere und batch-sichere Transaktionsmuster sowie Diagnoseabfragen für lang laufende und nicht mehr bestätigbare Transaktionen in produktiven Umgebungen behandelt.

Mit diesen Techniken lässt sich transaktionssicheres SQL entwickeln, das ausschließlich validierte Änderungen bestätigt. Fehler können mit TRY/CATCH oder Exception-Blöcken behandelt, Savepoints für partielle Rollbacks eingesetzt, idempotente Wiederholungslogiken für Deadlock-gefährdete Workloads erstellt und einzelne Fehler innerhalb von Batch-Verarbeitungen isoliert werden. Bei Problemen lassen sich Transaktionen außerdem direkt mit pg_stat_activity, sys.dm_tran_active_transactions, information_schema.innodb_trx oder v$transaction untersuchen.