transacciones en mysql con php

Primero de todo aclarar el tipo de engine que se está utilizando en MySql. Normalmente hay dos engines clásicos que podemos utilizar en MySql: MyISAM e InnoDB. Inicialmente el engine por defecto era MyISAM, pero la tendencia es utilizar InnoDB por la posibilidad de utilizar transacciones (MyISAM no tiene), bloqueo de registros e integridad referencial.

Actualmente la empresa que desarrolla InnoDB (InnoBase) fue comprada por Oracle. También se está trabajando en otro engine para MySql 6 llamado Falcon. Aquí tenéis un benchmark entre MyISAM vs InnoDB vs Falcon muy interesante.
He leído por algún sitio que la creación de Falcon viene a suplir la falta de engine transaccional propio de MySql y la compra de InnoDB por parte de Oracle.

Si lo que queremos es velocidad en las consultas lo mejor es utilizar MyISAM. Si lo que queremos es seguridad en nuestra aplicación lo recomendable es InnoDB (y en el futuro Falcon).
Por tanto, para seguir con los siguientes ejemplos, tenemos que tener un schema de MySql de tipo InnoDB.

Lo que vamos hacer con el siguiente ejemplo es el típico ejemplo de transacción bancaria de donde sacamos dinero de la cuenta "12345" y la transferimos a la cuenta "12346".

Tenemos la tabla "cuentas" con los siguientes valores.

mysql> select * from cuentas;
+----+------------+-------+
| id | num_cuenta | saldo |
+----+------------+-------+
|  1 |      12345 |  1200 |
|  2 |      12346 |     0 |
+----+------------+-------+
2 rows in set (0.00 sec)

Si ejecutamos el siguiente código PHP:

$cuenta_origen  = 12345;
$cuenta_destino = 12346;
$importe_mover  = 1000;

mysql_select_db($database, $bancobb);

$sql = "UPDATE cuentas SET saldo=saldo-$importe_mover 
        WHERE num_cuenta=$cuenta_origen";
mysql_query($sql, $bancobb) or die(mysql_error());

$sql = "UPDATE cuentas SET saldo=saldo+$importe_mover 
        WHERE num_cuenta=$cuenta_destino";
mysql_query($sql, $bancobb) or die(mysql_error());

echo "Fin...";

Lo que hemos hecho es quitar 1000€ de la cuenta "12345" y transferirla a la cuenta "12346". Si miramos el resultado de la tabla cuentas tenemos:

mysql> select * from cuentas;
+----+------------+-------+
| id | num_cuenta | saldo |
+----+------------+-------+
|  1 |      12345 |   200 |
|  2 |      12346 |  1000 |
+----+------------+-------+
2 rows in set (0.00 sec)

Que pasaría si entre un UPDATE y el otro UPDATE nuestro servidor tiene un problema y se reinicia sólo (un corte de luz o un reinicio del admin). Volvamos a dejar la tabla de cuentas como estaba y provocamos un "corte de luz" entre un UPDATE y otro UPDATE.

$cuenta_origen  = 12345;
$cuenta_destino = 12346;
$importe_mover  = 1000;

mysql_select_db($database, $bancobb);

$sql = "UPDATE cuentas SET saldo=saldo-$importe_mover 
        WHERE num_cuenta=$cuenta_origen";
mysql_query($sql, $bancobb) or die(mysql_error());

echo "Opss! se fue la luz...";
exit();

$sql = "UPDATE cuentas SET saldo=saldo+$importe_mover 
        WHERE num_cuenta=$cuenta_destino";
mysql_query($sql, $bancobb) or die(mysql_error());

echo "Fin...";

Si ejecutamos ahora el script y miramos el contenido de la tabla vemos como hemos quitado 1000€ de una cuenta, pero la otra sigue estando a 0€.
Hemos hecho desaparecer 1000€. Explica tú ahora al banco que has cometido un error en la programación y que has hecho desaparecer 1000€ de 1000 usuarios diferentes.

