mysql “Lock wait timeout exceeded” two transaction with different keys indexed 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.
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 &quot;row&quot; 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.