Transaktionen

Eine Transaktion ist eine Folge von SQL-Anweisungen, die als Einheit ausgeführt (Commit) oder zurückgenommen (Rollback) werden.

Unter diesem Thema:

Einführung

Was ist eine Transaktion?

Eine Transaktion ist eine Folge von SQL-Anweisungen, die als atomare Einheit verarbeitet werden. Alle Anweisungen in der Transaktion werden entweder angewendet (d. h. Commit) oder rückgängig gemacht (d. h. Rollback). Snowflake-Transaktionen garantieren ACID-Eigenschaften.

Die Transaktion kann sowohl Lese- als auch Schreibvorgänge umfassen.

Transaktionen folgen diesen Regeln:

  • Transaktionen sind niemals „verschachtelt“. Sie können z. B. keine „äußere“ Transaktion erstellen, die eine mit Commit bestätigte „innere“ Transaktion zurücknimmt (Rollback), und Sie könne auch keine „äußere“ Transaktion erstellen, die eine „innere“ Transaktion, die mit Rollback zurückgenommen wurde, mit Commit bestätigt.

  • Eine Transaktion ist genau einer Sitzung zugeordnet. Mehrere Sitzungen können nicht dieselbe Transaktion teilen. Weitere Informationen zur Verwendung von Transaktionen mit überlappenden Threads in derselben Sitzung finden Sie unter Transaktionen und Multithreading.

Terminologie

Unter diesem Thema:

  • Der Begriff „DDL“ umfasst sowohl CTAS-Anweisungen (CREATE TABLE AS SELECT ...) als auch andere DDL-Anweisungen.

  • Der Begriff „DML“ bezieht sich auf die Anweisungen INSERT, UPDATE, DELETE, MERGE und TRUNCATE.

  • Der Begriff „Abfrageanweisung“ bezieht sich auf die Anweisungen SELECT und CALL.

Obwohl eine CALL-Anweisung (die eine gespeicherte Prozedur aufruft) eine einzelne Anweisung ist, kann die von ihr aufgerufene gespeicherte Prozedur mehrere Anweisungen enthalten. Es gibt besondere Regeln für gespeicherte Prozeduren und Transaktionen.

Explizite Transaktionen

Eine Transaktion kann explizit gestartet werden, indem eine BEGIN-Anweisung ausgeführt wird. Snowflake unterstützt die Synonyme BEGIN WORK und BEGIN TRANSACTION. Snowflake empfiehlt die Verwendung von BEGIN TRANSACTION.

Eine Transaktion kann explizit durch die Ausführung von COMMIT oder ROLLBACK beendet werden. Snowflake unterstützt das Synonym COMMIT WORK für COMMIT, und das Synonym ROLLBACK WORK für ROLLBACK.

Wenn eine Transaktion bereits aktiviert ist, werden im Allgemeinen alle BEGIN TRANSACTION-Anweisungen ignoriert. Die Benutzer sollten zusätzliche BEGIN TRANSACTION-Anweisungen jedoch vermeiden, weil diese zusätzlichen BEGIN TRANSACTION-Anweisungen es menschlichen Lesern erschweren, den COMMIT- bzw. ROLLBACK-Anweisungen die zugehörigen BEGIN TRANSACTION -Anweisungen zuzuordnen.

Eine Ausnahme von dieser Regel betreffen verschachtelte Aufrufe von gespeicherten Prozeduren. Weitere Details dazu finden Sie unter Bereichsbezogene Transaktionen.

Bemerkung

Explizite Transaktionen sollten nur DML-Anweisungen und Abfrageanweisungen enthalten. DDL-Anweisungen führen das Commit aktivierter Transaktionen implizit aus (Details dazu finden Sie im Abschnitt DDL).

Implizite Transaktionen

Transaktionen können implizit gestartet und beendet werden, ohne dass explizit BEGIN TRANSACTION oder COMMIT/ROLLBACK ausgeführt werden muss. Implizite Transaktionen verhalten sich genauso wie explizite Transaktionen. Die Regeln, die bestimmen, wann die implizite Transaktion beginnt und wann sie endet, unterscheiden sich jedoch von den Regeln für explizite Transaktionen.

Die Regeln für das Anhalten und Starten hängen davon ab, ob es sich bei der Anweisung um eine DDL-Anweisung, eine DML-Anweisung oder eine Abfrageanweisung handelt. Wenn die Anweisung eine DML- oder Abfrageanweisung ist, hängen die Regeln davon ab, ob AUTOCOMMIT aktiviert ist.

DDL

Jede DDL-Anweisung wird als separate Transaktion ausgeführt.

Wenn eine DDL-Anweisung ausgeführt wird, während eine Transaktion aktiviert ist, führt die DDL-Anweisung Folgendes aus:

  1. Ein implizites Commit der aktivierten Transaktion

  2. die DDL-Anweisung als separate Transaktion

Da eine DDL-Anweisung eine eigene Transaktion ist, können Sie für eine DDL-Anweisung auch kein ROLLBACK ausführen. Die Transaktion, die die DDL enthält, wird abgeschlossen, bevor Sie ein explizites ROLLBACK ausführen können.

Wenn der DDL-Anweisung jedoch unmittelbar eine DML- oder Abfrageanweisung folgt, dann startet diese DML- oder Abfrageanweisung implizit eine neue Transaktion.

AUTOCOMMIT

Snowflake unterstützt einen AUTOCOMMIT-Parameter. AUTOCOMMIT ist standardmäßig aktiviert.

Bei aktiviertem AUTOCOMMIT:

  • Jede Anweisung außerhalb einer expliziten Transaktion wird so behandelt, als befände sie sich innerhalb ihrer eigenen impliziten Transaktion, die aus nur einer Anweisung besteht. Mit anderen Worten, für jede Anweisung wird automatisch ein COMMIT ausgeführt, wenn sie erfolgreich ist, und automatisch eine ROLLBACK, wenn sie fehlschlägt.

    Anweisungen innerhalb einer expliziten Transaktion sind von AUTOCOMMIT nicht betroffen. Beispielsweise werden Anweisungen innerhalb eines expliziten BEGIN TRANSACTION ... ROLLBACK auch dann zurückgesetzt, wenn AUTOCOMMIT TRUE ist.

Bei deaktiviertem AUTOCOMMIT:

  • In folgenden Fällen wird ein implizites BEGIN TRANSACTION ausgeführt:

    • Durch die erste DML- oder Abfrageanweisung nach dem Ende einer Transaktion. Dies gilt unabhängig davon, wodurch die vorhergehende Transaktion beendet wurde (z. B. durch DDL-Anweisung oder explizites Commit oder Rollback).

    • Durch die erste DML- oder Abfrageanweisung nach dem Deaktivieren von AUTOCOMMIT.

  • In folgenden Fällen wird ein implizites COMMIT ausgeführt (wenn eine Transaktion bereits aktiviert ist):

    • Bei Ausführung einer DDL-Anweisung.

    • Bei Ausführung einer ALTER SESSION SET AUTOCOMMIT-Anweisung, unabhängig davon, ob der neue Wert TRUE oder FALSE ist, und unabhängig davon, ob sich der neue Wert vom vorherigen Wert unterscheidet oder nicht. Wenn Sie z. B. AUTOCOMMIT auf FALSE setzen, obwohl es bereits FALSE ist, wird ein implizites COMMIT ausgeführt.

  • In folgenden Fällen wird ein implizites ROLLBACK ausgeführt (wenn eine Transaktion bereits aktiviert ist):

    • Bei Beendigung einer Sitzung.

    • Bei Beendigung einer gespeicherten Prozedur.

      Unabhängig davon, ob die aktivierte Transaktion der gespeicherten Prozedur explizit oder implizit gestartet wurde, rollt Snowflake die aktive Transaktion zurück und gibt eine Fehlermeldung aus.

