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

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

Wie die meisten Datenbanktransaktionen garantierten auch Snowflake-Transaktionen die ACID-Eigenschaften.

Snowflake unterstützt die READ COMMITTED-Transaktionsisolation.

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 INSERT, UPDATE, DELETE, MERGE und TRUNCATE.

  • Der Begriff „Abfrageanweisung“ bezieht sich auf 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. Gespeicherte Prozeduren und Transaktionen sind unter Gespeicherte Prozeduren und Transaktionen beschrieben.

Ausführungsbereich einer Snowflake-Transaktionen

Eine Transaktion hat einen „Ausführungsbereich“, der die zu einer Transaktion gehörenden Anweisungen bestimmt. Der Anfangs- und Endpunkt einer Transaktion bestimmt, welche Anweisungen in die Transaktion einbezogen werden. Anfang und Ende können explizit oder implizit sein.

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

Explizite Transaktionen

Eine Transaktion kann explizit gestartet werden, indem eine BEGIN-Anweisung ausgeführt wird.

Eine Transaktion kann explizit durch die Ausführung von COMMIT oder ROLLBACK beendet werden.

Wenn BEGIN TRANSACTION ausgeführt wird, während eine Transaktion offen ist (z. B. wenn eine zweite BEGIN TRANSACTION-Anweisung nach einer ersten BEGIN TRANSACTION-Anweisung ausgeführt wird, bevor ein COMMIT oder ROLLBACK für die erste BEGIN TRANSACTION-Anweisung ausgeführt wird), wird die zweite BEGIN TRANSACTION-Anweisung ignoriert. Die Benutzer sollten dies jedoch vermeiden, weil zusätzliche BEGIN TRANSACTION-Anweisungen es menschlichen Lesern erschwert, den COMMIT- bzw. ROLLBACK-Anweisungen die zugehörigen BEGIN TRANSACTION-Anweisungen zuzuordnen.

Bemerkung

Explizite Transaktionen sollten nur DML-Anweisungen und Abfrageanweisungen enthalten. DDL-Anweisungen führen implizit das COMMIT offener Transaktionen aus (Einzelheiten 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 oder um eine DML- oder 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 sich eine DDL-Anweisung außerhalb einer Transaktion befindet, wird die DDL-Anweisung so ausgeführt, als ob unmittelbar vor ihr ein BEGIN TRANSACTION und unmittelbar nach ihr ein COMMIT stünde:

BEGIN TRANSACTION;
ddl_statement;
COMMIT;      -- Or rollback if the statement fails.

(Wenn die DDL-Anweisung fehlschlägt, dann wird sie rückgängig gemacht und kein COMMIT ausgeführt).

Wenn sich eine DDL-Anweisung innerhalb einer offenen Transaktion befindet, wird die Anweisung so ausgeführt, als ob ihr unmittelbar COMMIT und BEGIN TRANSACTION vorangehen und ein COMMIT folgen würde. Im folgenden Beispiel wird der Code für die expliziten Anweisungen in Kleinbuchstaben geschrieben, während der Code in Großbuchstaben die impliziten BEGIN TRANSACTION und COMMIT Anweisungen zeigt, die durch die DDL-Anweisung verursacht werden:

begin;
...
COMMIT;
BEGIN TRANSACTION;
ddl_statement;
COMMIT;

(Wenn die DDL-Anweisung fehlschlägt, dann wird sie rückgängig gemacht und kein COMMIT ausgeführt).

Mit anderen Worten, die DDL-Anweisung führt zu einem COMMIT jeder offenen Transaktion und wird dann in ihrer eigenen Transaktion ausgeführt.

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. Um DDL-Anweisungen rückgängig zu machen, müssen Sie meist eine andere DDL-Anweisung ausführen (z. B. DROP für Tabelle, die Sie vorher erstellt hatten, ALTER, um eine vorher mit ALTER geänderte Tabelle wieder auf den ursprünglichen Stand zurücksetzen, oder UNDROP eines Objekts, das Sie vorher gelöscht hatten).

Eine DDL-Anweisung startet keine neue Transaktion, nachdem sie für sich selbst ein COMMIT ausgeführt hat. 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 AUTOCOMMIT. 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 (außerhalb einer expliziten Transaktion) wird automatisch ein COMMIT ausgeführt, wenn sie erfolgreich ist, und automatisch eine ROLLBACK ausgeführt, wenn sie fehlschlägt.

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. implizites Rollback, 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 offen ist):

    • Bei Ausführung einer DDL-Anweisung.

    • Bei Ausführung einer Anweisung, die AUTOCOMMIT aktiviert.

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

    • Bei Beendigung einer Sitzung.

    • Am Ende einer gespeicherten Prozedur, die eine BEGIN TRANSACTION-Anweisung enthält (unabhängig davon, ob das BEGIN TRANSACTION implizit oder explizit war).

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 offen, 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;

Die Ausgabe der abschließenden SELECT-Anweisung enthält 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.

