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

Source: https://docs.querylab.io/bigquery/transactions/

Updated: 2026-10-06

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

**move 30 from ana to ben**

```sql
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
```

Result in BigQuery (2 rows):

| id | balance |
| --- | --- |
| ana | 70 |
| ben | 50 |

A transaction can change up to 100 tables, with at most 100,000 partition changes, and runs inside a script or a [session](https://docs.querylab.io/bigquery/sessions/).

## 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:

**the second update fails, so nothing changes**

```sql
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
```

Result in BigQuery (2 rows):

| id | balance |
| --- | --- |
| ana | 100 |
| ben | 20 |

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

**the same updates without a transaction**

```sql
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
```

Result in BigQuery (2 rows):

| id | balance |
| --- | --- |
| ana | 70 |
| ben | 20 |

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 twice**

```sql
BEGIN TRANSACTION;
BEGIN TRANSACTION;
SELECT 1 AS x
```

BigQuery rejects this query:

```text
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**

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

Result in BigQuery (1 row):

| n |
| --- |
| 1 |

- **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](https://docs.querylab.io/bigquery/dml/#concurrency-and-limits).

## 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](https://docs.querylab.io/bigquery/merge/#replacing-a-partition-atomically)). Use a transaction when the change spans several statements or several tables that must stay consistent with each other.

Open the playground at https://app.querylab.io?playground=true&utm_source=docs&utm_medium=concepts to run it. It is the real app on public BigQuery datasets and needs no sign-in.

## Sources

Based on the BigQuery documentation by Google, CC BY 4.0, rewritten with examples checked against BigQuery:

- [Multi-statement transactions](https://cloud.google.com/bigquery/docs/transactions)

- [Quotas and limits, multi-statement transactions](https://cloud.google.com/bigquery/quotas#multi_statement_query_limits)