Conceptos Clave: Transacciones, ACID y Aislamiento en Bases de Datos

¿Qué son las propiedades ACID?

ACID es un acrónimo que representa un conjunto de cuatro propiedades fundamentales que garantizan que las transacciones en las bases de datos se realicen de forma confiable y consistente.

• Atomicidad: Asegura que una transacción se complete por completo o no se realice en absoluto. Si alguna parte de la transacción falla, toda la transacción se revierte (rollback), dejando la base de datos en su estado original.
• Consistencia: Garantiza que una transacción lleve la base de datos de un estado válido a otro estado válido. Las transacciones no pueden violar las reglas de integridad de la base de datos.
• Aislamiento: Asegura que múltiples transacciones que se ejecutan concurrentemente no interfieran entre sí. El efecto de una transacción no es visible para otras transacciones hasta que se completa (commit).
• Durabilidad: Garantiza que una vez que una transacción ha sido confirmada (committed), sus cambios persistirán y no se perderán, incluso en caso de fallos del sistema (como cortes de energía o caídas del servidor).

¿Cuáles son los tres problemas comunes en transacciones concurrentes?

Cuando múltiples transacciones acceden a los mismos datos simultáneamente, pueden surgir ciertos problemas si no se gestiona adecuadamente el aislamiento. Los tres problemas más comunes son:

• Dirty Read (Lectura Sucia): Ocurre cuando una transacción lee datos que han sido modificados por otra transacción, pero que aún no han sido confirmados (committed). Si la segunda transacción falla y se revierte, la primera transacción habrá leído datos que nunca existieron realmente en un estado consistente.
• Nonrepeatable Read (Lectura No Repetible): Sucede cuando una transacción lee los mismos datos dos veces y encuentra que han sido modificados por otra transacción entre las dos lecturas. Esto significa que la segunda lectura devuelve un valor diferente al de la primera.
• Phantom Read (Lectura Fantasma): Se produce cuando una transacción ejecuta una consulta que devuelve un conjunto de filas, y luego, al ejecutar la misma consulta nuevamente, encuentra nuevas filas que cumplen los criterios de la consulta. Estas nuevas filas fueron insertadas por otra transacción concurrente.

¿Cuáles son los cuatro niveles de aislamiento que se pueden aplicar a las transacciones?

Los niveles de aislamiento definen el grado en que una transacción debe estar aislada de las modificaciones realizadas por otras transacciones concurrentes. Los niveles estándar, definidos por SQL, son:

• READ UNCOMMITTED (Lectura No Confirmada): Es el nivel de aislamiento más bajo. Permite que sucedan los tres problemas (Dirty Read, Nonrepeatable Read, Phantom Read). Las sentencias SELECT se efectúan sin realizar bloqueos, por lo tanto, los cambios hechos por una transacción pueden ser vistos por otras transacciones antes de ser confirmados.
• READ COMMITTED (Lectura Confirmada): En este nivel, una transacción solo puede leer datos que han sido confirmados por otras transacciones. Evita el problema de Dirty Read. Sin embargo, aún pueden ocurrir Nonrepeatable Read y Phantom Read.
• REPEATABLE READ (Lectura Repetible): Este nivel garantiza que, dentro de una misma transacción, las lecturas repetidas de los mismos datos devolverán siempre el mismo valor. Evita los problemas de Dirty Read y Nonrepeatable Read. Sin embargo, aún puede ocurrir Phantom Read.
• SERIALIZABLE: Es el nivel de aislamiento más alto. Garantiza que las transacciones se ejecuten de forma totalmente aislada, como si se ejecutaran una tras otra (serialmente). Evita los tres problemas (Dirty Read, Nonrepeatable Read, Phantom Read). Es el nivel más seguro pero puede tener un impacto significativo en el rendimiento debido a los bloqueos extensivos.

Nivel de aislamiento por defecto en InnoDB (MySQL)

El nivel de aislamiento por defecto para las tablas con motor InnoDB en MySQL es REPEATABLE READ. Este nivel se puede alterar modificando la configuración en el fichero my.cnf o dinámicamente.

Diferencias entre motores InnoDB y MyISAM en MySQL

Los motores de almacenamiento InnoDB y MyISAM son dos de los más comunes en MySQL, con diferencias clave en sus características y casos de uso:

• InnoDB: Es el motor por defecto en las versiones recientes de MySQL. Soporta transacciones que cumplen las propiedades ACID. Implementa bloqueo a nivel de fila, lo que permite una mayor concurrencia para operaciones de escritura. Soporta claves foráneas y restricciones de integridad referencial. Es adecuado para aplicaciones que requieren alta fiabilidad de datos y concurrencia, como sistemas bancarios o de comercio electrónico.
• MyISAM: No soporta transacciones ni cumple las propiedades ACID. Utiliza bloqueo a nivel de tabla, lo que puede limitar la concurrencia, especialmente en operaciones de escritura intensivas. No soporta claves foráneas ni integridad referencial a nivel de motor. Es más rápido para operaciones de lectura y adecuado para aplicaciones donde la velocidad de lectura es crítica y no se requieren transacciones complejas o alta concurrencia de escritura.

Ejemplo de transacción bancaria y manejo de errores

Consideremos una tabla para almacenar información sobre cuentas bancarias:

CREATE TABLE cuentas (
    id INTEGER UNSIGNED PRIMARY KEY,
    saldo DECIMAL(11,2) CHECK (saldo >= 0)
);

Supongamos que queremos realizar una transferencia de dinero entre dos cuentas bancarias con la siguiente transacción:

BEGIN TRANSACTION;
UPDATE cuentas SET saldo = saldo - 100 WHERE id = 20;
UPDATE cuentas SET saldo = saldo + 100 WHERE id = 30;
COMMIT;

¿Qué ocurriría si el sistema falla o si se pierde la conexión entre el cliente y el servidor después de realizar la primera sentencia UPDATE?

Dado que se está utilizando un motor transaccional como InnoDB y la transacción se inició con BEGIN TRANSACTION, si el sistema falla o la conexión se pierde antes de que se ejecute la sentencia COMMIT, la propiedad de Atomicidad de ACID garantiza que la transacción completa será revertida (rollback). Ninguno de los cambios (ni la resta en la cuenta 20 ni la suma en la cuenta 30) se aplicará de forma permanente a la base de datos. La base de datos permanecerá en el estado en el que se encontraba antes de iniciar la transacción.

¿Qué ocurriría si no existiese alguna de las dos cuentas (id = 20 y id = 30)?

Si una o ambas cuentas no existen, las sentencias UPDATE correspondientes simplemente no encontrarán filas que coincidan con la condición WHERE. La sentencia UPDATE afectaría 0 filas. La transacción continuaría y, si no hay otros errores, se ejecutaría el COMMIT. La transacción se completaría con éxito, pero sin realizar ninguna modificación en el saldo de las cuentas inexistentes. La propiedad de Consistencia se mantendría, ya que no se violaría ninguna regla de integridad.

¿Qué ocurriría en el caso de que la primera sentencia UPDATE falle porque hay menos de 100 € en la cuenta y no se cumpla la restricción del CHECK establecida en la tabla?

Si la primera sentencia UPDATE intenta establecer un saldo negativo en la cuenta 20 (porque el saldo actual es menor a 100) y la restricción CHECK (saldo >= 0) está activa y es soportada por el motor (InnoDB la soporta a partir de MySQL 8.0.16), la sentencia UPDATE fallará. Cuando una sentencia dentro de una transacción falla, la transacción completa entra en un estado de error. Dependiendo de la configuración y el cliente, el sistema de base de datos (InnoDB) generalmente realizará un rollback automático de la transacción para mantener la Atomicidad y la Consistencia. Ninguno de los cambios (el intento fallido en la cuenta 20 y la suma en la cuenta 30, si se hubiera llegado a ejecutar) se aplicaría de forma permanente.

Opciones ON DELETE y ON UPDATE en claves foráneas

Las opciones ON DELETE y ON UPDATE se utilizan en la definición de claves foráneas (FOREIGN KEY) para especificar la acción que debe realizar el sistema de base de datos cuando se intenta borrar o actualizar una fila en la tabla padre que tiene filas relacionadas en la tabla hija.

• RESTRICT: Es la acción por defecto en muchos sistemas. No permite borrar o actualizar una fila en la tabla padre si existen filas relacionadas en la tabla hija. La operación falla con un error.
• CASCADE: Si se borra o actualiza una fila en la tabla padre, las filas correspondientes en la tabla hija que hacen referencia a esa fila también se borran o actualizan automáticamente.
• SET NULL: Si se borra o actualiza una fila en la tabla padre, los valores de las columnas de la clave foránea en las filas correspondientes de la tabla hija se establecen a NULL. Esto solo es posible si las columnas de la clave foránea en la tabla hija permiten valores NULL.
• NO ACTION: Es similar a RESTRICT. La operación en la tabla padre se permite solo si no hay filas relacionadas en la tabla hija. En algunos sistemas, puede diferir ligeramente de RESTRICT en el momento de la verificación.
• SET DEFAULT: Si se borra o actualiza una fila en la tabla padre, los valores de las columnas de la clave foránea en las filas correspondientes de la tabla hija se establecen a su valor por defecto. Esta opción no es soportada por el motor de almacenamiento InnoDB en MySQL.