Vorsicht

Ändern Sie keine AUTOCOMMIT-Einstellungen innerhalb einer gespeicherten Prozedur. Dies führt zu einer Fehlermeldung.

Mischen von impliziten und expliziten Anfangs- und Endpunkten einer Transaktion

Um das Schreiben von verwirrendem Code zu vermeiden, sollten Sie implizite und explizite Anfangs- und Endpunkte innerhalb derselben Transaktion nicht vermischen. Die folgenden Fälle sind zwar erlaubt, aber nicht empfehlenswert:

  • Eine implizit gestartete Transaktion kann durch ein explizites COMMIT oder ROLLBACK beendet werden.

  • Eine explizit gestartete Transaktion kann durch ein implizites COMMIT oder ROLLBACK beendet werden.

Fehlgeschlagene Anweisungen innerhalb einer Transaktion

Auch wenn COMMIT und ROLLBACK für eine Transaktion als Einheit ausgeführt werden, bedeutet dies nicht, dass die Transaktion als Einheit insgesamt erfolgreich war oder fehlgeschlagen ist. Wenn eine Anweisung innerhalb einer Transaktion fehlschlägt, können Sie für die Transaktion trotzdem ein COMMIT ausführen, und müssen nicht für die gesamte Transaktion ein ROLLBACK ausführen.

Wenn eine DML-Anweisung oder eine CALL-Anweisung in einer Transaktion fehlschlägt, werden die von dieser fehlgeschlagenen Anweisung vorgenommenen Änderungen rückgängig gemacht. Die Transaktion bleibt jedoch aktiviert, bis für die gesamte Transaktion ein COMMIT oder ROLLBACK ausgeführt wird. Wenn für die Transaktion ein COMMIT ausgeführt wird, werden die von den erfolgreichen Anweisungen vorgenommenen Änderungen angewendet.

Betrachten Sie beispielsweise den folgenden Code, der zwei gültige Werte und einen ungültigen Wert einfügt:

CREATE TABLE table1 (i int);
BEGIN TRANSACTION;
INSERT INTO table1 (i) VALUES (1);
INSERT INTO table1 (i) VALUES ('This is not a valid integer.');    -- FAILS!
INSERT INTO table1 (i) VALUES (2);
COMMIT;
SELECT i FROM table1 ORDER BY i;
Copy

Wenn die Anweisungen nach der fehlgeschlagenen INSERT-Anweisung ausgeführt werden, enthält die Ausgabe der abschließenden SELECT-Anweisung die Zeilen mit den ganzzahligen Werten 1 und 2, auch wenn eine der anderen Anweisungen in der Transaktion fehlgeschlagen ist.

Bemerkung

Die Anweisungen nach der fehlgeschlagenen INSERT-Anweisung werden möglicherweise ausgeführt oder auch nicht, je nachdem, wie die Anweisungen ausgeführt und wie Fehler verarbeitet werden.

Beispiel:

  • Wenn sich diese Anweisungen innerhalb einer gespeicherten Prozedur befinden, die in der Snowflake Scripting-Sprache geschrieben wurde, löst die fehlgeschlagene INSERT-Anweisung eine Ausnahme aus.

    • Wenn die Ausnahme nicht behandelt wird, wird die gespeicherte Prozedur nie abgeschlossen und die COMMIT-Anweisung wird nie ausgeführt, sodass für die offene Transaktion implizit ein Rollback ausgeführt wird. In diesem Fall hat die Tabelle weder den Wert 1 noch den Wert 2.

    • Wenn die gespeicherte Prozedur die Ausnahme behandelt und die Anweisungen vor der fehlgeschlagenen INSERT-Anweisung mit COMMIT festschreibt, aber die Anweisungen nach der fehlgeschlagenen INSERT-Anweisung nicht ausführt, wird nur die Zeile mit dem Wert 1 in der Tabelle gespeichert.

  • Wenn diese Anweisungen sich nicht innerhalb einer gespeicherten Prozedur befinden, hängt das Verhalten davon ab, wie die Anweisungen ausgeführt werden. Beispiel:

    • Wenn die Anweisungen über Snowsight ausgeführt werden, wird die Ausführung beim ersten Fehler angehalten.

    • Wenn die Anweisungen von SnowSQL mit der Option -f (Dateiname) ausgeführt werden, wird die Ausführung beim ersten Fehler nicht angehalten, sondern die Anweisungen nach dem Fehler werden ausgeführt.

Transaktionen und Multithreading

Obwohl mehrere Sitzungen nicht dieselbe Transaktion gemeinsam nutzen können, teilen sich mehrere Threads, die eine einzelne Verbindung verwenden, dieselbe Sitzung und somit dieselbe Transaktion. Dies kann zu unerwarteten Ergebnissen führen, z. B. wenn ein Thread ein Rollback einer Arbeit vornimmt, die in einem anderen Thread ausgeführt wird.

Diese Situation kann auftreten, wenn eine Clientanwendung, die einen Snowflake-Treiber (z. B. den Snowflake-JDBC-Treiber) oder einen Konnektor (z. B. den Snowflake-Konnektor für Python) verwendet, über mehrere Threads verfügt. Wenn zwei oder mehr Threads dieselbe Verbindung verwenden, teilen sich diese Threads auch die aktuelle Transaktion in dieser Verbindung. Ein BEGINTRANSACTION, COMMIT oder ROLLBACK von einem Thread wirkt sich auf alle anderen Threads aus, die die Verbindung gemeinsam verwenden. Wenn die Threads asynchron ausgeführt werden, können die Ergebnisse unvorhersehbar sein.

Ebenso wirkt sich das Ändern der AUTOCOMMIT-Einstellung in einem Thread auf die AUTOCOMMIT-Einstellung in allen anderen Threads aus, die dieselbe Verbindung verwenden.

Snowflake empfiehlt, dass Multithread-Clientprogramme mindestens eine der folgenden Aktionen ausführen:

  • Verwenden Sie für jeden Thread eine separate Verbindung.

    Beachten Sie aber, dass Ihr Code auch bei separaten Verbindungen in Wettlaufsituationen (Racebedingungen) gelangen kann, die unvorhersehbare Ausgaben generieren. Beispielsweise kann ein Thread Daten löschen, bevor ein anderer Thread versucht, diese zu aktualisieren.

  • Führen Sie die Threads nicht asynchron, sondern synchron aus, um die Reihenfolge zu steuern, in der die Schritte ausgeführt werden.

Gespeicherte Prozeduren und Transaktionen

Im Allgemeinen gelten die in den vorherigen Abschnitten beschriebenen Regeln auch für gespeicherte Prozeduren. Der folgende Abschnitt enthält zusätzliche Informationen, die speziell für gespeicherte Prozeduren gelten.

Eine Transaktion kann sich innerhalb einer gespeicherten Prozedur befinden, oder eine gespeicherte Prozedur kann sich innerhalb einer Transaktion befinden. Aber eine Transaktion kann nicht teilweise innerhalb und teilweise außerhalb einer gespeicherten Prozedur liegen, und sie kann nicht in einer gespeicherten Prozedur gestartet und in einer anderen gespeicherten Prozedur beendet werden.

