Transacciones de MySQL e Integridad de Datos

Introducción

En este laboratorio, aprenderá a utilizar transacciones de MySQL para garantizar la integridad de los datos. Las transacciones agrupan una serie de operaciones SQL en una única unidad de trabajo, donde todo se completa o nada se completa. Esto es esencial para tareas como transferencias financieras, donde todos los pasos deben completarse con éxito.

Practicará el inicio de una transacción con BEGIN, la permanencia de los cambios con COMMIT, la reversión de cambios con ROLLBACK y la realización de reversiones parciales utilizando SAVEPOINT. Al final de este laboratorio, comprenderá cómo gestionar las operaciones de la base de datos de forma segura y fiable.

Configurar la Base de Datos y las Tablas

Antes de trabajar con transacciones, necesita configurar una base de datos y una tabla. En este paso, creará una base de datos llamada bank_db y una tabla accounts para simular operaciones bancarias.

Primero, abra la terminal desde su escritorio.

Conéctese al servidor MySQL como usuario root. Este entorno de laboratorio no requiere contraseña para el usuario root.

sudo mysql -u root

Una vez conectado, verá el prompt de MySQL (mysql>), lo que indica que ahora puede ejecutar comandos SQL.

Cree la base de datos bank_db y cambie a ella para la sesión actual. La cláusula IF NOT EXISTS evita un error si la base de datos ya existe.

CREATE DATABASE IF NOT EXISTS bank_db;
USE bank_db;

A continuación, cree la tabla accounts. Esta tabla almacenará información de cuentas, incluyendo un ID, número de cuenta y saldo.

CREATE TABLE accounts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    account_number VARCHAR(20) UNIQUE NOT NULL,
    balance DECIMAL(10, 2) NOT NULL
);

Ahora, inserte dos cuentas de ejemplo en la tabla accounts. Estas servirán como nuestros datos iniciales.

INSERT INTO accounts (account_number, balance) VALUES
('ACC001', 1000.00),
('ACC002', 500.00);

Para confirmar que la tabla y los datos se crearon correctamente, ejecute la siguiente consulta:

SELECT * FROM accounts;

Debería ver las dos cuentas que acaba de insertar. La salida se verá así:

+----+----------------+---------+
| id | account_number | balance |
+----+----------------+---------+
|  1 | ACC001         | 1000.00 |
|  2 | ACC002         |  500.00 |
+----+----------------+---------+
2 rows in set (0.00 sec)

Con la base de datos y la tabla preparadas, está listo para aprender sobre transacciones.

Crear una Transacción Exitosa con BEGIN y COMMIT

Una transacción es una secuencia de operaciones realizadas como una única unidad lógica. Para que los cambios sean permanentes, debe hacerles COMMIT. En este paso, simulará una transferencia bancaria exitosa de ACC001 a ACC002.

Debería seguir en el shell de MySQL desde el paso anterior.

Primero, inicie una nueva transacción utilizando el comando BEGIN. Todos los comandos subsiguientes serán parte de esta transacción hasta que haga COMMIT o ROLLBACK.

BEGIN;

Ahora, realice las dos operaciones para la transferencia: deduzca $100 de ACC001 y agregue $100 a ACC002.

UPDATE accounts SET balance = balance - 100.00 WHERE account_number = 'ACC001';
UPDATE accounts SET balance = balance + 100.00 WHERE account_number = 'ACC002';

En este punto, los cambios solo son visibles dentro de su sesión actual. Puede verificar el estado temporal de las cuentas:

SELECT * FROM accounts;

La salida mostrará los saldos actualizados, pero aún no se han guardado permanentemente.

+----+----------------+---------+
| id | account_number | balance |
+----+----------------+---------+
|  1 | ACC001         |  900.00 |
|  2 | ACC002         |  600.00 |
+----+----------------+---------+
2 rows in set (0.00 sec)

Dado que ambas operaciones fueron exitosas, puede hacer que los cambios sean permanentes confirmando la transacción.

COMMIT;

El comando COMMIT guarda todos los cambios realizados durante la transacción. La transferencia ahora está completa y es visible para todas las demás conexiones a la base de datos. Puede verificar esto consultando la tabla nuevamente. Los saldos permanecerán en $900 y $600.

Deshacer una Transacción con ROLLBACK

