Does INSERT IGNORE lock the table even if it ignor

问题:

In MySQL, If I have an INSERT IGNORE command that eventually doesn't insert into the table, due to the uniqueness constraint. Does it ask and wait for a write lock? Or does MySQL recognize the IGNORE keyword, and uses a read lock before the write lock, and only if it really needs to write it asks for the write lock?

回答1:

It depends on the engine - MyIsam and InnoDb behave diferrently.

For MyIsam tables, if a record already exist in the table (even if it is not commited yet), a regular INSERT of the same record (the same unique key) in the other session reports a duplctate key error - so INSERT IGNORE just ignores the error and proceed further.

On InnoDB table, if the record is not locked, the regular INSERT will immediately report the duplicate key error (INSERT IGNORE will skip the error and go on).
But if the record is locked by the other session (for example the record is inserted but not commited yet, or the record is locked by an UPDATE or DELETE or SELECT FOR UPDATE command), the INSERT command will "hang" and will be waiting until the other session will remove the lock (by COMMIT or ROLLBACK). Then, if record still exists after removing the lock, INSERT will report the error (INSERT IGNORE will ignore the error), but if record doesn't exist, INSERT will add this record to the table.

IGNORE keyword just says "in case of any error just ignore it an go on", but it doesn't affect locking behaviour.