Blog de Carlos Zacarías: Transacciones y Niveles de Aislamiento en MySQL

Hola a todos, está vez he decidido postear algo respecto a transacciones en MySQL, para muchos un tema conocido pero para otros no. Este post intentará despejar algunas dudas.

¿Qué es una transacción?

Son un conjunto de órdenes que se ejecutan en un sistema de gestión de base de datos (MySQL, SQL Server, Oracle, PostgreSQL) formando una unidad de trabajo (a esto se le llama atomicidad)

MySQL (SGDB) es transaccional, porque es capaz de mantener la integridad de los datos, haciendo que las transacciones no finalicen en un estado intermedio. Es por ello que se le atribuye el acrónimo ACID.

Si por alguna causa el sistema debe cancelar la transacción, empieza a deshacer las órdenes ejecutadas hasta dejar la base de datos en su estado inicial (llamado punto de integridad).

ACID:

Atomicidad: se entiende que una transacción no es divisible, o sea, que deben ejecutarse todas las instrucciones de una transacción como una unidad lógica de trabajo e indivisible, en caso de que alguna falle no se ejecuta ninguna.
Coherencia: significa que sólo datos válidos pueden ser grabados en la base de datos. Si se ejecuta una transacción que compromete la coherencia interna de la base de datos, toda la transacción debe cancelarse.
Aislamiento: las transacciones que tengan lugar simultáneamente deben ejecutarse aisladas unas de otras hasta que finalizan.
Durabilidad: la garantía de que una transacción una vez confirmada no podrá ser desecha.

Causas posibles para que una transacción falle.

Falla del hardware o software.
Alta concurrencia a una base de datos.
Algunas ejecuciones paralelas pueden intercalarse de manera que pueden dejar a la base de datos en un estado inconsistente.
Falla del sistemas operativo.
Falla de energía eléctrica.
Falla en el software de base de datos.
Etc.

NOTA: Para sacarle mayor provecho a este post debemos ver algunos conceptos importantes como son: serialización y atomicidad.

Serialización

Antes pondré un ejemplo que nos va a ayudar a clarificar este concepto.

Ejemplo: Supongamos que en un sistema de inscripción de cursos, el curso Matemática le queda una sola vacante y dos alumnos desean llevar ese curso. Cuando los dos alumnos entran al sistema, se pueden ver los siguientes procesos:

El sistema buscará aquellos cursos que si puede llevar el alumno y tenga vacante disponible.
El alumno elige el curso Matemática.
El sistema asigna el curso Matemática al alumno que lo ha elegido.

Es posible que ambos alumnos hayan elegido el mismo curso y el sistema se los haya podido asignar dejando a la base de datos en un estado indeseable.

Entonces la serialización consiste en:

El estado de la base de datos debe quedar como si una operación fue realizada primero y otra después (a esto se le llama ejecución serializable).
Si una ejecución es serializable, nunca se le asignará a los dos alumnos el curso cuya limitante es la vacante.
Se debe tener en cuenta que no se desea que un proceso se lleve uno detrás de otro, pero si se necesita que el resultado sea serializable.

Atomicidad

Colocaré un ejemplo clásico que nos ayudará a ver con mayor claridad este concepto.

Ejemplo: En una aplicación un proceso de transferir fondos entre dos cuentas A1 y A2:

Verificar que A1 tenga suficiente dinero.
Aumenta el saldo de A2.
Disminuye el saldo de A1.

Supongamos que el sistema falla antes de ejecutar la tercera tarea, lo que genera que la base de datos se encuentre en un estado indeseable.

Entonces la atomicidad consiste en que que todas las operaciones se ejecuten o que ninguna lo haga.

Pregunta: --> ¿El uso de transacciones resuelve los problemas de atomicidad y serialización?

Rpta: "SI", porque una transacción está compuesta por un grupo de instrucciones SQL que se ejecutan atómicamente (se ejecutan todas o ninguna de ellas) y además se les exige ejecuciones serializables.

Partes de una transacción

Toda transacción comienza con la sentencia begin. Esta sentencia indica a la base de datos que se prepare porque vienen un conjuntos de instrucciones SQL que la modificaran.
Ejecución y validación del conjunto de sentencias SQL que modificarán la base de datos.
Aquí existen dos posibilidades

Si todo estuvo OK, entonces se debe ejecutar la instrucción commit la cual hace que la transacción termine de forma exitosa y hace permanente cualquier cambio realizado sobre la base de datos.
Si hubo algún problema, entonces se debe ejecutar la instrucción rollback la cual aborta la transacción y la hace terminar en forma no exitosa, cualquier cambio que la transacción pudo hacer a la BD se deshace.

