删除操作将整个表锁定在innodb中 [英] delete operation locks whole table in innodb

问题描述

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

I have an issue with table locking in InnoDB on delete operation. I have a table queue with for example one column and a lot of transactions which can insert rows into this queue or delete them. There isn't any two transactions working with the same rows at the same time. So, all row locks must be distinct. But sometimes when delete operation deletes the most part of rows in the table, InnoDB prefers to use table lock instead of row lock and that causes deadlocks.

我无法完全重现此死锁，但发现了锁问题. 即我有表queue:id，其值为(1,3,4,5,6,7)

I can't reproduce this deadlock exactly, but I found that lock problem. i.e. I have table queue:id with values(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

推荐答案

首先假定id是主键或至少是索引列.

First of all assuming id is a primary key or at least indexed column.

插入操作不应锁定表，因此有可能在删除记录的同时执行任何其他更新/删除查询.

Insert should not lock the table, so chances are any other update/delete query is executing at same time of deletion the records.

如果不是这种情况，则可能是由于@a_horse_with_no_name中提到的间隙锁定"所致.

If it is not the case then it can be due to "gap locking" as mentioned @a_horse_with_no_name.

因此，当您再次遇到此问题时，则需要在末尾存储所有进程显示完整的进程列表"，并选中显示引擎innodb状态"，在其中将显示与死锁相关的processid，这将帮助您得到确切的问题.

So at which time you get this issue again then you need to store all processes "show full processlist" at your end and also check "show engine innodb status" where it will show you processids related with deadlock, this will help you to get exact problem.

进一步您可以避免此锁定，以便根据主键一一删除所有行.

Further You can avoid this locking to delete all rows one by one based on primary key.

这篇关于删除操作将整个表锁定在innodb中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！