Beispiel:

  • Sie können eine Transaktion nicht vor dem Aufruf der gespeicherten Prozedur starten und dann die Transaktion innerhalb der gespeicherten Prozedur abschließen. Wenn Sie versuchen, dies zu tun, gibt Snowflake eine Fehlermeldung ähnlich Modifying a transaction that has started at a different scope is not allowed. aus.

  • Sie können eine Transaktion nicht innerhalb einer gespeicherten Prozedur starten und dann die Transaktion nach der Rückkehr aus der Prozedur abschließen. Wenn eine Transaktion innerhalb einer gespeicherten Prozedur gestartet wird und noch aktiviert ist, wenn die gespeicherte Prozedur beendet wird, dann tritt ein Fehler auf und die Transaktion wird rückgängig gemacht (Rollback).

Diese Regeln gelten auch für verschachtelte gespeicherte Prozeduren. Wenn Prozedur A die Prozedur B aufruft, dann kann B eine in A gestartete Transaktion nicht abschließen und umgekehrt. Zu jedem BEGIN TRANSACTION in A muss es ein entsprechendes COMMIT (oder ROLLBACK) in A geben, und zu jedem BEGIN TRANSACTION in B muss es ein entsprechendes COMMIT (oder ROLLBACK) in B geben.

Wenn eine gespeicherte Prozedur eine Transaktion enthält, kann diese Transaktion entweder einen Teil des Prozedurtextes oder den gesamten Prozedurtext der gespeicherten Prozedur enthalten. In der folgenden gespeicherten Prozedur befinden sich zum Beispiel nur einige der Anweisungen in einer Transaktion. (Für dieses und mehrere nachfolgende Beispiele wird der Einfachheit halber Pseudocode verwendet).

create procedure ...
    as
    $$
        ...
        statement1;

        BEGIN TRANSACTION;
        statement2;
        COMMIT;

        statement3;
        ...

    $$;
Copy

Nicht überlappende Transaktionen

In den Abschnitten unten wird Folgendes beschrieben:

  • Verwenden einer gespeicherten Prozedur innerhalb einer Transaktion

  • Verwenden einer Transaktion innerhalb einer gespeicherten Prozedur

Verwenden einer gespeicherten Prozedur innerhalb einer Transaktion

Im einfachsten Fall wird eine gespeicherte Prozedur als innerhalb einer Transaktion betrachtet, wenn die folgenden Bedingungen erfüllt sind:

  • Ein BEGIN TRANSACTION wird ausgeführt, bevor die gespeicherte Prozedur aufgerufen wird.

  • Das zugehörige COMMIT (oder ROLLBACK) wird ausgeführt, nachdem die gespeicherte Prozedur abgeschlossen ist.

  • Der Hauptteil der gespeicherten Prozedur enthält kein explizites oder implizites BEGIN TRANSACTION oder COMMIT (oder ROLLBACK).

Die gespeicherte Prozedur innerhalb der Transaktion folgt den Regeln der einschließenden Transaktion:

  • Wenn für die Transaktion ein COMMIT ausgeführt wird, dann gilt dieses für alle Anweisungen innerhalb der Prozedur.

  • Wenn für die Transaktion ein ROLLBACK ausgeführt wird, dann gilt dieses ROLLBACK für alle Anweisungen innerhalb der Prozedur.

Der folgende Pseudocode zeigt eine gespeicherte Prozedur, die vollständig innerhalb einer Transaktion aufgerufen wird:

CREATE PROCEDURE my_procedure()
...
AS
$$
    statement X;
    statement Y;
$$;

BEGIN TRANSACTION;
statement W;
CALL my_procedure();
statement Z;
COMMIT;
Copy

Dies entspricht der Ausführung der folgenden Anweisungssequenz:

begin;
statement W;
statement X;
statement Y;
statement Z;
commit;
Copy

Verwenden einer Transaktion innerhalb einer gespeicherten Prozedur

Sie können keine, eine oder mehrere Transaktionen innerhalb einer gespeicherten Prozedur ausführen. Der folgende Pseudocode zeigt ein Beispiel für zwei Transaktionen in einer gespeicherten Prozedur:

CREATE PROCEDURE p1()
...
$$
    begin transaction;
    statement C;
    statement D;
    commit;

    begin transaction;
    statement E;
    statement F;
    commit;
$$;
Copy

Die gespeicherte Prozedur könnte wie unten gezeigt aufgerufen werden:

BEGIN TRANSACTION;
statement A;
statement B;
COMMIT;

CALL p1();

BEGIN TRANSACTION;
statement G;
statement H;
COMMIT;
Copy

Dies entspricht der Ausführung der folgenden Anweisungssequenz:

BEGIN TRANSACTION;
statement A;
statement B;
COMMIT;

BEGIN TRANSACTION;
statement C;
statement D;
COMMIT;

BEGIN TRANSACTION;
statement E;
statement F;
COMMIT;

BEGIN TRANSACTION;
statement G;
statement H;
COMMIT;
Copy

In diesem Code werden vier separate Transaktionen ausgeführt. Jede Transaktion beginnt und endet entweder außerhalb der Prozedur, oder sie beginnt und endet innerhalb der Prozedur. Keine Transaktion wird über eine Prozedurgrenze hinweg ausgeführt, d. h. dass sich Anweisungen teilweise innerhalb und teilweise außerhalb der gespeicherten Prozedur befinden. Keine Transaktion ist in einer anderen Transaktion verschachtelt.

Bereichsbezogene Transaktionen

Eine gespeicherte Prozedur, die eine Transaktion enthält, kann aus einer anderen Transaktion heraus aufgerufen werden. So kann zum Beispiel eine Transaktion innerhalb einer gespeicherten Prozedur einen Aufruf einer anderen gespeicherten Prozedur enthalten, die eine Transaktion enthält.

Snowflake behandelt die innere Transaktion nicht als verschachtelt, stattdessen ist die innere Transaktion eine separate Transaktion. Snowflake nennt diese Transaktionen „autonome bereichsbezogene Transaktionen“ (oder einfach „bereichsbezogene Transaktionen“).

Der Anfangs- und Endpunkt jeder bereichsbezogenen Transaktion bestimmt, welche Anweisungen in die Transaktion einbezogen werden. Anfang und Ende können explizit oder implizit sein. Jede SQL-Anweisung ist Teil von nur einer Transaktion. Durch ein einschließendes ROLLBACK oder COMMIT wird ein eingeschlossenes COMMIT oder ROLLBACK nicht „rückgängig“ gemacht.

Bemerkung

Die Begriffe „innere“ und „äußere“ werden häufig verwendet, wenn verschachtelte Operationen beschrieben werden, wie z. B. Aufrufe verschachtelter gespeicherter Prozeduren. Transaktionen in Snowflake sind jedoch nicht wirklich „verschachtelt“. Um Verwirrung zu vermeiden, wenn auf Transaktionen Bezug genommen wird, werden in diesem Dokument daher die Begriffe „eingeschlossene“ und „einschließende“ Transaktion verwendet, anstatt „innere“ und „äußere“.

In der Abbildung unten werden zwei gespeicherte Prozeduren und zwei Transaktionen mit Gültigkeitsbereich gezeigt. In diesem Beispiel enthält jede gespeicherte Prozedur ihre eigene unabhängige Transaktion. Die erste gespeicherte Prozedur ruft die zweite gespeicherte Prozedur auf, sodass sich die Prozeduren zeitlich überschneiden – sie überschneiden sich jedoch nicht inhaltlich (bezüglich des Geltungsbereichs). Alle Anweisungen innerhalb des schattierten Innenkastens befinden sich in einer Transaktion, während sich alle anderen Anweisungen in einer anderen Transaktion befinden.

