Google utilise la technologie IA pour traduire le contenu dans votre langue préférée. Les traductions générées par IA peuvent contenir des erreurs.

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.

Lorsque le mode session est désactivé, 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.

Lorsque le mode session est activé et qu'il n'y a pas de gestionnaire d'exceptions, la transaction reste active jusqu'à ce que la session soit terminée ou qu'elle soit explicitement annulée. Quel que soit le mode de session, il est recommandé d'ajouter des gestionnaires d'exceptions pour s'assurer que les transactions sont correctement annulées dès que possible afin d'éviter de conserver les verrous et les ressources plus longtemps que nécessaire.

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 met à jour ou supprime des lignes dans une table, elle acquiert un verrou au niveau de la table plutôt que de verrouiller des partitions individuelles. Les autres transactions ou instructions LMD qui modifient des lignes dans la même table ne peuvent pas s'exécuter simultanément, même si elles ciblent différentes partitions d'une table partitionnée. BigQuery annule les transactions en conflit. Toutefois, BigQuery met en file d'attente les instructions LMD conflictuelles qui s'exécutent en dehors d'une transaction pour les exécuter ultérieurement, sous réserve des limites de mise en file d'attente.

Lorsque vous exécutez une instruction LMD en mutation dans une transaction multi-instructions, BigQuery conserve le verrou d'écriture sur la table cible jusqu'à ce que la transaction soit validée ou annulée, ou jusqu'à ce que la requête ou la session multi-instructions parente se termine. Ce comportement s'applique aux transactions d'une requête multi-instruction, d'une procédure stockée ou d'une session active, telle qu'une connexion ODBC ou JDBC. BigQuery ne libère pas le verrou lorsque l'instruction LMD individuelle se termine. Les calculs de longue durée dans la même transaction ouverte maintiennent la table verrouillée et entraînent l'échec des transactions de mutation simultanées sur cette table.

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, n'importe quelle opération peut être effectuée simultanément sur cette table, car les instructions INSERT n'entrent pas en conflit les unes avec les autres. Pour empêcher l'exécution simultanée de plusieurs transactions qui n'inséreraient que des lignes dans une table, démarrez chaque transaction avec une instruction DELETE qui ne modifie aucune ligne, telle que DELETE FROM TABLE_NAME WHERE FALSE;. L'inclusion de cette instruction entraîne l'acquisition par BigQuery d'un verrou au niveau de la table sur l'instruction DELETE. Par conséquent, toute autre transaction simultanée qui exécute également une instruction de mutation sur cette table entre en conflit et est annulée.

Éviter et résoudre les conflits de simultanéité

Pour réduire les conflits de simultanéité des transactions, suivez les pratiques suivantes :

Supprimez les wrappers de transaction inutiles : si une procédure stockée ou une requête à plusieurs instructions n'exécute qu'une seule instruction LMD de mutation, telle qu'une instruction MERGE, n'encapsulez pas l'instruction dans des instructions BEGIN TRANSACTION et COMMIT TRANSACTION. Une seule instruction LMD est déjà atomique. L'exécution de l'instruction en dehors d'une transaction multi-instructions permet à BigQuery d'appliquer la mise en file d'attente LMD.
Réessayez les transactions ayant échoué avec un intervalle exponentiel entre les tentatives : lorsqu'une transaction multi-instructions échoue en raison d'une mise à jour simultanée, BigQuery annule la transaction et libère le verrou de la table. Étant donné que BigQuery peut prendre plusieurs secondes pour libérer le verrou dans la couche de métadonnées, relancez les transactions ayant échoué en utilisant une stratégie d'intervalle exponentiel entre les tentatives. Attendez au moins 60 secondes avant la première nouvelle tentative.

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 récupère la transaction associée à un ID de job 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');

Rechercher les jobs qui se chevauchent pour une transaction ayant échoué

Lorsqu'une transaction ou une instruction LMD échoue avec une erreur de mise à jour simultanée, une vérification de l'historique des requêtes des instructions LMD individuelles peut ne pas afficher d'instruction LMD active au moment de l'échec. Cette situation se produit lorsqu'une requête ou une session multi-instructions parente exécute une instruction LMD antérieure dans une transaction et continue d'exécuter d'autres instructions tout en maintenant le verrouillage de la table.

Pour les charges de travail multiprojets, vous pouvez interroger la vue INFORMATION_SCHEMA.JOBS_BY_ORGANIZATION au lieu de INFORMATION_SCHEMA.JOBS_BY_PROJECT. La requête suivante joint les jobs LMD enfants à leurs jobs parents dans INFORMATION_SCHEMA.JOBS_BY_PROJECT pour identifier les jobs, scripts ou sessions de mutation qui se sont chevauchés avec un job ayant échoué :

SELECT
  child_jobs.job_id,
  child_jobs.parent_job_id,
  child_jobs.transaction_id,
  child_jobs.session_info.session_id,
  child_jobs.statement_type,
  child_jobs.start_time AS statement_start_time,
  child_jobs.end_time AS statement_end_time,
  parent_jobs.start_time AS parent_start_time,
  parent_jobs.end_time AS parent_end_time,
  COALESCE(parent_jobs.start_time, child_jobs.start_time) AS lock_window_start,
  COALESCE(
    parent_jobs.end_time, child_jobs.end_time, CURRENT_TIMESTAMP()
  ) AS lock_window_end,
  child_jobs.error_result.message AS error_message,
  child_jobs.query
FROM
  `region-REGION`.INFORMATION_SCHEMA.JOBS_BY_PROJECT AS child_jobs
LEFT JOIN
  `region-REGION`.INFORMATION_SCHEMA.JOBS_BY_PROJECT AS parent_jobs
  ON child_jobs.parent_job_id = parent_jobs.job_id
WHERE
  child_jobs.creation_time
    BETWEEN TIMESTAMP_SUB(TIMESTAMP 'START_TIME', INTERVAL 6 HOUR)
    AND TIMESTAMP_ADD(TIMESTAMP 'END_TIME', INTERVAL 1 HOUR)
  AND child_jobs.destination_table.project_id = 'PROJECT_ID'
  AND child_jobs.destination_table.dataset_id = 'DATASET_NAME'
  AND child_jobs.destination_table.table_id = 'TABLE_NAME'
  AND child_jobs.statement_type IN (
    'UPDATE', 'DELETE', 'MERGE', 'TRUNCATE_TABLE'
  )
  AND COALESCE(parent_jobs.start_time, child_jobs.start_time)
    <= TIMESTAMP 'END_TIME'
  AND COALESCE(parent_jobs.end_time, child_jobs.end_time, CURRENT_TIMESTAMP())
    >= TIMESTAMP 'START_TIME'
ORDER BY
  lock_window_start;

Remplacez les éléments suivants :

REGION : région de l'ensemble de données (par exemple, us).
START_TIME : code temporel start_time du job ayant échoué (par exemple, 2026-06-15 10:00:00 UTC).
END_TIME : code temporel end_time du job ayant échoué (par exemple, 2026-06-15 10:05:00 UTC).
PROJECT_ID : ID du projet contenant la table cible.
DATASET_NAME : nom de l'ensemble de données contenant la table cible.
TABLE_NAME : nom de la table cible.

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 dans une transaction.
Les métadonnées des sources de données externes ne peuvent pas être actualisées dans une transaction à l'aide d'une procédure système.