Tutorial: Coordenar transações entre tabelas

Neste tutorial, você usará os dois modos de transação para coordenar atualizações em várias instruções e tabelas em Azure Databricks: não interativas (BEGIN ATOMIC), que são confirmadas automaticamente e interativas (BEGIN TRANSACTION), o que fornece controle explícito. O tutorial também demonstra como usar transações com procedimentos armazenados e scripts SQL.

Requisitos

• Environment: acesso a um workspace do Azure Databricks.
• Computação: os tipos de computação com suporte variam de acordo com o modo de transação:
  • Um SQL Warehouse clássico ou sem servidor dá suporte a ambos os modos de transação.
  • A computação sem servidor dá suporte apenas a transações não interativas.
  • Clusters clássicos que executam o Databricks Runtime 18.0 ou superior dão suporte apenas a transações não interativas.
• Privilégios: CREATE TABLE em um Catálogo Unity schema.

Configurar tabelas de exemplo

Todas as tabelas gravadas em uma transação com várias instruções e várias tabelas devem:

• Tabelas gerenciadas no Catálogo Unity (Delta ou Iceberg)
• Ter confirmações de catálogo habilitadas

Crie duas tabelas de exemplo no Editor do SQL ou em um notebook:

-- Account data
CREATE TABLE IF NOT EXISTS sample_accounts (
  id INT,
  account_name STRING,
  balance DECIMAL(10,2)
) USING DELTA
TBLPROPERTIES (
  'delta.feature.catalogManaged' = 'supported'
);

-- Transaction records
CREATE TABLE IF NOT EXISTS sample_transactions (
  id INT,
  account_id INT,
  transaction_type STRING,
  amount DECIMAL(10,2)
) USING DELTA
TBLPROPERTIES (
  'delta.feature.catalogManaged' = 'supported'
);

ALTER TABLE <table_name> SET TBLPROPERTIES ('delta.feature.catalogManaged' = 'supported');

Insira dados de exemplo em ambas as tabelas:

INSERT INTO sample_accounts VALUES
  (1, 'Alice', 1000.00),
  (2, 'Bob', 500.00);

INSERT INTO sample_transactions VALUES
  (1, 1, 'deposit', 100.00);

Verifique a configuração:

SELECT * FROM sample_accounts;
SELECT * FROM sample_transactions;

Saída:

sample_accounts:
id	account_name	balance
1	  Alice	        1000.00
2	  Bob	          500.00

sample_transactions:
id	account_id	transaction_type	amount
1	           1	         deposit	100.00

Transações não interativas

Transações não interativas usam BEGIN ATOMIC ... END; sintaxe. Todas as instruções são executadas como uma única unidade atômica. Se cada instrução for bem-sucedida, o Azure Databricks fará a confirmação automaticamente. Se alguma instrução falhar, Azure Databricks reverterá todas as alterações automaticamente. Para obter padrões detalhados de sintaxe e uso, consulte transações não interativas.

Executar uma transação bem-sucedida

Atualize ambas as tabelas atomicamente:

BEGIN ATOMIC
  -- Update Alice's account balance
  UPDATE sample_accounts
  SET balance = balance + 100.00
  WHERE id = 1;

  -- Record the deposit transaction
  INSERT INTO sample_transactions
  VALUES (2, 1, 'deposit', 100.00);
END;

Verifique se o saldo de Alice agora é 1100.00:

SELECT * FROM sample_accounts WHERE id = 1;

Verifique se agora existem dois registros de transação:

SELECT * FROM sample_transactions;

A atualização de saldo e o registro de transação foram criados juntos. Se qualquer uma das instruções tivesse falhado, nenhuma mudança teria sido confirmada e Databricks teria encerrado a transação sem efeitos colaterais.

Usar SIGNAL para interromper uma transação sob uma condição

Você pode usar SIGNAL dentro de um BEGIN ATOMIC ... END; bloco para falhar na transação quando uma condição definida pelo usuário não for atendida. Este exemplo insere uma conta com um saldo negativo e, em seguida, usa SIGNAL para falhar na transação se a verificação de saldo falhar:

BEGIN ATOMIC
  INSERT INTO sample_accounts VALUES (3, 'Charlie', -50.00);

  IF (SELECT balance FROM sample_accounts WHERE id = 3) < 0 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Account balance cannot be negative';
  END IF;
END;

Isso SIGNAL gera um erro, o que faz com que toda a transação seja revertida automaticamente. Isso retorna zero linhas porque a inserção foi revertida:

SELECT * FROM sample_accounts WHERE id = 3;

Veja a reversão automática em caso de falha

Execute uma transação em que a primeira instrução é válida, mas a segunda faz referência a uma tabela que não existe:

BEGIN ATOMIC
  -- Valid
  INSERT INTO sample_accounts VALUES (4, 'David', 300.00);
  -- Invalid
  INSERT INTO non_existent_table VALUES (1, 2, 3);
END;

A transação falha com um erro. Isso retorna 0 linhas porque toda a transação foi revertida:

SELECT * FROM sample_accounts WHERE id = 4;

Mesmo que a primeira INSERT instrução fosse válida, ela foi revertida porque a segunda instrução falhou. Isso demonstra a garantia de tudo ou nada de transações.

Transações interativas