Illustration of two stored procedures, each with its own scoped transaction.

Im nächsten Beispiel unterscheiden sich die Grenzen der Transaktion von den Grenzen der gespeicherten Prozedur: Die Transaktion, die in der äußeren gespeicherten Prozedur beginnt, umfasst einige, aber nicht alle Anweisungen in der inneren gespeicherten Prozedur.

Illustration of two stored procedures and two scoped transactions, in which one transaction includes some statements from the inner stored procedure as well as all statements from the outer stored procedure.

Im obigen Code enthält die zweite gespeicherte Prozedur einige Anweisungen (SP2_T1_S2 und SP2_T1_S3), die sich innerhalb des Geltungsbereichs der ersten Transaktion befinden. Nur die Anweisung SP2_T2_S1, innerhalb des schattierten Innenkastens, befindet sich im Geltungsbereich der zweiten Transaktion.

Das nächste Beispiel demonstriert die Probleme, die auftreten, wenn eine Transaktion nicht innerhalb der gleichen gespeicherten Prozedur beginnt und endet. Das Beispiel enthält die gleiche Anzahl von COMMIT- und BEGIN-Anweisungen. Jedoch sind die BEGIN- und COMMIT-Anweisungen nicht korrekt gepaart, daher enthält dieses Beispiel zwei Fehler:

  • Die äußere gespeicherte Prozedur startet eine bereichsbezogene Transaktion, schließt sie aber nicht explizit ab. Daher verursacht diese bereichsbezogene Transaktion am Ende dieser gespeicherten Prozedur einen Fehler, und die aktivierte Transaktion wird implizit rückgängig gemacht (Rollback).

  • Die zweite gespeicherte Prozedur enthält ein COMMIT, aber es gibt in dieser gespeicherten Prozedur kein zugehöriges BEGIN. Mit diesem COMMIT wird nicht die offene Transaktion bestätigt, die in der ersten gespeicherten Prozedur gestartet wurde. Stattdessen verursacht das nicht korrekt gepaarte COMMIT einen Fehler.

Illustration of two stored procedures that create improperly-scoped transactions.

Das nächste Beispiel zeigt drei bereichsbezogene Transaktionen, die sich zeitlich überschneiden. In diesem Beispiel ruft die gespeicherte Prozedur p1() eine andere gespeicherte Prozedur p2() aus einer Transaktion heraus auf, und p2() enthält eine eigene Transaktion, sodass die in p2() gestartete Transaktion ebenfalls unabhängig ausgeführt wird. (Dieses Beispiel verwendet Pseudocode.)

CREATE PROCEDURE p2()
...
$$
    BEGIN TRANSACTION;
    statement C;
    COMMIT;
$$;

CREATE PROCEDURE p1()
...
$$
    BEGIN TRANSACTION;
    statement B;
    CALL p2();
    statement D;
    COMMIT;
$$;

BEGIN TRANSACTION;
statement A;
CALL p1();
statement E;
COMMIT;
Copy

In den folgenden drei bereichsbezogenen Transaktionen:

  • Die Transaktion, die außerhalb einer gespeicherten Prozedur liegt, enthält die Anweisungen A und E.

  • Die Transaktion in der gespeicherten Prozedur p1() enthält die Anweisungen B und D

  • Die Transaktion in p2() enthält die Anweisung C.

Die Regeln für bereichsbezogene Transaktionen gelten auch für rekursive Aufrufe gespeicherter Prozeduren. Ein rekursiver Aufruf ist nur ein spezifischer Typ eines geschachtelten Aufrufs und folgt den gleichen Transaktionsregeln wie ein geschachtelter Aufruf.

Vorsicht

Bereichsbezogene Transaktionen, die sich überlappen, können zu einem Deadlock führen, wenn sie Änderungen an demselben Datenbankobjekt (z. B. Tabelle) vornehmen. Bereichsbezogene Transaktionen sollten daher nur verwendet werden, wenn dies notwendig ist.

Bei deaktiviertem AUTOCOMMIT müssen Sie besonders vorsichtig sein, wenn Sie implizite Transaktionen und gespeicherte Prozeduren kombinieren. Wenn Sie versehentlich eine Transaktion am Ende einer gespeicherten Prozedur aktiviert lassen, wird für die Transaktion ein ROLLBACK ausgeführt.

Der folgende Code verursacht zum Beispiel ein implizites ROLLBACK am Ende der gespeicherten Prozedur:

CREATE PROCEDURE p1() ...
$$
    INSERT INTO parent_table ...;
    INSERT INTO child_table ...;
$$;


ALTER SESSION SET AUTOCOMMIT = FALSE;
CALL p1;
COMMIT WORK;
Copy

In diesem Beispiel wird mit dem Befehl zum Setzen von AUTOCOMMIT für jede aktivierte Transaktion ein COMMIT ausgeführt. Eine neue Transaktion wird nicht sofort gestartet. Die gespeicherte Prozedur enthält eine DML-Anweisung, die implizit eine neue Transaktion startet. Das implizite BEGIN TRANSACTION hat kein zugehöriges COMMIT oder ROLLBACK in der gespeicherten Prozedur. Da es eine aktivierte Transaktion am Ende der gespeicherten Prozedur gibt, wird für diese aktivierte Transaktion implizit ein ROLLBACK ausgeführt.

Wenn Sie die gesamte gespeicherte Prozedur in einer einzigen Transaktion ausführen möchten, starten Sie die Transaktion, bevor Sie die gespeicherte Prozedur aufrufen, und übergeben Sie die Transaktion nach dem Aufruf:

CREATE PROCEDURE p1() ...
$$
    INSERT INTO parent_table ...;
    INSERT INTO child_table ...;
$$;


ALTER SESSION SET AUTOCOMMIT = FALSE;
BEGIN TRANSACTION;
CALL p1;
COMMIT WORK;
Copy

In diesem Fall sind BEGIN und COMMIT korrekt gepaart, und der Code wird ohne Fehler ausgeführt.

Alternativ können Sie sowohl BEGIN TRANSACTION als auch COMMIT in die gespeicherte Prozedur einfügen, wie im folgenden Pseudocode-Beispiel gezeigt:

CREATE PROCEDURE p1() ...
$$
    BEGIN TRANSACTION;
    INSERT INTO parent_table ...;
    INSERT INTO child_table ...;
    COMMIT WORK;
$$;


ALTER SESSION SET AUTOCOMMIT = FALSE;
CALL p1;
Copy

Wenn Sie Ihre BEGIN/COMMIT-Blöcke in einer bereichsbezogenen Transaktion nicht korrekt koppeln, meldet Snowflake einen Fehler. Dieser Fehler kann weitere Auswirkungen haben, wie z. B. das Verhindern des Abschlusses einer gespeicherten Prozedur oder das Verhindern des Commit einer umschließenden Transaktion. Im folgenden Pseudocode-Beispiel wird z. B. für einige Anweisungen in der einschließenden gespeicherten Prozedur sowie die eingeschlossene gespeicherte Prozedur ein Rollback ausgeführt:

CREATE or replace PROCEDURE outer_sp1()
...
AS
$$
    insert 'osp1_alpha';
    BEGIN WORK;
    insert 'osp1_beta';
    CALL inner_sp2();
    INSERT 'osp1_delta';
    COMMIT WORK;
    INSERT 'osp1_omega';
$$;

