Crear transacciones

Si intentas transferir 1000 dólares de tu cuenta de ahorro a tu cuenta corriente y de repente descubres que el dinero se ha cargado, pero no ha llegado a la cuenta corriente, lo más probable es que te lleves un buen disgusto. 😿

Para protegerse de este tipo de errores, el programa que procesa tu solicitud de transferencia primero inicia una transacción, después ejecuta las consultas SQL necesarias para pasar el dinero de una cuenta a otra y, si todo va bien, termina la transacción con el comando COMMIT, que confirma los cambios.

Sin embargo, si surge algún problema, se ejecuta el comando ROLLBACK, que indica al servidor que deshaga todas las acciones realizadas desde el inicio de la transacción.

El proceso puede tener este aspecto:

MySQL 8.4
-- Inicio de la transacción
START TRANSACTION;

-- Cargo del dinero en la cuenta del remitente
UPDATE accounts SET user_balance = user_balance - 1000 WHERE user_id = 1;

-- Abono del dinero en la cuenta del destinatario
UPDATE accounts SET user_balance = user_balance + 1000 WHERE user_id = 2;

-- Confirmación de los cambios
COMMIT;

Si alguna de las consultas falla, en lugar de COMMIT se revierte la transacción:

MySQL 8.4
ROLLBACK;

Gracias a la transacción, el programa protege tus 1000 dólares: garantiza que o bien se quedan en la cuenta de origen, o bien se transfieren a la otra cuenta, sin riesgo de que se pierdan.

Iniciar y finalizar transacciones

Una transacción se puede finalizar:

• con el comando COMMIT, que indica al servidor que marque los cambios como permanentes y libere todos los recursos (es decir, los bloqueos de filas) usados durante la transacción
• con el comando ROLLBACK, que pide al servidor que devuelva los datos al estado anterior al inicio de la transacción. Una vez completada la reversión, también se liberan todos los recursos que usaba la transacción.

Además de con los comandos COMMIT y ROLLBACK, una transacción también puede terminar por factores externos. Por ejemplo, si el servidor se apaga, tu transacción se deshará automáticamente cuando el servidor se reinicie.

Puntos de guardado de una transacción

En ciertas situaciones puede que necesites revertir parte de una transacción sin deshacer todo el trabajo realizado. Para ello, puedes establecer uno o varios puntos de guardado dentro de la transacción. Así puedes volver a un punto concreto de la transacción, y no a su inicio.

Cada punto de guardado de una misma transacción debe tener un nombre único, lo que te permite usar muchos puntos de guardado distintos. Para crear un punto de guardado llamado my_savepoint, usa el siguiente comando:

MySQL 8.4
SAVEPOINT my_savepoint;

Para volver a un punto de guardado concreto, basta con escribir el comando ROLLBACK seguido de las palabras clave TO SAVEPOINT y el nombre del punto de guardado, por ejemplo:

MySQL 8.4
START TRANSACTION;

-- Creamos un punto de guardado antes de cambiar el saldo del primer usuario
SAVEPOINT before_updating_user_1;
UPDATE accounts SET balance = balance + 100 WHERE user_id = 1;

-- Comprobación de una condición para el primer usuario
-- por ejemplo, verificamos la lógica de las reglas de negocio

-- Aquí suponemos que la condición no se ha cumplido y hay que deshacer el cambio de saldo
ROLLBACK TO SAVEPOINT before_updating_user_1;

-- Actualizamos el saldo del segundo usuario
UPDATE accounts SET balance = balance + 200 WHERE user_id = 2;

-- Finalizamos la transacción
COMMIT;

MySQL 8.4
BEGIN;

-- Creamos un punto de guardado antes de cambiar el saldo del primer usuario
SAVEPOINT before_updating_user_1;
UPDATE accounts SET balance = balance + 100 WHERE user_id = 1;

-- Comprobación de una condición para el primer usuario
-- por ejemplo, verificamos la lógica de las reglas de negocio

-- Aquí suponemos que la condición no se ha cumplido y hay que deshacer el cambio de saldo
ROLLBACK TO SAVEPOINT before_updating_user_1;

-- Actualizamos el saldo del segundo usuario
UPDATE accounts SET balance = balance + 200 WHERE user_id = 2;

-- Finalizamos la transacción
COMMIT;

Como resultado de esta transacción, el saldo del primer usuario no cambiará gracias a la vuelta al punto de guardado, y el saldo del segundo usuario aumentará en 200. Esto muestra cómo las transacciones y los puntos de guardado te permiten gestionar los cambios en la base de datos con un alto nivel de control.

Cuando uses puntos de guardado, ten en cuenta lo siguiente:

• A pesar del nombre, al crear un punto de guardado no se guarda nada. Para que los cambios de la transacción sean permanentes, tienes que ejecutar el comando COMMIT.
• Si ejecutas ROLLBACK sin indicar un punto de guardado concreto, se ignorarán todos los puntos de guardado establecidos y se revertirá la transacción entera.

Ponte a prueba

Antes de empezar la transacción, cada uno de los dos usuarios tiene 500 en su cuenta. ¿Qué saldo tendrán el primer y el segundo usuario después de ejecutar estas consultas?

MySQL 8.4
START TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
SAVEPOINT after_debit;
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;
ROLLBACK TO SAVEPOINT after_debit;

COMMIT;

Las mismas consultas, pero al final, en lugar de ROLLBACK TO SAVEPOINT y COMMIT, solo se ejecuta ROLLBACK. ¿Qué quedará guardado en la base de datos?

MySQL 8.4
START TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
SAVEPOINT after_debit;
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;

ROLLBACK;