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.
我有一个包含21M记录的MySQL数据库，我正在尝试对大约1M记录进行更新，但是查询失败了ERROR 1206（HY000）：锁的总数超过了锁表大小。
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?
我没有权限更改像innodb_buffer_pool_size这样的MySQL配置参数。是否有不同的方法来实现相同的目标？
Thanks
谢谢
EDIT:
编辑：
- I've tried it in batches of 5000, it works a few times, but I get the same error
- 我已经尝试了5000批次，它可以工作几次，但我得到了同样的错误
- I've tried LOCK TABLES to lock the entire table and still it doesn't work.
- 我已经尝试过LOCK TABLES来锁定整个表格，但它仍无法正常工作。
2 个解决方案
#1
3
I think you can use the limit clause to do the updates in batches.
我认为你可以使用limit子句批量进行更新。
#2
1
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!
尝试在表级而不是行级锁定。使用LOCK TABLES MyTable WRITE。这可能会解决问题。虽然没有保证！不要忘记解锁桌子！
#1
3
I think you can use the limit clause to do the updates in batches.
我认为你可以使用limit子句批量进行更新。
#2
1
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!
尝试在表级而不是行级锁定。使用LOCK TABLES MyTable WRITE。这可能会解决问题。虽然没有保证！不要忘记解锁桌子！