Transacciones

Una transacción agrupa un conjunto de sentencias SQL en una unidad atómica: o se ejecutan todas con éxito o ninguna. Esto es fundamental para operaciones como transferencias bancarias.

-- Tabla de cuentas
CREATE TABLE `cuenta` (
    `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
    `nombre` VARCHAR(255) NOT NULL,
    `saldo` DECIMAL(10,2) NOT NULL
);

INSERT INTO cuenta(nombre, saldo) VALUES('Ana', 1000);
INSERT INTO cuenta(nombre, saldo) VALUES('Luis', 1000);

-- Operación de transferencia
UPDATE cuenta SET saldo = saldo - 100 WHERE nombre = 'Ana';
UPDATE cuenta SET saldo = saldo + 100 WHERE nombre = 'Luis';

Sin transacciones, un fallo tras la primera actualziación dejaría los datos inconsistentes. Las transacciones garantizan las propiedades ACID:

• Atomicidad: Todas las operaciones se completan o ninguna.
• Consistencia: Los datos cumplen todas las reglas definidas antes y después de la transacción.
• Aislamiento: Las transacciones concurrentes no interfieren entre sí.
• Durabilidad: Los cambios confirmados persisten incluso tras un fallo.

Uso de Transacciones

Para usar transacciones, el motor de almacenamiento debe soportarlas. En MySQL, InnoDB es el motor transaccional por defecto. Se controlan con:

• START TRANSACTION o BEGIN para iniciar.
• COMMIT para confirmar cambios.
• ROLLBACK para deshacer cambios.
• SET autocommit para habilitar/deshabilitar el modo de confirmación automática.

Ejemplo con rollback:

mysql> START TRANSACTION;
mysql> SELECT * FROM cuenta;
+----+--------+---------+
| id | nombre | saldo   |
+----+--------+---------+
| 1  | Ana    | 1000.00 |
| 2  | Luis   | 1000.00 |
+----+--------+---------+

mysql> UPDATE cuenta SET saldo = saldo - 100 WHERE nombre = 'Ana';
mysql> UPDATE cuenta SET saldo = saldo + 100 WHERE nombre = 'Luis';

mysql> SELECT * FROM cuenta;
+----+--------+---------+
| id | nombre | saldo   |
+----+--------+---------+
| 1  | Ana    | 900.00  |
| 2  | Luis   | 1100.00 |
+----+--------+---------+

mysql> ROLLBACK;
mysql> SELECT * FROM cuenta;
+----+--------+---------+
| id | nombre | saldo   |
+----+--------+---------+
| 1  | Ana    | 1000.00 |
| 2  | Luis   | 1000.00 |
+----+--------+---------+

Ejemplo con commit:

mysql> START TRANSACTION;
mysql> UPDATE cuenta SET saldo = saldo - 100 WHERE nombre = 'Ana';
mysql> UPDATE cuenta SET saldo = saldo + 100 WHERE nombre = 'Luis';
mysql> COMMIT;
mysql> SELECT * FROM cuenta;
+----+--------+---------+
| id | nombre | saldo   |
+----+--------+---------+
| 1  | Ana    | 900.00  |
| 2  | Luis   | 1100.00 |
+----+--------+---------+

Por defecto, autocommit está activado. Se puede desactivar:

mysql> SET AUTOCOMMIT = 0;

Con autocommit = 0, cada sentencia DML no se confirma hasta que se ejecute explícitamente COMMIT o ROLLBACK.

InnoDB y el modelo ACID

InnoDB implementa ACID con diversas técnicas:

• Atomicidad: mediante Undo Log.
• Consistencia: doble buffer de escritura (doublewrite buffer) y recuperación tras fallos.
• Aislamiento: bloqueos y MVCC (Control de Concurrencia Multiversión).
• Durabilidad: Redo Log, innodb_flush_log_at_trx_commit, sync_binlog, etc.

La consistencia es el objetivo final: se logra cuando se satisfacen atomicidad, aislamiento y durabilidad.

Implementación de la Atomicidad

Antes de modificar un dato, InnoDB guarda el estado anterior en el Undo Log. Si ocurre un error, se lee el Undo Log y se aplican las operaciones inversas para revertir los cambios. Así se asegura que todas las sentencias de una transacción se ejecuten o ninguna.

Implementación de la Durabilidad

Para evitar pérdidas de datos tras un fallo, InnoDB escribe todas las modificaciones en el Redo Log antes de escribirlas en los archivos de datos. Al reiniciar, se reprocesan las entradas del Redo Log que no se hayan persistido aún.

Implementación del Aislamiento

El aislamiento se logra mediante bloqueos y MVCC. MySQL puede atender múltiples clientes simultáneamente; los bloqueos evitan que transacciones concurrentes interfieran. Los niveles de aislamiento definen qué comportamientos se permiten (como lecturas sucias, lecturas no repetibles, etc.).

Bloqueos en InnoDB

InnoDB utiliza diferentes tipos de bloqueos:

• Bloqueos compartidos (S): permiten leer un registro mientras otros también pueden leerlo, pero no escribir.
• Bloqueos exclusivos (X): permiten modificar un registro e impiden cualquier otro bloqueo (lectura o escritura) sobre el mismo.
• Bloqueos de intención (IS, IX): son bloqueos a nivel de tabla que indican que una transacción planea adquirir un bloqueo compartido o exclusivo sobre filas de esa tabla. Mejoran la eficiencia al evitar escanera todas las filas para saber si hay bloqueos.
• Bloqueos de registro: bloquean un índice específico (por ejemplo, SELECT ... FOR UPDATE).
• Bloqueos de brecha (Gap Locks): bloquean el espacio entre registros de índice para evitar inserciones que causarían lecturas fantasma.
• Next-Key Locks: combinación de bloqueo de registro y bloqueo de brecha, utilizados por defecto en el nivel REPEATABLE READ para prevenir lecturas fantasma.
• Bloqueos de intención de inserción (Insert Intention Locks): un tipo especial de bloqueo de brecha que permite que varias transacciones inserten en el mismo hueco si es en posiciones diferentes.
• Bloqueos AUTO-INC: bloqueo a nivel de tabla para garantizar valores únicos en columnas AUTO_INCREMENT.

Los bloqueos se pueden monitorizar en performance_schema.data_locks.

Ejemplo de consulta con bloqueo compartido (modo lectura):

SELECT * FROM cuenta WHERE id < 2 FOR SHARE;   -- MySQL 8.0 en adelante
SELECT * FROM cuenta WHERE id < 2 LOCK IN SHARE MODE; -- versiones anteriores

Ejemplo con bloqueo exclusivo:

SELECT * FROM cuenta WHERE id = 1 FOR UPDATE;

Deadlocks (Interbloqueos)

Ocurren cuando dos transacciones esperan indefinidamente por recursos que la otra posee. InnoDB detecta deadlocks automáticamente y deshace una de las transacciones. Se puede consultar información con SHOW ENGINE INNODB STATUS.

Condiciones necesarias para un deadlock:

• Exclusión mutua: un recurso no puede ser compartido.
• No apropiación: un proceso no puede arrebatar el recurso a otro.
• Retener y esperar: un proceso retiene un recurso mientras espera otro.
• Espera circular: existe un ciclo de procesos donde cada uno espera al siguiente.

Para evitar deadlocks:

• Usar transacciones cortas.
• Acceder a las tablas siempre en el mismo orden.
• Utilizar índices adecuados para reducir bloqueos.
• Establecer un timeout de bloqueo (innodb_lock_wait_timeout).
• Emplear niveles de aislamiento más bajos cuando sea posible.

Niveles de Aislamiento

MySQL (InnoDB) ofrece cuatro niveles de aislamiento:

1. READ UNCOMMITTED: permite lecturas sucias (leer datos no confirmados). Muy inseguro.
2. READ COMMITTED: evita lecturas sucias, pero permite lecturas no repetibles (una misma consulta puede dar resultados diferentes dentro de la misma transacción).
3. REPEATABLE READ (por defecto): garantiza que una misma consulta devuelva los mismos resultados durante toda la transacción. Previene lecturas sucias y no repetibles, y mediante Next-Key Locks también evita la mayoría de las lecturas fantasma.
4. SERIALIZABLE: el nivel más alto; todas las transacciones se ejecutan en serie, eliminando cualquier concurrencia y todos los problemas de lectura.

Para ver y cambiar el nivel de aislamiento:

-- Ver nivel actual
SELECT @@GLOBAL.transaction_isolation;
SELECT @@SESSION.transaction_isolation;

-- Establecer nivel
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

No se puede cambiar el aislamiento dentro de una transacción en curso.

Ejemplo de problema: Lectura no repetible en READ COMMITTED

-- Sesión A (nivel READ COMMITTED)
START TRANSACTION;
SELECT saldo FROM cuenta WHERE nombre = 'Luis'; -- devuelve 1100.00

-- Sesión B
UPDATE cuenta SET saldo = 500 WHERE nombre = 'Luis';
COMMIT;

-- Sesión A (misma transacción)
SELECT saldo FROM cuenta WHERE nombre = 'Luis'; -- ahora devuelve 500.00 (lectura no repetible)

En REPEATABLE READ no ocurriría porque se usa una instantánea consistente (MVCC).

Control de Concurrencia Multiversión (MVCC)

MVCC permite que las transacciones lean datos consistentes sin bloquearse entre sí. Se basa en:

• Versiones de registros (Undo Log): cada modificación (UPDATE o DELETE) crea una nueva versión del registro, encadenada a la anterior mediante punteros roll_pointer. Así se forma una cadena de versiones.
• ReadView: cuando una transacción comienza a leer, construye una vista consistente que incluye las transacciones activas en ese momento. Las reglas para determinar qué versión es visible son:
  1. Si la versión fue creada por la propia transacción (m_creator_trx_id), es visible.
  2. Si el ID de transacción de la versión es menor que el ID mínimo activo (m_up_limit_id), significa que ya se confirmó antes de la transacción actual, luego es visible.
  3. Si el ID es mayor o igual que el límite superior (m_low_limit_id), la versión se creó después de la ReadView, no es visible.
  4. Si el ID está entre m_up_limit_id y m_low_limit_id, se verifica si está en la lista de transacciones activas. Si no está, la versión ya se confirmó y es visible; si está, no es visible y se revisa la versión anterior.

MVCC resuelve las lecturas sucias y las lecturas no repetibles en REPEATABLE READ, pero las lecturas fantasma requieren además Next-Key Locks.