Bug #9975 Updates to large table fails with an error for lock table
Submitted: 18 Apr 2005 15:43 Modified: 28 Jun 2005 13:12
Reporter: Disha Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:5.0.3 OS:Any (Any)
Assigned to: Heikki Tuuri CPU Architecture:Any

[18 Apr 2005 15:43] Disha
Description:
When we have a query updating rows from a view based on a table with 5 million rows, the update query fails with an error that the total number of locks exceeds the lock table size.

How to repeat:
1. Connect to the MySQL server as 'root'

2. Create table tb2 by executing the following query:

create table tb2 (
f59 numeric (0) unsigned, 
f60 numeric (64) unsigned, 
f61 numeric (0) zerofill, 
f62 numeric (64) zerofill, 
f63 numeric (0) unsigned zerofill, 
f64 numeric (64) unsigned zerofill, 
f65 numeric (0,0), 
f66 numeric (63,63), 
f67 numeric (0,0) unsigned, 
f68 numeric (63,63) unsigned, 
f69 numeric (0,0) zerofill, 
f70 numeric (63,63) zerofill, 
f71 numeric (0,0) unsigned zerofill, 
f72 numeric (63,63) unsigned zerofill, 
f73 real, 
f74 real unsigned, 
f75 real zerofill, 
f76 real unsigned zerofill, 
f77 double DEFAULT 7.7, 
f78 double unsigned DEFAULT 7.7, 
f79 double zerofill DEFAULT 7.7, 
f80 double unsigned zerofill DEFAULT 8.8, 
f81 float not null DEFAULT 8.8, 
f82 float unsigned not null DEFAULT 8.8, 
f83 float zerofill not null DEFAULT 8.8, 
f84 float unsigned zerofill not null DEFAULT 8.8, 
f85 float(0) not null DEFAULT 8.8, 
f86 float(23) not null DEFAULT 8.8, 
f87 float(0) unsigned not null DEFAULT 8.8, 
f88 float(23) unsigned not null DEFAULT 8.8, 
f89 float(0) zerofill not null DEFAULT 8.8, 
f90 float(23) zerofill not null DEFAULT 8.8, 
f91 float(0) unsigned zerofill not null DEFAULT 8.8, 
f92 float(23) unsigned zerofill not null DEFAULT 8.8, 
f93 float(24) not null DEFAULT 8.8, 
f94 float(53) not null DEFAULT 8.8, 
f95 float(24) unsigned not null DEFAULT 8.8, 
f96 float(53) unsigned not null DEFAULT 8.8, 
f97 float(24) zerofill not null DEFAULT 8.8, 
f98 float(53) zerofill not null DEFAULT 8.8, 
f99 float(24) unsigned zerofill not null DEFAULT 8.8, 
f100 float(53) unsigned zerofill not null DEFAULT 8.8, 
f101 date not null DEFAULT 20, 
f102 time not null DEFAULT 20, 
f103 datetime not null DEFAULT '2/2/2', 
f104 timestamp not null DEFAULT 20001231235959, 
f105 year not null DEFAULT 2000, 
f106 year(3) not null DEFAULT 2000, 
f107 year(4) not null DEFAULT 2000, 
f108 enum("1enum","2enum") not null DEFAULT 1, 
f109 set("1set","2set") not null DEFAULT 1
)//

2. Populate the table with 5 million rows

3. Create a view as follows:
CREATE VIEW test.v_4_402004 AS SELECT f59,f60,f61 FROM tb2 //

4. Execute a update query as follows:
UPDATE test.v_4_402004 SET f59 = 891 WHERE f60 = 105 //
				
Expected Results : The update query should succeed and complete without any errors

Actual Results :   The update query fails with an error for total number of locks exceeding the lock table size

ERROR 1206:  The total number of locks exceeds the lock table size
[18 Apr 2005 15:49] Disha
This has failed for Innodb. We can configure the maximum number of locks with bdb, but not with other db engines.
[18 Apr 2005 18:12] MySQL Verification Team
This also happens with a simple insert around 4700000 rows:

mysql> insert into tb2  select * from tb2 limit 100000;
ERROR 1206 (HY000): The total number of locks exceeds the lock table size
[28 Jun 2005 13:12] Heikki Tuuri
Hi!

InnoDB stores row locks in the buffer pool. Did you configure the buffer pool bigger than the default 8 MB?

I am closing this bug report, because the behavior probably is th expected one with a very small buffer pool. A single row lock may consume more than 1 byte.

Regards,

Heikki