Autonome Transaktionen in Oracle PL/SQL mit PRAGMA AUTONOMOUS_TRANSACTION

Eine autonome Transaktion läuft unabhängig von der aufrufenden Haupttransaktion. Sie besitzt einen eigenen Transaktionskontext und kann Änderungen mit COMMIT dauerhaft speichern, auch wenn die Haupttransaktion später mit ROLLBACK zurückgesetzt wird.

Autonome Transaktionen werden typischerweise für technische Protokollierungen verwendet, beispielsweise:

• Fehlerprotokolle
• Audit-Einträge
• technische Statusmeldungen
• Protokollierung fehlgeschlagener Verarbeitungsschritte

Die Direktive wird im Deklarationsteil einer PL/SQL-Prozedur, Funktion, eines Triggers oder eines anonymen Blocks angegeben:

PRAGMA AUTONOMOUS_TRANSACTION;

Grundlegendes Beispiel

Zuerst wird eine Tabelle für technische Protokolle angelegt:

CREATE TABLE verarbeitungs_log (
    log_id          NUMBER GENERATED BY DEFAULT AS IDENTITY,
    erstellt_am     TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL,
    meldung         VARCHAR2(1000),
    CONSTRAINT pk_verarbeitungs_log PRIMARY KEY (log_id)
);

Danach wird eine autonome Protokollierungsprozedur erstellt:

CREATE OR REPLACE PROCEDURE schreibe_log (
    p_meldung IN VARCHAR2
) AS
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    INSERT INTO verarbeitungs_log (
        meldung
    )
    VALUES (
        p_meldung
    );

    COMMIT;
END schreibe_log;
/

Die Prozedur startet beim Aufruf eine eigene Transaktion. Das darin ausgeführte COMMIT betrifft ausschließlich die autonome Transaktion.

Verhalten bei einem Rollback der Haupttransaktion

Für das Beispiel wird eine einfache Geschäftstabelle angelegt:

CREATE TABLE auftrag (
    auftrag_id    NUMBER PRIMARY KEY,
    bezeichnung   VARCHAR2(200)
);

Anschließend wird ein Datensatz eingefügt, ein Logeintrag geschrieben und die Haupttransaktion zurückgerollt:

BEGIN
    INSERT INTO auftrag (
        auftrag_id,
        bezeichnung
    )
    VALUES (
        100,
        'Testauftrag'
    );

    schreibe_log(
        'Auftrag 100 wurde angelegt.'
    );

    ROLLBACK;
END;
/

Nach dem Block ist der Auftrag nicht mehr vorhanden:

SELECT *
FROM auftrag
WHERE auftrag_id = 100;

Ergebnis:

Keine Zeilen ausgewählt

Der Protokolleintrag bleibt dagegen gespeichert:

SELECT
    log_id,
    erstellt_am,
    meldung
FROM verarbeitungs_log
ORDER BY log_id;

Mögliches Ergebnis:

Das ROLLBACK der Haupttransaktion hat keinen Einfluss auf das zuvor ausgeführte COMMIT der autonomen Transaktion.

Fehlerprotokollierung mit SQLCODE und SQLERRM

Ein typischer Einsatzzweck ist das Speichern technischer Fehler, bevor die Haupttransaktion zurückgesetzt wird.

Die Logtabelle kann dafür erweitert werden:

CREATE TABLE fehler_log (
    fehler_id        NUMBER GENERATED BY DEFAULT AS IDENTITY,
    erstellt_am      TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL,
    modul            VARCHAR2(100),
    fehlercode       NUMBER,
    fehlermeldung    VARCHAR2(4000),
    CONSTRAINT pk_fehler_log PRIMARY KEY (fehler_id)
);

Autonome Protokollierungsprozedur:

CREATE OR REPLACE PROCEDURE protokolliere_fehler (
    p_modul         IN VARCHAR2,
    p_fehlercode    IN NUMBER,
    p_fehlermeldung IN VARCHAR2
) AS
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    INSERT INTO fehler_log (
        modul,
        fehlercode,
        fehlermeldung
    )
    VALUES (
        p_modul,
        p_fehlercode,
        SUBSTR(p_fehlermeldung, 1, 4000)
    );

    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
END protokolliere_fehler;
/

Verwendung in einer fachlichen Prozedur:

CREATE OR REPLACE PROCEDURE verarbeite_auftrag (
    p_auftrag_id IN NUMBER
) AS
BEGIN
    INSERT INTO auftrag (
        auftrag_id,
        bezeichnung
    )
    VALUES (
        p_auftrag_id,
        NULL
    );

    RAISE_APPLICATION_ERROR(
        -20001,
        'Testfehler bei der Auftragsverarbeitung'
    );

