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.
- A transaction groups several
INSERT,UPDATE,DELETEandMERGEstatements, on one table or on several, so that they succeed or fail together. It runs inside a script, betweenBEGIN TRANSACTION;andCOMMIT TRANSACTION;. In a session, an open transaction stays open from one query to the next until it is committed or rolled back. - Inside the transaction, your own queries see the changes made so far. Until the commit, other queries do not see them.
- To keep a change only when it is sound, read the result after the statements and end the transaction in an
IF:ROLLBACK TRANSACTIONwhen the check fails andCOMMIT TRANSACTIONwhen it passes. After a rollback the tables are as they were atBEGIN TRANSACTION. - An error that nothing catches stops the script, and BigQuery rolls back the open transaction. A
BEGIN ... EXCEPTION WHEN ERROR THEN ... ENDblock catches an error, runs its handler and lets the script go on after the block, with the transaction still open. - A transaction cannot hold another
BEGIN TRANSACTION: BigQuery saysCannot begin a nested transaction in an active transaction. A loop pass that forgets itsCOMMIT TRANSACTIONorROLLBACK TRANSACTIONmakes the next pass fail this way. A transaction cannot holdCREATE TABLE,CREATE VIEW,DROP TABLEor any other DDL on a stored object either, and saysDDL statements are not supported in a transaction.CREATE TEMP TABLEandCREATE TEMP FUNCTIONare allowed. - A transaction that is still open when the script ends is rolled back. A
ROLLBACK TRANSACTIONor aCOMMIT TRANSACTIONwith no open transaction is an error.
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.