Bug #26984 ERROR 1206 (HY000): The total number of locks exceeds the lock table size
Submitted: 9 Mar 2007 1:34 Modified: 13 May 2010 16:04
Reporter: Roland Bouman Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S4 (Feature request)
Version:5.1.16 OS:Linux (linux)
Assigned to: Assigned Account CPU Architecture:Any
Tags: innodb, lock table
Triage: Triaged: D5 (Feature request)

[9 Mar 2007 1:34] Roland Bouman
Description:
It is fairly easy to exceed the lock table size for innodb; it will consistenly fail because of this when inserting a few million rows. Inserting from an Innodb table into a black hole table does not result in this error.

How to repeat:
CREATE TABLE INNODB_test (
    id int NOT NULL AUTO_INCREMENT
,   name varchar(32) NOT NULL,PRIMARY KEY (id)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

INSERT INTO INNODB_test(name) values (now());

INSERT INTO INNODB_test(name) SELECT unhex(sha1(now()+id)) FROM INNODB_test;

repeat last statement 22 times, the 23rd time it will fail:

mysql> INSERT INTO INNODB_test(name) SELECT unhex(sha1(now()+id)) FROM INNODB_test;
Query OK, 4194312 rows affected (1 min 38.95 sec)
Records: 4194312  Duplicates: 0  Warnings: 0

mysql> INSERT INTO INNODB_test(name) SELECT unhex(sha1(now()+id)) FROM INNODB_test;
ERROR 1206 (HY000): The total number of locks exceeds the lock table size

An equivalent BLACKHOLE table does not fail:

mysql> create table blackhole_test like innodb_test;
Query OK, 0 rows affected (0.20 sec)

mysql> INSERT INTO blackhole_test(name) SELECT unhex(sha1(now()+id)) FROM INNODB_test;
Query OK, 8388624 rows affected (3 min 29.79 sec)
Records: 8388624  Duplicates: 0  Warnings: 0

(please note that the numbers are slightly off as i accidentally inserted the inital row a second time during this test - it does work too by simple repeating all mentioned times.

Suggested fix:
Make lock table size configurable?
[9 Mar 2007 8:52] Valeriy Kravchuk
Thank you for a problem report. I agree with your suggested fix, so this looks like a reasonable feature request for InnoDB team. 

If this feature can not be implemented any time soon, we need to document this problem explicitely, with some way to calculate how much memory in the buffer pool will be used for N locks.
[28 Mar 2007 12:26] Heikki Tuuri
Hi!

InnoDB could let the lock table overflow from the buffer pool to the OS memory allocation.

Though, if you set innodb_buffer_pool_size to a reasonable value, you will probably never encounter this error.

Regards,

Heikki
[28 Mar 2007 13:05] Roland Bouman
Thanks Heikki!

How can I compute these values?
I could not find any documentation regarding this.

tia roland