I get the above error from MySQL.

What is going on, is that I have a InnoDB table "filltable", into which
I am doing a lot of INSERTs in parallel (populating a data warehouse).

In addition, at some point during the ongoing INSERT process, I issued
this command:

START TRANSACTION
INSERT INTO othertable ... SELECT from filltable
DELETE FROM filltable
COMMIT

The above query was stuck. and then I started getting these errors

"The total number of locks exceeds the lock table size"

Why am I getting this error? I was expecting INSERTs to lock up until
the transaction was done.

Thanks

i

On 9/18/2011 5:59 PM, Ignoramus30681 wrote:
Just what it says. When you INSERT rows, InnoDB will lock that row. As
you insert more rows, more rows will be locked. Eventually you have
more locks than your lock table can hold.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

On 2011-09-18 23:59, Ignoramus30681 wrote:
[...]
Googling for the error (you might want to give it a try indicates
that your bufferpool is to small.

Another opportunity is to lock the whole table and thereby eliminating
the need for row locks. I have not verified that innodb works this way,
so it might not work at all.


/Lennart

On 2011-09-19, Lennart Jonsson <erik.lennart.jonsson (AT) gmail (DOT) com> wrote:
Lennart, I changed my design yesterday.

Now I do

LOCK TABLES filltable WRITE, othertable WRITE
INSERT INTO othertable ... SELECT from filltable
UNLOCK TABLES

Unfortunately, I still get the error.

It seems to be that MySQL is not capable of INSERTing more than so
many rows at once.

I changed the buffer pool size to 2 GB, I will see if this helps.

i

On 2011-09-19 15:09, Ignoramus15208 wrote:
[...]
It appears as if innodb uses row-locking no matter what (atleast I
intepret it that way). On

http://dev.mysql.com/doc/refman/5.0/...ion-model.html

it is stated that:

The lock table in InnoDB is stored so space-efficiently that lock
escalation is not needed: Typically, several users are permitted to lock
every row in InnoDB tables, or any random subset of the rows, without
causing InnoDB memory exhaustion.


/Lennart

On 2011-09-19, Lennart Jonsson <erik.lennart.jonsson (AT) gmail (DOT) com> wrote:
Well, I guess I am not typical.

i

On 09/18/2011 05:59 PM, Ignoramus30681 wrote:
how many records are we talking about?

On 2011-09-19 19:06, Ignoramus15208 wrote:
:-) Don't get me wrong, without knowing any details, your ETL process
looks kinda normal to me. However, the statement above makes me wonder
whether innodb is the best choice for a DW. I googled for "mysql
datawarehouse engine" and it appears to be a couple of engines that
claims to be better than innodb for DW. Whether this is true or not (I
don't know), it's probably a good idea to read up on alternatives to
innodb for DW.


/Lennart

On 2011-09-19, boris <boris (AT) localhost (DOT) localdomain> wrote:
Roughly 250,000 or so.

i

On 2011-09-19, Lennart Jonsson <erik.lennart.jonsson (AT) gmail (DOT) com> wrote:
Lennart, right now, I am looking at Postgres closely. It appears to be
somewhat more robust. However, I have some MySQL experience and no
Postgres experience, and I am not yes convinced that Postgres is much
superior to MySQL in that regard.

What I do know by now, is that MySQL claims about "scalability"
contain more hot air than I would like.

Regarding the problem that started this thread, for example, the
proper behavior of a well designed database engine, would be to use
another locking technique instead of failing everything in a manner
that is hard to recover.

i