Transacciones de varias declaraciones

BigQuery admite transacciones de varias declaraciones dentro de una sola consulta o en varias consultas cuando se usan sesiones. Una transacción con varias declaraciones te permite realizar operaciones de mutación, como insertar o borrar filas, en una o más tablas, y confirmar o revertir los cambios de forma atómica.

Los usos de las transacciones de varias declaraciones incluyen lo siguiente:

Realizar mutaciones de DML en varias tablas como una sola transacción. Las tablas pueden abarcar varios conjuntos de datos o proyectos.
Realizar mutaciones en una sola tabla en varias etapas, en función de cálculos intermedios.

Las transacciones garantizan las propiedades ACID y admiten el aislamiento de instantáneas. Durante una transacción, todas las lecturas muestran una instantánea coherente de las tablas a las que se hace referencia en la transacción. Si una declaración en una transacción modifica una tabla, los cambios son visibles para las declaraciones posteriores dentro de la misma transacción.

Alcance de la transacción

Una transacción debe incluirse en una sola consulta de SQL, excepto en Session mode. Una consulta puede contener varias transacciones, pero no se pueden anidar. Puedes ejecutar transacciones de varias instrucciones en múltiples consultas en una sesión.

Para iniciar una transacción, usa el comando BEGIN TRANSACTION. La transacción finaliza cuando ocurre alguna de las siguientes situaciones:

La consulta ejecuta una declaración COMMIT TRANSACTION. Esta declaración confirma de forma atómica todos los cambios realizados dentro de la transacción.
La consulta ejecuta una declaración ROLLBACK TRANSACTION. Con esta declaración, se descartan todos los cambios realizados dentro de la transacción.
La consulta termina antes de llegar a cualquiera de estas dos declaraciones. En ese caso, BigQuery revierte de forma automática la transacción.

Si se produce un error durante una transacción y la consulta tiene un controlador de excepciones, BigQuery transfiere el control al controlador de excepciones. Dentro del bloque de excepciones, puedes elegir si deseas confirmar o revertir la transacción.

Si se produce un error durante una transacción y no hay un controlador de excepciones, la consulta fallará y BigQuery revertirá automáticamente la transacción.

En el siguiente ejemplo, se muestra un controlador de excepciones que revierte una transacción:

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;

Declaraciones admitidas en transacciones

Los tipos de declaraciones siguientes se admiten en las transacciones:

Declaraciones de consulta: SELECT
Declaraciones DML: INSERT, UPDATE, DELETE, MERGE y TRUNCATE TABLE
Declaraciones DDL en entidades temporales:
- CREATE TEMP TABLE
- CREATE TEMP FUNCTION
- DROP TABLE en una tabla temporal
- DROP FUNCTION en una función temporal

Las declaraciones DDL que crean o descartan entidades permanentes, como conjuntos de datos, tablas y funciones, no son compatibles con las transacciones.

Funciones de fecha y hora en transacciones

En una transacción, las siguientes funciones de fecha y hora tienen comportamientos especiales:

Las funciones CURRENT_TIMESTAMP, CURRENT_DATE y CURRENT_TIME muestran la marca de tiempo de cuando se inició la transacción.
No puedes usar la cláusula FOR SYSTEM_TIME AS OF para leer una tabla más allá de la marca de tiempo de cuando comenzó la transacción. Si lo haces, se muestra un error.

Ejemplo de una transacción

En este ejemplo, se supone que hay dos tablas llamadas Inventory y NewArrivals, creadas de la siguiente manera:

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 tabla Inventory contiene información sobre el inventario actual, y NewArrivals contiene información sobre los elementos recién llegados.

La siguiente transacción actualiza Inventory con los elementos nuevas y borra los registros correspondientes de NewArrivals. Si suponemos que todas las declaraciones se completan de forma correcta, los cambios en ambas tablas se confirman de forma atómica como una sola transacción.

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;

Simultaneidad de las transacciones

