All or nothing: transactions

Some changes only make sense together. Moving stock from one product to another takes units from one row and adds them to another, and the units are gone if the script stops between the two statements.

How it works

A transaction groups the statements of a script. BEGIN TRANSACTION opens it. COMMIT TRANSACTION keeps every change made since, and ROLLBACK TRANSACTION undoes all of them.

Common mistake

A transaction does not look at the data. A script that never tests its result commits wrong changes as readily as right ones.

Worked example

CREATE TEMP TABLE gift_cards AS
SELECT 'A' AS card, 50 AS balance UNION ALL SELECT 'B', 10;
BEGIN TRANSACTION;
UPDATE gift_cards SET balance = balance - 30 WHERE card = 'A';
UPDATE gift_cards SET balance = balance + 30 WHERE card = 'B';
COMMIT TRANSACTION;
SELECT card, balance FROM gift_cards ORDER BY card;

The two updates move 30 from card A to card B. Between BEGIN TRANSACTION and COMMIT TRANSACTION they are one change.

The last query runs after the commit and shows card A with 20 and card B with 40.

All BigQuery lessons