MySQL Bugs: #39654: Insufficient error message for "The total number of locks exceeds ..."

Bug #39654	Insufficient error message for "The total number of locks exceeds ..."
Submitted:	25 Sep 2008 19:00	Modified:	21 Sep 2009 18:43
Reporter:	Hakan Küçükyılmaz	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.1, 6.0	OS:	Any
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
Trying to insert via INSERT ... SELECT ends up with:

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

Problem: What does the error message means? How can the end user workaround this error?

How to repeat:
./mysql-test-run.pl --start-and-exit
mysql -uroot -P9306 -h127.0.0.1 test
create table t1 (i1 int) engine innodb;
insert into t1 values (1);
insert into t1 select * from t1;
...
. repeat until
...
mysql> insert into t1 select * from t1;
ERROR 1206 (HY000): The total number of locks exceeds the lock table size

Suggested fix:
Give a better explanation of the problem and a way to workaround it.

Thank you for the bug report. Verified as described:

mysql 5.1 >insert into t1 select * from t1;
Query OK, 2097152 rows affected (1 min 55.48 sec)
Records: 2097152  Duplicates: 0  Warnings: 0

mysql 5.1 >insert into t1 select * from t1;
ERROR 1206 (HY000): The total number of locks exceeds the lock table size
mysql 5.1 >

This is a message from InnoDB.

The workaround is to increase the size of innodb buffer pool, 

innodb_buffer_pool_size

Real life workaround would be on application level, of course, most likely some
form of piecemeal insert.
I'd suggest fixing this by adding a comment about InnoDB error 1206 to
5.1/6.0 reference manuals, in 5.1 the section is 13.5.15.1 InnoDB Error Codes.
Assigning to Vasil.

If yes, can you push the client the advice: increase the buffer pool size?

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.