mysql> select * from cuentas;
+----+------------+-------+
| id | num_cuenta | saldo |
+----+------------+-------+
|  1 |      12345 |   200 |
|  2 |      12346 |     0 |
+----+------------+-------+
2 rows in set (0.00 sec)

Para hacer correcta la serie de instrucciones debemos enmarcar los dos updates dentro de una transacción. Si utilizamos transacciones aseguraremos las 4 propiedades básicas: atomicidad, consistencia, aislamiento y durabilidad.

+ atomicidad: o se realiza todo o nada.
+ consistencia: si se viola alguna regla de integridad no se realizará la operación.
+ aislamiento: las operaciones de una transacción no influyen a la operaciones de otra transacción aunque estén trabajando con los mismos datos.
+ durabilidad: una vez hecho el commit los datos persistirán en la base de datos.

Para arrancar en MySql una transacción utilizamos la sentencia Sql "START TRANSACCTION". Para finalizarla utilizaremos un "COMMIT" si queremos guardar los cambios o un "ROLLBACK" si queremos cancelar toda la transacción. Recuerda que la idea es "o se ejecuta todo o no se ejecuta nada".

$cuenta_origen  = 12345;
$cuenta_destino = 12346;
$importe_mover  = 1000;

mysql_select_db($database, $bancobb);

$sql = "START TRANSACTION";
mysql_query($sql, $bancobb) or die(mysql_error());

$sql = "UPDATE cuentas SET saldo=saldo-$importe_mover 
        WHERE num_cuenta=$cuenta_origen";
mysql_query($sql, $bancobb) or die(mysql_error());

$sql = "UPDATE cuentas SET saldo=saldo+$importe_mover 
        WHERE num_cuenta=$cuenta_destino";
mysql_query($sql, $bancobb) or die(mysql_error());

$sql = "COMMIT";
mysql_query($sql, $bancobb) or die(mysql_error());

echo "Fin...";

Si miramos el resultado veremos como al igual que en el primer ejemplo movemos 1000€ de una cuenta a otra:

mysql> select * from cuentas;
+----+------------+-------+
| id | num_cuenta | saldo |
+----+------------+-------+
|  1 |      12345 |   200 |
|  2 |      12346 |  1000 |
+----+------------+-------+
2 rows in set (0.00 sec)

Provocamos ahora un "corte de luz", pero utilizando transacciones, tenemos:

$cuenta_origen  = 12345;
$cuenta_destino = 12346;
$importe_mover  = 1000;

mysql_select_db($database, $bancobb);

$sql = "START TRANSACTION";
mysql_query($sql, $bancobb) or die(mysql_error());

$sql = "UPDATE cuentas SET saldo=saldo-$importe_mover 
        WHERE num_cuenta=$cuenta_origen";
mysql_query($sql, $bancobb) or die(mysql_error());

echo "Opss! se fue la luz...";
exit();

$sql = "UPDATE cuentas SET saldo=saldo+$importe_mover 
        WHERE num_cuenta=$cuenta_destino";
mysql_query($sql, $bancobb) or die(mysql_error());

$sql = "COMMIT";
mysql_query($sql, $bancobb) or die(mysql_error());

echo "Fin...";

Si miramos ahora el contenido de la tabla vemos como no se ha producido ningún cambio. A pesar de que “se ha ido la luz” el primer UPDATE no se ha producido porque no se ha finalizado la transacción. La transacción finaliza cuando se hace el COMMIT. ¿Fácil no?. Pues a mover millones de una cuenta a otra.

mysql> select * from cuentas;
+----+------------+-------+
| id | num_cuenta | saldo |
+----+------------+-------+
|  1 |      12345 |  1200 |
|  2 |      12346 |     0 |
+----+------------+-------+
2 rows in set (0.00 sec)

Todas las sentencias SQL son atómicas por si solas. Es decir si no utilizamos transacciones después de un update o insert, este viene implícito con un commit.

Más información:
+ Transacciones MySql.
+ Errores informáticos.