Transações com várias instruções

O BigQuery é compatível com transações de várias instruções em uma única consulta ou em várias consultas ao usar as sessões. Essa transação permite executar operações de mutação, como inserir ou excluir linhas, em uma ou mais tabelas, e confirmar ou reverter as mudanças atomicamente.

Os usos para transações com várias instruções incluem:

• Realizar mutações DML em várias tabelas como uma única transação. As tabelas podem abranger vários conjuntos de dados ou projetos
• Realizar mutações em uma única tabela em vários estágios, com base em cálculos intermediários.

As transações garantem propriedades ACID e aceitam o isolamento de snapshot. Durante uma transação, todas as leituras retornam um snapshot consistente das tabelas referenciadas na transação. Se uma instrução em uma transação modificar uma tabela, as alterações ficarão visíveis para as próximas instruções da mesma transação.

Escopo da transação

Uma transação precisa estar contida em uma única consulta SQL, exceto quando estiver em Session mode. Uma consulta pode conter várias transações, mas não pode ser aninhada. É possível executar transações de várias instruções em várias consultas em uma sessão.

Para iniciar uma transação, use a instrução BEGIN TRANSACTION. A transação termina quando uma destas coisas acontece:

• A consulta executa uma instrução COMMIT TRANSACTION. Essa instrução confirma atomicamente todas as alterações feitas dentro da transação.
• A consulta executa uma instrução ROLLBACK TRANSACTION. Essa instrução abandona todas as alterações feitas dentro da transação.
• A consulta termina antes de chegar a qualquer uma dessas duas instruções. Nesse caso, o BigQuery reverte a transação automaticamente.

Se ocorrer um erro durante uma transação e a consulta tiver um gerenciador de exceções, o BigQuery transferirá o controle para o gerenciador. Dentro do bloco de exceção, é possível confirmar ou reverter a transação.

Quando o modo de sessão está desativado, se ocorrer um erro durante uma transação e não houver um gerenciador de exceções, a consulta falhará e o BigQuery reverterá a transação automaticamente.

Quando o modo de sessão está ativado e não há um gerenciador de exceções, a transação permanece ativa até que a sessão seja encerrada ou explicitamente revertida. Independentemente do modo de sessão, é uma boa prática adicionar gerenciadores de exceções para garantir que as transações sejam revertidas corretamente o mais rápido possível para evitar que os bloqueios e recursos sejam mantidos por mais tempo do que o necessário.

O exemplo a seguir mostra um gerenciador de exceções que reverte uma transação:

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;

Instruções compatíveis com transações

Os seguintes tipos de instruções são compatíveis com as transações:

• Instruções de consulta: SELECT
• Instruções DML: INSERT, UPDATE, DELETE, MERGE e TRUNCATE TABLE

• Instruções DDL em entidades temporárias:

  • CREATE TEMP TABLE
  • CREATE TEMP FUNCTION
  • DROP TABLE em uma tabela temporária
  • DROP FUNCTION em uma função temporária

Instruções DDL que criam ou descartam entidades permanentes, como conjuntos de dados, tabelas e funções, não são aceitas dentro de transações.

Funções de data/hora em transações

Em uma transação, as seguintes funções de data/hora têm comportamentos especiais:

• As funções CURRENT_TIMESTAMP, CURRENT_DATE e CURRENT_TIME retornarão o carimbo de data/hora em que a transação foi iniciada.

• Não é possível usar a cláusula FOR SYSTEM_TIME AS OF para ler uma tabela além do carimbo de data/hora de quando a transação foi iniciada. Fazer isso retornará um erro.

Exemplo de uma transação

Neste exemplo, pressupomos que há duas tabelas chamadas Inventory e NewArrivals, criadas da seguinte maneira:

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

A tabela Inventory contém informações sobre o inventário atual, e NewArrivals contém informações sobre itens recém-chegados.

A transação a seguir atualiza Inventory com novas chegadas e exclui os registros correspondentes de NewArrivals. Depois que todas as instruções forem concluídas, as alterações em ambas as tabelas serão confirmadas atomicamente como uma única transação.

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;

Simultaneidade da transação

Se uma transação atualizar ou excluir linhas em uma tabela, ela vai adquirir um bloqueio no nível da tabela em vez de bloquear partições individuais. Outras transações ou instruções DML que modificam linhas na mesma tabela não podem ser executadas simultaneamente, mesmo que segmentem partições diferentes de uma tabela particionada. O BigQuery cancela transações conflitantes. No entanto, o BigQuery coloca em fila as instruções DML conflitantes que são executadas fora de uma transação para serem executadas posteriormente, sujeitas aos limites de fila.