A veces, una operación dentro de una transacción falla, o puede decidir cancelarla. El comando ROLLBACK descarta todos los cambios realizados durante la transacción actual, restaurando la base de datos a su estado anterior al inicio de la transacción.

Comencemos una nueva transacción para agregar una nueva cuenta.

BEGIN;

Ahora, inserte una nueva cuenta ACC003 en la tabla accounts.

INSERT INTO accounts (account_number, balance) VALUES ('ACC003', 150.00);

Verifique la tabla para ver la nueva cuenta dentro de la transacción.

SELECT * FROM accounts;

Verá tres cuentas listadas.

+----+----------------+---------+
| id | account_number | balance |
+----+----------------+---------+
|  1 | ACC001         |  900.00 |
|  2 | ACC002         |  600.00 |
|  3 | ACC003         |  150.00 |
+----+----------------+---------+
3 rows in set (0.00 sec)

Ahora, imagine que se da cuenta de que esta cuenta no debería haberse agregado. En lugar de hacer COMMIT, puede cancelar toda la transacción usando ROLLBACK.

ROLLBACK;

Este comando deshace la operación INSERT. Para verificar esto, consulte la tabla accounts nuevamente.

SELECT * FROM accounts;

La cuenta ACC003 ha desaparecido y la tabla ha vuelto al estado en que se encontraba antes de escribir BEGIN.

+----+----------------+---------+
| id | account_number | balance |
+----+----------------+---------+
|  1 | ACC001         |  900.00 |
|  2 | ACC002         |  600.00 |
+----+----------------+---------+
2 rows in set (0.00 sec)

ROLLBACK es una herramienta fundamental para mantener la integridad de los datos cuando ocurren errores.

Usar SAVEPOINT para Rollbacks Parciales

Para transacciones largas, es posible que desee deshacer solo una parte del trabajo en lugar de la transacción completa. SAVEPOINT le permite establecer un marcador dentro de una transacción al que puede hacer ROLLBACK posteriormente.

Comencemos una nueva transacción.

BEGIN;

Primero, actualice el saldo de ACC001, aumentándolo en $50.

UPDATE accounts SET balance = balance + 50.00 WHERE account_number = 'ACC001';

Ahora, cree un punto de guardado (savepoint). Esto actúa como un marcador para el estado actual de la transacción.

SAVEPOINT after_update;

A continuación, realice otra operación: inserte una nueva cuenta ACC004.

INSERT INTO accounts (account_number, balance) VALUES ('ACC004', 300.00);

Supongamos que la inserción de ACC004 fue un error, pero la actualización de ACC001 fue correcta. Puede hacer ROLLBACK al punto de guardado para deshacer solo la instrucción INSERT.

ROLLBACK TO after_update;

Este comando revierte la transacción al estado en que se encontraba cuando creó el punto de guardado after_update. La inserción de ACC004 se deshace, pero la actualización de ACC001 permanece.

Consulte la tabla para confirmar. ACC004 debería haber desaparecido, pero el saldo de ACC001 debería estar actualizado.

SELECT * FROM accounts;

La salida debería mostrar ACC001 con un saldo de $950.00 y sin ACC004.

+----+----------------+---------+
| id | account_number | balance |
+----+----------------+---------+
|  1 | ACC001         |  950.00 |
|  2 | ACC002         |  600.00 |
+----+----------------+---------+
2 rows in set (0.00 sec)

Dado que desea mantener la actualización de ACC001, ahora puede confirmar la parte restante de la transacción.

COMMIT;

Ha utilizado con éxito un punto de guardado para realizar un rollback parcial. Ahora puede salir del shell de MySQL.

exit;

Resumen

En este laboratorio, ha aprendido los fundamentos de las transacciones de MySQL para mantener la integridad de los datos. Ha practicado el ciclo de vida completo de una transacción, desde iniciar una transacción con BEGIN hasta finalizarla con COMMIT o cancelarla con ROLLBACK.

Ha simulado con éxito una transferencia bancaria, asegurándose de que múltiples operaciones se trataran como una única unidad atómica. También ha aprendido a utilizar SAVEPOINT para obtener un control más preciso sobre transacciones complejas, permitiendo rollbacks parciales. Estas habilidades son cruciales para desarrollar aplicaciones de bases de datos robustas y confiables donde la consistencia de los datos es primordial.