I have a MySQL database with 21M records and I'm trying to do an update on about 1M records but the query fails with ERROR 1206 (HY000): The total number of locks exceeds the lock table size.
Is it possible to update the table without acquiring locks?
I don't have access to change MySQL configuration parameters like innodb_buffer_pool_size
. Is there a different way to achieve the same?
Thanks
EDIT:
- I've tried it in batche开发者_如何学Pythons of 5000, it works a few times, but I get the same error
- I've tried LOCK TABLES to lock the entire table and still it doesn't work.
I think you can use the limit clause to do the updates in batches.
Try locking at table level rather than row level. Use LOCK TABLES MyTable WRITE
. This might solve the problem. No guarantees though! Don't forget to unlock the tables either!
精彩评论