Transações interativas fornecem controle explícito sobre quando confirmar ou reverter. Use BEGIN TRANSACTION para iniciar e, em seguida, COMMIT para salvar alterações ou ROLLBACK para descartá-las.

Confirmar alterações

Inicie uma transação:

BEGIN TRANSACTION;

Fazer alterações (ainda não confirmadas):

INSERT INTO sample_accounts VALUES (5, 'Eve', 850.00);
UPDATE sample_accounts SET balance = balance + 50.00 WHERE id = 2;

Confirme-se para tornar as alterações permanentes:

COMMIT;

Verifique se a conta de Eve agora está visível:

SELECT * FROM sample_accounts WHERE id = 5;

Verifique se o saldo de Bob agora é 550.00:

SELECT * FROM sample_accounts WHERE id = 2;

Reverter alterações

Inicie uma nova transação:

BEGIN TRANSACTION;

Faça uma alteração:

INSERT INTO sample_accounts VALUES (6, 'Frank', 600.00);

Verifique se a alteração está visível em sua sessão (a linha não está visível para outras sessões até ser confirmada):

SELECT * FROM sample_accounts WHERE id = 6;

Reverta para descartar a alteração:

ROLLBACK;

Isso retorna zero linhas porque a inserção foi revertida:

SELECT * FROM sample_accounts WHERE id = 6;

Utilizar com procedimentos armazenados e scripts SQL

Você pode combinar transações com procedimentos armazenados para criar lógica de transação reutilizável. Esse padrão é útil para operações complexas executadas com frequência.

1. Criar as tabelas com confirmações de catálogo habilitadas

   CREATE SCHEMA IF NOT EXISTS main.retail;
   
   CREATE TABLE IF NOT EXISTS main.retail.orders (
     order_id STRING,
     customer_id STRING,
     amount DECIMAL(18,2)
   ) TBLPROPERTIES ('delta.feature.catalogManaged' = 'supported');
   
   CREATE TABLE IF NOT EXISTS main.retail.orders_staging (
     order_id STRING,
     customer_id STRING,
     amount DECIMAL(18,2),
     batch_id STRING
   ) TBLPROPERTIES ('delta.feature.catalogManaged' = 'supported');
   
   CREATE TABLE IF NOT EXISTS main.retail.total_sales (
     customer_id STRING,
     total_amount DECIMAL(18,2)
   ) TBLPROPERTIES ('delta.feature.catalogManaged' = 'supported');
   

2. Definir o procedimento armazenado

   CREATE OR REPLACE PROCEDURE main.retail.apply_order(
       IN  p_order_id      STRING,
       IN  p_customer_id   STRING,
       IN  p_order_amount  DECIMAL(18,2)
   )
   LANGUAGE SQL
   SQL SECURITY INVOKER
   MODIFIES SQL DATA
   AS
   BEGIN
       -- Insert the order
       INSERT INTO main.retail.orders (order_id, customer_id, amount)
       VALUES (p_order_id, p_customer_id, p_order_amount);
   
       -- Update total sales per customer
       MERGE INTO main.retail.total_sales AS t
       USING (
           SELECT
             p_customer_id  AS customer_id,
             p_order_amount AS order_amount
       ) s
         ON t.customer_id = s.customer_id
       WHEN MATCHED THEN
         UPDATE SET t.total_amount = t.total_amount + s.order_amount
       WHEN NOT MATCHED THEN
         INSERT (customer_id, total_amount)
         VALUES (s.customer_id, s.order_amount);
   END;
   
   

3. Definir a transação

   BEGIN ATOMIC
       -- Staging batch id for this transaction
       DECLARE new_order_id STRING DEFAULT uuid();
       DECLARE v_batch_id STRING DEFAULT uuid();
   
       -- 1) Stage incoming customer and order rows
       INSERT INTO main.retail.orders_staging (order_id, customer_id, amount, batch_id)
       VALUES (new_order_id, 'CUST_123', 249.99, v_batch_id);
   
       -- 2) Drive final writes from staging to production via stored procedure
       FOR o AS
         SELECT
           order_id,
           customer_id,
           amount
         FROM main.retail.orders_staging
         WHERE batch_id = v_batch_id
       DO
           CALL main.retail.apply_order(
             o.order_id,
             o.customer_id,
             o.amount
           );
       END FOR;
   
       -- 3) Clean up processed staging rows
       DELETE FROM main.retail.orders_staging
       WHERE batch_id = v_batch_id;
   END; -- 4) Commit the transaction
   
   

Se qualquer parte da transação falhar, o Databricks reverterá todas as alterações automaticamente.

Limpar

Remova as tabelas de exemplo:

DROP TABLE IF EXISTS sample_accounts;
DROP TABLE IF EXISTS sample_transactions;

DROP TABLE IF EXISTS main.retail.orders;
DROP TABLE IF EXISTS main.retail.orders_staging;
DROP TABLE IF EXISTS main.retail.total_sales;

Recursos adicionais

• Transações: visão geral do suporte à transação.
• Modos de transação: sintaxe detalhada e padrões para ambos os modos.
• Confirmações de catálogo: habilite o suporte à transação em suas tabelas.
• Use transações de diferentes clientes: execute transações de aplicativos JDBC, ODBC e Python.
• Instrução composta ATOMIC (transações não interativas)
• BEGIN TRANSACTION (transações interativas)
• COMMIT
• ROLLBACK