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

while running queries on a Large number of rows and your DB engine is InnoDB, you will face this error.

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

Basic reason for this is, Mysql is out of Lock Space,
You need to increase the value of “innodb_buffer_pool_size”

edit my.cnf

#
nano /etc/my.cnf

[mysqld]
local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
innodb_buffer_pool_size=2G

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid


see the highlighted line “innodb_buffer_pool_size=2G”
for me I needed to manage much rows so set to 2G ,
you can set to May be 512M or a number that suites your hardware.

MySQL restart is needed

#
/etc/init.d/mysqld restart
#

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.