MySQL Bugs: #26984: ERROR 1206 (HY000): The total number of locks exceeds the lock table size

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

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?

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.

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

Thanks Heikki!

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

tia roland