CREATE or replace PROCEDURE inner_sp2()
...
AS
$$
    BEGIN WORK;
    insert 'isp2';
    -- Missing COMMIT, so implicitly rolls back!
$$;

CALL outer_sp1();

SELECT * FROM st;
Copy

In diesem Beispiel wird nur ein einziger Wert eingefügt: „osp1_alpha“. Keiner der anderen Werte wird eingefügt, da ein COMMIT nicht korrekt mit einem BEGIN gepaart ist. Der Fehler wird wie folgt behandelt:

  1. Wenn die Prozedur inner_sp2() beendet ist, erkennt Snowflake, dass für das BEGIN in inner_sp2() kein entsprechendes COMMIT (oder ROLLBACK) vorhanden ist.

    1. Snowflake führt implizit ein Rollback für die bereichsbezogene Transaktion aus, die in inner_sp2() gestartet wurde.

    2. Snowflake gibt auch einen Fehler aus, weil der CALL-Befehl von inner_sp2() fehlgeschlagen ist.

  2. Weil CALL von inner_sp2() fehlgeschlagen ist und weil sich diese CALL-Anweisung in outer_sp1() befand, schlägt die gespeicherte Prozedur outer_sp1() ebenfalls fehl und gibt einen Fehler zurück, anstatt fortzufahren.

  3. Weil outer_sp1() die Ausführung nicht beendet:

    • Die INSERT-Anweisungen für die Werte „osp1_delta“ und „osp1_omega“ werden nie ausgeführt.

    • Für die offene Transaktion in outer_sp1() wird implizit ein Rollback und kein Commit ausgeführt, sodass das Einfügen des Werts „osp1_beta“ nie festgeschrieben (Commit) wird.

Isolationsebene

READ COMMITTED ist die derzeit einzige unterstützte Isolationsebene für Tabellen.

READ COMMITTED-Isolation

Bei der READ COMMITTED-Isolation „sieht“ eine Anweisung nur Daten, für die vor Beginn der Anweisung ein Commit ausgeführt wurde. Die Anweisung wird niemals Daten sehen, für die kein Commit ausgeführt wurde.

Wenn eine Anweisung innerhalb einer Transaktion mit mehreren Anweisungen ausgeführt wird:

  • Eine Anweisung sieht nur Daten, für die vor Ausführungsbeginn der Anweisung Commit ausgeführt wurde. Zwei aufeinanderfolgende Anweisungen in derselben Transaktion können unterschiedliche Daten sehen, wenn zwischen der Ausführung der ersten und der zweiten Anweisung eine andere Transaktion ein Commit ausführt.

  • Eine Anweisung sieht jedoch die Änderungen, die von vorherigen Anweisungen innerhalb derselben Transaktion ausgeführt wurden, auch wenn diese Änderungen noch nicht mit Commit bestätigt wurden.

Ressourcensperrung

Transaktionsoperationen rufen Sperren für eine Ressource ab, z. B. eine Tabelle, während diese Ressource geändert wird. Sperren sorgen dafür, dass andere Anweisungen die Ressource erst ändern können, wenn die Sperre aufgehoben wurde.

In den meisten Situationen gelten folgende Richtlinien:

  • COMMIT-Operationen (einschließlich AUTOCOMMIT und expliziter COMMIT) sperren Ressourcen, aber meist nur für kurze Zeit.

  • Die Operationen CREATE [DYNAMIC] TABLE, CREATE STREAM und ALTER TABLE sperren ihre zugrunde liegenden Ressourcen, wenn CHANGE_TRACKING = TRUE gesetzt wird, aber meist nur kurz. Wenn eine Tabelle gesperrt ist, sind nur die DML-Operationen UPDATE und DELETE blockiert. INSERT-Operationen sind NICHT blockiert.

  • Bei UPDATE-, DELETE- und MERGE-Anweisungen werden Sperren aufrechterhalten, damit nicht gleichzeitig andere UPDATE-, DELETE- und MERGE-Anweisungen ausgeführt werden können.

  • Die meisten INSERT- und COPY-Anweisungen schreiben nur in neue Partitionen. Diese Anweisungen können daher meist parallel zu anderen INSERT- und COPY-Operationen und manchmal auch parallel zu einer UPDATE-, DELETE- oder MERGE-Anweisung ausgeführt werden.

Sperren, die von einer Anweisung aufrechterhalten werden, werden erst bei COMMIT oder ROLLBACK der Transaktion freigegeben.

Wartezeit-Timeout für Sperren

Eine blockierte Anweisung ruft entweder eine Sperre für die Ressource ab, auf die sie gewartet hat, oder ein Timeout für die Wartezeit bis zur Freigabe der Ressource. Die Zeit (in Sekunden), die eine Anweisung gesperrt werden soll, kann durch Setzen des Parameters LOCK_TIMEOUT konfiguriert werden.

So ändern Sie beispielsweise das Sperrzeitlimit für die aktuelle Sitzung auf 2 Stunden (7.200 Sekunden):

ALTER SESSION SET LOCK_TIMEOUT=7200;

SHOW PARAMETERS LIKE 'lock%';

+--------------+-------+---------+---------+-------------------------------------------------------------------------------+
| key          | value | default | level   | description                                                                   |
|--------------+-------+---------+---------+-------------------------------------------------------------------------------|
| LOCK_TIMEOUT | 7200  | 43200   | SESSION | Number of seconds to wait while trying to lock a resource, before timing out  |
|              |       |         |         | and aborting the statement. A value of 0 turns off lock waiting i.e. the      |
|              |       |         |         | statement must acquire the lock immediately or abort. If multiple resources   |
|              |       |         |         | need to be locked by the statement, the timeout applies separately to each    |
|              |       |         |         | lock attempt.                                                                 |
+--------------+-------+---------+---------+-------------------------------------------------------------------------------+
Copy

Deadlocks

Deadlocks treten auf, wenn gleichzeitig ausgeführte Transaktionen auf Ressourcen warten, die von der jeweils anderen gesperrt wurden.

In Snowflake kann bei gleichzeitiger Ausführung von Autocommit-DML oder Abfrageanweisungen kein Deadlock auftreten. Deadlocks können jedoch bei explizit gestarteten Transaktionen und bei mehreren Anweisungen in jeder Transaktion auftreten. Snowflake erkennt Deadlocks und wählt die zuletzt ausgeführte Anweisung, die Teil des Deadlocks ist, zur Bearbeitung. Diese Anweisung wird mit ROLLBACK zurückgesetzt, aber die Transaktion selbst bleibt aktiviert, sodass für sie ein COMMIT oder ROLLBACK ausgeführt werden muss.

Das Erkennen von Deadlocks kann Zeit kosten.

Abbrechen von Transaktionen mittels Anweisungsfehlern

Damit ein Anweisungsfehler innerhalb einer Transaktion die Transaktion abbrechen kann, legen Sie den Parameter TRANSACTION_ABORT_ON_ERROR auf Sitzungs- oder Kontoebene fest.

Analysieren von gesperrten Transaktionen mit der Ansicht LOCK_WAIT_HISTORY

Die Ansicht LOCK_WAIT_HISTORY gibt Transaktionsdetails zurück, die bei der Analyse von blockierten Transaktionen nützlich sein können. Jede Zeile der Ausgabe enthält die Details einer Transaktion, die auf eine Sperre wartet, sowie die Details der Transaktionen, die gerade im Besitz dieser Sperre sind oder auf diese Sperre warten.

Ein Beispiel ist das folgende Szenario:

