Transactions multi-instructions

BigQuery accepte les transactions contenant plusieurs instructions dans les scripts. Une transaction à instructions multiples vous permet d'effectuer des opérations de mutation, telles que l'insertion ou la suppression de lignes, sur une ou plusieurs tables, et de procéder au commit ou au rollback des modifications de manière atomique.

Les transactions multi-instructions sont utilisées notamment pour les opérations suivantes :

  • Effectuer des mutations LMD sur plusieurs tables en une seule transaction. Les tables peuvent couvrir plusieurs ensembles de données ou projets.
  • Effectuer des mutations sur une seule table en plusieurs étapes, en fonction de calculs intermédiaires.

Les transactions garantissent des propriétés ACID et sont compatibles avec l'isolation d'instantané. Au cours d'une transaction, toutes les lectures renvoient un instantané cohérent des tables référencées dans la transaction. Si une instruction dans une transaction modifie une table, les modifications sont visibles pour les instructions suivantes au sein de la même transaction.

Portée de la transaction

Une transaction doit être contenue dans un seul script SQL. Un script peut contenir plusieurs transactions, mais elles ne peuvent pas être imbriquées.

Pour lancer une transaction, utilisez l'instruction BEGIN TRANSACTION. La transaction se termine lorsque l'une des situations suivantes se présente :

  • Le script exécute une instruction COMMIT TRANSACTION. Cette instruction valide de manière atomique toutes les modifications effectuées dans la transaction.
  • Le script exécute une instruction ROLLBACK TRANSACTION. Cette instruction abandonne toutes les modifications apportées à la transaction.
  • Le script se termine avant d'atteindre l'une de ces deux instructions. Dans ce cas, BigQuery effectue automatiquement le rollback de la transaction.

Si une erreur se produit lors d'une transaction et que le script dispose d'un gestionnaire d'exceptions, BigQuery transfère le contrôle au gestionnaire d'exceptions. Dans le bloc d'exceptions, vous pouvez choisir de procéder au commit ou au rollback de la transaction.

Si une erreur se produit lors d'une transaction et qu'il n'y a pas de gestionnaire d'exceptions, le script échoue et BigQuery annule automatiquement la transaction.

L'exemple suivant illustre un gestionnaire d'exceptions qui effectue le rollback d'une transaction :

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;

Instructions acceptées dans les transactions

Les types d'instructions suivants sont compatibles avec les transactions :

  • Instructions de requête : SELECT
  • Instructions LMD : INSERT, UPDATE, DELETE et MERGE
  • Instructions LDD sur des entités temporaires :

    • CREATE TEMP TABLE
    • CREATE TEMP FUNCTION
    • DROP TABLE sur une table temporaire
    • DROP FUNCTION sur une fonction temporaire

Les instructions LDD qui créent ou suppriment des entités permanentes, telles que des ensembles de données, des tables et des fonctions, ne sont pas compatibles avec les transactions.

Fonctions de date et heure dans les transactions

Dans une transaction, les fonctions de date/heure suivantes ont des comportements spéciaux :

Exemple de transaction

Cet exemple suppose que deux tables nommées Inventory et NewArrivals sont créées comme suit :

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

La table Inventory contient des informations sur l'inventaire actuel, et NewArrivals contient des informations sur les articles nouvellement arrivés.

La transaction suivante met à jour Inventory avec les nouveaux articles et supprime les enregistrements correspondants de NewArrivals. En supposant que toutes les instructions aboutissent, les modifications des deux tables sont validées de manière atomique en tant que transaction unique.

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;

Simultanéité des transactions

Si une transaction mute (met à jour ou supprime) des lignes d'une table, les autres transactions ou instructions LMD qui mutent les lignes d'une même table ne peuvent pas être exécutées simultanément. Les transactions en conflit sont annulées. Les instructions LMD en conflit qui s'exécutent en dehors d'une transaction sont mises en file d'attente pour être exécutées ultérieurement, sous réserve des limites de mise en file d'attente.

Les opérations de lecture ou d'ajout de nouvelles lignes peuvent s'exécuter simultanément avec la transaction. Par exemple, chacune des opérations suivantes peut être effectuée simultanément sur une table alors qu'une transaction mute les données de la même table :

  • Instructions SELECT
  • Opérations de lecture de l'API BigQuery Storage Read
  • Requêtes de BigQuery BI Engine
  • Instructions INSERT
  • Tâches de chargement utilisant la disposition WRITE_APPEND pour ajouter des lignes
  • Écritures en flux continu

Si une transaction ne fait que lire une table ou y ajouter de nouvelles lignes, toute opération peut être effectuée simultanément sur cette table.

Afficher les informations sur les transactions

BigQuery attribue un ID de transaction à chaque transaction comportant plusieurs instructions. L'ID de transaction est associé à chaque requête qui s'exécute dans la transaction. Pour afficher les ID de transaction de vos tâches, interrogez les vues INFORMATION_SCHEMA.JOBS_BY_* de la colonne transaction_id.

Lorsqu'un script s'exécute, BigQuery crée une tâche enfant pour chaque instruction du script. Pour une transaction donnée, chaque tâche enfant associée à cette transaction a la même valeur transaction_id.

Les exemples suivants montrent comment trouver des informations sur vos transactions.

Rechercher toutes les transactions validées ou annulées

La requête suivante renvoie toutes les transactions qui ont été validées.

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

La requête suivante renvoie toutes les transactions qui ont été annulées.

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

Rechercher les heures de début et de fin d'une transaction

La requête suivante renvoie les heures de début et de fin pour un ID de transaction spécifié.

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;

Rechercher la transaction dans laquelle une tâche est en cours d'exécution

La requête suivante permet d'obtenir la transaction associée à un ID de tâche spécifié. Elle renvoie NULL si la tâche n'est pas en cours d'exécution dans une transaction multi-instructions.

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

Rechercher la tâche en cours exécutée dans une transaction

La requête suivante renvoie des informations sur la tâche en cours d'exécution dans une transaction spécifiée, le cas échéant.

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;

Rechercher les transactions actives qui affectent une table

La requête suivante renvoie les transactions actives qui affectent une table spécifiée. Pour chaque transaction active, elle renvoie également l'ID de la tâche parente, c'est-à-dire la tâche du script.

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
  transaction_id, parent_job_id, 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

Rechercher les transactions actives exécutées dans un script

La requête suivante renvoie les transactions actives pour une tâche particulière, spécifiée par l'ID de la tâche qui exécute le script.

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")

Limites

  • Une transaction ne peut pas s'étendre sur plusieurs scripts.
  • Les transactions ne peuvent pas utiliser d'instructions LDD qui affectent les entités permanentes.
  • Dans une transaction, les vues matérialisées sont interprétées comme des vues logiques. Vous pouvez toujours interroger une vue matérialisée dans une transaction, mais cela n'entraîne pas d'amélioration des performances ou de réduction des coûts par rapport à la vue logique équivalente.