Hay situaciones en las que necesitamos hacer más de una consulta al mismo tiempo, y todas tienen que ser correctas para que los datos sean consistentes y tengan sentido, de otro modo tendríamos información “colgada” y desvinculada. En el mejor de los casos esta información provocaría un desperdicio de espacio en la base de datos, pero lo más probable es que además lleve a errores a la hora de mostrarse y de hacer cálculos con ella.

Parte de este problema se resuelve teniendo una buena estructura de base de datos, con claves foráneas relacionando los campos correspondientes.

Pero para mayor seguridad, es bueno usar lo que en jerga databasera se conoce como transacciones.

Las transacciones sirven para asegurar la consistencia de la información, asegurando que un conjunto de sentencias se ejecuten correctamente, o no se ejecuten.

En un principio MySQL no las soportaba, esto cambió a partir de la versión 4, y con el uso de tablas InnoDB. Nótese que el motor por defecto para las tablas en MySQL hasta las versiones 5.* era MyISAM, que no soporta transacciones.

Un ejemplo

Supongamos que un sitio web bancario tiene 2 usuarios, ambos trabajando sobre la misma cuenta.

El usuario 1 pide incrementar su saldo en 10, mientras que el usuario 2 pide disminuirlo (a través de un formulario, por ejemplo)

El programador del sistema no puede decidir el orden en el que se ejecutarán las consultas, así que bien podría suceder lo siguiente:

bal1 := ... SELECT balance FROM cuentas WHERE cuenta=X -- usuario 1
bal2 := ... SELECT balance FROM cuentas WHERE cuenta=X -- usuario 2

En este punto, existen dos copias de la aplicación que contienen una variable $balance cada una. Supongamos que ambas necesitan actualizar el valor en la base de datos:

UPDATE cuentas SET balance=(bal1+10) WHERE cuenta=X -- usuario 1
UPDATE cuentas SET balance=(bal2-10) WHERE cuenta=X -- usuario 2

El resultado es que ambas copias del programa ejecutaron sus consultas con la información de balance que tenían, por lo que el resultado final es como si la consulta del usuario 1 no se hubiera ejecutado nunca, ya que el usuario 2 actualiza el registro con información vieja. Al final, en vez de quedar con el mismo saldo, la cuenta termina perdiendo 10.

Lo que se necesita para este conjunto de consultas, es lo que se denomina ACID, un acrónimo inglés que quiere decir Atomicidad, Consistencia, Aislamiento y Durabilidad. Recomiendo leer la información de Wikipedia para entender de qué se trata esto, pero lo importante es lo siguiente: las transacciones son un conjunto de consultas que se ejecutan como si fuesen una. Y por esto, permiten asegurar la consistencia de los datos, ya que si en mitad del proceso una consulta falla, todos los cambios producidos por consultas anteriores pueden ser revertidos.

¿Cómo usar transacciones?

Usar transacciones es muy simple: antes de ejecutar la primer consulta, se ejecuta una que solamente contiene BEGIN. Luego se ejecutan las consultas que deban ejecutarse. Si éstas resultan exitosas, se termina la transacción con COMMIT, lo cual provoca que los cambios hechos por las consultas anteriores sean permanentes. Si las consultas fallan en algún paso, se puede volver al estado anterior al comienzo de la transacción ejecutando ROLLBACK

Aunque los datos no sean realmente escritos a la o las tablas involucradas hasta ejecutar el COMMIT, las consultas devuelven lo mismo que si lo fueran, es decir, para saber si una consulta falló basta con ver el valor de retorno de mysql_query y para ver el número de filas afectadas sigue valiendo usar mysql_num_rows

Mientras la transacción está ejecutándose, los datos (en el caso de InnoDB las filas y en el caso de MyISAM las tablas) afectados quedan bloqueados, nadie puede acceder a ellos. Cualquier consulta que tenga que ver con los mismos datos será demorada hasta que la transacción termine. Esto implica que usar transacciones es un poco más lento que no usarlas, pero a la vez implica que los datos involucrados no pueden ser modificados por otra copia de la aplicación, y por lo tanto se evita la situación planteada al principio como ejemplo.

Dicha situación, implementada de forma “transaccional” en PHP, quedaría:

 1 <?php
 2 mysql_query("BEGIN");
 3 $balance = ..... mysql_query("SELECT balance FROM cuentas WHERE cuenta=X");
 4 $resultado = mysql_query("UPDATE cuentas SET balance=$balance+10 WHERE cuenta=X");
 5 
 6 if ($resultado !== false)
 7     // la consulta fue exitosa
 8     mysql_query("COMMIT");
 9 else
10     mysql_query("ROLLBACK");

(falta revisar que la consulta que obtiene el balance sea exitosa, pero de todas formas esto es solo un ejemplo)

autocommit

MySQL tiene una variable de entorno llamada autocommit, que por defecto tiene el valor 1. Configurado de esta manera no se pueden usar transacciones, porque MySQL automáticamente hace un COMMIT luego de cada consulta.

Para usar transacciones entonces, hay que poner autocommit a 0 (desactivarlo).

Nota: si autocommit se pone a cualquier número N > 1, MySQL hace un COMMIT automático luego de N consultas.

Para cambiar el valor de autocommit, simplemente se usa

SET autocommit = 0;

He aquí lo que sé sobre las transacciones. Es un tema interesante y cualquiera que programe un sistema relativamente complejo debería considerar si necesita transacciones para asegurar la “salud” de sus tablas y datos.

Enlaces relacionados

• Transactions in MySQL - DatabaseJournal
• Transactions in MySQL - Webmonkey
• MySQL - Wikipedia
• ACID - Wikipedia
• MyISAM - Wikipedia
• InnoDB - Wikipedia
• Transacción - Wikipedia

Como lamentablemente suele suceder, las versiones en inglés de los artículos de Wikipedia contienen información más detallada que las versiones en español.