When you are trying to run an update or insert query in a database which contains huge amount of data (crore) in xampp - MySQL, you might see this error
ERROR 1206 (HY000): The total number of locks exceeds the lock table size
Running out of space for locks usually indicates that the small default size of the InnoDB buffer pool is being used or operations on very large tables are being attempted with a huge number of row locks, perhaps comparing several large tables. The only way to fix it for sure is to adjust
Solution
1. Stop MySQL and open my.cnf file which is located in /opt/lampp/etc/my.cnf in Linux
2. increase the values of innodb_buffer_pool_size and innodb-log-file-size
for example :
innodb_buffer_pool_size = 2G
innodb-log-file-size = 512 M
innodb-log-file-size should be 25% of innodb_buffer_pool_size
In a dedicated system you can use up to 80% of the total RAM in the system for the InnoDB buffer pool.
3. Delete the log files ib_logfile0, ib_logfile1 which is located in /opt/lampp/var/mysql
4. Start MySQL
ERROR 1206 (HY000): The total number of locks exceeds the lock table size
Running out of space for locks usually indicates that the small default size of the InnoDB buffer pool is being used or operations on very large tables are being attempted with a huge number of row locks, perhaps comparing several large tables. The only way to fix it for sure is to adjust
i
nnodb_buffer_pool_size
and restart MySQL. By default, this is set to only 8MB, which is too small for anyone who is using InnoDB to do anything.Solution
1. Stop MySQL and open my.cnf file which is located in /opt/lampp/etc/my.cnf in Linux
2. increase the values of innodb_buffer_pool_size and innodb-log-file-size
for example :
innodb_buffer_pool_size = 2G
innodb-log-file-size = 512 M
innodb-log-file-size should be 25% of innodb_buffer_pool_size
In a dedicated system you can use up to 80% of the total RAM in the system for the InnoDB buffer pool.
3. Delete the log files ib_logfile0, ib_logfile1 which is located in /opt/lampp/var/mysql
4. Start MySQL