EXCEPTION
    WHEN OTHERS THEN
        protokolliere_fehler(
            p_modul         => 'VERARBEITE_AUFTRAG',
            p_fehlercode    => SQLCODE,
            p_fehlermeldung => SQLERRM
        );

        ROLLBACK;
        RAISE;
END verarbeite_auftrag;
/

Der Fehler wird erneut ausgelöst, damit die aufrufende Anwendung erkennt, dass die Verarbeitung fehlgeschlagen ist. Der Logeintrag bleibt trotzdem dauerhaft gespeichert.

COMMIT oder ROLLBACK ist verpflichtend

Eine autonome Transaktion muss vor dem Verlassen des autonomen Blocks ausdrücklich abgeschlossen werden.

Falsch:

CREATE OR REPLACE PROCEDURE schreibe_log_falsch (
    p_meldung IN VARCHAR2
) AS
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    INSERT INTO verarbeitungs_log (
        meldung
    )
    VALUES (
        p_meldung
    );
END schreibe_log_falsch;
/

Beim Aufruf kann Oracle folgenden Fehler auslösen:

ORA-06519: active autonomous transaction detected and rolled back

Richtig:

BEGIN
    INSERT INTO verarbeitungs_log (
        meldung
    )
    VALUES (
        p_meldung
    );

    COMMIT;
END;

Alternativ muss bei einem Fehler ein ROLLBACK ausgeführt werden.

Die autonome Transaktion sieht keine unbestätigten Änderungen

Die autonome Transaktion ist unabhängig von der Haupttransaktion. Sie kann daher unbestätigte Änderungen der Haupttransaktion normalerweise nicht sehen.

Beispiel:

BEGIN
    INSERT INTO auftrag (
        auftrag_id,
        bezeichnung
    )
    VALUES (
        200,
        'Noch nicht bestätigter Auftrag'
    );

    schreibe_log(
        'Auftrag 200 wurde vorbereitet.'
    );

    ROLLBACK;
END;
/

Die autonome Prozedur kann den neuen Auftrag nicht zuverlässig aus der Tabelle lesen, solange die Haupttransaktion ihn noch nicht bestätigt hat.

Deshalb sollten alle benötigten Informationen als Parameter an die Protokollierungsprozedur übergeben werden:

schreibe_log(
    'Auftrag 200 wurde vorbereitet.'
);

Nicht darauf verlassen:

SELECT bezeichnung
INTO v_bezeichnung
FROM auftrag
WHERE auftrag_id = 200;

Gefahr einer Sperre oder eines Deadlocks

Die Haupttransaktion kann Datensätze sperren, auf die anschließend die autonome Transaktion zugreifen möchte.

Beispiel:

UPDATE auftrag
SET bezeichnung = 'Geändert'
WHERE auftrag_id = 300;

Wird danach eine autonome Prozedur aufgerufen, die denselben Datensatz aktualisieren möchte, wartet sie auf die Sperre der Haupttransaktion.

Die Haupttransaktion wartet jedoch gleichzeitig darauf, dass die autonome Prozedur beendet wird. Dadurch kann ein Deadlock entstehen.

Autonome Protokollierungen sollten daher bevorzugt in eigenen Logtabellen arbeiten und keine durch die Haupttransaktion gesperrten Geschäftsdaten verändern.

Geeignete Verwendung

Autonome Transaktionen sind geeignet für:

• dauerhafte Fehlerprotokollierung
• technische Audit-Einträge
• Diagnoseinformationen
• Protokollierung unabhängig vom fachlichen Rollback
• bestimmte Datenbanktrigger mit eigenständiger Protokollierung

Ungeeignete Verwendung

Autonome Transaktionen sollten nicht verwendet werden, um:

• fachliche Teiländerungen künstlich dauerhaft zu speichern
• fehlende Transaktionskonzepte zu umgehen
• in jeder Prozedur automatisch ein COMMIT auszuführen
• zusammengehörige Geschäftsdaten getrennt zu bestätigen
• Sperrprobleme zu umgehen
• unkontrollierte Nebenwirkungen in Triggern zu erzeugen

Ein fachlicher Vorgang sollte grundsätzlich entweder vollständig erfolgreich sein oder vollständig zurückgesetzt werden. Autonome Transaktionen sind deshalb vor allem für technische Nebeninformationen geeignet.

Kurze Zusammenfassung

Deklaration:
PRAGMA AUTONOMOUS_TRANSACTION;

Transaktionskontext:
Unabhängig von der Haupttransaktion

COMMIT:
Bestätigt nur die autonome Transaktion

ROLLBACK der Haupttransaktion:
Hat keinen Einfluss auf bereits bestätigte autonome Änderungen

Typischer Einsatz:
Fehler- und Audit-Protokollierung

Wichtig:
Autonome Transaktion immer mit COMMIT oder ROLLBACK abschließen