InnoDB row level locks without SELECTs?

Question

I have several (>30) MySQL clients inserting into InnoDB tables concurrently. I keep encountering lock wait timeouts and can't figure out why. Here is the situation:

Each client runs the following transactions repeatedly:

Start transaction
Insert one row into table A
for i = 1 to 22
  Do some time-consuming calculation (30 sec)
  Insert results into table B
end
Commit transaction

The lock wait timeouts happen when inserting into table A. Table B's primary key is the same as table A's augmented by another field. Table B has a foreign key into table A.

There are no SELECTs anywhere. No row is inserted twice. Transaction isolation level is REPEATABLE_READ.

SHOW ENGINE INNODB STATUS shows up to 36 row-level locks for some active transactions.

I do not understand why there are any locks. Can anyone give me some hints as to what's going on?

Answer 1

MySQL is taking too much of the innodb buffer pool size for your table locking.

Insert innodb_buffer_pool_size=2g in my.cnf file, which is most probably in /etc/mysql/