Deleting MySQL rows causes lock table error
- by Dave L
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.
