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

O BigQuery suporta transações com várias declarações numa única consulta ou em várias consultas quando usa sessões. Uma transação com várias declarações permite-lhe realizar operações de mutação, como inserir ou eliminar linhas numa ou mais tabelas, e confirmar ou reverter as alterações de forma atómica.

As utilizações das transações com várias declarações incluem:

• Executar mutações de DML em várias tabelas como uma única transação. As tabelas podem abranger vários conjuntos de dados ou projetos.
• Realizar mutações numa única tabela em várias fases, com base em cálculos intermédios.

As transações garantem as propriedades ACID e suportam o isolamento de instantâneos. Durante uma transação, todas as leituras devolvem uma imagem consistente das tabelas referenciadas na transação. Se uma declaração numa transação modificar uma tabela, as alterações são visíveis para declarações subsequentes na mesma transação.

Nota: não é garantido que as leituras de origens de dados externas sejam consistentes numa transação se a origem de dados subjacente for alterada durante a transação.

Âmbito da transação

Uma transação tem de estar contida numa única consulta SQL, exceto quando estiver em Session mode. Uma consulta pode conter várias transações, mas não podem estar aninhadas. Pode executar transações de várias declarações em várias consultas numa sessão.

Para iniciar uma transação, use a declaração BEGIN TRANSACTION. A transação termina quando ocorre uma das seguintes situações:

• A consulta executa uma declaração COMMIT TRANSACTION. Esta declaração compromete atomicamente todas as alterações feitas na transação.
• A consulta executa uma declaração ROLLBACK TRANSACTION. Esta declaração abandona todas as alterações feitas na transação.
• A consulta termina antes de atingir qualquer uma destas duas declarações. Nesse caso, o BigQuery reverte automaticamente a transação.

Se ocorrer um erro durante uma transação e a consulta tiver um gestor de exceções, o BigQuery transfere o controlo para o gestor de exceções. No bloco de exceções, pode escolher se quer confirmar ou reverter a transação.

Se ocorrer um erro durante uma transação e não existir um controlador de exceções, a consulta falha e o BigQuery reverte automaticamente a transação.

O exemplo seguinte mostra um controlador 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;

Extratos suportados em transações

Os seguintes tipos de extratos são suportados em transações:

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

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

  • CREATE TEMP TABLE
  • CREATE TEMP FUNCTION
  • DROP TABLE numa mesa temporária
  • DROP FUNCTION numa função temporária

As declarações DDL que criam ou eliminam entidades permanentes, como conjuntos de dados, tabelas e funções, não são suportadas em transações.

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

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

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

• Não pode usar a cláusula FOR SYSTEM_TIME AS OF para ler uma tabela para além da data/hora em que a transação foi iniciada. Se o fizer, é devolvido um erro.

Exemplo de uma transação

Este exemplo pressupõe que existem duas tabelas denominadas Inventory e NewArrivals, criadas da seguinte forma:

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 a tabela NewArrivals contém informações sobre os artigos recém-chegados.

As seguintes atualizações de transações Inventory com novas chegadas e eliminam os registos correspondentes de NewArrivals. Partindo do princípio de que todas as declarações são concluídas com êxito, as alterações em ambas as tabelas sã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;

Concorrência de transações

Se uma transação alterar (atualizar ou eliminar) linhas numa tabela, outras transações ou declarações DML que alterem linhas na mesma tabela não podem ser executadas em simultâneo. As transações em conflito são canceladas. As declarações DML em conflito que são executadas fora de uma transação são colocadas em fila para execução posterior, sujeitas a limites de filas.

As operações que leem ou acrescentam novas linhas podem ser executadas em simultâneo com a transação. Por exemplo, é possível realizar qualquer uma das seguintes operações em simultâneo numa tabela enquanto uma transação altera os dados na mesma tabela:

• SELECT extratos
• Operações de leitura da API BigQuery Storage Read
• Consultas do BigQuery BI Engine
• INSERT extratos
• Carregue trabalhos que usam a disposição WRITE_APPEND para anexar linhas
• Escritas de streaming

Se uma transação apenas lê uma tabela ou acrescenta novas linhas à mesma, qualquer operação pode ser realizada em simultâneo nessa tabela.

Ver informações de transações

O BigQuery atribui um ID da transação a cada transação com várias declarações. O ID da transação está anexado a cada consulta executada na transação. Para ver os IDs das transações dos seus trabalhos, consulte as vistas INFORMATION_SCHEMA.JOBS* para a coluna transaction_id.

Quando uma transação com várias declarações é executada, o BigQuery cria uma tarefa secundária para cada declaração na transação. Para uma determinada transação, todas as tarefas secundárias associadas a essa transação têm o mesmo transaction_idvalor.

Os exemplos seguintes mostram como encontrar informações sobre as suas transações.

Encontre todas as transações confirmadas ou revertidas

A seguinte consulta devolve todas as transações que foram confirmadas com êxito.

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 seguinte consulta devolve todas as transações que foram revertidas com êxito.

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;

Encontre a hora de início e de fim de uma transação

A seguinte consulta devolve as horas de início e fim 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;

Encontre a transação na qual uma tarefa está a ser executada

A seguinte consulta obtém a transação associada a um ID da tarefa especificado. Devolve NULL se a tarefa não estiver a ser executada numa transação com várias declarações.

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

Encontre a tarefa atual em execução numa transação

A seguinte consulta devolve informações sobre a tarefa que está atualmente em execução numa transação especificada, se existir.

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;

Encontre as transações ativas que afetam uma tabela

A consulta seguinte devolve as transações ativas que afetam uma tabela especificada. Para cada transação ativa, se a transação estiver a ser executada como parte de consultas com várias declarações, como num procedimento armazenado, também devolve o ID da tarefa principal. Se a transação estiver a ser executada numa sessão, também devolve 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;

Encontre as transações ativas em execução numa transação com várias declarações

A seguinte consulta devolve as transações ativas para uma tarefa específica, especificada pelo ID da tarefa que está a executar a transação de várias declaraçõ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 declarações DDL que afetem entidades permanentes.
• Numa transação, as vistas materializadas são interpretadas como vistas lógicas. Ainda pode consultar uma vista materializada numa transação, mas não resulta em nenhuma melhoria de desempenho nem redução de custos em comparação com a vista lógica equivalente.

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

• Uma transação pode alterar dados em, no máximo, 100 tabelas e pode fazer, no máximo, 100 000 modificações de partições.

• O BI Engine não acelera as consultas numa transação.

• Não é possível atualizar os metadados de origens de dados externas numa transação com um procedimento do sistema.