Quando você executa uma instrução DML de mutação em uma transação de várias instruções, o BigQuery mantém o bloqueio de gravação na tabela de destino até que a transação seja confirmada ou revertida ou até que a consulta ou sessão de várias instruções pai seja encerrada. Esse comportamento se aplica a transações em uma consulta de várias instruções, um procedimento armazenado ou uma sessão ativa, como uma conexão ODBC ou JDBC. O BigQuery não libera o bloqueio quando a instrução DML individual é concluída. As computações de longa duração na mesma transação aberta mantêm a tabela bloqueada e fazem com que as transações de mutação simultâneas nessa tabela falhem.

As operações que leem ou anexam novas linhas podem ser executadas ao mesmo tempo que a transação. Por exemplo, qualquer uma das operações a seguir pode ser executada simultaneamente em uma tabela enquanto uma transação altera os dados na mesma tabela:

• instruções SELECT
• Operações de leitura da API Storage Read do BigQuery
• Consultas do BigQuery BI Engine
• instruções INSERT
• Jobs de carregamento que usam a disposição WRITE_APPEND para anexar linhas
• Gravações de streaming

Se uma transação apenas ler uma tabela ou anexar novas linhas a ela, qualquer operação poderá ser realizada simultaneamente nessa tabela, porque as instruções INSERT não entram em conflito umas com as outras. Para evitar que várias transações que, de outra forma, apenas inserissem linhas sejam executadas simultaneamente em uma tabela, inicie cada transação com uma instrução DELETE que não modifique nenhuma linha, como DELETE FROM TABLE_NAME WHERE FALSE;. A inclusão dessa instrução faz com que o BigQuery adquira um bloqueio no nível da tabela na instrução DELETE. Assim, qualquer outra transação simultânea que também execute uma instrução de mutação nessa tabela entra em conflito e é cancelada.

Evitar e resolver conflitos de simultaneidade

Para reduzir os conflitos de simultaneidade de transações, use as seguintes práticas:

• Remova wrappers de transação desnecessários: se um procedimento armazenado ou uma consulta de várias instruções executar apenas uma única instrução DML de mutação, como uma instrução MERGE, não encapsule a instrução em instruções BEGIN TRANSACTION e COMMIT TRANSACTION. Uma única instrução DML já é atômica. A execução da instrução fora de uma transação de várias instruções permite que o BigQuery aplique a fila de DML.
• Tentar novamente transações com falha com espera exponencial: quando uma transação de várias instruções falha devido a uma atualização simultânea, o BigQuery reverte a transação e libera o bloqueio da tabela. Como o BigQuery pode levar vários segundos para liberar o bloqueio na camada de metadados, tente novamente as transações com falha usando uma estratégia de espera exponencial. Aguarde pelo menos 60 segundos antes da primeira nova tentativa.

Como visualizar informações da transação

O BigQuery atribui um ID de transação a cada transação com várias instruções. O ID da transação é anexado a cada consulta executada dentro da transação. Para conferir os IDs de transação dos jobs, consulte as visualizações INFORMATION_SCHEMA.JOBS* da coluna transaction_id.

Quando uma transação de várias instruções é executada, o BigQuery cria um job filho para cada instrução. Em uma determinada transação, todos os job filhos associados a ela têm o mesmo valor transaction_id.

Os exemplos a seguir mostram como encontrar informações sobre suas transações.

Encontrar todas as transações confirmadas ou revertidas

A consulta a seguir retorna todas as transações que foram confirmadas.

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;

A consulta a seguir retorna todas as transações que foram revertidas.

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;

Encontrar o horário de início e de término de uma transação

A consulta a seguir retorna os horários de início e término de um ID de transação especificado.

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;

Encontrar a transação em que um job está sendo executado

A consulta a seguir recebe a transação associada a um ID da tarefa especificado. Ela retornará NULL se o job não estiver em execução em uma transação de várias instruções.

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

Encontrar o job em execução em uma transação

A consulta a seguir retorna informações sobre o job que está sendo executado em uma transação especificada, se houver.

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;

Encontrar as transações ativas que afetam uma tabela

A consulta a seguir retorna as transações ativas que afetam uma tabela especificada. Para cada transação ativa, se a transação estiver em execução como parte de consultas com várias instruções, por exemplo, em um procedimento armazenado, ela também retornará o ID da tarefa pai. Se a transação estiver em execução em uma sessão, ela também retornará as informações da sessão.

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;