Si una transacción cambia (actualiza o borra) las filas de una tabla, entonces otras transacciones o declaraciones DML que mutan filas en la misma tabla no se pueden ejecutar simultáneamente. Las transacciones en conflicto se cancelan. Las declaraciones DML en conflicto que se ejecutan fuera de una transacción se ponen en cola para ejecutarse más tarde, sujetas a los límites de puesta en cola.

Las operaciones que leen o agregan filas nuevas pueden ejecutarse de forma simultánea con la transacción. Por ejemplo, cualquiera de las siguientes operaciones se pueden realizar de forma simultánea en una tabla, mientras que una transacción muta los datos en la misma tabla:

Declaraciones SELECT
Operaciones de lectura de la API de lectura de almacenamiento de BigQuery
Consultas de BigQuery BI Engine
Declaraciones INSERT
Trabajos de carga que usan la disposición WRITE_APPEND para agregar filas
Operaciones de escritura por transmisión

Si una transacción solo lee una tabla o le agrega filas nuevas, cualquier operación se puede realizar de forma simultánea en esa tabla.

Visualiza la información de la transacción

BigQuery asigna un ID de transacción a cada transacción de varias declaraciones. El ID de transacción se adjunta a cada consulta que se ejecuta dentro de la transacción. Para ver los ID de transacción de tus trabajos, consulta las vistas INFORMATION_SCHEMA.JOBS* de la columna transaction_id.

Cuando se ejecuta una transacción de varias declaraciones, BigQuery crea un trabajo secundario para cada declaración en la transacción. Para una transacción determinada, cada trabajo secundario asociado con esa transacción tiene el mismo valor transaction_id.

En los siguientes ejemplos, se muestra cómo encontrar información sobre tus transacciones.

Busca todas las transacciones confirmadas o revertidas

La siguiente consulta muestra todas las transacciones que se confirmaron de forma correcta.

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 siguiente consulta muestra todas las transacciones que se revertiron de forma correcta.

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;

Encuentra la hora de inicio y finalización de una transacción

La siguiente consulta muestra los tiempos de inicio y finalización para un ID de transacción especificado.

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;

Encuentra la transacción en la que se ejecuta un trabajo

La siguiente consulta obtiene la transacción asociada con un ID de trabajo especificado. Muestra NULL si el trabajo no está en ejecución dentro de una transacción de varias declaraciones.

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

Encuentra el trabajo actual que se ejecuta dentro de una transacción

La siguiente consulta muestra información sobre el trabajo que se ejecuta actualmente dentro de una transacción especificada, si existe.

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;

Encuentra las transacciones activas que afectan a una tabla

La siguiente consulta muestra las transacciones activas que afectan a una tabla especificada. Si una transacción activa se ejecuta como parte de consultas de varias sentencias, como dentro de un procedimiento almacenado, también devolverá el ID del trabajo superior. Si la transacción se ejecuta dentro de una sesión, también mostrará la información de la sesión.

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;

Encuentra las transacciones activas que se ejecutan en una transacción de varias declaraciones

La siguiente consulta muestra las transacciones activas para un trabajo en particular, especificadas por el ID del trabajo que ejecuta la transacción de varias declaraciones.

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

Limitaciones

Las transacciones no pueden usar declaraciones DDL que afecten a entidades permanentes.
Dentro de una transacción, las vistas materializadas se interpretan como vistas lógicas. Aunque aún puedes consultar una vista materializada dentro de una transacción, eso no genera ninguna mejora en el rendimiento ni una reducción de costos en comparación con la vista lógica equivalente.
Cuando una transacción con varias declaraciones que falla, se activa una operación de reversión que se encarga de deshacer todos los cambios pendientes así como de excluir los reintentos.
Una transacción puede mutar datos en un máximo de 100 tablas y puede realizar como máximo 100,000 modificaciones de particiones.
BI Engine no acelera las consultas dentro de una transacción.