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 gemeinsam angewendet (mit einem Commit ausgeführt) oder rückgängig gemacht (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önnen 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, die Datenbankobjekte definieren.

• 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. Benutzer sollten jedoch zusätzliche BEGIN TRANSACTION Anweisungen vermeiden, da sie es menschlichen Lesern sehr viel schwerer machen, einer COMMIT- (oder ROLLBACK-)Anweisung mit die zugehörige BEGIN TRANSACTION-Anweisung zuzuweisen.

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 die Transaktion trotzdem ein Commit ausführen, anstatt ein Rollback auszufü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 die Transaktion bestätigt wird, werden die durch die erfolgreichen Anweisungen vorgenommenen Änderungen übernommen.

Betrachten Sie zum Beispiel den folgenden Code, der zwei gültige Werte und einen ungültigen Wert in eine Tabelle 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 sich mehrere Sitzungen nicht dieselbe Transaktion teilen können, teilen sich mehrere Threads, die eine einzige Verbindung verwenden, dieselbe Sitzung und damit dieselbe Transaktion. Diese Verhaltensweise kann zu unerwarteten Ergebnissen führen, wenn ein Thread ein Rollback einer Arbeit vornimmt, die in einem anderen Thread ausgeführt wurde.

Diese Situation kann auftreten, wenn eine Clientanwendung, die einen Snowflake-Treiber (z. B. den Snowflake-JDBC-Treiber) oder einen Konnektor (z. B. den Snowflake Connector 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 my_procedure()
  ...
  AS
  $$
    statement X;
    statement Y;
  $$;

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

BEGIN TRANSACTION;
statement W;
statement X;
statement Y;
statement Z;
COMMIT;

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 einschließenden 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 einschließende 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;

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.

Wenn AUTOCOMMIT deaktiviert ist, sollten 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 BEGINTRANSACTION 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;

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 zurück, weil der CALL zu inner_sp2() fehlgeschlagen ist.

2. Da CALL an 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 Transaktion in outer_sp1() wird implizit ein Rollback und kein Commit ausgeführt, sodass das Einfügen des Wertes osp1_beta nie festgeschrieben (Commit) wird.

Timeout-Parameter sperren¶

Zwei Parameter steuern das Timeout für Sperren: LOCK_TIMEOUT und HYBRID_TABLE_LOCK_TIMEOUT.

ALTER SESSION SET LOCK_TIMEOUT=7200;
SHOW PARAMETERS LIKE 'lock_timeout';

+--------------+-------+---------+---------+-------------------------------------------------------------------------------+--------+
| key          | value | default | level   | description                                                                   | type   |
|--------------+-------+---------+---------+-------------------------------------------------------------------------------+--------|
| LOCK_TIMEOUT | 7200  | 43200   | SESSION | Number of seconds to wait while trying to lock a resource, before timing out  | NUMBER |
|              |       |         |         | 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.                                                                 |        |
+--------------+-------+---------+---------+-------------------------------------------------------------------------------+--------+

ALTER SESSION SET HYBRID_TABLE_LOCK_TIMEOUT=600;
SHOW PARAMETERS LIKE 'hybrid_table_lock_timeout';

+---------------------------+-------+---------+---------+--------------------------------------------------------------------------------+--------|
| key                       | value | default | level   | description                                                                    | type   |
|---------------------------+-------+---------+---------+--------------------------------------------------------------------------------+--------+
| HYBRID_TABLE_LOCK_TIMEOUT | 600   | 3600    | SESSION | Number of seconds to wait while trying to acquire locks, before timing out and | NUMBER |
|                           |       |         |         | aborting the statement. A value of 0 turns off lock waiting i.e. the statement |        |
|                           |       |         |         | must acquire the lock immediately or abort.                                    |        |
+---------------------------+-------+---------+---------+--------------------------------------------------------------------------------+--------+

Deadlocks¶

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

Beachten Sie die folgenden Regeln:

• Deadlocks können nicht auftreten, wenn Abfragen mit Autocommit gleichzeitig ausgeführt werden. Dies gilt sowohl für Standardtabellen als auch für Hybridtabellen, da SELECT-Anweisungen immer schreibgeschützt sind.

• Deadlocks können bei Autocommit-DML-Operationen auf Standardtabellen nicht auftreten, aber sie können bei Autocommit-DML-Operationen auf Hybridtabellen auftreten.

• Deadlocks können auftreten, wenn Transaktionen explizit gestartet werden und mehrere Anweisungen in jeder Transaktion ausgeführt werden. Snowflake erkennt Deadlocks und wählt dass 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¶

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

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

• Wenn eine Transaktion aus hybriden Tabellen liest oder in sie schreibt und 5 Minuten im Leerlauf ist, wird sie automatisch abgebrochen und zurückgesetzt, unabhängig davon, ob sie andere Transaktionen daran hindert, dieselbe Tabelle zu ändern.

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.

Siehe auch Sichtbarkeit von Transaktionen und Sperren für Hybridtabellen.

Überwachen von Transaktionen und Sperren¶

Mit dem Befehl SHOW TRANSACTIONS erhalten Sie eine Liste der Transaktionen, die vom aktuellen Benutzer (in allen Sitzungen dieses Benutzers) oder von allen Benutzern in allen Sitzungen des Kontos ausgeführt werden. Das folgende Beispiel bezieht sich auf die Sitzungen des aktuellen Benutzers.

SHOW TRANSACTIONS;

+---------------------+---------+-----------------+--------------------------------------+-------------------------------+---------+-------+
|                  id | user    |         session | name                                 | started_on                    | state   | scope |
|---------------------+---------+-----------------+--------------------------------------+-------------------------------+---------+-------|
| 1721165674582000000 | CALIBAN | 186457423713330 | 551f494d-90ed-438d-b32b-1161396c3a22 | 2024-07-16 14:34:34.582 -0700 | running |     0 |
| 1721165584820000000 | CALIBAN | 186457423749354 | a092aa44-9a0a-4955-9659-123b35c0efeb | 2024-07-16 14:33:04.820 -0700 | running |     0 |
+---------------------+---------+-----------------+--------------------------------------+-------------------------------+---------+-------+

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

Sie können auch die Funktion CURRENT_TRANSACTION verwenden, um die Transaktions-ID der aktuell in der Sitzung laufenden Transaktion zurückzugeben.

SELECT CURRENT_TRANSACTION();

+-----------------------+
| CURRENT_TRANSACTION() |
|-----------------------|
| 1721161383427000000   |
+-----------------------+

Wenn Sie die Transaktions-ID kennen, die Sie überwachen möchten, können Sie den Befehl DESCRIBE TRANSACTION verwenden, um Details über die Transaktion zu erhalten, während sie noch läuft oder nachdem sie bestätigt oder abgebrochen wurde. Beispiel:

DESCRIBE TRANSACTION 1721161383427000000;

+---------------------+---------+----------------+--------------------------------------+-------------------------------+-----------+-------------------------------+
|                  id | user    |        session | name                                 | started_on                    | state     | ended_on                      |
|---------------------+---------+----------------+--------------------------------------+-------------------------------+-----------+-------------------------------|
| 1721161383427000000 | CALIBAN | 10363774361222 | 7db0ec5c-2e5d-47be-ac37-66cbf905668b | 2024-07-16 13:23:03.427 -0700 | committed | 2024-07-16 13:24:14.402 -0700 |
+---------------------+---------+----------------+--------------------------------------+-------------------------------+-----------+-------------------------------+

Sichtbarkeit von Transaktionen und Sperren für Hybridtabellen¶

Wenn Sie sich die Ausgaben von Befehlen und Ansichten für Transaktionen ansehen, die auf Hybridtabellen oder Sperren auf Hybridtabellenzeilen zugreifen, beachten Sie das folgende Verhalten:

• Transaktionen werden nur aufgeführt, wenn sie andere Transaktionen sperren oder wenn sie gesperrt sind.

• Denken Sie daran, dass Transaktionen, die auf Hybridtabellen zugreifen, Sperren auf Zeilenebene halten (Typ ROW). Wenn zwei Transaktionen auf verschiedene Zeilen in derselben Tabelle zugreifen, blockieren sie sich nicht gegenseitig.

• Transaktionen werden nur aufgeführt, wenn eine gesperrte Transaktion länger als 5 Sekunden gesperrt war.

• Wenn eine Transaktion nicht mehr gesperrte, erscheint sie möglicherweise noch in der Ausgabe, aber nicht länger als 15 Sekunden.

Auch für die SHOW LOCKS-Ausgabe gelten die folgenden Regeln:

• Eine Sperre wird nur aufgeführt, wenn eine Transaktion die Sperre hält und die andere Transaktion für diese bestimmte Sperre blockiert ist.

• In der Spalte type zeigen Sperren für Hybridtabellen ROW.

• Die Spalte resource zeigt immer die ID der Blocking-Transaktion an. (Die blockierte Transaktion wird durch die Transaktion mit dieser ID blockiert)

• In vielen Fällen erzeugen Abfragen auf Hybridtabellen keine Abfrage-IDs. Siehe Hybridtabellen.

Beispiel:

SHOW LOCKS;

+---------------------+------+---------------------+-------------------------------+---------+-------------+--------------------------------------+
| resource            | type |         transaction | transaction_started_on        | status  | acquired_on | query_id                             |
|---------------------+------+---------------------+-------------------------------+---------+-------------+--------------------------------------|
| 1721165584820000000 | ROW  | 1721165584820000000 | 2024-07-16 14:33:04.820 -0700 | HOLDING | NULL        |                                      |
| 1721165584820000000 | ROW  | 1721165674582000000 | 2024-07-16 14:34:34.582 -0700 | WAITING | NULL        | 01b5b715-0002-852b-0000-a99500665352 |
+---------------------+------+---------------------+-------------------------------+---------+-------------+--------------------------------------+

In der Ansicht LOCK_WAIT_HISTORY verhält sich die Ausgabe wie folgt:

• Die Spalten requested_at und acquired_at geben an, wann Sperren auf Zeilenebene angefragt und erworben wurden, vorbehaltlich der allgemeinen Regeln für die Meldung von Transaktionsaktivitäten mit Hybridtabellen.

• Die Spalten lock_type und object_name zeigen beide den Wert Row an.

• Die Spalten schema_id und schema_name sind immer leer (0 bzw. NULL).

• Die Spalte object_id zeigt immer die ID des Blocking-Objekts an.

• Die Spalte blocker_queries ist ein JSON-Array mit genau einem Element, das die blockierende Transaktion anzeigt.

• Auch wenn mehrere Transaktionen für dieselbe Zeile gesperrt sind, werden sie in der Ausgabe als mehrere Zeilen angezeigt.

Beispiel:

SELECT query_id, object_name, transaction_id, blocker_queries
  FROM SNOWFLAKE.ACCOUNT_USAGE.LOCK_WAIT_HISTORY
  WHERE requested_at >= DATEADD('hours', -48, CURRENT_TIMESTAMP()) LIMIT 1;

+--------------------------------------+-------------+---------------------+---------------------------------------------------------+
| QUERY_ID                             | OBJECT_NAME |      TRANSACTION_ID | BLOCKER_QUERIES                                         |
|--------------------------------------+-------------+---------------------+---------------------------------------------------------|
| 01b5b715-0002-852b-0000-a99500665352 | Row         | 1721165674582000000 | [                                                       |
|                                      |             |                     |   {                                                     |
|                                      |             |                     |     "is_snowflake": false,                              |
|                                      |             |                     |     "query_id": "01b5b70d-0002-8513-0000-a9950065d43e", |
|                                      |             |                     |     "transaction_id": 1721165584820000000               |
|                                      |             |                     |   }                                                     |
|                                      |             |                     | ]                                                       |
+--------------------------------------+-------------+---------------------+---------------------------------------------------------+

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

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