Transactions multi-instructions

BigQuery accepte les transactions multi-instructions dans une seule requête ou sur plusieurs requêtes lorsque vous utilisez des sessions. Une transaction multi-instructions 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 une seule requête SQL, sauf en Session mode. Une requête peut contenir plusieurs transactions, mais elles ne peuvent pas être imbriquées. Vous pouvez exécuter des transactions à plusieurs instructions sur plusieurs requêtes au cours d'une session.

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

La requête exécute une instruction COMMIT TRANSACTION. Cette instruction valide de manière atomique toutes les modifications effectuées dans la transaction.
La requête exécute une instruction ROLLBACK TRANSACTION. Cette instruction abandonne toutes les modifications apportées à la transaction.
La requête 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 la requête 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, la requête é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, MERGE et TRUNCATE TABLE
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 :

Les fonctions CURRENT_TIMESTAMP, CURRENT_DATE et CURRENT_TIME renvoient l'horodatage au début de la transaction.
Vous ne pouvez pas utiliser la clause FOR SYSTEM_TIME AS OF pour lire une table au-delà de l'horodatage au début de la transaction. Cette opération va renvoyer une erreur.

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* de la colonne transaction_id.

Lorsqu'une transaction à plusieurs instructions est exécutée, BigQuery crée une tâche enfant pour chaque instruction de la transaction. 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, si la transaction est exécutée dans le cadre de requêtes à plusieurs instructions, telles qu'une procédure stockée, elle renvoie également l'ID du job parent. Si la transaction est en cours d'exécution dans une session, elle renvoie également les informations sur la session.

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;

Rechercher les transactions actives exécutées dans une transaction multi-instructions

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 la transaction à plusieurs instructions.

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

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.
Une transaction à plusieurs instructions qui échoue déclenche une opération de rollback, et annule toutes les modifications en attente et empêche d'effectuer de nouvelles tentatives.
Une transaction peut muter des données dans 100 tables au maximum et effectuer au maximum 100 000 modifications de partition.
BI Engine n'accélère pas les requêtes au sein d'une transaction.