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.

Hinweis: Es ist nicht garantiert, dass Lesevorgänge aus externen Datenquellen innerhalb einer Transaktion konsistent sind, wenn sich die zugrunde liegende Datenquelle während der Transaktion ändert.

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 der Sitzungsmodus deaktiviert ist und 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.

Wenn der Sitzungsmodus aktiviert ist und kein Ausnahme-Handler vorhanden ist, bleibt die Transaktion aktiv, bis die Sitzung beendet oder explizit zurückgesetzt wird. Unabhängig vom Sitzungsmodus empfiehlt es sich, Ausnahme-Handler hinzuzufügen, damit Transaktionen so schnell wie möglich ordnungsgemäß zurückgesetzt werden. So wird vermieden, dass Sperren und Ressourcen länger als nötig gehalten werden.

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 aktualisiert oder löscht, wird eine Sperre auf Tabellenebene und nicht auf Partitionsebene angewendet. Andere Transaktionen oder DML-Anweisungen, die Zeilen in derselben Tabelle mutieren, können nicht gleichzeitig ausgeführt werden, auch wenn sie auf verschiedene Partitionen einer partitionierten Tabelle abzielen. BigQuery bricht in Konflikt stehende Transaktionen ab. BigQuery stellt jedoch in Konflikt stehende DML-Anweisungen, die außerhalb einer Transaktion ausgeführt werden, in eine Warteschlange, um sie später auszuführen. Dabei gelten die Warteschlangenlimits.

Warnung: BigQuery stellt automatisch bis zu 20 eigenständige mutierende DML-Anweisungen pro Tabelle in die Warteschlange. Für Transaktionen mit mehreren Anweisungen gilt jedoch ein Nebenläufigkeitslimit von einer mutierenden Transaktion pro Tabelle. BigQuery stellt sie nicht in die Warteschlange. Wenn eine gleichzeitige Transaktion mit mehreren Anweisungen versucht, eine gesperrte Tabelle zu ändern, wird die Warteschlange umgangen und die Transaktion schlägt sofort mit der folgenden Fehlermeldung fehl: Transaction is aborted due to concurrent update against table.

Wenn Sie eine mutierende DML-Anweisung innerhalb einer Transaktion mit mehreren Anweisungen ausführen, hält BigQuery die Schreibsperre für die Zieltabelle, bis die Transaktion festgeschrieben oder zurückgesetzt wird oder bis die übergeordnete Abfrage mit mehreren Anweisungen oder die Sitzung endet. Dieses Verhalten gilt für Transaktionen in einer Abfrage mit mehreren Anweisungen, einer gespeicherten Prozedur oder einer aktiven Sitzung, z. B. einer ODBC- oder JDBC-Verbindung. BigQuery gibt die Sperre nicht frei, wenn die einzelne DML-Anweisung abgeschlossen ist. Lang andauernde Berechnungen in derselben offenen Transaktion halten die Tabelle gesperrt und führen dazu, dass gleichzeitige mutierende Transaktionen in dieser Tabelle fehlschlagen.

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 anhängt, kann jeder Vorgang gleichzeitig für diese Tabelle ausgeführt werden, da INSERT-Anweisungen nicht miteinander in Konflikt stehen. Wenn Sie verhindern möchten, dass mehrere Transaktionen, die sonst nur Zeilen einfügen würden, gleichzeitig in einer Tabelle ausgeführt werden, starten Sie jede Transaktion mit einer DELETE-Anweisung, die keine Zeilen ändert, z. B. DELETE FROM TABLE_NAME WHERE FALSE;. Wenn Sie diese Anweisung einfügen, erhält BigQuery eine Sperre auf Tabellenebene für die DELETE-Anweisung. Alle anderen gleichzeitigen Transaktionen, die ebenfalls eine ändernde Anweisung für diese Tabelle ausführen, stehen im Konflikt und werden abgebrochen.

Nebenläufigkeitskonflikte vermeiden und lösen

So reduzieren Sie Konflikte bei der Transaktions-Nebenläufigkeit:

• Unnötige Transaktions-Wrapper entfernen: Wenn eine gespeicherte Prozedur oder eine Abfrage mit mehreren Anweisungen nur eine einzige mutierende DML-Anweisung wie eine MERGE-Anweisung ausführt, sollten Sie die Anweisung nicht in BEGIN TRANSACTION- und COMMIT TRANSACTION-Anweisungen verpacken. Eine einzelne DML-Anweisung ist bereits atomar. Wenn Sie die Anweisung außerhalb einer Transaktion mit mehreren Anweisungen ausführen, kann BigQuery die DML-Warteschlange anwenden.
• Fehlgeschlagene Transaktionen mit exponentiellem Backoff wiederholen: Wenn eine Transaktion mit mehreren Anweisungen aufgrund einer gleichzeitigen Aktualisierung fehlschlägt, führt BigQuery ein Rollback der Transaktion durch und hebt die Tabellensperre auf. Da es mehrere Sekunden dauern kann, bis BigQuery die Sperre in der Metadatenebene aufhebt, sollten Sie fehlgeschlagene Transaktionen mit einer exponentiellen Backoff-Strategie wiederholen. Warten Sie vor dem ersten Wiederholungsversuch mindestens 60 Sekunden.

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. Für jede aktive Transaktion, wenn die Transaktion als Teil von Abfragen mit mehreren Anweisungen ausgeführt wird, z. B. in einer gespeicherten Prozedur, wird auch die übergeordnete Job-ID 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');

