Google utilizza la tecnologia AI per tradurre i contenuti nella tua lingua preferita. Le traduzioni generate dall'AI potrebbero contenere errori.

Transazioni con più istruzioni

BigQuery supporta le transazioni con più istruzioni all'interno di una singola query o in più query quando si utilizzano le sessioni. Una transazione con più istruzioni consente di eseguire operazioni di mutazione, come l'inserimento o l'eliminazione di righe in una o più tabelle, ed eseguire il commit o il rollback delle modifiche in modo atomico.

Gli utilizzi delle transazioni con più istruzioni includono:

Esecuzione di mutazioni DML su più tabelle come singola transazione. Le tabelle possono estendersi su più set di dati o progetti.
Esecuzione di mutazioni su una singola tabella in più fasi, in base a calcoli intermedi.

Le transazioni garantiscono le proprietà ACID e supportano l'isolamento degli snapshot. Durante una transazione, tutte le letture restituiscono uno snapshot coerente delle tabelle a cui viene fatto riferimento nella transazione. Se un'istruzione in una transazione modifica una tabella, le modifiche sono visibili alle istruzioni successive all'interno della stessa transazione.

Ambito della transazione

Una transazione deve essere contenuta in una singola query SQL, tranne quando si trova in Session mode. Una query può contenere più transazioni, ma non possono essere nidificate. Puoi eseguire transazioni con più istruzioni su più query in una sessione.

Per avviare una transazione, utilizza l'istruzione BEGIN TRANSACTION. La transazione termina quando si verifica una delle seguenti condizioni:

La query esegue un'istruzione COMMIT TRANSACTION. Questa istruzione esegue il commit atomico di tutte le modifiche apportate all'interno della transazione.
La query esegue un'istruzione ROLLBACK TRANSACTION. Questa istruzione annulla tutte le modifiche apportate all'interno della transazione.
La query termina prima di raggiungere una di queste due istruzioni. In questo caso, BigQuery esegue automaticamente il rollback della transazione.

Se si verifica un errore durante una transazione e la query ha un gestore delle eccezioni, BigQuery trasferisce il controllo al gestore delle eccezioni. All'interno del blocco delle eccezioni, puoi scegliere se eseguire il commit o il rollback della transazione.

Quando la modalità sessione è disattivata, se si verifica un errore durante una transazione e non è presente un gestore di eccezioni, la query non va a buon fine e BigQuery esegue automaticamente il rollback della transazione.

Quando la modalità sessione è abilitata e non esiste un gestore di eccezioni, la transazione rimane attiva finché la sessione non viene terminata o non viene eseguito il rollback esplicito. Indipendentemente dalla modalità di sessione, è buona norma aggiungere gestori di eccezioni per garantire che il rollback delle transazioni venga eseguito correttamente il prima possibile, in modo da evitare di mantenere blocchi e risorse più a lungo del necessario.

L'esempio seguente mostra un gestore di eccezioni che esegue il rollback di una transazione:

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;

Istruzioni supportate nelle transazioni

Nelle transazioni sono supportati i seguenti tipi di istruzioni:

Istruzioni di query: SELECT
Istruzioni DML: INSERT, UPDATE, DELETE, MERGE e TRUNCATE TABLE
Istruzioni DDL su entità temporanee:
- CREATE TEMP TABLE
- CREATE TEMP FUNCTION
- DROP TABLE su una tabella temporanea
- DROP FUNCTION su una funzione temporanea

Le istruzioni DDL che creano o eliminano entità permanenti, come set di dati, tabelle e funzioni, non sono supportate all'interno delle transazioni.

Funzioni di data/ora nelle transazioni

All'interno di una transazione, le seguenti funzioni di data/ora hanno comportamenti speciali:

Le funzioni CURRENT_TIMESTAMP, CURRENT_DATE e CURRENT_TIME restituiscono il timestamp dell'inizio della transazione.
Non puoi utilizzare la clausola FOR SYSTEM_TIME AS OF per leggere una tabella oltre il timestamp di inizio della transazione. In questo caso, viene restituito un errore.

Esempio di transazione

