MySQL Error 1206 occurs when the number of requested locks exceeds the available lock table size.

Why it happens

This error usually occurs when executing a large update query. For example:

Suppose we have a table called users that stores user information and contains a large number of records:

1MySQL [email protected]:test> select count(*) from users;
2+----------+
3| count(*) |
4+----------+
5| 259464541|
6+----------+
71 row in set
8Time: 0.035s

The table structure is as follows:

1CREATE TABLE `users` (
2  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
3  `name` char(100) NOT NULL,
4  `passwd` char(32) NOT NULL DEFAULT '',
5  `email` varchar(100) NOT NULL DEFAULT '',
6  `description` varchar(255)
7  PRIMARY KEY (`id`,`name`,`passwd`,`email`),
8  KEY `name` (`name`),
9) ENGINE=InnoDB

If we want to remove the newline characters in the description field, we can execute the following update SQL:

1update users set description=REPLACE(description, '\n', '\\n')

However, due to the large amount of data, InnoDB uses row-level locks when executing the update SQL, which results in Error 1206.

How to resolve Error 1206: The number of locks exceeds the lock table size

To resolve this issue, there're two methods:

1. Quick solution

Referring to the previous example, if we update the rows in smaller batches, it won't trigger the "too many locks" error. For example:

1update users set description=REPLACE(description, '\n', '\\n') limit 1000

This query will execute successfully and MySQL returns the result very fast.

With a similar idea in mind, we can split the large SQL query into smaller batches. We can create a procedure in MySQL, with example code as follows:

 1DELIMITER $$
 2CREATE PROCEDURE updateByBatch()
 3
 4   BEGIN
 5      DECLARE a INT Default 1 ;
 6      simple_loop: LOOP
 7         update users set about=REPLACE(about, '\n', '\\n') where id >= a and id <= a+10000 and locate
 8('\n',about) > 0;select a;
 9         SET a=a+10000;
10         IF a>271216564 THEN
11            LEAVE simple_loop;
12         END IF;
13   END LOOP simple_loop;
14END $$
15
16call updateByBatch()

2. Permanant solution

You can increase the innodb_buffer_pool_size value permanantly, which determines the size of the buffer pool that InnoDB uses to store data, indexes, and lock tables.

You can follow these steps to increase the innodb_buffer_pool_size:

Locate the MySQL configuration file, my.cnf (or my.ini on Windows). This file is typically located in /etc/ or /etc/mysql/ on Linux and in the MySQL installation directory on Windows.

Open the configuration file with your preferred text editor. For example, using vim on Linux:

1sudo vim /etc/mysql/my.cnf

Look for the [mysqld] or [mysqld_safe] section in the configuration file. If it doesn't exist, you can create a new [mysqld] section.

Increase the innodb_buffer_pool_size value. The default value is usually 8MB or 128MB. You can increase it according to your system resources and requirements. For example, to set the value to 512MB, add or modify the following line under the [mysqld] section:

1innodb_buffer_pool_size=512M

Make sure to choose a value that doesn't exceed the available memory on your system. A general recommendation is to set the innodb_buffer_pool_size to about 70-80% of your total system memory, but this depends on your specific use case and other running applications.

Save the configuration file and exit the text editor. If you used vim, enter :wq to save and exit.

Restart the MySQL server to apply the changes. The command to restart MySQL instance:

1sudo systemctl restart mysql
2
3or 
4
5sudo service mysql restart

After restarting the MySQL server, the new innodb_buffer_pool_size value should be in effect, which should help resolve the "Error 1206: The number of locks exceeds the lock table size" issue. If the problem persists, consider further increasing the innodb_buffer_pool_size value, but be cautious not to exceed your system's available memory size.