delete操作锁定innodb中的整个表

Question

在执行删除操作时，我在InnoDB中遇到了表锁定问题。例如，我有一个包含一列和许多事务的表队列，可以在此队列中插入行或删除行。没有任何两个事务同时处理相同的行。因此，所有行锁必须是不同的。但有时，当删除操作删除表中的大部分行时，InnoDB倾向于使用表锁而不是行锁，这会导致死锁。

我不能准确地重现这个死锁，但我发现了那个锁问题。例如，我有表队列:id，值为(1，3，4，5，6，7)

事务1：

insert into queue value(2);

事务2：

delete from queue where id in (1,3,4,5,6,7); -- here the lock comes

Answer 1

首先，假设id是一个主键或者至少是一个索引列。

Insert不应该锁定表，因此任何其他update/delete查询很可能在删除记录的同时执行。

如果不是这样，那么它可能是由于@a_horse_with_no_name提到的“间隙锁定”。

所以当你再次遇到这个问题时，你需要在你的终端存储所有的进程"show full processlist“，并检查"show engine innodb status”，它将显示与死锁相关的进程，这将帮助你获得确切的问题。

此外，您还可以避免这种锁定，从而根据主键逐个删除所有行。