Esta página se ha traducido con Cloud Translation API.

Transacciones con varias instrucciones

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

Las transacciones con varias instrucciones se pueden usar para 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 fases, en función de los cálculos intermedios.

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

Ámbito de la transacción

Una transacción debe estar incluida en una sola consulta SQL, excepto cuando se encuentre en Session mode. Una consulta puede contener varias transacciones, pero no pueden anidarse. Puedes ejecutar transacciones de varias instrucciones en varias consultas de una sesión.

Para iniciar una transacción, usa la instrucción BEGIN TRANSACTION. La transacción finaliza cuando se produce alguna de las siguientes situaciones:

La consulta ejecuta una COMMIT TRANSACTION declaración. Esta instrucción confirma de forma atómica todos los cambios realizados en la transacción.
La consulta ejecuta una ROLLBACK TRANSACTION declaración. Esta instrucción abandona todos los cambios realizados en la transacción.
La consulta finaliza antes de llegar a cualquiera de estas dos instrucciones. En ese caso, BigQuery revierte automáticamente la transacción.

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

Si se produce un error durante una transacción y no hay ningún controlador de excepciones, la consulta falla y BigQuery revierte 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;

Instrucciones admitidas en transacciones

Se admiten los siguientes tipos de instrucciones en las transacciones:

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

Las instrucciones DDL que crean o eliminan entidades permanentes, como conjuntos de datos, tablas y funciones, no se admiten en 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 devuelven la marca de tiempo en la que 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 en la que se inició la transacción. Si lo hace, se devolverá un error.

Ejemplo de una transacción

En este ejemplo, se presupone 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 la tabla NewArrivals contiene información sobre los artículos recién llegados.

La siguiente transacción actualiza Inventory con las novedades y elimina los registros correspondientes de NewArrivals. Si todas las instrucciones se completan correctamente, 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;

Transacciones simultáneas

Si una transacción modifica (actualiza o elimina) filas de una tabla, otras transacciones o instrucciones DML que modifiquen filas de la misma tabla no se podrán ejecutar simultáneamente. Las transacciones conflictivas se cancelan. Las instrucciones de DML en conflicto que se ejecutan fuera de una transacción se ponen en cola para ejecutarse más adelante, sujetas a los límites de la cola.

Las operaciones que leen o añaden filas nuevas se pueden ejecutar simultáneamente con la transacción. Por ejemplo, se puede realizar cualquiera de las siguientes operaciones de forma simultánea en una tabla mientras una transacción modifica los datos de la misma tabla:

SELECT extractos
Operaciones de lectura de la API Storage Read de BigQuery
Consultas de BigQuery BI Engine
INSERT extractos
Tareas de carga que usan la disposición WRITE_APPEND para añadir filas
Escrituras de streaming

Si una transacción solo lee una tabla o añade filas nuevas a ella, se puede realizar cualquier operación simultáneamente en esa tabla.

Ver información de las transacciones

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

Cuando se ejecuta una transacción con varias instrucciones, BigQuery crea un trabajo secundario para cada instrucción de la transacción. En una transacción determinada, todos los trabajos secundarios asociados a ella tienen el mismo valor transaction_id.

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

Buscar todas las transacciones confirmadas o restauradas

La siguiente consulta devuelve todas las transacciones que se han confirmado correctamente.

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 devuelve todas las transacciones que se han restaurado correctamente.

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;

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

La siguiente consulta devuelve las horas de inicio y de finalización de 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;

Buscar la transacción en la que se está ejecutando un trabajo

La siguiente consulta obtiene la transacción asociada a un ID de tarea especificado. Devuelve NULL si el trabajo no se está ejecutando en una transacción con varias instrucciones.

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

Buscar el trabajo actual que se está ejecutando en una transacción

La siguiente consulta devuelve información sobre la tarea que se está ejecutando en una transacción específica, si la hay.

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;

Buscar las transacciones activas que afectan a una tabla

La siguiente consulta devuelve las transacciones activas que afectan a una tabla especificada. En cada transacción activa, si la transacción se ejecuta como parte de consultas de varias instrucciones, como en un procedimiento almacenado, también devuelve el ID del trabajo principal. Si la transacción se ejecuta en una sesión, también devuelve 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;

Buscar las transacciones activas que se están ejecutando en una transacción con varias instrucciones

La siguiente consulta devuelve las transacciones activas de un trabajo concreto, especificado por el ID del trabajo que ejecuta la transacción de varias instrucciones.

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 instrucciones DDL que afecten a entidades permanentes.
Dentro de una transacción, las vistas materializadas se interpretan como vistas lógicas. Puedes seguir consultando una vista materializada dentro de una transacción, pero no se consigue ninguna mejora del rendimiento ni reducción de costes en comparación con la vista lógica equivalente.
Una transacción con varias instrucciones que falla activa una operación de reversión, que deshace todos los cambios pendientes e impide que se vuelva a intentar.
Una transacción puede mutar datos en un máximo de 100 tablas y realizar un máximo de 100.000 modificaciones de partición.
BI Engine no acelera las consultas dentro de una transacción.
Los metadatos de las fuentes de datos externas no se pueden actualizar en una transacción con un procedimiento del sistema.