マルチステートメントトランザクション

BigQuery は、セッションの使用時に単一クエリ内部で、または複数のクエリにまたがってマルチステートメントトランザクションをサポートします。マルチステートメントトランザクションを使用すると、1 つ以上のテーブルに対して行の挿入や削除などの変更操作を実行し、その結果をアトミックに commit またはロールバックできます。

マルチステートメントトランザクションには、次のようなものがあります。

複数のテーブルに対して DML ミューテーションを単一トランザクションとして実行する。テーブルは、複数のデータセットまたはプロジェクトにまたがる場合があります。
計算の中間結果に応じて、1 つのテーブルに対するミューテーションを複数の段階で実行する。

トランザクションは、ACID 特性を保証し、スナップショット分離をサポートします。トランザクションの実行中、すべての読み取りはトランザクション内で参照されるテーブルについて整合性のあるスナップショットを返します。トランザクション内のステートメントでテーブルが変更されると、同じトランザクション内の後続のステートメントにその変更内容が反映されます。

トランザクションのスコープ

Session mode の場合を除き、各トランザクションは単一の SQL クエリに含まれる必要があります。1 つのクエリに複数のトランザクションを含めることができますが、ネストさせることはできません。セッション内の複数のクエリにわたって、マルチステートメントトランザクションを実行できます。

トランザクションを開始するには、BEGIN TRANSACTION ステートメントを使用します。次のいずれかが発生するとトランザクションが終了します。

クエリが COMMIT TRANSACTION ステートメントを実行した。このステートメントは、トランザクション内で行われたすべての変更をアトミックに commit します。
クエリが ROLLBACK TRANSACTION ステートメントを実行した。このステートメントは、トランザクション内で行われたすべての変更を破棄します。
上のいずれかのステートメントに到達する前にクエリが終了した。この場合、BigQuery は自動的にトランザクションをロールバックします。

トランザクションでエラーが発生した場合、クエリに例外ハンドラがあれば、BigQuery は例外ハンドラに制御を渡します。例外ブロック内でトランザクションを commit またはロールバックできます。

トランザクションでエラーが発生したときに例外ハンドラがない場合は、クエリが失敗し、BigQuery はトランザクションを自動的にロールバックします。

次の例は、トランザクションをロールバックする例外ハンドラを示しています。

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;

トランザクションでサポートされるステートメント

トランザクションでは、次の種類のステートメントがサポートされています。

クエリステートメント: SELECT
DML ステートメント: INSERT、UPDATE、DELETE、MERGE、TRUNCATE TABLE
一時エンティティに対する DDL ステートメント:
- CREATE TEMP TABLE
- CREATE TEMP FUNCTION
- 一時テーブルに対する DROP TABLE
- 一時関数に対する DROP FUNCTION

データセット、テーブル、関数などの永続的なエンティティを作成または削除する DDL ステートメントは、トランザクション内でサポートされません。

トランザクション内の日時関数

トランザクション内では、次の日時関数は特別な動作を行います。

CURRENT_TIMESTAMP 関数、CURRENT_DATE 関数、CURRENT_TIME 関数は、トランザクション開始時のタイムスタンプを返します。
トランザクションの開始時に、FOR SYSTEM_TIME AS OF 句を使用してタイムスタンプを超えるテーブルを読み取ることはできません。このような操作を行うと、エラーが返されます。

トランザクションの例

この例では、次のような Inventory と NewArrivals という 2 つのテーブルが存在することを前提としています。

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');

Inventory テーブルには現在のインベントリに関する情報が格納され、NewArrivals には新たに追加されたアイテムの情報が格納されています。

次のトランザクションでは、新たに追加されたアイテムで Inventory を更新し、それに対応するレコードを NewArrivals から削除します。すべてのステートメントが正常に処理すると、両方のテーブルの変更が 1 つのトランザクションとしてアトミックに commit されます。

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;

トランザクションの同時実行

あるトランザクションでテーブル内の行が変更（更新または削除）された場合、同じテーブル内の行を変更する他のトランザクションまたは DML ステートメントを同時に実行することはできません。競合するトランザクションはキャンセルされます。トランザクションの外部で実行される DML ステートメントの競合は、後で実行するために、キューの上限に従ってキューに登録されます。

新しい行の読み取りまたは追加は、トランザクションと同時に実行できます。たとえば、トランザクションでテーブル内のデータが変更されるときに、同じテーブルに対して次の処理を同時に実行できます。

SELECT ステートメント
BigQuery Storage Read API の読み取りオペレーション
BigQuery BI Engine のクエリ
INSERT ステートメント
WRITE_APPEND 処理で行を追加する読み込みジョブ
ストリーミング書き込み

トランザクションがテーブルの読み込みまたは新しい行の追加のみを行う場合は、そのテーブルに対して任意のオペレーションを同時に実行できます。

トランザクション情報の表示

BigQuery は、マルチステートメントトランザクションに対して 1 つのトランザクション ID を割り当てます。トランザクション ID は、トランザクション内で実行される各クエリに関連付けられます。ジョブのトランザクション ID を表示するには、INFORMATION_SCHEMA.JOBS* ビューに対してクエリを実行し、transaction_id 列を取得します。

マルチステートメントトランザクションが実行されると、BigQuery はトランザクション内のステートメントごとに子ジョブを作成します。トランザクションごとに、そのトランザクションに関連するすべての子ジョブに同じ transaction_id 値が設定されます。

次の例は、トランザクションに関する情報を見つける方法を示しています。

commit 済みまたはロールバック対象のすべてのトランザクションを検索する

次のクエリは、正常に commit されたすべてのトランザクションを返します。

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;

次のクエリは、正常にロールバックされたすべてのトランザクションを返します。

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;

トランザクションの開始時刻と終了時刻を検索する

次のクエリは、指定されたトランザクション ID の開始時刻と終了時刻を返します。

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;

ジョブが実行されているトランザクションを検索する

次のクエリは、指定されたジョブ ID に関連付けられたトランザクションを取得します。ジョブがマルチステートメントトランザクションで実行されていない場合は、NULL が返されます。

SELECT transaction_id
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE job_id = 'JOB_ID';

トランザクション内で現在実行中のジョブを検索する

次のクエリは、指定されたトランザクション内で実行中のジョブに関する情報を返します（ジョブが存在する場合）。

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;

テーブルに影響するアクティブなトランザクションを検索する

次のクエリは、指定されたテーブルに影響するアクティブなトランザクションを返します。アクティブなトランザクションごとに、トランザクションがマルチステートメントクエリの一部として（ストアドプロシージャ内などで）実行されている場合は、親ジョブ ID も返されます。トランザクションがセッション内で実行されている場合は、セッション情報も返されます。

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;