MySQL Bugs: #15667: The total number of locks exceeds the lock table size

Bug #15667	The total number of locks exceeds the lock table size
Submitted:	11 Dec 2005 13:51	Modified:	19 Oct 2008 7:30
Reporter:	Kevin xu	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S1 (Critical)
Version:	4.1, 6.0.4	OS:	Windows (Win XP)
Assigned to:		CPU Architecture:	Any

Description:
When load a large data file (2GB) file to the database via MySQL Query (version 1.1.13), it's going to fail with with this error message: "The total number of locks exceeds the lock table size"

How to repeat:
When load a large data file (2GB) file to the database via MySQL Query (version 1.1.13), it's going to fail with with this error message: "The total number of locks exceeds the lock table size"

Thank you for a problem report. Please, inform about the exact version of MySQL server used (4.1.x). Your server variables' values will be usefull to. So, please, send the results of the SHOW VARIABLES statement (or your my.ini file).

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Same exact thing just happened to me - but on Mac OS X - on a MacBook Pro. using DBVis. But wondering if it's really a bug - or are we just doing something wrong?

Same thing on Linux, MySQL 5.

insert into archive select * from largetable where conditions...;

archive table is myisam fixed with no keys.
largetable is innodb, has 6mln short rows, conditions match 20% of it.

Same with MySQL 5.1.19-beta

root@xyz[abc]> alter table messages_user_inbox partition by hash(message_id) partitions 50;
ERROR 1206 (HY000): The total number of locks exceeds the lock table size

The error occurs after 3 hours...

*************************** 1. row ***************************
           Name: messages_user_inbox
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 6272490
 Avg_row_length: 428
    Data_length: 2688548864
Max_data_length: 0
   Index_length: 405438464
      Data_free: 0
 Auto_increment: 50374572
    Create_time: 2007-06-28 22:18:33
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment: InnoDB free: 17646592 kB

this generates the same "total number of locks exceeds the lock table size" error
for a table of a few hundred thousand rows, just trying to merge 2 rows)

update mytable set myidcol=concat(myidcol,colb);

This looks like InnoDB-related bug. Any repeatable test case for version 5.x.y is greatly appreciated.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

I get this error when trying to delete more than 10000 records from a 6.5 million row dataset.  There are multiple `on delete cascade` statements associated with the records being deleted.

Running 5.0.24 on Mandriva Corporate Server 4.0

I'd love to be able to delete all of my stale data, but it's incredibly slow to delete in its current state.

I have 10 millions of rows in one table. When i try to delete the 4 lakhs rows, i got "The total number of locks exceeds the lock table size".

Please advise.

All reporters:

Please, increase your innodb_buffer_pool_size ten times at least if you have a default size:

| innodb_buffer_pool_size         | 8388608                                           

Check if it will make any difference.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Reporters please if you are able to provide feedback as requested by Valery
comment here and open the bug report. Thanks in advance.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Encountered the same problem, and after applying Valeriy's solution it was fixed.

Server version: 5.0.51a-3ubuntu5.1
Os: Ubuntu server 64bit 8.04

So, this is not a bug. You should just set server variables properly.

I am getting the same error. I do have innodb table

| user                           | InnoDB |      10 | Compact    | 4289383 |            517 |  2220900352 |               0 |   2933047296 | 2291138560 |       63300260 | 2009-04-28 04:32:29 | NULL                | NULL       | latin1_swedish_ci |     NULL |                |         | 

when try to change the engine to ndbcluster it giving the error. 
I execute the ALTER TABLE on one of the sql nodes in cluster. That SQL node have this innodb table. 

please help.

I tried increasing innodb_buffer_pool_size to 2gb and it's still timing out on me.  I've even tried using phpMyAdmin to go in and delete a single record - and it times out.  In our case, this is running on a RedHat server, not Windows.  This is 60gb worth of data that I can't get rid of.

Not a permanent solution but dbas might want to check if underlying table has gone through lots of truncate/delete and rebuilding table will reduce Data_length, Avg_row_length and that would mean less number of locks required. I had similar problem and i just did alter table tablename Engine= InnoDB; which rebuilt the table and now insert works without "The total number of locks exceeds the lock table size" error.

i had the same problem selecting a few million records out of one table into an archive table on an innodb database. i bumped the default value for innodb_buffer_pool_size from 8388608 to 83886080 and it solved my problem, however going from 8 megs to 16 megs didn't work, neither did 32 megs or 64 megs. i had to bump it up 10x for it to work. 

There has to be way of calculating an adequate value for this based on the massiveness of your dataset. I would love to see that formula.