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.

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.

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 alterar (atualizar ou excluir) linhas em uma tabela, outras transações ou instruções DML que modificam linhas na mesma tabela não poderão ser executadas simultaneamente. As transações conflitantes são canceladas. As instruções DML conflitantes executadas fora de uma transação são enfileiradas para execução posterior e estão sujeitas a limites de enfileiramento.

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
Carregamento de jobs que usam a disposição WRITE_APPEND para anexar linhas
Gravações de streaming

Se uma transação só lê uma tabela ou anexa novas linhas a ela, qualquer operação poderá ser executada simultaneamente nessa tabela.

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 de job 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 ela 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 do job 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');

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.