Sich überschneidende Jobs für eine fehlgeschlagene Transaktion finden

Wenn eine Transaktion oder DML-Anweisung mit einem Fehler aufgrund einer gleichzeitigen Aktualisierung fehlschlägt, wird bei einer Prüfung des Abfrageverlaufs einzelner DML-Anweisungen zum Zeitpunkt des Fehlers möglicherweise keine aktive DML-Anweisung angezeigt. Diese Situation tritt auf, wenn eine übergeordnete Abfrage mit mehreren Anweisungen oder eine Sitzung eine frühere DML-Anweisung innerhalb einer Transaktion ausführt und andere Anweisungen ausführt, während die Tabellensperre beibehalten wird.

Für projektübergreifende Arbeitslasten können Sie die Ansicht INFORMATION_SCHEMA.JOBS_BY_ORGANIZATION anstelle von INFORMATION_SCHEMA.JOBS_BY_PROJECT abfragen. In der folgenden Abfrage werden untergeordnete DML-Jobs mit ihren übergeordneten Jobs in INFORMATION_SCHEMA.JOBS_BY_PROJECT verknüpft, um mutierende Jobs, Skripts oder Sitzungen zu identifizieren, die sich mit einem fehlgeschlagenen Job überschnitten haben:

SELECT
  child_jobs.job_id,
  child_jobs.parent_job_id,
  child_jobs.transaction_id,
  child_jobs.session_info.session_id,
  child_jobs.statement_type,
  child_jobs.start_time AS statement_start_time,
  child_jobs.end_time AS statement_end_time,
  parent_jobs.start_time AS parent_start_time,
  parent_jobs.end_time AS parent_end_time,
  COALESCE(parent_jobs.start_time, child_jobs.start_time) AS lock_window_start,
  COALESCE(
    parent_jobs.end_time, child_jobs.end_time, CURRENT_TIMESTAMP()
  ) AS lock_window_end,
  child_jobs.error_result.message AS error_message,
  child_jobs.query
FROM
  `region-REGION`.INFORMATION_SCHEMA.JOBS_BY_PROJECT AS child_jobs
LEFT JOIN
  `region-REGION`.INFORMATION_SCHEMA.JOBS_BY_PROJECT AS parent_jobs
  ON child_jobs.parent_job_id = parent_jobs.job_id
WHERE
  child_jobs.creation_time
    BETWEEN TIMESTAMP_SUB(TIMESTAMP 'START_TIME', INTERVAL 6 HOUR)
    AND TIMESTAMP_ADD(TIMESTAMP 'END_TIME', INTERVAL 1 HOUR)
  AND child_jobs.destination_table.project_id = 'PROJECT_ID'
  AND child_jobs.destination_table.dataset_id = 'DATASET_NAME'
  AND child_jobs.destination_table.table_id = 'TABLE_NAME'
  AND child_jobs.statement_type IN (
    'UPDATE', 'DELETE', 'MERGE', 'TRUNCATE_TABLE'
  )
  AND COALESCE(parent_jobs.start_time, child_jobs.start_time)
    <= TIMESTAMP 'END_TIME'
  AND COALESCE(parent_jobs.end_time, child_jobs.end_time, CURRENT_TIMESTAMP())
    >= TIMESTAMP 'START_TIME'
ORDER BY
  lock_window_start;

Ersetzen Sie Folgendes:

• REGION: die Dataset-Region (z. B. us).
• START_TIME: der start_time-Zeitstempel des fehlgeschlagenen Jobs (z. B. 2026-06-15 10:00:00 UTC).
• END_TIME: der end_time-Zeitstempel des fehlgeschlagenen Jobs (z. B. 2026-06-15 10:05:00 UTC).
• PROJECT_ID: die ID des Projekts, das die Zieltabelle enthält.
• DATASET_NAME: der Name des Datasets, das die Zieltabelle enthält.
• TABLE_NAME: der Name der Zieltabelle.

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 fehlgeschlagene Transaktion mit mehreren Anweisungen löst einen Rollback-Vorgang aus, bei dem alle ausstehenden Änderungen rückgängig gemacht werden und keine Wiederholungsversuche möglich sind.

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

• Metadaten für externe Datenquellen können innerhalb einer Transaktion nicht mit einem Systemverfahren aktualisiert werden.