How to Solve Mysql Error 1206: The total number of locks exceeds the lock table size

Mysql solves The total number of locks exceeds the lock table size error
The total number of locks exceeds the lock table size error occurs when a field is modified correctly the first time, but the second time when a local UPDATE of one million data is performed

Quoting the following explanation from the Internet

If you’re running an operation on a large number of rows within a table that uses the InnoDB storage engine, you might see this error: ERROR 1206 (HY000): The total number of locks exceeds the lock table size MySQL is trying to tell you that it doesn’t have enough room to store all of the row locks that it would need to execute your query. The only way to fix it for sure is to adjust innodb_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. If you need a temporary workaround, reduce the amount of rows you’re manipulating in one query. For example, if you need to delete a million rows from a table, try to delete the records in chunks of 50,000 or 100,000 rows. If you’re inserting many rows, try to insert portions of the data at a single time.

It turns out that this problem occurs when InnoDB table performs update, insert, and delete operations on large batches of data, so you need to adjust the value of InnoDB global innodb_buffer_pool_size to solve this problem and restart mysql service. Check the current database storage engine and use ENGINE=InnoDB type at creation time. Default innodb_buffer_pool_size=8M

#View the MySQL storage engine
mysql> show variables like '%storage_engine%';
+----------------------------------+--------+
| Variable_name                    | Value  |
+----------------------------------+--------+
| default_storage_engine           | InnoDB |
| default_tmp_storage_engine       | InnoDB |
| disabled_storage_engines         |        |
| internal_tmp_disk_storage_engine | InnoDB |
+----------------------------------+--------+
4 rows in set, 1 warning (0.00 sec)

# Check the size of MySQL cache pool
#You can see that the default cache pool size is 8388608 = 8 * 1024 * 1024 = 8 MB. you need to change it to a larger size.
mysql> show variables like "%_buffer_pool_size%";
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| innodb_buffer_pool_size | 8388608 |
+-------------------------+---------+
1 row in set, 1 warning (0.00 sec)

#Modify innodb_buffer_pool_size
mysql> SET GLOBAL innodb_buffer_pool_size=2147483648;
#change to 2g.
#Note that the changes take effect after mysql 5.7, but the previous version has to be modified in my.cnf and restarted.

Similar Posts: