Gestión de Transacciones MySQL: Aislamiento, COMMIT y ROLLBACK

Introducción a la Persistencia de Datos en Transacciones

Las transacciones son fundamentales para asegurar la integridad de los datos. A continuación, se demuestran los efectos de BEGIN, COMMIT y ROLLBACK, y cómo el aislamiento afecta a múltiples conexiones.

Ejemplo 1: Aislamiento de Transacciones (INSERT)

Inicio de la Transacción (Conexión 1)

Iniciamos una transacción e insertamos un registro en la tabla innotest:

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO innotest VALUES(5);
Query OK, 1 row affected (0.00 sec)

Consulta desde la Segunda Conexión (ID 525)

Ahora, desde la segunda conexión (ID 525) consultamos los datos de nuestra tabla. El registro insertado en la primera conexión aún no es visible debido al aislamiento transaccional.

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 525 to server version: 4.0.13
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> SELECT * FROM innotest;
+-------+
| campo |
+-------+
| 1     |
| 2     |
| 3     |
| 4     |
+-------+
4 rows in set (0.00 sec)

Como se puede observar, el registro que hemos insertado desde la primera conexión no es regresado, puesto que forma parte de una transacción que no ha sido completada. Ahora, desde la primera conexión ejecutamos la misma consulta SELECT, donde sí es visible:

mysql> SELECT * FROM innotest;
+-------+
| campo |
+-------+
| 1     |
| 2     |
| 3     |
| 4     |
| 5     |
+-------+
5 rows in set (0.00 sec)

Confirmación de la Transacción (COMMIT)

Después de completar la transacción con una sentencia COMMIT en la primera conexión, podremos verificar que desde la segunda conexión los cambios ya son visibles, volviéndose permanentes.

mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)

Verificación desde la segunda conexión:

mysql> SELECT * FROM innotest;
+-------+
| campo |
+-------+
| 1     |
| 2     |
| 3     |
| 4     |
| 5     |
+-------+
5 rows in set (0.00 sec)

Ejemplo 2: Uso de ROLLBACK en Sentencias UPDATE y DELETE

En el ejemplo anterior usamos únicamente sentencias INSERT; sin embargo, el mismo comportamiento transaccional sucede con sentencias UPDATE o DELETE.

Creación de la Tabla 'ventas' (InnoDB)

Vamos a crear una sencilla tabla llamada ventas que sea del tipo InnoDB, esencial para el manejo de transacciones.

mysql> CREATE TABLE ventas(
-> id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
-> producto VARCHAR(30) NOT NULL,
-> cantidad TINYINT NOT NULL) ENGINE = InnoDB;
Query OK, 0 rows affected (0.96 sec)

Insertamos un registro inicial y lo verificamos:

mysql> INSERT INTO ventas VALUES(0,'Gansito marinela',3);
Query OK, 1 row affected (0.16 sec)
mysql> SELECT * FROM ventas;
+----+------------------+----------+
| id | producto         | cantidad |
+----+------------------+----------+
| 1  | Gansito marinela | 3        |
+----+------------------+----------+
1 row in set (0.01 sec)

Demostración de ROLLBACK (Deshacer Cambios)

Ahora vamos a iniciar una transacción con la sentencia BEGIN y actualizamos el registro.

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> UPDATE ventas SET cantidad=4 WHERE id=1;
Query OK, 1 row affected (0.07 sec)
Líneas correspondientes: 1 Cambiadas: 1 Avisos: 0

Verificamos que los cambios han sucedido dentro de la transacción actual:

mysql> SELECT * FROM ventas;
+----+------------------+----------+
| id | producto         | cantidad |
+----+------------------+----------+
| 1  | Gansito marinela | 4        |
+----+------------------+----------+
1 row in set (0.00 sec)

Si queremos deshacer los cambios, entonces ejecutamos un ROLLBACK.

mysql> ROLLBACK;
Query OK, 0 rows affected (0.06 sec)

Verificamos que se deshicieron los cambios y el valor original fue restaurado:

mysql> SELECT * FROM ventas;
+----+------------------+----------+
| id | producto         | cantidad |
+----+------------------+----------+
| 1  | Gansito marinela | 3        |
+----+------------------+----------+
1 row in set (0.00 sec)

Confirmación Definitiva (COMMIT)

Vamos a actualizar el registro usando otra transacción para confirmar los cambios de forma permanente.

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> UPDATE ventas SET cantidad=2 WHERE id=1;
Query OK, 1 row affected (0.00 sec)
Líneas correspondientes: 1 Cambiadas: 1 Avisos: 0
mysql> SELECT * FROM ventas;
+----+------------------+----------+
| id | producto         | cantidad |
+----+------------------+----------+
| 1  | Gansito marinela | 2        |
+----+------------------+----------+
1 row in set (0.00 sec)

Confirmamos los cambios mediante COMMIT.

mysql> COMMIT;
Query OK, 0 rows affected (0.05 sec)

En este momento, los cambios son permanentes y definitivos.

mysql> SELECT * FROM ventas;
+----+------------------+----------+
| id | producto         | cantidad |
+----+------------------+----------+
| 1  | Gansito marinela | 2        |
+----+------------------+----------+
1 row in set (0.00 sec)