Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
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... –  a_horse_with_no_name Jan 8 '13 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." –  alex Jan 8 '13 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 Jan 8 '13 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. –  alex Jan 9 '13 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/

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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