Transaktionen mit mehreren Anweisungen

BigQuery unterstützt Transaktionen mit mehreren Anweisungen innerhalb einer einzelnen Abfrage oder über mehrere Abfragen hinweg, wenn Sitzungen verwendet werden. Mit einer Transaktion mit mehreren Anweisungen können Sie mutierende Vorgänge, z. B. das Einfügen oder Löschen von Zeilen, in einer oder mehreren Tabellen ausführen und die Änderungen entweder atomar vornehmen oder ein Rollback durchführen.

Anwendungen für Transaktionen mit mehreren Anweisungen:

DML-Mutationen an mehreren Tabellen als eine Transaktion ausführen. Die Tabellen können mehrere Datasets oder Projekte umfassen.
Ausführen von Mutationen an einer einzelnen Tabelle in mehreren Phasen, basierend auf Zwischenberechnungen.

Transaktionen garantieren ACID-Attribute und unterstützen die Snapshot-Isolation. Während einer Transaktion geben alle Lesevorgänge einen konsistenten Snapshot der Tabellen zurück, auf die in der Transaktion verwiesen wird. Wenn eine Anweisung in einer Transaktion eine Tabelle ändert, sind die Änderungen für nachfolgende Anweisungen innerhalb derselben Transaktion sichtbar.

Transaktionsbereich

Eine Transaktion muss in einer einzelnen SQL-Abfrage enthalten sein, es sei denn, sie befindet sich in Session mode. Eine Abfrage kann mehrere Transaktionen enthalten, die jedoch nicht verschachtelt werden können. Sie können Transaktionen mit mehreren Anweisungen für mehrere Abfragen in einer Sitzung ausführen.

Zum Starten einer Transaktion verwenden Sie die Anweisung BEGIN TRANSACTION: Die Transaktion endet, wenn einer der folgenden Ereignisse eintritt:

Die Abfrage führt eine COMMIT TRANSACTION-Anweisung aus. Für diese Anweisung werden alle in der Transaktion vorgenommenen Änderungen in kleinstmöglichen Schritten per Commit übernommen.
Die Abfrage führt eine ROLLBACK TRANSACTION-Anweisung aus. Diese Anweisung verwirft alle innerhalb der Transaktion vorgenommenen Änderungen.
Die Abfrage endet, bevor eine dieser beiden Anweisungen erreicht wird. In diesem Fall führt BigQuery automatisch ein Rollback der Transaktion durch.

Wenn während einer Transaktion ein Fehler auftritt und das die Abfrage einen Ausnahme-Handler hat, überträgt BigQuery die Kontrolle an den Ausnahme-Handler. Innerhalb des Ausnahmeblocks können Sie auswählen, ob die Transaktion übergeben oder ein Rollback durchgeführt werden soll.

Wenn während einer Transaktion ein Fehler auftritt und kein Ausnahme-Handler vorhanden ist, schlägt die Abfrage fehl und BigQuery führt automatisch ein Rollback der Transaktion durch.

Das folgende Beispiel zeigt einen Ausnahme-Handler, der ein Rollback einer Transaktion durchführt:

BEGIN

  BEGIN TRANSACTION;
  INSERT INTO mydataset.NewArrivals
    VALUES ('top load washer', 100, 'warehouse #1');
  -- Trigger an error.
  SELECT 1/0;
  COMMIT TRANSACTION;

EXCEPTION WHEN ERROR THEN
  -- Roll back the transaction inside the exception handler.
  SELECT @@error.message;
  ROLLBACK TRANSACTION;
END;

In Transaktionen unterstützte Anweisungen

Die folgenden Anweisungstypen werden in Transaktionen unterstützt:

Abfrageanweisungen: SELECT
DML-Anweisungen: INSERT, UPDATE, DELETE, MERGE und TRUNCATE TABLE
DDL-Anweisungen für temporäre Entitäten:
- CREATE TEMP TABLE
- CREATE TEMP FUNCTION
- DROP TABLE für eine temporäre Tabelle
- DROP FUNCTION für eine temporäre Funktion

DDL-Anweisungen, die permanente Entitäten wie Datasets, Tabellen und Funktionen erstellen oder löschen, werden innerhalb von Transaktionen nicht unterstützt.

Datums-/Zeitfunktionen in Transaktionen

Innerhalb einer Transaktion haben die folgenden Datums-/Zeitfunktionen besondere Verhaltensweisen:

CURRENT_TIMESTAMP-, CURRENT_DATE- und CURRENT_TIME-Funktionen geben den Zeitstempel für den Beginn der Transaktion zurück.
Sie können die FOR SYSTEM_TIME AS OF-Klausel nicht verwenden, um eine Tabelle nach dem Zeitstempel zu lesen, an dem die Transaktion begonnen hat. Hierbei wird ein Fehler zurückgegeben.

