Apuntes sobre Mysql

“SET PASSWORD has no significance for user 'root'@'localhost'”

Transacciones: todo o nada

Una transacción es un conjunto de operaciones SQL que se ejecutan como una unidad indivisible: o se completan todas, o no se ejecuta ninguna. No hay término medio.

Parece una restricción, pero en realidad es una garantía. Cuando manejas datos donde la coherencia importa, necesitas saber que si algo falla a mitad del proceso, la base de datos queda exactamente como estaba antes.

El problema sin transacciones

Imagina una transferencia bancaria: restar 100€ de la cuenta de Ana y sumar 100€ a la cuenta de Luis. Son dos operaciones SQL:

UPDATE cuentas SET saldo = saldo - 100 WHERE titular = 'Ana';
UPDATE cuentas SET saldo = saldo + 100 WHERE titular = 'Luis';

¿Qué pasa si el servidor se cae, la conexión se corta o hay un error entre la primera y la segunda instrucción? Sin transacciones: se descuentan los 100€ de Ana pero nunca llegan a Luis. El dinero desaparece de la base de datos.

Con una transacción, ese escenario es imposible: si la segunda operación no se completa, la primera se deshace automáticamente.

ACID: las cuatro propiedades de una transacción

Las transacciones se definen por cuatro propiedades, conocidas por el acrónimo ACID:

  • Atomicidad (Atomicity): todo o nada. Si alguna operación falla, se deshacen todas las anteriores del mismo bloque.

  • Consistencia (Consistency): la base de datos siempre pasa de un estado válido a otro estado válido. Las reglas de integridad (claves foráneas, restricciones) se respetan siempre.

  • Aislamiento (Isolation): dos transacciones simultáneas no se interfieren entre sí. Cada una trabaja como si fuera la única que existe en ese momento.

  • Durabilidad (Durability): una vez confirmada (COMMIT), la transacción persiste aunque el servidor se caiga un segundo después. Los datos están a salvo.

Los tres comandos fundamentales

BEGIN / START TRANSACTION

Abre la transacción. A partir de aquí, todas las operaciones SQL son provisionales hasta que se confirmen o se deshagan. Los dos comandos son equivalentes:

BEGIN;
-- o equivalentemente:
START TRANSACTION;

COMMIT

Confirma todas las operaciones de la transacción y las hace permanentes. Es el punto de no retorno:

COMMIT;

ROLLBACK

Deshace todas las operaciones desde el último BEGIN. La base de datos queda exactamente como estaba antes de abrir la transacción:

ROLLBACK;

Ejemplo completo: transferencia bancaria

-- Abrimos la transacción
BEGIN;

-- Primera operación: descontar de Ana
UPDATE cuentas SET saldo = saldo - 100 WHERE titular = 'Ana';

-- Segunda operación: abonar a Luis
UPDATE cuentas SET saldo = saldo + 100 WHERE titular = 'Luis';

-- Todo fue bien: confirmamos
COMMIT;

Si algo falla entre el BEGIN y el COMMIT:

BEGIN;

UPDATE cuentas SET saldo = saldo - 100 WHERE titular = 'Ana';

-- Error, conexión caída, lógica de negocio fallida...
-- Deshacemos todo:
ROLLBACK;
-- Ana conserva sus 100€, Luis no recibe nada. Estado coherente.

Ejemplo: registro de usuario

En muchas aplicaciones web, registrar un usuario implica escribir en varias tablas a la vez (datos de acceso, perfil, configuración inicial). Si una falla, el registro debe quedar limpio:

BEGIN;

-- Crear el usuario
INSERT INTO usuarios (email, password_hash) VALUES ('ana@ejemplo.com', '$2y$...');

-- Obtener el ID recién insertado
SET @nuevo_id = LAST_INSERT_ID();

-- Crear su perfil
INSERT INTO perfiles (usuario_id, nombre, fecha_registro)
VALUES (@nuevo_id, 'Ana García', NOW());

-- Asignar rol por defecto
INSERT INTO usuarios_roles (usuario_id, rol_id) VALUES (@nuevo_id, 1);

-- Todo correcto: confirmar
COMMIT;

Si cualquiera de los tres INSERT falla, el ROLLBACK deja la base de datos sin el usuario, sin el perfil y sin el rol. No hay datos huérfanos.

SAVEPOINT: puntos de guardado dentro de una transacción

Un SAVEPOINT es un punto intermedio dentro de una transacción al que puedes hacer ROLLBACK sin deshacer todo desde el principio:

BEGIN;

INSERT INTO pedidos (cliente_id, total) VALUES (5, 150.00);

SAVEPOINT pedido_creado;  -- Marcamos este punto

INSERT INTO lineas_pedido (pedido_id, producto_id, cantidad) VALUES (LAST_INSERT_ID(), 10, 2);
INSERT INTO lineas_pedido (pedido_id, producto_id, cantidad) VALUES (LAST_INSERT_ID(), 99, 1);

-- Si algo falla en las líneas, volvemos al punto anterior sin borrar el pedido
ROLLBACK TO SAVEPOINT pedido_creado;

-- O si todo fue bien:
COMMIT;

AUTOCOMMIT: el comportamiento por defecto

Por defecto, MySQL opera en modo autocommit: cada sentencia SQL es una transacción automática que se confirma inmediatamente. Por eso cuando ejecutas un UPDATE sin BEGIN, el cambio es permanente al instante.

Puedes desactivarlo para una sesión:

SET autocommit = 0;  -- Desactivar autocommit

-- A partir de aquí, necesitas COMMIT explícito para confirmar
UPDATE cuentas SET saldo = saldo - 100 WHERE titular = 'Ana';
COMMIT;  -- Sin esto, el cambio no se guarda

En la práctica, es más claro y predecible usar BEGIN / COMMIT explícitamente y dejar autocommit activado.

Nota: Las transacciones solo funcionan con motores que las soporten. InnoDB (el predeterminado desde MySQL 5.5) las soporta completamente. MyISAM no: si usas BEGIN con una tabla MyISAM, las operaciones se ejecutan como si no hubiera transacción.

¿Cuándo usar transacciones?

Siempre que tengas dos o más operaciones que deben ser atómicas:

  • Transferencias de dinero o puntos entre cuentas
  • Registro de usuario (varias tablas relacionadas)
  • Procesamiento de un pedido (crear pedido + actualizar stock + registrar pago)
  • Importación de datos donde o entran todos los registros o no entra ninguno
  • Cualquier operación que modifique varias tablas relacionadas entre sí

Si solo haces una operación (un INSERT simple, un SELECT), no necesitas transacción explícita: el autocommit ya la gestiona.

Transacciones desde PHP

Cuando controlas la transacción desde el lenguaje de programación, el patrón habitual es abrir la transacción, ejecutar las operaciones dentro de un bloque de control de errores y hacer COMMIT si todo fue bien o ROLLBACK si algo falló:

-- Lo que MySQL recibe desde PHP sería equivalente a:
START TRANSACTION;
INSERT INTO ...;
UPDATE ...;
COMMIT;   -- o ROLLBACK si hubo un error en PHP

Veremos la implementación concreta en el manual de PHP, cuando trabajemos con la extensión mysqli.

En la próxima lección: Consultas avanzadas: JOINs múltiples, funciones agregadas, subconsultas y vistas para análisis complejo de datos.

TOP