MariaDB / Deadlocks

Bienvenidos sean a este post, hoy hablaremos sobre los bloqueos «mueltos».

Ya hemos hablado de que son los bloqueos o locks en este post, pero que sucederia cuando dos bloqueos se encuentran al mismo tiempo en el mismo lugar? Ambos bloqueos se quedan esperando que termine el otro y genera un bucle infinito que nunca acabara generando al «bloqueo de la muerte» o deadlock, afortunadamente InnoDB (al igual que otros motores) dispone de un mecanismo para detectarlos y para poder desbloquearlo el mecanismo mira cual es la transaccion que ingresa, actualiza o elimina la menor cantidad de filas y en base al resultado procede a terminarla, esta transaccion que se termina se la denomina como victima, a veces un motor de almacenamiento que utiliza bloqueos a nivel de tabla puede estar involucrado con un deadlock entre las cuales puede envolver a tablas de InnoDB, para el caso de este motor solo puede ser detectado si la variable innodb_table_locks esta con el valor ON, en cualquier entorno donde existan las concurrencias los deadlocks son normal, cuando un deadlock ocurre una o mas transacciones terminan con el error 1213.

Si bien esto no nos concierne y la aplicacion deberia ser capaz de manejarlo perfectamente reiniciando las transacciones que fueron detenidas, aunque si esta situacion se vuelve muy recurrente debes analizar la situacion para arreglar este problema, algnas soluciones pueden ser:

• Transacciones pequeñas causan menos deadlocks
• Reducir la cantidad de bloqueos a traves del uso correcto de indices
• Evitar que dos transacciones que acceden a las mismas tablas lo hagan en el mismo orden
• Los niveles de aislamiento puede ayudarnos con esto por medio de READ COMMITTED o READ UNCOMMITTED

Para diagnosticar sobre deadlocks podemos usar a SHOW ENGINE INNODB STATUS, el cual vimos en este post, pero si nosotros queremos monitorear todos los deadlocks en el servidor debemos establecer con el valor ON a la variable innodb_print_all_deadlocks, para entenderlo vamos a desarrollar un ejemplo, para este ejemplo deben seleccionar algunas base de datos y en ella crearemos dos tablas de la siguiente forma:

MariaDB [base2]> create table t1 (
    -> a INT PRIMARY KEY,
    -> b INT UNIQUE)
    -> ENGINE = InnoDB;

MariaDB [base2]> create table t2 (
    -> a INT PRIMARY KEY,
    -> b INT UNIQUE)
    -> ENGINE = InnoDB;

Con nuestras dos tablas creadas vamos a agregar informacion en las mismas mediante los siguientes comandos:

MariaDB [base2]> insert into t1 values (1,1);

MariaDB [base2]> insert into t2 values (1,1);

Una vez ingresado nuestros datos en las dos tablas vamos a ejecutar el siguiente comando:

MariaDB [base2]> start transaction;

Esto dara comienzo a la transaccion, despues ejecutaremos el siguiente comando:

MariaDB [base2]> select * from t1 where b = 1 for update;

Este nos devolvera el query solicitado, nuestro siguiente paso sera abrir otra terminal y realizar otra conexion a la base que seleccionamos para crear estas dos tablas, una vez realizado ejecutamos primero este comando:

MariaDB [base2]> start transaction;

Y luego este comando:

MariaDB [base2]> select * from t2 where b = 1 for update;

Este nos devolvera el query solicitado tal como vimos en el caso anterior pero ahora debemos volver a la primera conexion y ejecutaremos el siguiente comando:

MariaDB [base2]> select * from t2 where b = 1 for update;

Si todo sale bien no nos devolvera ninguna salida del query, ahora volvamos a la segunda conexion y ejecutemos el siguiente comando:

MariaDB [base2]> select * from t1 where b = 1 for update;

En esta ocasion si obtendremos una salida que sera la siguiente:

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

Como pueden ver recibimos una notificacion y si vuelven a la primera conexion veran que ahora si se realizo el query y vimos la salida pero esto es debido a que se «mato» al query en la segunda conexion, con nuestro deadlock creado podemos pasar a diagnosticarlo mediante el sigueinte comando:

MariaDB [base2]> show engine innodb status;

Esto nos devolvera toda la informacion del estado del motor de almacenamiento, yo les voy a pasar el extracto de la informacion que me devolvio a mi:

------------------------
LATEST DETECTED DEADLOCK
------------------------
2021-08-24 16:57:21 0x7f1e26c6e700
*** (1) TRANSACTION:
TRANSACTION 703, ACTIVE 60 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1128, 3 row lock(s)
MySQL thread id 38, OS thread handle 139767476623104, query id 100 localhost tinchicus Statistics
select * from t2 where b = 1 for update
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 25 page no 4 n bits 72 index b of table `base2`.`t2` trx id 703 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 4; hex 80000001; asc     ;;

*** (2) TRANSACTION:
TRANSACTION 704, ACTIVE 21 sec starting index read
mysql tables in use 1, locked 1
5 lock struct(s), heap size 1128, 3 row lock(s)
MySQL thread id 39, OS thread handle 139767476315904, query id 101 localhost tinchicus Statistics
select * from t1 where b = 1 for update
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 25 page no 4 n bits 72 index b of table `base2`.`t2` trx id 704 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 4; hex 80000001; asc     ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 24 page no 4 n bits 72 index b of table `base2`.`t1` trx id 704 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 4; hex 80000001; asc     ;;

*** WE ROLL BACK TRANSACTION (2)

Nota: 
Esta informacion puede variar con respecto a la de ustedes.

Observen como nos pasa informacion sobre las distintas transacciones desde cuantas tablas involucra hasta los tiempos que esta activo, incluyendo los bloqueos y otros datos mas, tambien nos informa que espera sobre el lock y por ultimo cual fue la transaccion que se elimino.

En resumen, hoy hemos visto deadlocks, que son, como se generan, como podemos evitarlo, tambien hemos visto un ejemplo para verlo en accion y cuales son los datos que nos devuelven los diagnosticos, espero les haya sido de utilidad sigueme en tumblr, Twitter o Facebook para recibir una notificacion cada vez que subo un nuevo post en este blog, nos vemos en el proximo post.