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?