I had a couple million rows to delete but they can't be deleted at once without this error

ERROR 1206 (HY000): The total number of locks exceeds the lock table size

So I wrote a script to delete 100,000 rows 10,000 at a time. It ran once but when I run it a second time I get the error on the first attempt to delete 10,000.

The way I'm trying to delete the 10,000 rows is to use a delete statement that refers to all 2 million rows but I use a limit clause to affect only 10,000.

I've tried adding an "unlock tables;" statement to the script before the first delete but that doesn't help. I still get the lock table error on the first delete.

Any ideas how I can do this?

Is there a way I can tell it NOT to lock records? I can make sure nothing else is accessing the table.

  • How many RAM you have? grep innodb_buffer_pool_size /path/to/my.cnf? – quanta Aug 13 '11 at 16:44
  • My config file doesn't have that constant in it. But if the file runs once, isn't that proof there's enough ram allocated? – Dave L Aug 13 '11 at 20:25

Is it the InnoDB storage engine?

This error means that MySQL doesn't have enough room to store all of the rows lock that it would need to execute your query. Depend on RAM you have, set innodb_buffer_pool_size to a reasonable value (256MB, 512MB), you will probably never encounter this error.

Check the default value with mysql> show global variables like 'innodb_buffer%';.

  • I ran your query and I got this result: innodb_buffer_pool_size 8388608. I assume that means I should not be having that problem right? – Dave L Aug 15 '11 at 15:47
  • 8MB maybe too small. Just change it as I suggested. – quanta Aug 15 '11 at 16:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.