Transactions in BigQuery

BEGIN TRANSACTION ... COMMIT makes several DML statements all-or-nothing, across up to 100 tables. A failed statement doesn't roll back by itself inside an EXCEPTION block, transactions can't be nested or contain permanent DDL, and a conflicting transaction is cancelled, not queued.

Every BigQuery DML statement is atomic on its own. To make several statements succeed or fail together, for example moving money between two accounts or replacing data in two tables, put them in a transaction inside a script: BEGIN TRANSACTION, the statements, then COMMIT TRANSACTION. Until the commit, other queries don’t see the changes.

A transaction

CREATE TEMP TABLE accounts (id STRING, balance INT64);
INSERT INTO accounts VALUES ('ana', 100), ('ben', 20);
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 30 WHERE id = 'ana';
UPDATE accounts SET balance = balance + 30 WHERE id = 'ben';
COMMIT TRANSACTION;
SELECT * FROM accounts ORDER BY id

A transaction can change up to 100 tables, with at most 100,000 partition changes, and runs inside a script or a session.

Rolling back

If a statement inside a transaction fails and nothing catches the error, the script stops and BigQuery rolls the transaction back. If you catch the error with an EXCEPTION block, you have to roll back yourself:

CREATE TEMP TABLE accounts (id STRING, balance INT64);
INSERT INTO accounts VALUES ('ana', 100), ('ben', 20);
BEGIN
  BEGIN TRANSACTION;
  UPDATE accounts SET balance = balance - 30 WHERE id = 'ana';
  UPDATE accounts SET balance = balance + CAST('thirty' AS INT64) WHERE id = 'ben';
  COMMIT TRANSACTION;
EXCEPTION WHEN ERROR THEN
  ROLLBACK TRANSACTION;
END;
SELECT * FROM accounts ORDER BY id

Without a transaction, the first UPDATE stays done when the second one fails, and the accounts no longer add up:

CREATE TEMP TABLE accounts (id STRING, balance INT64);
INSERT INTO accounts VALUES ('ana', 100), ('ben', 20);
BEGIN
  UPDATE accounts SET balance = balance - 30 WHERE id = 'ana';
  UPDATE accounts SET balance = balance + CAST('thirty' AS INT64) WHERE id = 'ben';
EXCEPTION WHEN ERROR THEN
  SELECT 'error caught';
END;
SELECT * FROM accounts ORDER BY id

That is the difference a transaction makes: ana lost 30 and ben never got it.

What a transaction sees

A transaction reads the tables as they were when it started, plus its own changes. Other queries see the tables as they were before the transaction until it commits. CURRENT_TIMESTAMP(), CURRENT_DATE() and CURRENT_TIME() inside a transaction all return the transaction’s start time, so every statement in it agrees on “now”.

What can’t go in a transaction

• Another transaction. Transactions don’t nest:

  BEGIN TRANSACTION twiceBigQuery rejects this query

  123

  BEGIN TRANSACTION;
  BEGIN TRANSACTION;
  SELECT 1 AS x

  Error in BigQuery

  Cannot begin a nested transaction in an active transaction at [2:1]

• DDL on permanent objects. You can create and drop temporary tables and functions, but not permanent tables, views, functions or datasets.

  a temp table inside a transaction is fine

  12345

  BEGIN TRANSACTION;
  CREATE TEMP TABLE staging (id INT64);
  INSERT INTO staging VALUES (1);
  COMMIT TRANSACTION;
  SELECT COUNT(*) AS n FROM staging

  Result in BigQuery

  	n
  1	1

  1 row

• Time travel past the start. FOR SYSTEM_TIME AS OF can’t read later than the transaction’s start time.

• Materialized view benefits. Inside a transaction a materialized view is read as a plain view, with no speed-up and no lower cost.

Concurrency

While a transaction is changing rows of a table:

• SELECT, INSERT and reads through the Storage Read API on that table run normally.
• Another transaction that changes rows of the same table conflicts with it, and one of them is cancelled. It isn’t queued.
• An UPDATE, DELETE or MERGE outside a transaction waits in the queue until the transaction ends.

So keep transactions short. A long-running transaction on a busy table cancels other transactions and makes regular DML wait. See the concurrency limits in INSERT, UPDATE and DELETE.

When to use one

Most BigQuery pipelines don’t need transactions: a single MERGE or CREATE OR REPLACE TABLE ... AS SELECT is already atomic, and replacing a partition with one MERGE ... ON FALSE is too (see MERGE). Use a transaction when the change spans several statements or several tables that must stay consistent with each other.