多语句事务

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 语句。

事务中的日期/时间函数

在事务中，以下日期/时间函数具有特殊行为：

事务示例

此示例假设有两个名为 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 不会加快事务内的查询速度。