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
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?

  • 1
    I think that's because of MySQL's "gap locking". It locks rows that aren't there in order to prepare for inserts "near by" (e.g. with the next higher auto-increment). If I'm not mistaken this has something to do with the clustered index that is build from the primary key. But I could be totally mistaken as well...
    – user1822
    Commented Jan 8, 2013 at 20:30
  • According to my understanding of the manual, several concurrent inserts into the same gap should be possible if the inserted rows are disjoint: dev.mysql.com/doc/refman/5.5/en/innodb-record-level-locks.html "A type of gap lock called an insertion intention gap lock is set by INSERT operations prior to row insertion. This lock signals the intent to insert in such a way that multiple transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap."
    – aecker
    Commented Jan 8, 2013 at 20:45
  • I believe row lock will be held on master table (if I understood you right, it's A) when you insert a record into details (B) until you commit/rollback transaction
    – a1ex07
    Commented Jan 8, 2013 at 22:33
  • But what would be locked in A? Presumably the row corresponding to the inserts in B, right? That shouldn't be a problem since each transaction involves only exactly one row in A and a few corresponding rows in B. I simply don't understand why the transactions interact.
    – aecker
    Commented Jan 9, 2013 at 8:35

1 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/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.