即使忽略表的插入，INSERT IGNORE也会锁定表吗? [英] Does INSERT IGNORE lock the table even if it ignores the insert?

问题描述

在MySQL中，由于唯一性约束，如果我有一个INSERT IGNORE命令，该命令最终未插入表中.它会询问并等待写锁定吗?还是MySQL能够识别IGNORE关键字，并在写锁之前使用读锁，并且仅在确实需要写时才要求写锁?

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?

推荐答案

这取决于引擎-MyIsam和InnoDb的行为不同.

对于MyIsam表，如果表中已经存在一条记录(即使尚未提交)，则在另一个会话中对同一记录(相同的唯一键)的常规INSERT报告一个重复的键错误-因此INSERT IGNORE只会忽略错误，然后继续进行操作.

在InnoDB表上，如果记录未锁定，则常规INSERT将立即报告重复键错误(INSERT IGNORE将跳过该错误并继续).
但是，如果该记录被另一个会话锁定(例如，该记录已插入但尚未提交，或者该记录被UPDATE或DELETE或SELECT FOR UPDATE命令锁定)，则INSERT命令将挂起"并处于等待状态直到另一个会话将删除该锁(通过COMMIT或ROLLBACK).然后，如果删除锁定后记录仍然存在，则INSERT将报告错误(INSERT IGNORE将忽略该错误)，但是，如果记录不存在，则INSERT将此记录添加到表中.

IGNORE关键字只是说如果有任何错误，请忽略它，然后继续"，但这不会影响锁定行为.

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.

这篇关于即使忽略表的插入，INSERT IGNORE也会锁定表吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！