Questa pagina è stata tradotta dall'API Cloud Translation.

Transazioni multi-istruzione

BigQuery supporta le transazioni con più istruzioni all'interno di una singola query o in più query quando si utilizzano le sessioni. R transazione multi-istruzione consente di eseguire operazioni mutanti, inserendo o eliminando righe in una o più tabelle e eseguendo il commit o il rollback a livello atomico.

Le transazioni con più istruzioni possono essere utilizzate per:

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

Garanzia di transazioni Proprietà ACID e e supportare 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 transazioni modifica una tabella, le modifiche sono visibili nelle istruzioni successive nell'ambito della stessa transazione.

Ambito della transazione

Una transazione deve essere contenuta in una singola query SQL, tranne quando è 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 ignora 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 di eccezioni, la query ha esito negativo e BigQuery esegue automaticamente il rollback transazione.

L'esempio seguente mostra un gestore delle 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 istruzioni:

Istruzioni query: SELECT
Istruzioni DML: INSERT, UPDATE, DELETE, MERGE e TRUNCATE TABLE
Istruzioni DDL sulle 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 presentano comportamenti speciali:

La CURRENT_TIMESTAMP, CURRENT_DATE, e CURRENT_TIME restituiscono il timestamp di inizio della transazione.
Non puoi utilizzare la clausola FOR SYSTEM_TIME AS OF per leggere una tabella oltre il timestamp dell'inizio della transazione. Questa operazione restituisce 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 corrente e 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 dichiarazioni siano completate il commit delle modifiche in entrambe le tabelle è stato eseguito atomicamente come 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 modifica (aggiorna o elimina) le righe di una tabella, altre transazioni o istruzioni DML che modificano le righe della stessa tabella non possono essere eseguite contemporaneamente. Le transazioni in conflitto vengono annullate. Istruzioni DML in conflitto eseguite al di fuori di una transazione vengono messe in coda per essere eseguite in un secondo momento, in base alle limiti di accodamento.

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

SELECT estratti conto
Operazioni di lettura dell'API BigQuery Storage di lettura
Query da BigQuery BI Engine
INSERT estratti conto
Job di caricamento che utilizzano l'istruzione WRITE_APPEND per aggiungere righe
Operazioni di scrittura flussi

Se una transazione legge solo una tabella o vi aggiunge nuove righe, qualsiasi operazione può eseguire contemporaneamente in quella tabella.

Visualizzare le informazioni sulle transazioni

BigQuery assegna un ID transazione a ogni dichiarazione multipla transazione. L'ID transazione è associato a ogni query eseguita all'interno la transazione. Per visualizzare gli ID transazione dei job, esegui una query su INFORMATION_SCHEMA.JOBS* visualizzazioni per la colonna transaction_id.

Quando viene eseguita una transazione multi-istruzione, BigQuery crea per ogni istruzione della transazione. Per una determinata transazione, ogni job secondario associato ha lo stesso valore transaction_id.

I seguenti esempi mostrano come trovare informazioni sulle tue transazioni.

Trovare tutte le transazioni committate o sottoposte a rollback

La seguente query restituisce tutte le transazioni per 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 di fine di una transazione

La seguente query restituisce l'ora di inizio e l'ora di fine per un dell'ID transazione.

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 recupera la transazione associata a un ID job specificato. it 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 query seguente 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;

Trovare le transazioni attive che interessano una tabella

La seguente query restituisce le transazioni attive che interessano una tabella. Per ogni transazione attiva, se è in esecuzione nell'ambito di a più istruzioni, ad esempio all'interno di una stored procedure 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ù istruzioni

La seguente query restituisce le transazioni attive per un particolare job: specificato dall'ID del job che esegue la transazione multi-istruzione.

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

Per le 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 non si ottiene alcun miglioramento delle prestazioni o riduzione dei costi rispetto alla vista logica equivalente.
Una transazione con più istruzioni 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 massimo 100 tabelle ed eseguire al massimo 100.000 modifiche delle partizioni.
BI Engine non accelera all'interno di una transazione.