Transacciones en MySQL

Como mencionamos, MySQL soporta Transacciones y adhiere al estándar SQL para sus sentencias.

Autocommit

MySQL, al igual que otros SGBD, utiliza una configuración global conocida como modo autocommit o de commit automático. Esta configuración, si está habilitada, establece que, si no se está dentro de una Transacción explícita, cada sentencia es de por sí atómica. Es decir, se la trata como si estuviera rodeada de las sentencias START TRANSACTION y COMMIT, implícitamente.

El modo autocommit está habilitado por defecto en MySQL, es por esto que todas sentencias que venimos ejecutando sobre MySQL previamente (desde MySQL Workbench, por ejemplo) tienen efecto inmediato y permanente en la base de datos sin que hayamos hecho uso de una Transacción explícita.

Esto también implica que estas sentencias (con modo autocommit habilitado) tampoco pueden ser descartadas manualmente mediante la sentencia ROLLBACK, pues sus cambios ya son permanentes. Aunque, si la sentencia falla, sí se realiza un rollback automático.

Al iniciar una Transacción explícita con START TRANSACTION, MySQL momentáneamente deshabilita el modo autocommit durante la duración de la Transacción, que es el comportamiento que describimos en la sección anterior.

Es decir, no se guardarán los cambios de las sentencias subsiguientes, hasta que se ejecute un COMMIT (o un ROLLBACK), momento en el cual, se vuelve a habilitar el autocommit.

¡Importante!

Al usar un Conector para trabajar con un servidor de MySQL, el modo autocommit está deshabilitado por defecto, por lo tanto, debemos ejecutar un commit manualmente si queremos hacer los cambios permanentes. Esto lo vimos al utilizar mysql-connector-python, con el cuál debíamos ejecutar el método commit() para que los cambios tengan efecto en la base de datos (el inicio de la Transacción estaba implícito).

Ejemplo de implementación con mysql-connector-python

1
import mysql.connector
2

3
conexion = mysql.connector.connect(host='localhost', user='root', password='password', database='banco')
4

5
cursor = conexion.cursor()
6

7
# Cambiamos el saldo
8
cursor.execute('UPDATE cuentas SET saldo = saldo - 5000 WHERE id_cuenta = 1246542576')
9

10
cursor.execute('UPDATE cuentas SET saldo = saldo + 5000 WHERE id_cuenta = 1945387487')
11

12
cursor.execute('INSERT INTO transferencias (id_cuenta_origen, id_cuenta_destino, monto, fecha) VALUES (1246542576, 1945387487, 5000, CURDATE())')
13

14
# Guardamos los cambios
15
conexion.commit()

MySQL nos permite cambiar el estado de la configuración de autocommit mediante la sentencia SET. Esta configuración se establece a nivel de la sesión, por lo tanto, se reestablecerá a su valor por defecto una vez cerrada la conexión.

1
SET autocommit = 0  -- 1 habilita, 0 deshabilita

Después de deshabilitar el modo autocommit (asignando 0), los cambios realizados sobre las tablas no se volverán permanentes automáticamente, sino que tendremos que guardarlos ejecutando un COMMIT explícitamente. Alternativamente podemos ejecutar un ROLLBACK para descartar cualquier cambio.

Niveles de aislamiento

El aislamiento de Transacciones es una de las bases del procesamiento de bases de datos. El aislamiento es la “I” del acrónimo ACID; el nivel de aislamiento es la configuración que afina el equilibrio entre rendimiento y confiabilidad, coherencia y reproducibilidad de los resultados cuando múltiples Transacciones realizan cambios y realizan consultas al mismo tiempo.

Las bases de datos InnoDB de MySQL ofrecen los 4 niveles de aislamiento establecidos en el estándar SQL:1992: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, y SERIALIZABLE. El nivel de aislamiento por defecto en MySQL es REPEATABLE READ.

Los niveles de aislamiento establecen cómo y cuándo se pueden leer los datos dentro de una Transacción, estableciendo distintas estrategias de bloqueo.

Describiremos brevemente estos niveles, ordenados desde el más usado al menos usado.

REPEATABLE READ: este es el nivel de aislamiento por defecto de MySQL y asegura lecturas consistentes dentro de la misma Transacción. Es decir, las lecturas que se hagan dentro de una Transacción serán consistentes entre sí, y no se verán afectadas por cambios generados por otras Transacciones, ya que leen de un estado fijo establecido por la primera lectura dentro de la Transacción.
READ COMMITTED: cada lectura, aún dentro de la misma Transacción, genera su propio estado fijo distinto al de otras lecturas. Puede sufrir la lectura de “registros fantasma”, es decir, dos consultas iguales, pero con resultados distintos, ya que puede leer cambios guardados que hagan otras Transacciones.
READ UNCOMMITTED: cada lectura es no bloqueante por defecto, lo que implica que se pueden leer datos previos a un COMMIT de otras Transacciones, es decir, datos que posiblemente no sean los permanentes. Esto se conoce como “dirty read” o lectura sucia. Por lo tanto, las lecturas en este modo no son consistentes.
SERIALIZABLE: cada lectura se convierte automáticamente en bloqueante si autocommit está deshabilitado. Es similar a REPEATABLE READ, pero con reglas más estrictas. Solo es usado en situaciones especiales.

Modos de acceso

En MySQL existen 2 modos de acceso a los datos para las Transacciones, READ WRITE (lectura y escritura) y READ ONLY (solo lectura). Solo es posible establecer uno de estos modos a la vez.

READ WRITE: es el modo por defecto, aunque también se lo puede establecer explícitamente con la sentencia SET TRANSACTION, y permite tanto leer, como modificar tablas en la Transacción.

READ ONLY: en este modo, los cambios a las tablas están prohibidos.

El modo de acceso de una Transacción puede ser establecido al momento iniciarla, como una cláusula de START TRANSACTION. Por ejemplo:

1
START TRANSACTION READ ONLY;

Por otro lado, se puede establecer el modo de acceso para otras Transacciones, además de la actual, con la sentencia SET TRANSACTION como veremos a continuación.

Establecer configuración de una Transacción

Para modificar el nivel de aislamiento y el modo de acceso de una Transacción, o de todas las subsiguientes Transacciones de la sesión, o todas las subsiguientes Transacciones del servidor, podemos usar la sentencia SET TRANSACTION con sus cláusulas.

La sintaxis de esta sentencia es la siguiente:

1
SET <ámbito> TRANSACTION
2
    <cláusula>

Donde <ámbito> puede omitirse para aplicar la configuración solo a la próxima Transacción, puede ser SESSION para aplicar la configuración a todas las siguientes Transacciones de la sesión (conexión) actual o puede ser GLOBAL para aplicar la configuración a todas las sesiones posteriores (las actuales no se ven afectadas).

<cláusula> es una o más cláusulas separadas por comas, que establecen el nivel de aislamiento y/o el modo de acceso de la Transacción. Los niveles de aislamiento se establecen con las palabras clave ISOLATION LEVEL <nivel>, con <nivel> uno de los niveles antes vistos. Por otro lado, los modos se escriben directamente.

Por ejemplo:

1
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE,
2
    READ WRITE;
3

4
START TRANSACTION;
5
...
6
COMMIT;

En este ejemplo, configuramos todas las próximas Transacción de la sesión con nivel de aislamiento SERIALIZABLE y modo de acceso READ WRITE.