Questo esempio presuppone che esistano due tabelle denominate Inventory e NewArrivals, create come segue:

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 tabella Inventory contiene informazioni sull'inventario attuale, mentre NewArrivals contiene informazioni sugli articoli appena arrivati.

La seguente transazione aggiorna Inventory con i nuovi arrivi ed elimina i record corrispondenti da NewArrivals. Supponendo che tutte le istruzioni vengano completate correttamente, viene eseguito il commit delle modifiche in entrambe le tabelle in modo atomico come singola transazione.

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;

Concorrenza delle transazioni

Se una transazione aggiorna o elimina righe in una tabella, acquisisce un blocco a livello di tabella anziché bloccare singole partizioni. Altre transazioni o istruzioni DML che modificano le righe nella stessa tabella non possono essere eseguite contemporaneamente, anche se hanno come target partizioni diverse di una tabella partizionata. BigQuery annulla le transazioni in conflitto. Tuttavia, BigQuery mette in coda le istruzioni DML in conflitto che vengono eseguite al di fuori di una transazione per l'esecuzione successiva, in base ai limiti di coda.

Quando esegui un'istruzione DML di mutazione all'interno di una transazione con più istruzioni, BigQuery mantiene il blocco di scrittura sulla tabella di destinazione fino al commit o al rollback della transazione o fino al termine della query o della sessione con più istruzioni padre. Questo comportamento si applica alle transazioni in una query con più istruzioni, una stored procedure o una sessione attiva, ad esempio una connessione ODBC o JDBC. BigQuery non rilascia il blocco al termine della singola istruzione DML. I calcoli a lunga esecuzione nella stessa transazione aperta mantengono la tabella bloccata e causano l'errore delle transazioni di mutazione simultanee su quella tabella.

Le operazioni che leggono o aggiungono nuove righe possono essere eseguite contemporaneamente alla transazione. Ad esempio, una qualsiasi delle seguenti operazioni può essere eseguita contemporaneamente su una tabella mentre una transazione modifica i dati nella stessa tabella:

SELECT istruzioni
Operazioni di lettura dell'API BigQuery Storage Read
Query da BigQuery BI Engine
INSERT istruzioni
Job di caricamento che utilizzano la disposizione WRITE_APPEND per aggiungere righe
Scritture in modalità flusso

Se una transazione legge solo una tabella o aggiunge nuove righe, qualsiasi operazione può essere eseguita contemporaneamente su quella tabella perché le istruzioni INSERT non sono in conflitto tra loro. Per impedire l'esecuzione simultanea su una tabella di più transazioni che altrimenti inserirebbero solo righe, avvia ogni transazione con un'istruzione DELETE che non modifica nessuna riga, ad esempio DELETE FROM TABLE_NAME WHERE FALSE;. L'inclusione di questa istruzione fa sì che BigQuery acquisisca un blocco a livello di tabella sull'istruzione DELETE, pertanto qualsiasi altra transazione simultanea che esegue anche un'istruzione di mutazione su quella tabella entra in conflitto e viene annullata.

Evita e risolvi i conflitti di contemporaneità

Per ridurre i conflitti di concorrenza delle transazioni, utilizza le seguenti pratiche:

Rimuovi i wrapper delle transazioni non necessari: se una stored procedure o una query con più istruzioni esegue una singola istruzione DML di mutazione, ad esempio un'istruzione MERGE, non eseguire il wrapping dell'istruzione nelle istruzioni BEGIN TRANSACTION e COMMIT TRANSACTION. Una singola istruzione DML è già atomica. L'esecuzione dell'istruzione al di fuori di una transazione con più istruzioni consente a BigQuery di applicare la coda DML.
Riprova le transazioni non riuscite con il backoff esponenziale: quando una transazione con più istruzioni non riesce a causa di un aggiornamento simultaneo, BigQuery esegue il rollback della transazione e rilascia il blocco della tabella. Poiché BigQuery può impiegare diversi secondi per rilasciare il blocco nel livello dei metadati, riprova le transazioni non riuscite utilizzando una strategia di backoff esponenziale. Attendi almeno 60 secondi prima del primo nuovo tentativo.

Visualizzare le informazioni sulle transazioni

