Contents
How do you fix 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.
How do I lock a table in MySQL?
The correct way to use LOCK TABLES and UNLOCK TABLES with transactional tables, such as InnoDB tables, is to begin a transaction with SET autocommit = 0 (not START TRANSACTION ) followed by LOCK TABLES , and to not call UNLOCK TABLES until you commit the transaction explicitly.
Does delete query lock table?
A delete statement places an exclusive (X) lock on the table. This mean no other query can modify the table’s data until the DELETE transaction completes. You can still read data, but need to use the NOLOCK hint or read uncommitted isolation level.
Does MySQL transaction lock table?
1 Answer. A single statement like that works the same with MyISAM or InnoDB, with a transaction or with autocommit=ON. InnoDB locks only rows, not tables.
Why does MySQL say total number of locks exceeds lock table size?
ERROR 1206 (HY000): The total number of locks exceeds the lock table size. InnoDB stores its lock tables in the main buffer pool. This means that the number of locks you can have at the same time is limited by the innodb_buffer_pool_size variable that was set when MySQL was started.
How often do I need to delete rows in MySQL?
If you need to delete a million rows, try deleting 5-10% of those rows per transaction. This may allow you to sneak under the lock table size limitations and clear out some data without restarting MySQL. To learn more about InnoDB’s parameters, visit the MySQL documentation.
Where are the lock tables stored in MySQL?
InnoDB stores its lock tables in the main buffer pool. This means that the number of locks you can have at the same time is limited by the innodb_buffer_pool_size variable that was set when MySQL was started. By default, MySQL leaves this at 8MB, which is pretty useless if you’re doing anything with InnoDB on your server.
What does error code 1206 mean in MySQL?
I’m running a report in MySQL. One of the queries involves inserting a large amount of rows into a temp table. When I try to run it, I get this error: Error code 1206: The number of locks exceeds the lock table size. I’ve read that changing the configuration file to increase the buffer pool size will help, but that does nothing.