Beispiel einer Transaktion

In diesem Beispiel wird davon ausgegangen, dass es zwei Tabellen mit den Namen Inventory und NewArrivals gibt, die so erstellt wurden:

CREATE OR REPLACE TABLE mydataset.Inventory
(
 product string,
 quantity int64,
 supply_constrained bool
);

CREATE OR REPLACE TABLE mydataset.NewArrivals
(
 product string,
 quantity int64,
 warehouse string
);

INSERT mydataset.Inventory (product, quantity)
VALUES('top load washer', 10),
     ('front load washer', 20),
     ('dryer', 30),
     ('refrigerator', 10),
     ('microwave', 20),
     ('dishwasher', 30);

INSERT mydataset.NewArrivals (product, quantity, warehouse)
VALUES('top load washer', 100, 'warehouse #1'),
     ('dryer', 200, 'warehouse #2'),
     ('oven', 300, 'warehouse #1');

Die Tabelle Inventory enthält Informationen zum aktuellen Inventar und NewArrivals enthält Informationen zu neu angekommenen Artikeln.

Die folgende Transaktion aktualisiert Inventory mit neuen Eingangswerten und löscht die entsprechenden Datensätze aus NewArrivals. Wenn alle Anweisungen erfolgreich abgeschlossen wurden, werden die Änderungen in beiden Tabellen in kleinstmöglichen Schritten als eine einzelne Transaktion übergeben.

BEGIN TRANSACTION;

-- Create a temporary table that holds new arrivals from 'warehouse #1'.
CREATE TEMP TABLE tmp
  AS SELECT * FROM mydataset.NewArrivals WHERE warehouse = 'warehouse #1';

-- Delete the matching records from the NewArravals table.
DELETE mydataset.NewArrivals WHERE warehouse = 'warehouse #1';

-- Merge the records from the temporary table into the Inventory table.
MERGE mydataset.Inventory AS I
USING tmp AS T
ON I.product = T.product
WHEN NOT MATCHED THEN
 INSERT(product, quantity, supply_constrained)
 VALUES(product, quantity, false)
WHEN MATCHED THEN
 UPDATE SET quantity = I.quantity + T.quantity;

-- Drop the temporary table and commit the transaction.
DROP TABLE tmp;

COMMIT TRANSACTION;

Gleichzeitigkeit der Transaktion

Wenn eine Transaktion Zeilen in einer Tabelle ändert (aktualisiert oder löscht), können andere Transaktionen oder DML-Anweisungen, die Zeilen in derselben Tabelle mutieren, nicht gleichzeitig ausgeführt werden. In Konflikt stehende Transaktionen werden abgebrochen. Widersprüchliche DML-Anweisungen, die außerhalb einer Transaktion ausgeführt werden, werden unter Einhaltung der Warteschlangenlimits zum späteren Ausführen in die Warteschlange gestellt.

Vorgänge, die neue Zeilen lesen oder anhängen, können gleichzeitig mit der Transaktion ausgeführt werden. Beispielsweise können die folgenden Vorgänge gleichzeitig für eine Tabelle ausgeführt werden, während eine Transaktion Daten in derselben Tabelle mutiert:

SELECT-Anweisungen
BigQuery Storage Read API-Lesevorgänge
Abfragen von BigQuery BI Engine
INSERT-Anweisungen
Ladejobs, die die WRITE_APPEND-Anordnung zum Anfügen von Zeilen verwenden
Streaming-Schreibvorgänge

Wenn eine Transaktion nur eine Tabelle liest oder neue Zeilen an sie angehängt wird, können alle Vorgänge gleichzeitig an dieser Tabelle ausgeführt werden.

Transaktionsinformationen ansehen

BigQuery weist jeder Transaktion mit mehreren Anweisungen eine Transaktions-ID zu. Die Transaktions-ID wird an jede Abfrage angehängt, die innerhalb der Transaktion ausgeführt wird. Fragen Sie die INFORMATION_SCHEMA.JOBS*-Ansichten für die Spalte transaction_id ab, um die Transaktions-IDs für Ihre Jobs anzeigen zu lassen.

Bei der Ausführung einer Transaktion mit mehreren Anweisungen erstellt BigQuery für jede Anweisung in der Transaktion einen untergeordneten Job. Für eine bestimmte Transaktion hat jeder untergeordnete Job, der mit dieser Transaktion verknüpft ist, denselben transaction_id-Wert.

Die folgenden Beispiele zeigen, wie Sie Informationen zu Ihren Transaktionen finden.