BigQuery assegna un ID transazione a ogni transazione con più istruzioni. L'ID transazione è associato a ogni query eseguita all'interno della transazione. Per visualizzare gli ID transazione dei tuoi job, esegui una query sulle viste INFORMATION_SCHEMA.JOBS* per la colonna transaction_id.

Quando viene eseguita una transazione con più istruzioni, BigQuery crea un job secondario per ogni istruzione della transazione. Per una determinata transazione, ogni job secondario associato a quella transazione ha lo stesso valore transaction_id.

I seguenti esempi mostrano come trovare informazioni sulle transazioni.

Trova tutte le transazioni di cui è stato eseguito il commit o il rollback

La seguente query restituisce tutte le transazioni di cui è stato eseguito il commit.

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 seguente query restituisce tutte le transazioni per cui è stato eseguito il rollback con successo.

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;

Trova l'ora di inizio e di fine di una transazione

La seguente query restituisce gli orari di inizio e di fine di un ID transazione specificato.

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;

Trova la transazione in cui è in esecuzione un job

La seguente query recupera la transazione associata a un ID job specificato. Restituisce NULL se il job non è in esecuzione all'interno di una transazione con più istruzioni.

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

Trova il job corrente in esecuzione all'interno di una transazione

La seguente query restituisce informazioni sul job attualmente in esecuzione all'interno di una transazione specificata, se presente.

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;

Trova le transazioni attive che interessano una tabella

La seguente query restituisce le transazioni attive che interessano una tabella specificata. Per ogni transazione attiva, se la transazione viene eseguita nell'ambito di query con più istruzioni, ad esempio all'interno di una stored procedure, restituisce anche l'ID job principale. Se la transazione viene eseguita all'interno di una sessione, restituisce anche le informazioni sulla sessione.

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;

Trova le transazioni attive in esecuzione in una transazione con più istruzioni

La seguente query restituisce le transazioni attive per un determinato job, specificato dall'ID del job che esegue la transazione con più istruzioni.

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

Trova i job sovrapposti per una transazione non riuscita

Quando una transazione o un'istruzione DML non riesce con un errore di aggiornamento simultaneo, un controllo della cronologia delle query delle singole istruzioni DML potrebbe non mostrare un'istruzione DML attiva al momento dell'errore. Questa situazione si verifica quando una query o una sessione con più istruzioni padre esegue un'istruzione DML precedente all'interno di una transazione e continua a eseguire altre istruzioni mantenendo il blocco della tabella.

Per i workload tra progetti, puoi eseguire query sulla vista INFORMATION_SCHEMA.JOBS_BY_ORGANIZATION anziché su INFORMATION_SCHEMA.JOBS_BY_PROJECT. La seguente query unisce i job DML secondari ai job principali in INFORMATION_SCHEMA.JOBS_BY_PROJECT per identificare job, script o sessioni di mutazione che si sono sovrapposti a un job non riuscito:

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;

Sostituisci quanto segue:

REGION: la regione del set di dati (ad esempio us).
START_TIME: il timestamp start_time del job non riuscito (ad esempio, 2026-06-15 10:00:00 UTC).
END_TIME: il timestamp end_time del job non riuscito (ad esempio, 2026-06-15 10:05:00 UTC).
PROJECT_ID: l'ID del progetto che contiene la tabella di destinazione.
DATASET_NAME: il nome del set di dati che contiene la tabella di destinazione.
TABLE_NAME: il nome della tabella di destinazione.

Limitazioni

Le transazioni non possono utilizzare istruzioni DDL che influiscono sulle entità permanenti.
All'interno di una transazione, le viste materializzate vengono interpretate come viste logiche. Puoi comunque eseguire query su una vista materializzata all'interno di una transazione, ma non otterrai alcun miglioramento delle prestazioni o riduzione dei costi rispetto alla vista logica equivalente.
Una transazione con più istruzioni che non va a buon fine attiva un'operazione di rollback, annullando tutte le modifiche in attesa e impedendo i nuovi tentativi.
Una transazione può modificare i dati in un massimo di 100 tabelle ed eseguire un massimo di 100.000 modifiche alle partizioni.
BI Engine non accelera le query all'interno di una transazione.
I metadati per le origini dati esterne non possono essere aggiornati all'interno di una transazione utilizzando una procedura di sistema.