MySQL - Error The total number of locks exceeds the lock table size

Este error me ocurrió cuando la aplicación que estaba utilizando estaba intentando lanzar una SQL que tenía una claúsula IN en el where con demasiados elementos, y el buffer no tiene capacidad para tratarlos todos para que nos entendamos. Para ello hay que ampliar el tamaño de la variable innodb_buffer_pool_size.

ERROR 1206 (HY000): The total number of locks exceeds the lock table size

Por defecto, la variable innodb_buffer_pool_size viene configurada con 8Mb. Lo ideal es configurarlo hasta un máximo del 80% de la memoria de nuestra máquina, y que al menos sea un 60% superior al tamaño de nuestras BBDD. Con esta SQL podemos saber el tamaño recomendado que necesitaríamos poner:

SELECT CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) RIBPS
FROM (SELECT SUM(data_length+index_length) Total_InnoDB_Bytes
FROM information_schema.tables
WHERE engine='InnoDB') A;

pero tenemos que tener en cuenta el no pasarnos del 80% del total de nuestra memoria.

Para saber qué valor tiene actualmente basta con lanzar el siguiente comando desde consola:

SHOW VARIABLES LIKE '%innodb_buffer_pool_size%';

Una vez sepamos el valor que queremos asignarle, tenemos que modificar el fichero /etc/my.cnf, y poner justo debajo de [mysqld] lo siguiente:

innodb_buffer_pool_size=50M

Si nuestro cambio es para una máquina de desarrollo no es necesario ponerle mucha cantidad.

Y reiniciarmos el servidor apache:

cd /etc/init.d/mysql start

/etc/init.d/mysql stop

Puede que de error el comando mysql y haya que usar el comando mysqld. Con cualquiera de los funciona.