Gespeicherte Prozeduren und Transaktionen

Im Allgemeinen folgen gespeicherte Prozeduren den gleichen Regeln für Transaktionen wie oben unter Ausführungsbereich einer Snowflake-Transaktionen beschrieben. 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.

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 am Ende der gespeicherten Prozedur immer noch offen ist, dann wird für diese Transaktion am Ende der gespeicherten Prozedur automatisch im Hintergrund ein ROLLBACK ausgeführt.

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. Mit anderen Worten, für jedes BEGIN TRANSACTION muss es ein entsprechendes COMMIT (oder ROLLBACK) auf der gleichen „Ebene“ geben. Die Nichtbeachtung dieser Regel kann ein implizites ROLLBACK zur Folge haben.

Wenn eine gespeicherte Prozedur eine Transaktion enthält, kann diese Transaktion nur einen Teil des Prozedurtextes der gespeicherten Prozedur enthalten. Beispielsweise wird Folgendes unterstützt:

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

        BEGIN TRANSACTION;
        statement2;
        COMMIT;

        statement3;
        ...

    $$;

Nicht überlappende Transaktionen

Eine gespeicherte Prozedur kann mehr als eine Transaktion enthalten, solange jede Transaktion innerhalb der gespeicherten Prozedur beginnt und abgeschlossen wird und sich die Transaktionen nicht überlappen. Innerhalb einer gespeicherten Prozedur ist beispielsweise Folgendes gültig:

...
statement1;

begin;
statement2;
commit;

statement3;

begin;
statement4;
commit;

statement5;

...

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:

  • Es wird ein BEGIN TRANSACTION ausgeführt, bevor die gespeicherte Prozedur aufgerufen wird (und das zugehörige COMMIT oder ROLLBACK wurde erst ausgeführt, nachdem die gespeicherte Prozedur aufgerufen wurde).

  • Ein COMMIT (oder ROLLBACK) wird ausgeführt, nachdem die gespeicherte Prozedur abgeschlossen ist.

  • Der Hauptteil der gespeicherten Prozedur enthält kein 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 COMMIT 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;

Dies entspricht der Ausführung der folgenden Anweisungssequenz:

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

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;

$$;

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;

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;

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.

Überlappende Transaktionen

In diesem Abschnitt werden sich überlappende Transaktionen beschrieben.

Eine gespeicherte Prozedur, die eine Transaktion enthält, kann aus einer anderen Transaktion heraus aufgerufen werden. Die äußere Transaktion kann sich in einer äußeren gespeicherten Prozedur oder außerhalb jeder gespeicherten Prozedur befinden.

Die innere Transaktion wird nicht als verschachtelt behandelt, stattdessen ist die innere Transaktion eine separate Transaktion. Snowflake verwendet den Begriff der „autonomen bereichsbezogene Transaktion“ (oder einfach „bereichsbezogene Transaktion“), weil die Ausführung jeder Transaktion in einem konzeptionell unabhängigen Ausführungsbereich erfolgt.

Bemerkung

Anmerkung zur Terminologie:

Die Begriffe „innere“ und „äußere“ werden häufig verwendet, wenn verschachtelte Operationen beschrieben werden, wie z. B. Aufrufe verschachtelter gespeicherter Prozeduren. Snowflake unterstützt zwar verschachtelte Prozeduraufrufe, aber keine verschachtelten Transaktionen. Um zwischen beiden Fällen klar zu unterscheiden, werden in diesem Dokument bei autonome bereichsbezogene Transaktionen die Begriffe „eingeschlossene“ und „einschließende“ statt „innere“ und „äußere“ verwendet, während bei verschachtelten Prozeduraufrufen weiterhin die Begriffe „innere“ und „äußere“ verwendet werden.

Eine eingeschlossene Transaktion und eine einschließende Transaktion überlappen sich zeitlich, aber nicht inhaltlich. Jede SQL-Anweisung ist Teil nur einer Transaktion und kann somit nur mit einem COMMIT oder ROLLBACK bestätigt bzw. rückgängig gemacht werden. Durch ein einschließendes ROLLBACK oder COMMIT wird ein eingeschlossenes COMMIT oder ROLLBACK nicht „rückgängig“ gemacht.

Der folgende Pseudocode zeigt eine gespeicherte Prozedur, die sowohl eine Transaktion enthält als auch in einer Transaktion enthalten ist:

Erstellen Sie eine gespeicherte Prozedur, die eine Transaktion enthält:

CREATE PROCEDURE p_enclosed ()
...
$$
    statement B;

    begin;
    statement C;
    statement D;
    commit;

    statement E;
$$;

Rufen Sie die gespeicherten Prozedur von innerhalb einer einschließenden Transaktion auf:

begin;
statement A;
call p_enclosed(); -- contains its own begin/commit
statement F;
commit;

Die vorhergehenden Anweisungen entsprechen ungefähr (aber nicht genau) der folgenden Sequenz:

BEGIN TRANSACTION;
statement A;
statement B;
statement E;
statement F;
COMMIT;

