UPDATE query in MySQL on large table

Question

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 batches 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.

http://rackerhacker.com/2010/02/16/mysql-the-total-number-of-locks-exceeds-the-lock-table-size-2/ — Haim Evgi, Feb 02 '11 at 15:22
What a stupid limitation. Anyone know of a work around? I guess use MyISAM? — chmullig, Mar 05 '11 at 15:44

score 3 · Accepted Answer · answered Feb 02 '11 at 15:17

3

I think you can use the limit clause to do the updates in batches.

answered Feb 02 '11 at 15:17

Faisal Feroz

12,458
4
40
51

I've tried it in batches of 5000, it works a few times, but I get the same error – rampr Feb 02 '11 at 15:29
Then perhaps try tinier chunks? :) – gnur Feb 07 '11 at 11:59

score 1 · Answer 2 · answered Feb 02 '11 at 15:22

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!

answered Feb 02 '11 at 15:22

anothershrubery

20,461
14
53
98

UPDATE query in MySQL on large table

2 Answers2