Example of blocked and blocker transactions.

In diesem Szenario werden die folgenden Daten zurückgegeben:

  • Transaktion B ist die Transaktion, die auf eine Sperre wartet.

  • Transaktion B hat die Sperre zum Zeitstempel T1 angefordert.

  • Transaktion A ist die Transaktion, die gerade im Besitz der Sperre ist.

  • Abfrage 2 in Transaktion A ist die Blockierungsabfrage.

Abfrage 2 ist die Blockierungsabfrage, weil sie die erste Anweisung in Transaktion A (der Transaktion, die im Besitz der Sperre ist) ist, bei der Transaktion B (die Transaktion, die auf die Sperre wartet) mit dem Warten begonnen hat.

Es ist jedoch zu beachten, dass eine spätere Abfrage in Transaktion A (Abfrage 5) ebenfalls die Sperre erworben hat. Es ist möglich, dass nachfolgende parallele Ausführungen dieser Transaktionen dazu führen, dass Transaktion B bei einer anderen Abfrage, die die Sperre in Transaktion A erwirbt, blockiert. Daher müssen Sie alle Abfragen in der ersten Blockierungstransaktion untersuchen.

Untersuchen von Anweisungen mit langer Ausführungszeit

  1. Fragen Sie die Account Usage-Ansicht QUERY_HISTORY-Ansicht nach Transaktionen ab, die in den letzten 24 Stunden auf Sperren gewartet haben:

    SELECT query_id, query_text, start_time, session_id, execution_status, total_elapsed_time,
           compilation_time, execution_time, transaction_blocked_time
      FROM snowflake.account_usage.query_history
      WHERE start_time >= dateadd('hours', -24, current_timestamp())
      AND transaction_blocked_time > 0
      ORDER BY transaction_blocked_time DESC;
    
    Copy

    Überprüfen Sie die Ergebnisse der Abfrage, und beachten Sie dabei die Abfrage-IDs der Abfragen mit hohen TRANSACTION_BLOCKED_TIME-Werten.

  2. Um Blockierungstransaktionen für die im vorherigen Schritt identifizierten Abfragen zu finden, suchen Sie in der Ansicht LOCK_WAIT_HISTORY nach Zeilen mit diesen Abfragen-IDs:

    SELECT object_name, lock_type, transaction_id, blocker_queries
      FROM snowflake.account_usage.lock_wait_history
      WHERE query_id = '<query_id>';
    
    Copy

    In der Spalte blocker_queries der Ergebnisse können mehrere Abfragen vorhanden sein. Beachten Sie in der Ausgabe die transaction_id jeder einzelnen Blockierungsabfrage.

  3. Fragen Sie die Ansicht QUERY_HISTORY für jede Transaktion in der blocker_queries-Ausgabe ab:

    SELECT query_id, query_text, start_time, session_id, execution_status, total_elapsed_time, compilation_time, execution_time
      FROM snowflake.account_usage.query_history
      WHERE transaction_id = '<transaction_id>';
    
    Copy

    Untersuchen Sie das Abfrageergebnisse. Wenn eine Anweisung der Transaktion eine DML-Anweisung war und auf der gesperrten Ressource operiert hat, kann die Anweisung möglicherweise die Sperre zu einem bestimmten Zeitpunkt während der Transaktion erhalten haben.

Transaktionsbefehle und -funktionen

Snowflake bietet die folgenden SQL-Befehle zur Unterstützung von Transaktionen:

Darüber hinaus bietet Snowflake die folgenden Kontextfunktionen zum Abrufen von Informationen zu den Transaktionen einer Sitzung:

Abbrechen von Transaktionen

Wenn in einer Sitzung eine Transaktion ausgeführt und die Sitzung abrupt unterbrochen wird, um zu verhindern, dass für die Transaktion ein Commit oder Rollback ausgeführt wird, verbleibt die Transaktion im getrennten Zustand, einschließlich aller Sperren, die die Transaktion auf Ressourcen hält. Wenn dies geschieht, müssen Sie die Transaktion möglicherweise abbrechen.

Um eine laufende Transaktion abzubrechen, kann der Benutzer, der die Transaktion gestartet hat, oder ein Kontoadministrator die Systemfunktion SYSTEM$ABORT_TRANSACTION aufrufen.

Wenn die Transaktion nicht vom Benutzer abgebrochen wird:

  • Wenn sie eine andere Transaktion daran hindert, eine Sperre auf dieselbe Tabelle zu erhalten und für 5 Minuten im Leerlauf ist, wird sie automatisch abgebrochen und zurückgesetzt (Rollback).

  • Wenn sie andere Transaktionen nicht daran hindert, dieselbe Tabelle zu ändern, und wenn sie älter als 4 Stunden ist, wird sie automatisch abgebrochen und zurückgesetzt (Rollback).

Best Practices

  • Eine Transaktion sollte Anweisungen enthalten, die miteinander in Zusammenhang stehen und die gemeinsam erfolgreich sind oder fehlschlagen können, z. B. Geld von einem Konto abheben und dasselbe Geld auf ein anderes Konto einzahlen. Bei einem Rollback erhält entweder der Zahler das Geld oder der Empfänger, d. h. das Geld „verschwindet“ nicht (von einem Konto abgehoben, aber nie auf das andere Konto eingezahlt).

    Im Allgemeinen sollte eine Transaktion nur zusammengehörige Anweisungen enthalten. Wird die Granularität einer Anweisung verringert, bedeutet dies, dass eine Transaktion bei einem ROLLBACK nützliche Arbeit rückgängig macht, die eigentlich nicht rückgängig gemacht werden sollte.

  • Durch größere Transaktionen kann in einigen Fällen die Leistung verbessert werden.

    Obwohl im vorhergehenden Aufzählungspunkt betont wurde, wie wichtig es ist, nur Anweisungen zu gruppieren, für die COMMIT und ROLLBACK wirklich als Gruppe ausgeführt werden sollten, können größere Transaktionen manchmal nützlich sein. In Snowflake, wie in den meisten Datenbanken, werden bei der Verwaltung von Transaktionen Ressourcen verbraucht. Beispielsweise ist das Einfügen von 10 Zeilen mit nur einer Transaktion im Allgemeinen schneller und billiger als das Einfügen von je einer Zeile in 10 separaten Transaktionen. Das Kombinieren von mehreren Anweisungen in nur einer Transaktion kann die Leistung verbessern.

  • Zu große Transaktionen können die Parallelität verringern oder die Gefahr von Deadlocks erhöhen. Wenn Sie sich entscheiden, nicht miteinander in Beziehung stehende Anweisungen zu gruppieren, um die Leistung zu verbessern (wie im vorherigen Aufzählungspunkt beschrieben), bedenken Sie, dass eine Transaktion Sperren auf Ressourcen erwerben kann, was andere Abfragen verzögern oder zu Deadlocks führen kann.

  • Snowflake empfiehlt, AUTOCOMMIT aktiviert zu lassen und so viele explizite Transaktionen wie möglich zu verwenden. Die Verwendung expliziter Transaktionen macht es für den menschlichen Leser einfacher zu erkennen, wo Transaktionen beginnen und wo sie enden. Dies, kombiniert mit AUTOCOMMIT, macht es weniger wahrscheinlich, dass Ihr Code unbeabsichtigte Rollbacks erfährt, zum Beispiel am Ende einer gespeicherten Prozedur.

  • Vermeiden Sie es, AUTOCOMMIT nur zu ändern, um implizit eine neue Transaktion zu starten. Verwenden Sie stattdessen BEGIN TRANSACTION, um deutlich zu machen, wo eine neue Transaktion beginnt.

  • Vermeiden Sie die Ausführung von mehr als einer BEGIN TRANSACTION-Anweisung nacheinander. Zusätzliche BEGIN TRANSACTION-Anweisungen erschweren es, zu erkennen, wo eine Transaktion tatsächlich beginnt, und erschweren es, die zu COMMIT/ROLLBACK-Befehlen jeweils gehörenden BEGIN TRANSACTION-Befehle zu finden.

