The total number of locks exceeds the lock table size

This issue can be resolved by setting the higher values for the MySQL variable innodb_buffer_pool_size. The default value for innodb_buffer_pool_size will be 8,388,608.

To change the settings value for innodb_buffer_pool_size please see the below set.

1. Locate the file my.cnf from the server. For Linux servers this will be mostly at /etc/my.cnf
2. Add the line innodb_buffer_pool_size=64MB to this file
3. Restart the MySQL server

To restart the MySQL server, you can use anyone of the below 2 options:

1. service mysqld restart
2. /etc/init.d/mysqld restart

Reference The total number of locks exceeds the lock table size

I found another way to solve it - use Table Lock. Sure, it can be unappropriate for your application - if you need to update table at same time.

See: Try using LOCK TABLES to lock the entire table, instead of the default action of InnoDB's MVCC row-level locking. If I'm not mistaken, the "lock table" is referring to the InnoDB internal structure storing row and version identifiers for the MVCC implementation with a bit identifying the row is being modified in a statement, and with a table of 60 million rows, probably exceeds the memory allocated to it. The LOCK TABLES command should alleviate this problem by setting a table-level lock instead of row-level:

SET @@AUTOCOMMIT=0;
LOCK TABLES avgvol WRITE, volume READ;
INSERT INTO avgvol(date,vol)
SELECT date,avg(vol) FROM volume
GROUP BY date;
UNLOCK TABLES;

Jay Pipes, Community Relations Manager, North America, MySQL Inc.

From the MySQL documentation (that you already have read as I see):

1206 (ER_LOCK_TABLE_FULL)

The total number of locks exceeds the lock table size. To avoid this error, increase the value of innodb_buffer_pool_size. Within an individual application, a workaround may be to break a large operation into smaller pieces. For example, if the error occurs for a large INSERT, perform several smaller INSERT operations.

If increasing innodb_buffer_pool_size doesnt help, then just follow the indication on the bolded part and split up your INSERT into 3. Skip the UNIONs and make 3 INSERTs, each with a JOIN to the topThreetransit table.

First, you can use sql command show global variables like 'innodb_buffer%'; to check the buffer size.

Solution is find your my.cnf file and add,

[mysqld]
innodb_buffer_pool_size=1G # depends on your data and machine

DO NOT forget to add [mysqld], otherwise, it won't work.

In my case, ubuntu 16.04, my.cnf is located under the folder /etc/mysql/.

I am running MySQL windows with MySQL workbench. Go to Server > Server status At the top it says configuration file: "path" (C:\ProgramData\MySQL\...\my.ini)

Then in the file "my.ini" press control+F and find buffer_pool_size. Set the value higher, I would recommend 64 MB (default is 8 MB).

Restart the server by going to Instance>Startup/Shutdown > Stop server (and then later start server again)

In my case I could not delete entries from my table.