多语句事务

BigQuery 支持在单个查询中或者跨多个查询（使用会话时）使用多语句事务。通过多语句事务，您可以执行更改操作，例如对一个或多个表执行插入或删除行的操作，以及以原子方式提交或回滚更改。

多语句事务的用途包括：

对多个表执行 DML 变更（作为单个事务进行）。这些表可以跨多个数据集或项目。
根据中间计算在多个阶段对单个表执行变更。

事务可保证 ACID 属性并支持快照隔离。在执行事务期间，所有读取都返回在事务中引用的表的一致快照。如果事务中的语句修改表，则更改对同一事务中的后续语句可见。

事务范围

事务必须包含在单个 SQL 查询中，在 Session mode 中时除外。一个查询可以包含多个事务，但事务不能嵌套。您可以在一个会话中对多个查询运行多语句事务。

如需启动一项事务，请使用 BEGIN TRANSACTION 语句。当发生以下任一情况时，事务将会结束：

查询执行 COMMIT TRANSACTION 语句。此语句以原子方式提交在事务内进行的所有更改。
查询执行 ROLLBACK TRANSACTION 语句。此语句会放弃在事务内进行的所有更改。
查询在达到这两条语句中的任何一个之前结束。在这种情况下，BigQuery 会自动回滚事务。

如果在执行事务期间发生错误，并且查询具有异常处理程序，则 BigQuery 会将控制权转移给异常处理程序。在异常块内，您可以选择是提交还是回滚事务。

如果在执行事务期间发生错误且没有异常处理程序，则查询将失败并且 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 的表，如下所示：

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 中删除相应的记录。假设所有语句都成功完成，则两个表中的更改将作为单个事务以原子方式提交。

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 会为每个多语句事务分配事务 ID。事务 ID 会附加到在事务内执行的每个查询。如需查看作业的事务 ID，请查询 INFORMATION_SCHEMA.JOBS* 视图的 transaction_id 列。

多语句事务运行时，BigQuery 会为事务中的每条语句创建一项子作业。对于给定事务，与该事务关联的每项子作业都具有相同的 transaction_id 值。

以下示例展示了如何查找有关事务的信息。

查找所有已提交或已回滚的事务

以下查询会返回已成功提交的所有事务。

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;

查找多语句事务中运行的活跃事务

以下查询返回特定作业（由运行多语句事务的作业的 ID 指定）的活跃事务。

SELECT DISTINCT transaction_id
FROM
  `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE
  parent_job_id = "JOB_ID"
EXCEPT DISTINCT
SELECT transaction_id
FROM
  `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE
  parent_job_id = "JOB_ID"
  AND (statement_type = 'COMMIT_TRANSACTION'
       OR statement_type = 'ROLLBACK_TRANSACTION');

限制

事务不能使用会影响永久性实体的 DDL 语句。
在事务中，具体化视图会被解释为逻辑视图。您仍然在事务中可以查询具体化视图，但与等效的逻辑视图相比，该查询不会导致任何性能改进或费用降低。
失败的多语句事务会触发回滚操作，从而撤销所有待更改项并阻止重试。
一个事务最多可以更改 100 个表中的数据，最多可以执行 10 万个分区修改。
BI Engine 不会加快事务内的查询速度。