Transaktions-ID

Jeder Snowflake-Transaktion wird eine eindeutige Transaktions-ID zugewiesen. Die Transaktions-ID ist eine vorzeichenbehaftete 64-Bit (lange) Ganzzahl. Der Wertebereich ist -9.223.372.036.854.775.808 (-2 63) bis 9.223.372.036.854.775.807 (2 63 – 1).

Beispiele

Einfaches Beispiel für bereichsbezogene Transaktion und gespeicherte Prozedur

Hier ist ein einfaches Beispiel für die bereichsbezogene Transaktionen: Die gespeicherte Prozedur enthält eine Transaktion, die eine Zeile mit dem Wert 12 einfügt und dann ein ROLLBACK ausführt. Für die äußere Transaktion wird ein COMMIT ausgeführt. Die Ausgabe zeigt, dass alle Zeilen im Ausführungsbereich der äußeren Transaktion beibehalten werden, während die Zeile im Ausführungsbereich der inneren Transaktion nicht beibehalten wird.

Beachten Sie, dass sich nur ein Teil der gespeicherten Prozedur innerhalb ihrer eigenen Transaktion befindet und daher Werte beibehalten werden, die mit den INSERT-Anweisungen eingefügt werden, die sich in der gespeicherten Prozedur, aber außerhalb der Transaktion der gespeicherten Prozedur befinden.

Erstellen Sie zwei Tabellen:

create table tracker_1 (id integer, name varchar);
create table tracker_2 (id integer, name varchar);
Copy

Erstellen Sie die gespeicherte Prozedur:

create procedure sp1()
returns varchar
language javascript
AS
$$
    // This is part of the outer transaction that started before this
    // stored procedure was called. This is committed or rolled back
    // as part of that outer transaction.
    snowflake.execute (
        {sqlText: "insert into tracker_1 values (11, 'p1_alpha')"}
        );

    // This is an independent transaction. Anything inserted as part of this
    // transaction is committed or rolled back based on this transaction.
    snowflake.execute (
        {sqlText: "begin transaction"}
        );
    snowflake.execute (
        {sqlText: "insert into tracker_2 values (12, 'p1_bravo')"}
        );
    snowflake.execute (
        {sqlText: "rollback"}
        );

    // This is part of the outer transaction started before this
    // stored procedure was called. This is committed or rolled back
    // as part of that outer transaction.
    snowflake.execute (
        {sqlText: "insert into tracker_1 values (13, 'p1_charlie')"}
        );

    // Dummy value.
    return "";
$$;
Copy

Rufen Sie die gespeicherte Prozedur auf:

begin transaction;
insert into tracker_1 values (00, 'outer_alpha');
call sp1();
insert into tracker_1 values (09, 'outer_zulu');
commit;
Copy

Die Ergebnisse sollten 00, 11, 13 und 09 sein. Die Zeile mit ID = 12 sollte nicht enthalten sein. Diese Zeile befand sich im Bereich der eingeschlossenen Transaktion, für die ein ROLLBACK ausgeführt wurde. Alle anderen Zeilen befanden sich im Bereich äußeren Transaktion, für die ein COMMIT ausgeführt wurde. Beachten Sie insbesondere, dass sich die Zeilen mit den IDs 11 und 13 innerhalb der gespeicherten Prozedur, aber außerhalb der innersten Transaktion und damit im Bereich der einschließenden Transaktion befanden, für die ein COMMIT ausgeführt wurde.

select id, name FROM tracker_1
union all
select id, name FROM tracker_2
order by id;
+----+-------------+
| ID | NAME        |
|----+-------------|
|  0 | outer_alpha |
|  9 | outer_zulu  |
| 11 | p1_alpha    |
| 13 | p1_charlie  |
+----+-------------+
Copy

Protokollieren von Informationen unabhängig vom Erfolg einer Transaktion

Dies ist ein einfaches, praktisches Beispiel für die Verwendung einer bereichsbezogenen Transaktion. In diesem Beispiel protokolliert eine Transaktion bestimmte Informationen. Diese protokollierten Informationen bleiben erhalten, unabhängig davon, ob die Transaktion selbst erfolgreich ist oder fehlschlägt. Diese Technik kann dazu verwendet werden, alle versuchten Aktionen zu verfolgen, unabhängig davon, ob sie erfolgreich waren oder nicht.

Erstellen Sie zwei Tabellen:

create table data_table (id integer);
create table log_table (message varchar);
Copy

Erstellen Sie die gespeicherte Prozedur:

create procedure log_message(MESSAGE VARCHAR)
returns varchar
language javascript
AS
$$
    // This is an independent transaction. Anything inserted as part of this
    // transaction is committed or rolled back based on this transaction.
    snowflake.execute (
        {sqlText: "begin transaction"}
        );
    snowflake.execute (
        {sqlText: "insert into log_table values ('" + MESSAGE + "')"}
        );
    snowflake.execute (
        {sqlText: "commit"}
        );

    // Dummy value.
    return "";
$$;

create procedure update_data()
returns varchar
language javascript
AS
$$
    snowflake.execute (
        {sqlText: "begin transaction"}
        );
    snowflake.execute (
        {sqlText: "insert into data_table (id) values (17)"}
        );
    snowflake.execute (
        {sqlText: "call log_message('You should see this saved.')"}
        );
    snowflake.execute (
        {sqlText: "rollback"}
        );

    // Dummy value.
    return "";
$$;
Copy

Rufen Sie die gespeicherte Prozedur auf:

begin transaction;
call update_data();
rollback;
Copy

Die Datentabelle ist leer, da für die Transaktion ein ROLLBACK ausgeführt wurde:

select * from data_table;
+----+
| ID |
|----|
+----+
Copy

Die Protokollierungstabelle ist jedoch nicht leer. Die Einfügung in die Protokollierungstabelle erfolgte in einer von der Einfügung in „data_table“ separaten Transaktion.

select * from log_table;
+----------------------------+
| MESSAGE                    |
|----------------------------|
| You should see this saved. |
+----------------------------+
Copy

Beispiel für bereichsbezogene Transaktionen und gespeicherte Prozeduren

In den nächsten Beispielen werden die unten gezeigten Tabellen und gespeicherten Prozeduren verwendet. Durch die Übergabe geeigneter Parameter kann der Aufrufer steuern, wo BEGIN TRANSACTION-, COMMIT- und ROLLBACK-Anweisungen innerhalb der gespeicherten Prozedur ausgeführt werden.

Erstellen Sie die Tabellen:

create table tracker_1 (id integer, name varchar);
create table tracker_2 (id integer, name varchar);
create table tracker_3 (id integer, name varchar);
Copy

Diese Prozedur ist die einschließende gespeicherte Prozedur, die je nach den ihr übergebenen Parametern eine einschließende Transaktion erstellen kann.

create procedure sp1_outer(
    USE_BEGIN varchar,
    USE_INNER_BEGIN varchar,
    USE_INNER_COMMIT_OR_ROLLBACK varchar,
    USE_COMMIT_OR_ROLLBACK varchar
    )
