Just Sherekan – Blog de Programación

En MySQL, una de las ventajas de trabajar con tablas InnoDB es el soporte para transacciones que provee. En este artículo voy a explicar que son las transacciones y como funcionan en MySQL.

Que son las transacciones

Una transacción, es básicamente un grupo de sentencias SQL agrupadas de manera que deban ejecutarse todas juntas o no ejecutarse, es decir, nunca ejecutarse unas sentencias si y otras no.

Veámoslo con un ejemplo. Imaginemos un sistema bancario, el posee una base de datos, con una tabla accounts para almacenar los datos de todas las cuentas de los clientes (el Nº de cuenta, el saldo, etc.).

Cada vez que un cliente hace una transacción, el sistema transfiere determinada cantidad de dinero de una cuenta A a una cuenta B, de esta manera:

• Hace un UPDATE de la cuenta A restándole el dinero a transferir
• Hace un UPDATE de la cuenta B sumándole el dinero

A simple vista parece simplemente perfecto… pero… ¿qué pasaría si justo después del primer UPDATE ocurriera algún fallo y se detuviera la ejecución?

¡Entonces habríamos restado el dinero de la cuenta A sin habérselo sumado a la cuenta B!

Oh no! that’s a BIG BUG!

¿Cómo lo solucionamos?

Necesitamos usar transacciones.

Una transacción funciona de la siguiente manera, ejecuta las sentencias (siguiendo con el ejemplo anterior los UPDATE para las cuentas A y B) y si llegara a haber un error, por medio de journaling restablece la base de datos tal como estaba antes de iniciar la transacción, de esta manera se asegura que las sentencias se ejecuten todas o no se ejecute ninguna.

¿Qué es journaling?

Es un mecanismo por el cual por cada sentencia que se ejecute, se crea una sentencia para restablecer los cambios realizados por esa sentencia.

Por ejemplo, si ejecutáramos un INSERT entonces se crearía una sentencia DELETE que eliminaría el registro recién creado en caso de que la transacción falle, y de esa manera restablecer todo como estaba al inicio de la transacción.

Transacciones en MySQL

El tipo de tabla que hay que usar para poder trabajar con transacciones en MySQL, es InnoDB.

Vamos a crear una tabla accounts para hacer las pruebas:
[sourcecode language="sql"]CREATE TABLE accounts (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(20) NOT NULL,
balance INT NOT NULL
) ENGINE=InnoDB;[/sourcecode]
Insertamos algunos registros:
[sourcecode language="sql"]INSERT INTO accounts (name, balance) VALUES (‘A’, 100);
INSERT INTO accounts (name, balance) VALUES (‘B’ 0);[/sourcecode]
Nos quedaría algo así:

+----+------+---------+
| id | name | balance |
+----+------+---------+
|  1 | A    |     100 |
|  2 | B    |       0 |
+----+------+---------+

Ahora vamos a hacer una transferencia de todo el dinero de la cuenta A hacia la cuenta B. Por lo que vamos a hacer un UPDATE en la cuenta A eliminando el dinero existente, y otro UPDATE en la cuenta B para añadirle dicho dinero.

Sin transacciones

[sourcecode language="sql"]UPDATE accounts SET balance=0 WHERE name=’A';
UPDATE accounts SET balance=100 WHERE name=’B';[/sourcecode]

Si ocurriera un error antes de que se ejecutase la segunda sentencia, estaríamos en un problema, ya que como dijimos antes, el dinero no se añadiría en la cuenta B y quedarían las dos cuentas con un saldo de cero.

+----+------+---------+
| id | name | balance |
+----+------+---------+
|  1 | A    |       0 |
|  2 | B    |       0 |
+----+------+---------+

Con transacciones

[sourcecode language="sql"]BEGIN
UPDATE accounts SET balance=0 WHERE name=’A';
UPDATE accounts SET balance=100 WHERE name=’B';
COMMIT[/sourcecode]

El procedimiento para crear una transacción es bastante simple:

• Iniciamos una nueva transacción con la sentencia BEGIN
• Ejecutamos todas las sentencias que queramos que pertenezcan a esa transacción
• Ejecutamos la sentencia COMMIT para aceptar la transacción. Sin ejecutar esta sentencia, todas las acciones anteriores se restablecerían. Una vez ejecutada la sentencia COMMIT, los cambios quedan de manera definitiva.
• También tenemos la sentencia ROLLBACK, que sirve para deshacer todos los cambios hechos desde el inicio de la transacción.

Veamos que pasaría si ocurriera un error y se detuviera la ejecución del sistema, usando transacciones:

[sourcecode language="sql"]BEGIN
UPDATE accounts SET balance=0 WHERE name=’A';
EXIT[/sourcecode]

Los cambios se restablecerían, ya que no se realizó el COMMIT. Entonces la tabla quedaría igual que como estaba:

+----+------+---------+
| id | name | balance |
+----+------+---------+
|  1 | A    |     100 |
|  2 | B    |       0 |
+----+------+---------+

Ahora veamos, que pasaría si hacemos el COMMIT, es decir si todo ocurre sin ningún error ni nada por el estilo:

[sourcecode language="sql"]BEGIN
UPDATE accounts SET balance=0 WHERE name=’A';
UPDATE accounts SET balance=100 WHERE name=’B';
COMMIT[/sourcecode]

Entonces los cambios si harían efecto esta vez:

+----+------+---------+
| id | name | balance |
+----+------+---------+
|  1 | A    |       0 |
|  2 | B    |     100 |
+----+------+---------+

Veamos también un ejemplo de la sentencia ROLLBACK:

[sourcecode language="sql"]BEGIN
UPDATE accounts SET balance=200 WHERE name=’A';
UPDATE accounts SET balance=300 WHERE name=’B';
ROLLBACK
UPDATE accounts SET balance=50 WHERE name=’A';
COMMIT[/sourcecode]

Al ejecutar la sentencia ROLLBACK después de los dos primeros UPDATES, entonces estos quedarían descartados, y solo se realizaría el tercero, quedando así la tabla:

+----+------+---------+
| id | name | balance |
+----+------+---------+
|  1 | A    |      50 |
|  2 | B    |       0 |
+----+------+---------+