Transaktionen¶

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.

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.

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;

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.

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.

Nicht überlappende Transaktionen¶

In den Abschnitten unten wird Folgendes beschrieben:

• Verwenden einer gespeicherten Prozedur innerhalb einer Transaktion

• Verwenden einer Transaktion innerhalb einer gespeicherten Prozedur

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

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

BEGIN TRANSACTION;
statement A;
statement B;
COMMIT;

CALL p1();

BEGIN TRANSACTION;
statement G;
statement H;
COMMIT;

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;

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.

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.

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.

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.

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.

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;

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;

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;

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.

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.

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:

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.

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);

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 "";
$$;

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;

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  |
+----+-------------+

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);

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 "";
$$;

Rufen Sie die gespeicherte Prozedur auf:

begin transaction;
call update_data();
rollback;

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

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

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. |
+----------------------------+

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);

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 "";
$$;

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 "";
$$;

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;

-- 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 |
+----+------------+

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;

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      |
+----+---------------+

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;

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. |
+----------------------------------------------------------+

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

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