Control de Transacciones en MySQL con InnoDB: Commit y Rollback Explicados

Introducción al Control de Transacciones en MySQL

A continuación, veremos mediante un ejemplo práctico cómo funciona el motor de almacenamiento InnoDB en MySQL al gestionar transacciones utilizando las sentencias BEGIN, ROLLBACK y COMMIT.

Creación de la Tabla y Datos Iniciales

Primero, creamos una tabla llamada innotest e insertamos algunos registros iniciales:

mysql> CREATE TABLE innotest (campo INT NOT NULL PRIMARY KEY) ENGINE = InnoDB;
Query OK, 0 rows affected (0.10 sec)

mysql> INSERT INTO innotest VALUES(1);
Query OK, 1 row affected (0.08 sec)

mysql> INSERT INTO innotest VALUES(2);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO innotest VALUES(3);
Query OK, 1 row affected (0.04 sec)

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

Uso de ROLLBACK en Transacciones

Iniciamos una nueva transacción, insertamos un registro adicional y verificamos el estado de la tabla:

mysql> BEGIN;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO innotest VALUES(4);
Query OK, 1 row affected (0.00 sec)

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

Si en este momento ejecutamos un ROLLBACK, la transacción no será completada, y los cambios realizados sobre la tabla no tendrán efecto.

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

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

Pérdida de Conexión sin COMMIT

Ahora vamos a ver qué sucede si perdemos la conexión al servidor antes de que la transacción sea completada.

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

mysql> INSERT INTO innotest VALUES(4);
Query OK, 1 row affected (0.00 sec)

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

mysql> EXIT;
Bye

Cuando obtengamos de nuevo la conexión, podemos verificar que el registro no se insertó, ya que la transacción no fue completada.

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 449 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 |
+-------+
3 rows in set (0.00 sec)

Uso de COMMIT para Persistencia de Datos

Ahora vamos a repetir la sentencia INSERT ejecutada anteriormente, pero haremos un COMMIT antes de perder la conexión al servidor al salir del monitor de MySQL.

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

mysql> INSERT INTO innotest VALUES(4);
Query OK, 1 row affected (0.00 sec)

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

mysql> EXIT;
Bye

Una vez que hacemos un COMMIT, la transacción es completada, y todas las sentencias SQL que han sido ejecutadas previamente afectan de manera permanente a las tablas de la base de datos.

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 450 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)