Total number of locks exceeds table lock size

Question

I am running a report in MySQL. One of the queries involves nesting a large number of rows in a temp table. When I try to run it, I get this error:

Error code 1206: the number of locks exceeds the size of the lock table.

Asked requests:

create temporary table SkusBought( customerNum int(11), sku int(11), typedesc char(25), key `customerNum` (customerNum) )ENGINE=InnoDB DEFAULT CHARSET=latin1; insert into skusBought select t1.* from (select customer, sku, typedesc from transactiondatatransit where (cat = 150 or cat = 151) AND daysfrom07jan1 > 731 group by customer, sku union select customer, sku, typedesc from transactiondatadelaware where (cat = 150 or cat = 151) AND daysfrom07jan1 > 731 group by customer, sku union select customer, sku, typedesc from transactiondataprestige where (cat = 150 or cat = 151) AND daysfrom07jan1 > 731 group by customer, sku) t1 join (select customernum from topThreetransit group by customernum) t2 on t1.customer = t2.customernum; 

I read that modifying the configuration file to increase the size of the buffer pool will help, but it does nothing. What is the way to fix this as a temporary workaround or permanent fix?

EDIT: changed part of the request. This should not affect it, but I still found, replaced everything and did not understand that it ruined it. Does not affect the question.

EDIT 2: Added typedesc to t1. I changed it in the request, but not here.

Answer 1

This problem can be solved by setting higher values ​​for the MySQL variable innodb_buffer_pool_size . The default innodb_buffer_pool_size for innodb_buffer_pool_size will be 8,388,608 .

To change the setting value for innodb_buffer_pool_size , see below installed.

• Locate the my.cnf file from the server. For Linux servers, this will be mainly on /etc/my.cnf
• Add the line innodb_buffer_pool_size=64MB to this file
• Reboot MySQL server

To restart the MySQL server, you can use either of the following two options:

• mysqld restart service
• /etc/init.d/mysqld restart

Link Total number of locks exceeds the size of the lock table

Answer 2

I found another way to solve this problem - use Table Lock. Of course, this may not be appropriate for your application - if you need to update the table at the same time.

See: Try using LOCK TABLES to lock the entire table, instead of the default row level action of InnoDB MVCC. If I am not mistaken, the "lock table" refers to the internal structure of InnoDB, which stores line and version identifiers for MVCC implementation with a bit that identifies the line that changes in the instruction and with a table of 60 million rows, probably exceeds the memory allocated to it. The LOCK TABLES command should fix this problem by setting a table-level lock instead of a 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, Public Relations Manager, North America, MySQL Inc.

Answer 3

From the MySQL documentation (which you already read, as I see):

1206 (ER_LOCK_TABLE_FULL)

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

If increasing innodb_buffer_pool_size does not help, just follow the directions on the highlighted half-dead part and divide your INSERT by 3. Skip UNION and do 3 INSERT, each of which has a JOIN in the topThreetransit table.

Answer 4

If you structured your tables correctly so that each of them had relatively unique values, then a less intensive way to do this would be to do 3 separate insert-in statements, 1 for each table, with a join filter for each insert -

 INSERT INTO SkusBought... SELECT t1.customer, t1.SKU, t1.TypeDesc FROM transactiondatatransit AS T1 LEFT OUTER JOIN topThreetransit AS T2 ON t1.customer = t2.customernum WHERE T2.customernum IS NOT NULL 

Repeat this for the other two tables - copy / paste is a great method, just change the name of the FROM table. ** If you are trying to prevent duplicate entries in the SkusBought table, you can add the following join code in each section before the WHERE clause.

 LEFT OUTER JOIN SkusBought AS T3 ON t1.customer = t3.customer AND t1.sku = t3.sku 

- and then the last line of the WHERE clause -

 AND t3.customer IS NULL 

The source code uses several subqueries, and the UNION statement can be expensive, since it will first create its own temporary table to populate data from three separate sources before inserting it into the table in which you want to work ALONG another subquery to filter the results .

Answer 5

on windows: if you have mysql workbench. Go to server status. find the location of the server executable in my case:

 C:\ProgramData\MySQL\MySQL Server 5.7 

open my.ini file and find buffer_pool_size. Set the value to high. the default value is 8M. Here is how I fixed this problem

Answer 6

I am running MySQL windows with Workbench MySql. Go to C:\ProgramData\MySQL\...\my.ini Server> Server Status. The configuration file is indicated at the top of the file: "path" ( C:\ProgramData\MySQL\...\my.ini )

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

Restart sruver by going to Instance> Startup / Shutdown> Stop server (and then restart the server)

In my case, I was not able to delete records from my table.

Answer 7

It is worth saying that the figure used for this setting is in BYTES - it was difficult!

Answer 8

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

The solution will 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 will not work.

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