Tutoriel : Coordonner les transactions entre les tables

info

Aperçu

Les transactions qui écrivent dans les tables Iceberg gérées par Unity Catalog sont en préversion privée. Pour rejoindre cette préversion, soumettez le formulaire d'inscription à la préversion des tables Iceberg gérées.

Dans ce tutoriel, vous utilisez les deux modes de transaction pour coordonner les mises à jour sur plusieurs instructions et tables sur Databricks : non interactif (BEGIN ATOMIC), qui commit automatiquement, et interactif (BEGIN TRANSACTION), qui vous donne un contrôle explicite. Ce tutoriel montre également comment utiliser les transactions avec les procédures stockées et le script SQL.

Exigences

• Environnement : Accès à un workspace Databricks.

• Compute : les types de compute pris en charge varient selon le mode de transaction :

  • Un SQL Warehouse classique ou serverless prend en charge les deux modes de transaction.
  • Le compute serverless ne prend en charge que les transactions non interactives.
  • Les clusters classiques exécutant Databricks Runtime 18,0 ou supérieur ne prennent en charge que les transactions non interactives.

• Privilèges : CREATE TABLE dans un schéma Unity Catalog.

Configurer les tables d'échantillons

Toutes les tables écrites dans une transaction multi-déclaration, multi-table doivent :

• Soyez des tables gérées par Unity Catalog (Delta ou Iceberg)
• Activer les commits du catalogue

Créez deux exemples de tables dans l'Éditeur SQL ou un Notebook:

SQL

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

remarque

Pour activer les transactions sur une table existante, exécutez :

SQL

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

Insérer des exemples de données dans les deux tables :

SQL

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

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

Vérifier la configuration :

SQL

SELECT * FROM sample_accounts;
SELECT * FROM sample_transactions;

Résultat :

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

Transactions non interactives

Les transactions non interactives utilisent la syntaxe BEGIN ATOMIC ... END;. Toutes les instructions sont exécutées comme une seule unité atomique. Si chaque instruction réussit, Databricks commit automatiquement. Si une déclaration échoue, Databricks annule toutes les modifications automatiquement. Pour une syntaxe et des modèles d'utilisation détaillés, voir transactions non interactives.

Exécuter une transaction réussie

Mettez à jour les deux tables de manière atomique :

SQL

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;

Vérifiez que le solde d'Alice est maintenant de 1 100,00 :

SQL

SELECT * FROM sample_accounts WHERE id = 1;

Vérifiez que deux enregistrements de transaction existent maintenant :

SQL

SELECT * FROM sample_transactions;

La mise à jour du solde et l'enregistrement de la transaction ont été créés ensemble. Si l'une des instructions avait échoué, aucune modification n'aurait été validée, et Databricks aurait annulé la transaction sans effets secondaires.

Utilisez SIGNAL pour faire échouer une transaction sous condition.

Vous pouvez utiliser SIGNAL à l'intérieur d'un bloc BEGIN ATOMIC ... END; pour faire échouer la transaction lorsqu'une condition définie par l'utilisateur n'est pas remplie. Cet exemple insère un compte avec un solde négatif, puis utilise SIGNAL pour faire échouer la transaction si la vérification du solde échoue :

SQL

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;

Le SIGNAL génère une erreur, ce qui entraîne l'annulation automatique de l'intégralité de la transaction. Ceci ne renvoie aucune ligne, car l'insertion a été annulée :

SQL

SELECT * FROM sample_accounts WHERE id = 3;

Voir la restauration automatique en cas de défaillance

Exécutez une transaction où la première instruction est valide, mais la seconde fait référence à une table qui n'existe pas :

SQL

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

La transaction échoue avec une erreur. Ceci renvoie 0 ligne, car la transaction entière a été annulée :

SQL

SELECT * FROM sample_accounts WHERE id = 4;

Même si la première instruction INSERT était valide, elle a été annulée parce que la deuxième instruction a échoué. Cela démontre la garantie tout-ou-rien des transactions.

Transactions interactives

Les transactions interactives vous donnent un contrôle explicite sur le moment de commit ou d'annulation. Utilisez BEGIN TRANSACTION pour start, puis COMMIT pour enregistrer les modifications ou ROLLBACK pour les annuler.

Commit changes

start une transaction :

SQL

BEGIN TRANSACTION;

Apporter des modifications (non encore validées) :

SQL

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

commit pour rendre les modifications permanentes :

SQL

COMMIT;

Vérifiez que le compte d'Eve est maintenant visible :

SQL

SELECT * FROM sample_accounts WHERE id = 5;

Vérifiez que le solde de Bob est maintenant de 550.00:

SQL

SELECT * FROM sample_accounts WHERE id = 2;

Annuler les modifications

start a new transaction :

SQL

BEGIN TRANSACTION;

Apporter une modification :

SQL

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

Vérifiez que le changement est visible dans votre session (la ligne n'est pas visible pour les autres sessions tant qu'elle n'est pas validée) :

SQL

SELECT * FROM sample_accounts WHERE id = 6;

Restaurez pour annuler la modification :

SQL

ROLLBACK;

Ceci renvoie zéro ligne car l'insertion a été annulée :

SQL

SELECT * FROM sample_accounts WHERE id = 6;

Utiliser avec des procédures stockées et des scripts SQL

Vous pouvez combiner des transactions avec des procédures stockées pour créer une logique de transaction réutilisable. Ce modèle est utile pour les opérations complexes que vous exécutez fréquemment.

1. Créer les tables avec les commits de catalogue activés

   SQL

   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. Définissez la procédure stockée

   SQL

   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. Définir la transaction

   SQL

   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
   

Si une partie de la transaction échoue, Databricks annule automatiquement toutes les modifications.

Nettoyer

Supprimer les tables d’échantillon :

SQL

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;

Ressources supplémentaires

• Transactions: Aperçu de la prise en charge des transactions.
• Modes de transaction: Syntaxe détaillée et modèles pour les deux modes.
• Commits de catalogue: activer le support des transactions sur vos tables.
• Utiliser des transactions depuis différents clients: Exécuter des transactions depuis des applications JDBC, ODBC et Python.
• Instruction composée ATOMIC (transactions non interactives)
• BEGIN TRANSACTION (transactions interactives)
• commit
• ROLLBACK