BEGIN TRANSACTION;
statement C;
statement D;
COMMIT;

Die oben gezeigte Sequenz entspricht nur annähernd der tatsächlichen Ausführung der sich überlappenden Transaktionen, da sich die Transaktionen zeitlich tatsächlich überlappen und Snowflake die READ COMMITTED-Isolationsebene verwendet. In READ COMMITTED-Isolation sieht jede Anweisung nur Daten und Änderungen, die von anderen Transaktionen mit COMMIT bestätigt wurden oder die innerhalb der aktuellen Transaktion geändert wurden. Eine Anweisung kann Änderungen, die innerhalb anderer Transaktionen ausgeführt und noch nicht mit COMMIT bestätigt wurden, nicht „sehen“.

Tatsächlich wurde in der vorhergehenden Anweisung in etwa Folgendes ausgeführt:

Transaktion 1

Transaktion 2

Anmerkungen

BEGIN TRANSACTION;

Anweisung A;

Anweisung B;

Sieht nur die Änderung von Anweisung A.

BEGIN TRANSACTION;

Anweisung C;

Anweisung D;

Sieht nur die Änderung von Anweisung C.

COMMIT;

Anweisung E;

Sieht Änderungen der Anweisungen A, B, C und D.

Anweisung F;

Sieht Änderungen der Anweisungen A, B, C, D und E.

COMMIT;

Da die beiden oben gezeigten Transaktionen unabhängig voneinander sind, sieht z. B. die Anweisung C nicht die Änderungen, die von der anderen Transaktion in den Anweisungen A und B vorgenommen wurden, denn obwohl diese Änderungen vor der Ausführung der Anweisung C vorgenommen wurden, wurden die Änderungen vor der Anweisung C nicht mit COMMIT bestätigt.

Die Anweisungen E und F dagegen sehen die Änderungen der Anweisungen C und D, da die Änderungen C und D vor dem COMMIT der Anweisungen E und F erfolgten.

Das bedeutet, dass, obwohl sich die Anweisungen B und E in derselben Transaktion befinden, B und E unterschiedliche Werte für Zeilen sehen können, die durch die Anweisungen C und D geändert wurden.

Für die Transaktionen werden COMMIT und ROLLBACK unabhängig voneinander ausgeführt. Wenn z. B. das COMMIT innerhalb der gespeicherten Prozedur durch einen ROLLBACK ersetzt würde, dann würde für die Anweisungen A, B, E und F trotzdem das COMMIT ausgeführt werden. Wenn das COMMIT nach der Prozedur durch ein ROLLBACK ersetzt würde (und das COMMIT innerhalb der Prozedur bliebe ein COMMIT), dann würde für die Anweisungen C und D das COMMIT ausgeführt werden, während für die Anweisungen A, B, E und F das ROLLBACK ausgeführt werden würde.

Die Regeln für autonome bereichsbezogene Transaktionen gelten auch dann, wenn gespeicherte Prozeduren verschachtelt werden. Wenn beispielsweise eine gespeicherte Prozedur P1 eine andere gespeicherte Prozedur P2 von innerhalb eines BEGIN TRANSACTION/COMMIT-Blocks aus aufruft und P2 eine eigene Transaktion enthält, dann würde die in P2 gestartete Transaktion ebenfalls unabhängig ausgeführt werden. Betrachten Sie beispielsweise den folgenden 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;

Im folgenden Beispiel gibt es drei autonome bereichsbezogene Transaktionen. Eine Transaktion enthält die Anweisungen A und E, eine Transaktion enthält die Anweisungen B und D, und eine dritte Transaktion enthält die Anweisung C.

Gespeicherte Prozeduren können sich selbst rekursiv aufrufen. Ein rekursiver Aufruf ist nur ein spezifischer Typ eines geschachtelten Aufrufs und folgt den gleichen Transaktionsregeln wie ein geschachtelter Aufruf.

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

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.

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

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 neueste Anweisung, die Teil des Deadlocks ist, als Opfer. Die Anweisung wird mit ROLLBACK zurückgesetzt, aber die Transaktion selbst bleibt offen, sodass für sie ein COMMIT oder ROLLBACK ausgeführt werden muss.

Das Erkennen von Deadlocks kann viel Zeit in Anspruch nehmen.

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.

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 offen bleibt, setzt Snowflake die Transaktion in der Regel zurück, nachdem sie vier Stunden lang inaktiv war.

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 die Ausführung von mehr als einer BEGIN TRANSACTION-Anweisung in einer Zeile. 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.

Beispiele

Einfaches Beispiel für bereichsbezogene Transaktion und gespeicherte Prozedur

Hier ist ein einfaches Beispiel für die autonome 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 INSERTs, die sich in der gespeicherten Prozedur, aber außerhalb der Transaktion der gespeicherten Prozedur befinden, beibehalten werden.

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

Beispiele für autonome 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 "";
$$;

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;

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

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;

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

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