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 set of tables that I need to delete their records with a specific key and insert new records. Different transactions are used one for each key:

Transaction 1:

DELETE FROM table1 WHERE id=1; -- many records
DELETE FROM table2 WHERE id=1; -- many records
INSERT INTO table1 VALUES (1,...),...; -- many records
INSERT INTO table2 VALUES (1,...),...; -- many records

Transaction 2:

DELETE FROM table1 WHERE id=2; -- many records
DELETE FROM table2 WHERE id=2; -- many records
INSERT INTO table1 VALUES (2,...),...; -- many records
INSERT INTO table2 VALUES (2,...),...; -- many records

When the two transactions are running at the same time, I get the lock wait timeout exceeded error for one of them. The id for both table is indexed, so it should be row based locking, why do I still get this error?

Using InnoDB tables.

share|improve this question
add comment

1 Answer

If the values for the 'id' are adjacent, like '1' and '2' in your example, including the case where they are not adjacent but there are no rows in the index with values in between them, then I would suggest that you are bumping into the index gap lock... which is part of "row" locking.

InnoDB performs row-level locking in such a way that when it searches or scans a table index, it sets shared or exclusive locks on the index records it encounters. Thus, the row-level locks are actually index-record locks. In addition, a next-key lock on an index record also affects the “gap” before that index record. That is, a next-key lock is an index-record lock plus a gap lock on the gap preceding the index record. If one session has a shared or exclusive lock on record R in an index, another session cannot insert a new index record in the gap immediately before R in the index order.

http://dev.mysql.com/doc/refman/5.6/en/innodb-record-level-locks.html

As that page suggests, a transaction isolation level of READ COMMITTED might be helpful, if that's not what you're using and if the gap locks are the issue.

It also might be contention for the primary key on the inserts.

The INNODB_LOCKS and INNODB_LOCK_WAITS tables in information_schema could provide additional diagnostic information, as might SHOW ENGINE INNODB STATUS; though it can be cryptic.

share|improve this answer
    
Thanks @michael-sqlbot –  Navid Nov 19 '13 at 19:20
add comment

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.