Transazioni con più estratti conto
BigQuery supporta transazioni con più istruzioni all'interno di una singola query o in più query quando utilizzi le sessioni. Una transazione con più istruzioni consente di eseguire operazioni di modifica, come l'inserimento o l'eliminazione di righe in una o più tabelle, nonché il commit o il rollback delle modifiche a livello atomico.
Gli utilizzi per le transazioni con più estratti conto includono:
- Esecuzione di modifiche DML su più tabelle come singola transazione. Le tabelle possono includere 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 nelle istruzioni successive nella stessa transazione.
Ambito della transazione
Una transazione deve essere contenuta in una singola query SQL, tranne che 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.
Se si verifica un errore durante una transazione e non esiste un gestore delle eccezioni, la query non riesce e BigQuery esegue automaticamente il rollback della transazione.
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;
Estratti conto supportati nelle transazioni
Nelle transazioni sono supportati i seguenti tipi di istruzione:
- Istruzioni di query:
SELECT
- Istruzioni DML:
INSERT
,UPDATE
,DELETE
,MERGE
eTRUNCATE TABLE
Istruzioni DDL su entità temporanee:
CREATE TEMP TABLE
CREATE TEMP FUNCTION
DROP TABLE
su una tabella temporaneaDROP 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
eCURRENT_TIME
restituiscono il timestamp relativo all'inizio della transazione.Non puoi utilizzare la clausola
FOR SYSTEM_TIME AS OF
per leggere una tabella oltre il timestamp relativo all'inizio della transazione. In questo modo viene restituito un errore.
Esempio di una 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 siano state completate correttamente, il commit delle modifiche in entrambe le tabelle viene eseguito atomicamente come una 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;
Contemporaneità transazioni
Se una transazione modifica (aggiorna o elimina) le righe di una tabella, le altre transazioni o istruzioni DML che modificano le righe nella stessa tabella non possono essere eseguite contemporaneamente. Le transazioni in conflitto sono state annullate. Le istruzioni DML in conflitto eseguite al di fuori di una transazione vengono messe in coda per l'esecuzione successiva, soggette ai limiti di coda.
Le operazioni che leggono o aggiungono nuove righe possono essere eseguite in concomitanza con la transazione. Ad esempio, tutte le seguenti operazioni possono essere eseguite contemporaneamente su una tabella, mentre una transazione modifica i dati nella stessa tabella:
SELECT
estratti conto- Operazioni di lettura dell'API BigQuery Storage Read
- Query da BigQuery BI Engine
INSERT
estratti conto- Carica i job che utilizzano la disposizione
WRITE_APPEND
per aggiungere righe - Scrittura dei flussi di dati
Se una transazione legge solo una tabella o vi aggiunge nuove righe, qualsiasi operazione può essere eseguita contemporaneamente sulla tabella.
Visualizzazione delle 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 nelle viste INFORMATION_SCHEMA.JOBS*
per la colonna transaction_id
.
Quando viene eseguita una transazione con più istruzioni, BigQuery crea un job figlio per ogni istruzione nella transazione. Per una determinata transazione, ogni job figlio associato a quella transazione ha lo stesso valore transaction_id
.
I seguenti esempi mostrano come trovare informazioni sulle tue transazioni.
Trovare tutte le transazioni di commit o 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 di cui è stato eseguito il rollback.
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;
Trovare l'ora di inizio e l'ora di fine di una transazione
La seguente query restituisce l'ora di inizio e di fine per 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;
Trovare la transazione in cui è in esecuzione un job
La seguente query ottiene la transazione associata a un ID job specificato. Restituisce NULL
se il job non è in esecuzione in 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 un'eventuale transazione specificata.
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;
Trovare le transazioni attive che interessano una tabella
La seguente query restituisce le transazioni attive che interessano una tabella specificata. Per ogni transazione attiva, se viene eseguita nell'ambito di query a più istruzioni, ad esempio all'interno di una procedura archiviata, restituisce anche l'ID job padre. Se la transazione viene eseguita all'interno di una sessione, vengono restituite 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;
Trovare le transazioni attive in esecuzione in una transazione con più estratti conto
La seguente query restituisce le transazioni attive per un determinato job, specificate 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');
Limitazioni
- Nelle transazioni non è possibile utilizzare istruzioni DDL che interessano 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 ciò non comporta alcun miglioramento delle prestazioni o riduzione dei costi rispetto alla vista logica equivalente.
Una transazione con più istruzioni che non riesce attiva un'operazione di rollback, annullando tutte le modifiche in attesa e precludendo i nuovi tentativi.
Una transazione può modificare i dati di un massimo di 100 tabelle e può eseguire al massimo 100.000 modifiche alle partizioni.
BI Engine non accelera le query all'interno di una transazione.