Analyse typischer Fehler beim Arbeiten mit verschachtelten Transaktionen

Programmieren mit Postgres ist manchmal wie ein Adventure-Game: Manchmal wird es zum Quest „Finde deinen Fehler“. In diesem Abschnitt reden wir über typische Fehler und Stolpersteine, die dir bei verschachtelten Transaktionen begegnen können. Los geht’s!

Falsche Nutzung von Transaktionsbefehlen in Funktionen und Prozeduren

Fehler: Versuch, COMMIT, ROLLBACK oder SAVEPOINT innerhalb einer FUNCTION zu verwenden.

Warum: In PostgreSQL werden Funktionen (CREATE FUNCTION ... LANGUAGE plpgsql) immer innerhalb einer äußeren Transaktion ausgeführt, und alle Transaktionsbefehle innerhalb einer Funktion sind verboten. Ein Versuch, sie zu nutzen, führt zu einem Syntaxfehler.

Beispiel für einen Fehler:

CREATE OR REPLACE FUNCTION f_bad() RETURNS void AS $$
BEGIN
    SAVEPOINT sp1;  -- Fehler: Transaktionsbefehle sind verboten
END;
$$ LANGUAGE plpgsql;

Wie richtig:

Für atomare Operationen, die „alles oder nichts“ sein sollen, nutze Funktionen ohne explizite Transaktionsbefehle. Wenn du schrittweise Änderungen festhalten willst, verwende Prozeduren.

Fehler: Versuch, ROLLBACK TO SAVEPOINT in einer Prozedur mit PL/pgSQL zu nutzen.

Warum: In PostgreSQL 17 sind nur die Befehle COMMIT, ROLLBACK, SAVEPOINT, RELEASE SAVEPOINT innerhalb von Prozeduren (CREATE PROCEDURE ... LANGUAGE plpgsql) erlaubt. Aber ROLLBACK TO SAVEPOINT in PL/pgSQL geht nicht! Jeder Versuch führt zu einem Syntaxfehler.

Beispiel für einen Fehler:

CREATE PROCEDURE p_bad()
LANGUAGE plpgsql
AS $$
BEGIN
    SAVEPOINT sp1;
    -- ...
    ROLLBACK TO SAVEPOINT sp1; -- Fehler! Nicht erlaubt
END;
$$;

Wie richtig:

Für einen „teilweisen Rollback“ nutze BEGIN ... EXCEPTION ... END-Blöcke – die erzeugen automatisch einen Savepoint; bei einem Fehler im Block werden alle Änderungen bis zum Blockanfang zurückgesetzt.

CREATE PROCEDURE p_good()
LANGUAGE plpgsql
AS $$
BEGIN
    BEGIN
        -- Operationen, die einen Fehler werfen könnten
        ...
    EXCEPTION
        WHEN OTHERS THEN
            RAISE NOTICE 'Rollback innerhalb von BEGIN ... EXCEPTION ... END';
    END;
END;
$$;

Verschachtelte Prozeduraufrufe: Einschränkungen und typische Fehler

Fehler: Aufruf einer Prozedur mit explizitem COMMIT/ROLLBACK innerhalb einer bereits offenen Client-Transaktion.

Warum: Prozeduren mit Transaktionskontrolle funktionieren nur im Autocommit-Modus korrekt (eine Prozedur – eine Transaktion), sonst gibt es beim Versuch, COMMIT oder ROLLBACK innerhalb der Prozedur zu nutzen, einen Fehler: Die Transaktion ist auf Client-Ebene schon offen.

Beispiel:

# In Python mit psycopg2 ist autocommit standardmäßig False
cur.execute("BEGIN;")
cur.execute("CALL my_proc();")   -- Fehler beim Versuch von COMMIT in my_proc

Wie richtig:

• Vor dem Aufruf von Prozeduren schalte die Verbindung auf autocommit.
• Rufe Prozeduren nicht über Funktionen oder SELECT auf.

Fehler: Aufruf von Prozeduren mit Transaktionskontrolle (COMMIT, ROLLBACK) funktioniert nicht, wenn sie NICHT mit CALL (z.B. über SELECT) aufgerufen werden.

Warum: Nur der Aufruf über CALL (oder im anonymen DO-Block) erlaubt Transaktionskontrolle. Ein Aufruf aus einer Funktion ist nicht erlaubt.

Probleme mit Sperren und Deadlocks

Sperren sind wie ungebetene Gäste: Erst nerven sie, dann gibt’s Chaos. Ein Deadlock entsteht, wenn Transaktionen ewig aufeinander warten. Typisches Beispiel:

1. Transaktion A sperrt eine Zeile in der Tabelle orders und versucht, eine Zeile in products zu aktualisieren.
2. Transaktion B sperrt eine Zeile in products und versucht, eine Zeile in orders zu aktualisieren.

