Curso de MariaDB / Comparar aislamiento, bloqueos e interbloqueos con dos conexiones

El aislamiento determina qué cambios concurrentes puede leer una transacción, mientras que los bloqueos coordinan operaciones que entran en conflicto. Con dos conexiones separadas y una secuencia controlada puede comparar REPEATABLE READ, READ COMMITTED, una espera que agota su tiempo y un interbloqueo como comportamientos distintos.

Preparar dos conexiones independientes A y B

Abra dos clientes de línea de comandos contra el mismo servidor de prácticas y la base shopcourse. La guía del cliente de MariaDB explica la conexión. Compruebe que CONNECTION_ID devuelve identificadores distintos. Pegar las dos partes en un único cliente no crea concurrencia. Use únicamente datos de prácticas y revise los límites de START TRANSACTION, COMMIT y ROLLBACK antes de empezar.

Ejecute el bloque siguiente por separado en A y en B. Guarda el aislamiento y el tiempo de espera propios de cada sesión para restaurarlos al final. Compruebe también el servidor con SELECT VERSION();. Los cambios del ejercicio son SESSION, no GLOBAL.

SELECT CONNECTION_ID(),DATABASE(),@@SESSION.tx_isolation;
SET @saved_isolation=@@SESSION.tx_isolation;
SET @saved_wait=@@SESSION.innodb_lock_wait_timeout;

Cree la tabla siguiente solo en A. Compruebe antes que accounts_36_lab no existe; si existe, inspeccione y conserve lo necesario antes de decidir si es un objeto prescindible del ejercicio.

USE shopcourse;
CREATE TABLE accounts_36_lab (
 account_id INT PRIMARY KEY,balance INT NOT NULL
) ENGINE=InnoDB;
INSERT INTO accounts_36_lab VALUES(1,1000),(2,2000);
Caso Regla observada Distinción
SELECT ordinario en REPEATABLE READ Lectura coherente de una instantánea dentro de la transacción No equivale a una lectura con bloqueo
SELECT ordinario en READ COMMITTED Una versión confirmada visible para cada sentencia Lecturas repetidas pueden cambiar
Espera por bloqueo Espera a que termine una operación incompatible Agotar el tiempo de espera no implica un interbloqueo

Comparar REPEATABLE READ en el orden A → B → A

Empiece con el saldo 1000 del ID 1 que deja la preparación. Respete las etiquetas de conexión: B actúa únicamente después de que A termine su primera lectura.

A-1: establecer la instantánea

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT balance FROM accounts_36_lab WHERE account_id=1;

B-1: confirmar un cambio

START TRANSACTION;
UPDATE accounts_36_lab SET balance=1100 WHERE account_id=1;
COMMIT;

A-2: leer dentro de la misma transacción

SELECT balance FROM accounts_36_lab WHERE account_id=1;

A-1 y A-2 devuelven 1000. La segunda lectura ordinaria de A conserva su instantánea aunque B haya confirmado 1100. La comparación se refiere a SELECT sin bloqueo; no sustituya estas lecturas por FOR UPDATE para intentar obtener la misma prueba.

A-3: terminar y volver a leer

ROLLBACK;
SELECT balance FROM accounts_36_lab WHERE account_id=1;

La lectura posterior a ROLLBACK devuelve 1100. La referencia oficial de SET TRANSACTION explica el alcance de cada nivel de aislamiento y la diferencia entre lecturas ordinarias y operaciones con bloqueo.

Repetir con READ COMMITTED desde un reinicio explícito

B-0: restablecer el saldo del ejercicio anterior

ROLLBACK;
START TRANSACTION;
UPDATE accounts_36_lab SET balance=1000 WHERE account_id=1;
COMMIT;

A-1: primera lectura con READ COMMITTED

ROLLBACK;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
SELECT balance FROM accounts_36_lab WHERE account_id=1;

B-1: confirmar el saldo 1100

START TRANSACTION;
UPDATE accounts_36_lab SET balance=1100 WHERE account_id=1;
COMMIT;

A-2: segunda lectura y finalización

SELECT balance FROM accounts_36_lab WHERE account_id=1;
ROLLBACK;

A devuelve primero 1000 y después 1100: cada lectura ordinaria utiliza los datos confirmados visibles para esa sentencia. Esto no basta para elegir el aislamiento de una aplicación; también hay que valorar sus operaciones de escritura y las reglas de coherencia que debe mantener.

Distinguir un bloqueo retenido de su tiempo de espera

A-1: retener el bloqueo de la fila 1

START TRANSACTION;
SELECT account_id,balance FROM accounts_36_lab
WHERE account_id=1 FOR UPDATE;

B-1: intentar modificar la misma fila

SET SESSION innodb_lock_wait_timeout=3;
START TRANSACTION;
UPDATE accounts_36_lab SET balance=balance+1 WHERE account_id=1;

Deje abierta la transacción de A. B debería recibir el error de tiempo de espera de bloqueo, normalmente 1205, tras unos tres segundos. El valor limita la espera de esta sesión; no constituye una promesa de duración exacta. No dé por hecho que el error ha cancelado toda la transacción de B.

B-2: cancelar el intento y restaurar la espera

ROLLBACK;
SET SESSION innodb_lock_wait_timeout=@saved_wait;

A-2: liberar el bloqueo

ROLLBACK;

El bloqueo se tomó dentro de una transacción explícita. No quite ese límite para reproducir el ejercicio: la referencia oficial de FOR UPDATE explica sus requisitos transaccionales en InnoDB. Una espera por una sola dependencia no es lo mismo que el ciclo del caso siguiente.

Producir un interbloqueo con cuatro pasos intercalados

Un interbloqueo, también llamado deadlock, aparece cuando las transacciones se esperan entre sí formando un ciclo. Compruebe que las dos transacciones anteriores terminaron. Estos son cuatro pasos intercalados: cambie de ventana en el orden A-1 → B-1 → A-2 → B-2, en lugar de ejecutar todo como un script secuencial.

Paso Conexión Acción
1 A Modificar y bloquear la fila 1
2 B Modificar y bloquear la fila 2
3 A Solicitar la fila 2 y esperar
4 B Solicitar la fila 1 y completar el ciclo

A-1: bloquear solo la primera fila

START TRANSACTION;
UPDATE accounts_36_lab SET balance=balance+1 WHERE account_id=1;

B-1: bloquear la otra fila

START TRANSACTION;
UPDATE accounts_36_lab SET balance=balance+1 WHERE account_id=2;

A-2: solicitar la fila retenida por B

UPDATE accounts_36_lab SET balance=balance+1 WHERE account_id=2;

A queda esperando. Sin esperar a que termine A, cambie a B y ejecute la sentencia siguiente.

B-2: solicitar la fila retenida por A

UPDATE accounts_36_lab SET balance=balance+1 WHERE account_id=1;

Con la detección de interbloqueos de InnoDB habilitada, una de las transacciones puede recibir Deadlock found, normalmente el error 1213. No se garantiza qué conexión será la víctima. La otra operación puede continuar al liberarse los bloqueos de la víctima. Ejecute ROLLBACK; por separado en ambas ventanas para cerrar la prueba sin conservar incrementos.

Una aplicación debe distinguir este caso del tiempo de espera y aplicar una política de reintentos limitada para la transacción completa, con protección frente a duplicar operaciones. Repetir únicamente una sentencia o reintentar indefinidamente no define una recuperación correcta. La referencia oficial del error 1213 identifica este interbloqueo y la necesidad de reiniciar la transacción.

Restaurar cada sesión y mantener un orden de bloqueo coherente

Adquirir varias filas en un orden de claves coherente y mantener cortas las transacciones reduce situaciones de espera circular. Las condiciones muy amplias o la falta de índices útiles pueden aumentar el alcance de los bloqueos. Investigue las consultas, los registros y el orden de operaciones; no clasifique automáticamente un interbloqueo como un fallo genérico de conexión.

ROLLBACK;
SET SESSION tx_isolation=@saved_isolation;
SET SESSION innodb_lock_wait_timeout=@saved_wait;
SELECT @@SESSION.tx_isolation,@@SESSION.innodb_lock_wait_timeout;

Ejecute este bloque en A y en B y compare con los valores guardados al principio de cada sesión. Las variables de usuario desaparecen cuando se cierra la conexión: si abrió otra, revise de nuevo su configuración, sin suponer que conserva las variables anteriores.

Una vez cerradas ambas transacciones, elimine la tabla siguiente solo si sigue siendo un objeto de prácticas prescindible. DROP TABLE elimina estructura y registros y no se revierte con un ROLLBACK ordinario.

DROP TABLE shopcourse.accounts_36_lab;