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
add comment

Your Answer

 
discard

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

Browse other questions tagged or ask your own question.