Encontrar as transações ativas em execução em uma transação de várias instruções

A consulta a seguir retorna as transações ativas de um job específico, especificadas pelo ID do job que está executando a transação de várias instruções.

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

Encontrar jobs sobrepostos para uma transação com falha

Quando uma transação ou instrução DML falha com um erro de atualização simultânea, uma verificação do histórico de consultas de instruções DML individuais pode não mostrar uma instrução DML ativa no momento da falha. Essa situação ocorre quando uma consulta ou sessão pai de várias instruções executa uma instrução DML anterior dentro de uma transação e continua executando outras instruções enquanto mantém o bloqueio da tabela.

Para cargas de trabalho entre projetos, consulte a visualização INFORMATION_SCHEMA.JOBS_BY_ORGANIZATION em vez de INFORMATION_SCHEMA.JOBS_BY_PROJECT. A consulta a seguir mescla jobs de DML filhos com os jobs pai em INFORMATION_SCHEMA.JOBS_BY_PROJECT para identificar jobs, scripts ou sessões de mutação que se sobrepuseram a um job com falha:

SELECT
  child_jobs.job_id,
  child_jobs.parent_job_id,
  child_jobs.transaction_id,
  child_jobs.session_info.session_id,
  child_jobs.statement_type,
  child_jobs.start_time AS statement_start_time,
  child_jobs.end_time AS statement_end_time,
  parent_jobs.start_time AS parent_start_time,
  parent_jobs.end_time AS parent_end_time,
  COALESCE(parent_jobs.start_time, child_jobs.start_time) AS lock_window_start,
  COALESCE(
    parent_jobs.end_time, child_jobs.end_time, CURRENT_TIMESTAMP()
  ) AS lock_window_end,
  child_jobs.error_result.message AS error_message,
  child_jobs.query
FROM
  `region-REGION`.INFORMATION_SCHEMA.JOBS_BY_PROJECT AS child_jobs
LEFT JOIN
  `region-REGION`.INFORMATION_SCHEMA.JOBS_BY_PROJECT AS parent_jobs
  ON child_jobs.parent_job_id = parent_jobs.job_id
WHERE
  child_jobs.creation_time
    BETWEEN TIMESTAMP_SUB(TIMESTAMP 'START_TIME', INTERVAL 6 HOUR)
    AND TIMESTAMP_ADD(TIMESTAMP 'END_TIME', INTERVAL 1 HOUR)
  AND child_jobs.destination_table.project_id = 'PROJECT_ID'
  AND child_jobs.destination_table.dataset_id = 'DATASET_NAME'
  AND child_jobs.destination_table.table_id = 'TABLE_NAME'
  AND child_jobs.statement_type IN (
    'UPDATE', 'DELETE', 'MERGE', 'TRUNCATE_TABLE'
  )
  AND COALESCE(parent_jobs.start_time, child_jobs.start_time)
    <= TIMESTAMP 'END_TIME'
  AND COALESCE(parent_jobs.end_time, child_jobs.end_time, CURRENT_TIMESTAMP())
    >= TIMESTAMP 'START_TIME'
ORDER BY
  lock_window_start;

Substitua:

• REGION: a região do conjunto de dados (por exemplo, us).
• START_TIME: o carimbo de data/hora start_time do job com falha (por exemplo, 2026-06-15 10:00:00 UTC).
• END_TIME: o carimbo de data/hora end_time do job com falha (por exemplo, 2026-06-15 10:05:00 UTC).
• PROJECT_ID: o ID do projeto que contém a tabela de destino.
• DATASET_NAME: o nome do conjunto de dados que contém a tabela de destino.
• TABLE_NAME: o nome da tabela de destino.

Limitações

• As transações não podem usar instruções DDL que afetam entidades permanentes.
• Dentro de uma transação, as visualizações materializadas são interpretadas como visualizações lógicas. Também é possível consultar uma visualização materializada dentro de uma transação, mas isso não resulta em nenhuma melhoria de desempenho ou redução de custos em comparação com a visualização lógica equivalente.

• Uma transação de várias instruções que falha aciona uma operação de reversão, desfazendo todas as alterações pendentes e impedindo novas tentativas.

• Uma transação pode modificar os dados em, no máximo, 100 tabelas e executar, no máximo, 100.000 modificações de partição.

• O BI Engine não acelera consultas dentro de uma transação.

• Não é possível atualizar os metadados de fontes de dados externas em uma transação usando um procedimento do sistema.