returns varchar
language javascript
AS
$$
    // This should be part of the outer transaction started before this
    // stored procedure was called. This should be committed or rolled back
    // as part of that outer transaction.
    snowflake.execute (
        {sqlText: "insert into tracker_1 values (11, 'p1_alpha')"}
        );

    // This is an independent transaction. Anything inserted as part of this
    // transaction is committed or rolled back based on this transaction.
    if (USE_BEGIN != '')  {
        snowflake.execute (
            {sqlText: USE_BEGIN}
            );
        }
    snowflake.execute (
        {sqlText: "insert into tracker_2 values (12, 'p1_bravo')"}
        );
    // Call (and optionally begin/commit-or-rollback) an inner stored proc...
    var command = "call sp2_inner('";
    command = command.concat(USE_INNER_BEGIN);
    command = command.concat("', '");
    command = command.concat(USE_INNER_COMMIT_OR_ROLLBACK);
    command = command.concat( "')" );
    snowflake.execute (
        {sqlText: command}
        );
    if (USE_COMMIT_OR_ROLLBACK != '') {
        snowflake.execute (
            {sqlText: USE_COMMIT_OR_ROLLBACK}
            );
        }

    // This is part of the outer transaction started before this
    // stored procedure was called. This is committed or rolled back
    // as part of that outer transaction.
    snowflake.execute (
        {sqlText: "insert into tracker_1 values (13, 'p1_charlie')"}
        );

    // Dummy value.
    return "";
$$;
Copy

Diese Prozedur ist die innere gespeicherte Prozedur, die je nach den an sie übergebenen Parametern eine eingeschlossene Transaktion erstellen kann.

create procedure sp2_inner(
    USE_BEGIN varchar,
    USE_COMMIT_OR_ROLLBACK varchar)
returns varchar
language javascript
AS
$$
    snowflake.execute (
        {sqlText: "insert into tracker_2 values (21, 'p2_alpha')"}
        );

    if (USE_BEGIN != '')  {
        snowflake.execute (
            {sqlText: USE_BEGIN}
            );
        }
    snowflake.execute (
        {sqlText: "insert into tracker_3 values (22, 'p2_bravo')"}
        );
    if (USE_COMMIT_OR_ROLLBACK != '')  {
        snowflake.execute (
            {sqlText: USE_COMMIT_OR_ROLLBACK}
            );
        }

    snowflake.execute (
        {sqlText: "insert into tracker_2 values (23, 'p2_charlie')"}
        );

    // Dummy value.
    return "";
$$;
Copy

COMMIT für die mittlere von drei Ebenen ausführen

Dieses Beispiel enthält drei Transaktionen. In diesem Beispiel wird für die „mittlere“ Ebene ein COMMIT ausgeführt (die Transaktion, die von der äußersten Transaktion eingeschlossen ist und die innerste Transaktion einschließt). Dadurch wird für die äußerste und die innerste Transaktion das ROLLBACK ausgeführt.

begin transaction;
insert into tracker_1 values (00, 'outer_alpha');
call sp1_outer('begin transaction', 'begin transaction', 'rollback', 'commit');
insert into tracker_1 values (09, 'outer_charlie');
rollback;
Copy

Das Ergebnis ist, dass nur die Zeilen in der mittleren Transaktion (12, 21 und 23) mit COMMIT bestätigt werden. Für die Zeilen in der äußeren Transaktion und der inneren Transaktion wird kein COMMIT ausgeführt.

-- Should return only 12, 21, 23.
select id, name from tracker_1
union all
select id, name from tracker_2
union all
select id, name from tracker_3
order by id;
+----+------------+
| ID | NAME       |
|----+------------|
| 12 | p1_bravo   |
| 21 | p2_alpha   |
| 23 | p2_charlie |
+----+------------+
Copy

ROLLBACK für die mittlere von drei Ebenen ausführen

Dieses Beispiel enthält drei Transaktionen. In diesem Beispiel wird für die „mittlere“ Ebene ein ROLLBACK ausgeführt (die Transaktion, die von der äußersten Transaktion eingeschlossen ist und die innerste Transaktion einschließt). Dadurch wird für die äußerste und die innerste Transaktion das COMMIT ausgeführt.

begin transaction;
insert into tracker_1 values (00, 'outer_alpha');
call sp1_outer('begin transaction', 'begin transaction', 'commit', 'rollback');
insert into tracker_1 values (09, 'outer_charlie');
commit;
Copy

Das Ergebnis ist, dass alle Zeilen mit Ausnahme der Zeilen in der mittleren Transaktion (12, 21 und 23) mit COMMIT bestätigt werden.

select id, name from tracker_1
union all
select id, name from tracker_2
union all
select id, name from tracker_3
order by id;
+----+---------------+
| ID | NAME          |
|----+---------------|
|  0 | outer_alpha   |
|  9 | outer_charlie |
| 11 | p1_alpha      |
| 13 | p1_charlie    |
| 22 | p2_bravo      |
+----+---------------+
Copy

Verwenden der Fehlerbehandlung bei Transaktionen in gespeicherten Prozeduren

Der folgende Code zeigt eine einfache Fehlerbehandlung für eine Transaktion in einer gespeicherten Prozedur. Wenn der Parameterwert „fail“ übergeben wird, versucht die gespeicherte Prozedur eine Löschoperation in zwei vorhandenen Tabellen und einer nicht vorhandenen Tabelle auszuführen, und die gespeicherte Prozedur fängt den Fehler ab und gibt eine Fehlermeldung zurück. Wenn der Parameterwert „fail“ nicht übergeben wird, versucht die Prozedur, die Löschoperation in zwei vorhandenen Tabellen auszuführen, und ist erfolgreich.

Erstellen Sie die Tabellen und die gespeicherte Prozedur:

begin transaction;

create table parent(id integer);
create table child (child_id integer, parent_ID integer);

-- ----------------------------------------------------- --
-- Wrap multiple related statements in a transaction,
-- and use try/catch to commit or roll back.
-- ----------------------------------------------------- --
-- Create the procedure
create or replace procedure cleanup(FORCE_FAILURE varchar)
  returns varchar not null
  language javascript
  as
  $$
  var result = "";
  snowflake.execute( {sqlText: "begin transaction;"} );
  try {
      snowflake.execute( {sqlText: "delete from child where parent_id = 1;"} );
      snowflake.execute( {sqlText: "delete from parent where id = 1;"} );
      if (FORCE_FAILURE === "fail")  {
          // To see what happens if there is a failure/rollback,
          snowflake.execute( {sqlText: "delete from no_such_table;"} );
          }
      snowflake.execute( {sqlText: "commit;"} );
      result = "Succeeded";
      }
  catch (err)  {
      snowflake.execute( {sqlText: "rollback;"} );
      return "Failed: " + err;   // Return a success/error indicator.
      }
  return result;
  $$
  ;

commit;
Copy

Rufen Sie die gespeicherte Prozedur auf, und erzwingen Sie einen Fehler:

call cleanup('fail');
+----------------------------------------------------------+
| CLEANUP                                                  |
|----------------------------------------------------------|
| Failed: SQL compilation error:                           |
| Object 'NO_SUCH_TABLE' does not exist or not authorized. |
+----------------------------------------------------------+
Copy

Rufen Sie die gespeicherte Prozedur auf, ohne einen Fehler zu erzwingen:

call cleanup('do not fail');
+-----------+
| CLEANUP   |
|-----------|
| Succeeded |
+-----------+
Copy