The error “1206 – The total number of locks exceeds the lock table size” indicates that the lock table size has exceeded its limit. This is typically caused by the innodb_buffer_pool_size parameter being set too low in the MySQL configuration.
To resolve this issue, you can try the following approaches:
-
Increase the lock table size limit: In the configuration file (such as my.cnf or my.ini), increase or modify the value of the
innodb_buffer_pool_sizeparameter. Adjust it to a larger size, for example, 1G or more, and then restart the MySQL service to apply the changes. -
Optimize query statements: Check if there are other queries that frequently access the same tables, especially long-running transactions. Try to optimize the logic of these queries or transactions to minimize competition for lock resources.
-
Split update operations: If there is a large amount of data in the table, consider performing update operations in batches, updating only a portion of the data at a time instead of all at once. For instance, use a WHERE clause to limit the scope of the updates and gradually update according to actual needs.
-
Adjust transaction isolation levels: In some cases, lowering the transaction isolation level can also reduce the usage of lock resources. However, this may introduce other concurrency and data consistency issues, so consider this option carefully based on the specific situation.
Please note that depending on your system configuration and specific circumstances, it may be necessary to combine multiple methods to resolve this issue. If the problem persists, it is advisable to consult a professional database administrator or conduct a more in-depth MySQL performance tuning.