Transacciones en MySQL

Las operaciones de escritura en MySQL (inserciones, actualizaciones y borrados) se realizan por defecto en modo autocommit, es decir, se actualizan automáticamente en la base de datos.

Este comportamiento es bueno para la mayoría de aplicaciones ya que los cambios se propagan muy rápido, sin embargo no son todo ventajas.

Si queremos realizar una operación compleja en la que actualizamos varios campos o tablas y falla alguna de las operaciones, la base de datos puede quedar en un estado inconsistente o no deseado.

En este caso resultaría útil poder deshacer los cambios, decirle de alguna forma que todas esas operaciones van 'unidas', o se ejecutan todas, o ninguna.

Existe una forma de hacer lo anterior, crear una transacción, los cambios se guardarán en la base de datos si todo ha ido bien, si no, volveremos al estado anterior.

MySQL soporta transacciones, pero no con todos los motores, MyISAM por ejemplo no lo soporta, InnoDB sí.

Un ejemplo de como funcionan las transacciones.

mysql> create table tabla (name char(20), unique(name)) engine = InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into tabla set name = 'primero';
Query OK, 1 row affected (0.01 sec)

mysql> insert into tabla set name = 'segundo';
Query OK, 1 row affected (0.01 sec)

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

mysql> insert into tabla set name = 'tercero';
Query OK, 1 row affected (0.00 sec)

mysql> select * from tabla;
+---------+
| name    |
+---------+
| primero | 
| segundo | 
| tercero | 
+---------+
3 rows in set (0.00 sec)

mysql> insert into tabla set name = 'segundo';
ERROR 1062 (23000): Duplicate entry 'segundo' for key 1
mysql> rollback;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from tabla;
+---------+
| name    |
+---------+
| primero | 
| segundo | 
+---------+
2 rows in set (0.00 sec)

Como se puede ver al hacer rollback volvemos al estado anterior de la base de datos, si hubiésemos querido guardar los cambios tendríamos que haber ejecutado commit.

Durante la transacción los resultados que se muestran están en memoria, pero no físicamente guardados en la base de datos, si abrimos otra sesión de mysql esos cambios no los veremos hasta que se realice un commit.