Del ejemplo de transferencia de fondos entre cuenta entonces debería quedar algo así:

begin
La cuenta A1 no tiene suficientes fondos --> rollback
Se aumenta el saldo de A2 al monto especificado.
Se disminuye el saldo de A1 en el monto especificado.
commit

Niveles de aislamiento en transacciones

SQL permite definir diferentes ciertos niveles de aislamiento para el tratamiento de las transacciones.

Serializable.
Read Commited.
Repeatable Read
Read Uncommited.

Para explicarlo mejor, nos basaremos de un ejemplo:

Ejemplo:

El bar de Pepe vende dos tipos de cerveza Cristal y Pilsen a S/3.5 y S/4.0 respectivamente.
Juan hace una consulta sobre la cerveza más barata y sobre la cerveza más cara.
Pepe al mismo tiempo que Juan hace la consulta modifica la base de datos, eliminando ambas marcas de cerveza pero ingresando una nueva marca Kunstman a S/5.0
Juan ejecuta las siguientes consultas

A estas consultas les llamaremos (max) y (min) respectivamente.
Por su parte Pepe ejecuta

A estas consultas les llamaremos (del) e (ins) respectivamente.
Supongamos que ambas consultas se ejecutan simultáneamente en la base de datos.
Lo único que podemos asegurar es que (max) se ejecutó antes que (min) y que (del) se ejecuto antes que (ins)
Muestro una imagen de una posible ejecución

Juan lee que el precio máximo es de la cerveza Pilsen a S/ 4.0 y lee como mínimo el precio de Kunstman a S/ 5.0

Nivel Serializable

Si Juan ejecuta sus instrucciones con una base de datos MySQL con un nivel de aislamiento serializable, entonces la base de datos responderá con datos antes o después de la ejecución de las instrucciones de Pepe pero nunca en el medio. Por lo tanto con esto nos aseguramos que un grupo de instrucciones se ejecuten antes y otro después.

Se le consideran como el nivel máximo de aislamiento y también genera el máximo nivel de bloqueos.

Nivel Read Commited (lecturas confirmadas)

Este nivel de aislamiento evita la lectura sucia de datos. Este nivel hace que SGBD lea y devuelva información que ha sido confirmada.

Por ejemplo, Pepe ejecuta (del) e (ins) pero luego lo piensa, se arrepiente y hace rollback para deshacer los cambios.

Si Juan ejecuta su consulta después del (ins) y antes del rollback.

Juan lee el precio S/5.0 como máximo y mínimo, sin embargo S/5.0 es un dato que nunca existirá (lectura sucia). Este nivel evita este tipo de lecturas ya que nunca fue confirmada

Los problemas de este nivel son:

Lecturas no repetibles: dos sentencias SELECT iguales y consecutivas podrían devolver datos diferentes.
Datos fantasma: dos sentencias SELECT iguales y consecutivas podrían aparecer y desaparecer filas.

Otra posibilidad de lectura sucia es que si Pepe hace commit, Juan lea como máximo S/4.5 y como mínimo S/5.0 si las consultas se realizan de la siguiente forma.

Nivel Repeatable Read (lectura repetible)

Este nivel de aislamiento garantiza que dos consultas consecutivas diferentes dentro de una transacción devolverán información consistente.

Supongamos que Juan ejecuta sus consultas sobre una base de datos MySQL con nivel de aislamiento Repeatable Read y el orden de sus consultas es:

Durante las lecturas (max), Juan leyó S/3.5 y S/4.0, el SGBD debe asegurar que durante (min) se vean adicionalmente a S/5.0, los valores de S/3.5 y S/4.0 ya que estos fueron vistos en la lectura anterior, por lo que Juan verá datos consistentes: máximo precio es S/4.0 y el mínimo es S/3.5 aunque esto no refleje el estado actual de la base de datos.

El problema de este nivel son los datos fantasma: dos sentencias SELECT iguales y consecutivas podrían aparecer datos diferentes.

Por ejemplo Juan intenta leer dos veces el precio máximo (max).

Si la consulta es ejecutada cuando la base de datos se encuentra con el nivel de aislamiento repeatable read se asegura que todo lo que lee en el primer (max) lo lee también en el segundo (max), sin embargo en un caso obtiene que el máximo es S/4.0 y luego S/5.0.