Am Ende kann keine der Transaktionen weitermachen. Das ist wie zwei Autos, die gleichzeitig um eine enge Kurve wollen – und es gibt Stau.

Beispiel:

-- Transaktion A
BEGIN;
UPDATE orders SET status = 'Processing' WHERE id = 1;

-- Transaktion B
BEGIN;
UPDATE products SET stock = stock - 1 WHERE id = 10;

-- Jetzt versucht Transaktion A, dieselbe Zeile in `products` zu aktualisieren,
-- und Transaktion B will die Zeile in `orders` ändern.
-- Deadlock!

Wie vermeiden?

1. Immer in derselben Reihenfolge updaten. Zum Beispiel erst orders, dann products.
2. Vermeide zu lange Transaktionen.
3. Nutze LOCK mit Bedacht und wähle das minimale Sperrlevel.

Falsche Nutzung von dynamischem SQL (EXECUTE)

Dynamisches SQL kann, wenn man nicht aufpasst, echt Kopfschmerzen machen. Der häufigste Fehler: SQL-Injection. Zum Beispiel:

EXECUTE 'SELECT * FROM orders WHERE id = ' || user_input;

Wenn user_input sowas wie 1; DROP TABLE orders; enthält, kannst du dich von der Tabelle orders verabschieden.

Wie vermeiden? Nutze Prepared Statements:

EXECUTE 'SELECT * FROM orders WHERE id = $1' USING user_input;

So schützt du deine App vor SQL-Injections.

Rollback der Transaktion nach falscher Fehlerbehandlung

Wenn Fehler nicht richtig behandelt werden, kann die Transaktion in einem ungültigen Zustand bleiben. Zum Beispiel:

BEGIN;

INSERT INTO orders (order_id, status) VALUES (1, 'Pending');

BEGIN;
-- Irgendeine Operation, die einen Fehler wirft
INSERT INTO non_existing_table VALUES (1);
-- Fehler, aber die Transaktion wurde nicht beendet

COMMIT; -- Fehler: aktuelle Transaktion ist abgebrochen

Wegen des Fehlers bleibt der ganze Code hängen.

Wie vermeiden? Nutze EXCEPTION-Blöcke für sauberen Rollback:

BEGIN
    INSERT INTO orders (order_id, status) VALUES (1, 'Pending');
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE NOTICE 'Fehler ist aufgetreten, Transaktion wird zurückgesetzt.';
END;

Wie Fehler vermeiden: Tipps und Tricks

• Beim Schreiben einer komplexen Prozedur fang immer mit Pseudocode an. Schreib alle Schritte und mögliche Fehlerquellen auf.
• Nutze SAVEPOINT für isolierte Rollbacks. Aber vergiss nicht, sie nach der Nutzung freizugeben.
• Vermeide lange Transaktionen – je länger die Transaktion, desto höher das Risiko für Sperren.
• Bei verschachtelten Prozeduraufrufen: Achte darauf, dass äußerer und innerer Transaktionskontext sauber synchronisiert sind.
• Checke immer die Performance deiner Prozeduren mit EXPLAIN ANALYZE.
• Logge Fehler in Tabellen oder Textdateien – das macht Debugging leichter.

Beispiele für Fehler und deren Behebung

Beispiel 1: Fehler beim Aufruf einer verschachtelten Prozedur

Fehlerhafter Code:

BEGIN;

CALL process_order(5);

-- Innerhalb von process_order gab es ein ROLLBACK
-- Die ganze Transaktion ist ungültig
COMMIT; -- Fehler

Korrigierter Code:

BEGIN;

SAVEPOINT sp_outer;

CALL process_order(5);

-- Rollback nur bei Fehler
ROLLBACK TO SAVEPOINT sp_outer;

COMMIT;

Beispiel 2: Deadlock-Problem

Fehlerhafter Code:

-- Transaktion A
BEGIN;
UPDATE orders SET status = 'Processing' WHERE id = 1;
-- Wartet auf `products`

-- Transaktion B
BEGIN;
UPDATE products SET stock = stock - 1 WHERE id = 10;
-- Wartet auf `orders`

Behebung:

-- Beide Abfragen laufen in derselben Reihenfolge:
-- Erst `products`, dann `orders`.
BEGIN;
UPDATE products SET stock = stock - 1 WHERE id = 10;
UPDATE orders SET status = 'Processing' WHERE id = 1;
COMMIT;

Diese Fehler zeigen, warum Arbeit mit Transaktionen Aufmerksamkeit und Erfahrung braucht. Aber wie man so schön sagt: Je mehr Praxis, desto weniger ROLLBACK im echten Leben (und im Job).