1. While emptying a table in MySQL 5.7, which contained over 3 million records, the system prompted: 1206 – The total number of locks exceeds the lock tablesize when about 2 million records remained. See Figure 1.
2. Reference: THE TOTAL NUMBER OF LOCKS EXCEEDS THE LOCK TABLE SIZE.
3. This issue can be resolved by setting a higher value for the MySQL variable “innodb_buffer_pool_size”. The default value of innodb_buffer_pool_size is 8,388,608. On Windows, its location is: “C:\ProgramData\MySQL\MySQL Server 5.7\my.ini”.
4. Edit C:\ProgramData\MySQL\MySQL Server 5.7\my.ini,
# innodb_buffer_pool_size=8M
innodb_buffer_pool_size=16M
5. In Computer Management – Services and Applications – Services, locate MySQL57 and restart it. See Figure 2.
6. Windows could not stop the MySQL57 service (on the local computer). Error 1053: The service did not respond to the start or control request in a timely fashion. I ultimately decided to restart the computer. See Figure 3.
7. I emptied the table again in MySQL 5.7, and it succeeded. See Figure 4.
