Mysql - Error Lock wait timeout exceeded

La variable innodb_lock_wait_timeout indica el tiempo que va a mantenerse en espera hasta que un resgistro pueda quedar bloqueado. Pasado ese tiempo, si sigue bloqueado interrumpirá la tarea que estaba haciendo.

La siguiente instrucción te puede dar una pista de qué produce los bloqueos. Hay que lanzarlo justamente después de que se produzca para tener información más certera:

mysql -h localhost -u root -e "show engine innodb statusg" > /home/user/innodb_status.txt

También podemos comprobar qué valor tenemos actualmente de la variable que comentábamos:

mysql> show variables like 'innodb_lock_wait_timeout';

Por defecto tiene el valor 50, e indica que realiza una espera de 50" si se produce un bloqueo. Yo conseguí resolver mi problema ampliando el valor de esa variable a 100.

Para ampliar el valor podemos hacerlo así:

mysql> set innodb_lock_wait_timeout=100

O modificando el tichero /etc/my.cnf poniendo la variable e el valor justo después de [mysqld]. Como la anterior forma no me funcionaba, tuve que hacerlo modificando el fichero de configuración.

Forzar el desbloqueo de tablas en MySQL

Otro método que nos puede ser muy útil para resolver nuestro problema es mirando el estado de los procesos de MySQL. Con esto lo que queremos hacer es romper los bloqueos existentes que estén causados por una SQL.

Para ello vamos a averiguar qué tablas tienen bloqueos y después vamos a listar los procesos de MySQL que están activos para después matarlos manualmente.

1) Entramos en MySQL desde consola de comandos:

mysql -u usario -pcontraseña (sin espacio entre -p y la contraseña)

2) Mostramos la tablas que tienen bloqueos:

mysql> show open tables where in_use>0;

3) Mostramos la lista de procesos que están en ejecución, y nos fijamos en aquel que esté usando la tabla que en el comando anterior nos apareció como que tenía bloqueos:

mysql> show processlist;

+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host      | db   | Command | Time | State | Info             |
+----+------+-----------+------+---------+------+-------+------------------+
|  4 | root | localhost | NULL | Query   |    0 | NULL  | show processlist |
+----+------+-----------+------+---------+------+-------+------------------+

Este resultado es un ejemplo para que se vea qué información mostraría. Si hubiera una tabla con bloqueo, saldría en la lista.

4) Ahora solo queda matar el proceso que produce el bloqueo indicando el ID del proceso:

mysql> kill id_proceso;

Cuando lo hayamos hecho, si listamos de nuevo los procesos, aparecerá marcado como killed en la columna Command.

Si el listado de procesos a matar fuera muy grande, se puede gestionar de la siguiente manera:

• Generamos un TXT con todos los comandos kill por cada uno de los procesos:

mysql> SELECT concat('KILL ',id,';')
FROM information_schema.processlist
WHERE user='root' and INFO like 'SELECT%' into outfile '/tmp/procesos.txt';

• Lanzamos todos los comandos kill:

mysql> source /tmp/procesos.txt;

Si vemos que seguimos sin resolver el problema, podemos intentar en un último caso en reiniciar el MySQL.