Alle festgeschriebenen oder zurückgesetzten Transaktionen suchen

Die folgende Abfrage gibt alle Transaktionen zurück, die erfolgreich übergeben wurden.

SELECT transaction_id, parent_job_id, query
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE statement_type = "COMMIT_TRANSACTION" AND error_result IS NULL;

Die folgende Abfrage gibt alle Transaktionen zurück, die erfolgreich zurückgesetzt wurden.

SELECT
  transaction_id, parent_job_id, query
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE statement_type = "ROLLBACK_TRANSACTION" AND error_result IS NULL;

Start- und Endzeit einer Transaktion ermitteln

Die folgende Abfrage gibt die Start- und Endzeiten für eine angegebene Transaktions-ID zurück.

SELECT transaction_id, start_time, end_time, statement_type
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_USER
WHERE transaction_id = "TRANSACTION_ID"
AND statement_type IN
  ("BEGIN_TRANSACTION", "COMMIT_TRANSACTION", "ROLLBACK_TRANSACTION")
ORDER BY start_time;

Transaktion suchen, in der ein Job ausgeführt wird

Die folgende Abfrage ruft die Transaktion ab, die der angegebenen Job-ID zugeordnet ist. Gibt NULL zurück, wenn der Job nicht innerhalb einer Transaktion mit mehreren Anweisungen ausgeführt wird.

SELECT transaction_id
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE job_id = 'JOB_ID';

Aktuellen Job finden, der in einer Transaktion ausgeführt wird

Die folgende Abfrage gibt Informationen zu dem Job zurück, der derzeit innerhalb einer bestimmten Transaktion ausgeführt wird, sofern vorhanden.

SELECT job_id, query, start_time, total_slot_ms
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE transaction_id = 'TRANSACTION_ID' AND state = RUNNING;

Aktive Transaktionen finden, die sich auf eine Tabelle auswirken

Die folgende Abfrage gibt die aktiven Transaktionen zurück, die sich auf eine angegebene Tabelle auswirken. Wenn die Transaktion für jede aktive Transaktion als Teil von Abfragen mit mehreren Anweisungen ausgeführt wird, z. B. in einer gespeicherten Prozedur, wird auch die ID des übergeordneten Jobs zurückgegeben. Wenn die Transaktion innerhalb einer Sitzung ausgeführt wird, werden auch die Sitzungsinformationen zurückgegeben.

WITH running_transactions AS (
  SELECT DISTINCT transaction_id
  FROM
    `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
  EXCEPT DISTINCT
  SELECT transaction_id
  FROM
    `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
  WHERE
    statement_type = 'COMMIT_TRANSACTION'
    OR statement_type = 'ROLLBACK_TRANSACTION'
)
SELECT
  jobs.transaction_id, parent_job_id, session_info, query
FROM
  `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT AS jobs, running_transactions
WHERE
  destination_table = ("PROJECT_NAME", "DATASET_NAME", "TABLE_NAME")
  AND jobs.transaction_id = running_transactions.transaction_id;

Aktive Transaktionen suchen, die in einer Transaktion mit mehreren Anweisungen ausgeführt werden

Bei der folgenden Abfrage werden die aktiven Transaktionen für einen bestimmten Job zurückgegeben, der durch die ID des Jobs angegeben ist, auf dem die Transaktion mit mehreren Anweisungen ausgeführt wird.

SELECT DISTINCT transaction_id
FROM
  `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE
  parent_job_id = "JOB_ID"
EXCEPT DISTINCT
SELECT transaction_id
FROM
  `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE
  parent_job_id = "JOB_ID"
  AND (statement_type = 'COMMIT_TRANSACTION'
       OR statement_type = 'ROLLBACK_TRANSACTION');

Beschränkungen

Transaktionen können keine DDL-Anweisungen verwenden, die sich auf permanente Entitäten auswirken.
Innerhalb einer Transaktion werden materialisierte Ansichten als logische Ansichten interpretiert. Sie können weiterhin eine materialisierte Ansicht innerhalb einer Transaktion abfragen, dies führt jedoch im Vergleich zur entsprechenden logischen Ansicht nicht zu einer Leistungsverbesserung oder Kostensenkung.
Eine Transaktion mit mehreren Anweisungen, die fehlschlägt, löst einen Rollback-Vorgang aus, wobei alle ausstehenden Änderungen rückgängig gemacht und Wiederholungsversuche beendet werden.
Eine Transaktion kann Daten in maximal 100 Tabellen mutieren und höchstens 100.000 Partitionsänderungen ausführen.
BI Engine beschleunigt Abfragen innerhalb einer Transaktion nicht.