Why I can't insert new row into partially locked table in MySQL 5 InnoDB?

Question

In manual it said that InnoDB has row-level locking, so why if I select some of the rows with FOR UPDATE statement it won't let me insert new row into that table? New row shouldn't be locked after all, it wasn't selected.

Answer 1

In InnoDB, an insert on an auto-increment column results in a table lock. Selecting a row FOR UPDATE results in a row level lock.

Since you can't acquire a table lock if there are row locks, the UPDATE prevents the INSERT until its transaction is committed.