开发者

UPDATE query in MySQL on large table

开发者 https://www.devze.com 2023-02-08 07:18 出处:网络
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.

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:

  1. I've tried it in batche开发者_如何学Pythons of 5000, it works a few times, but I get the same error
  2. 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!

0

精彩评论

暂无评论...
验证码 换一张
取 消