使用主键 IN 语句删除时的 Mysql Innodb 死锁

Question

寻求帮助以了解为什么会出现这种僵局。我已经阅读了 MySQL 文档和许多关于死锁相关问题的 SO 问题，但我显然遗漏了一些东西。

这是有问题的表:

CREATE TABLE `table_queue` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB ;

它有一大堆其他列已被删除以保持简单(没有一个是外键)。

两个或多个线程正在执行以下查询:

DELETE q  FROM table_queue q WHERE q.id IN(165765,165770,165782,165787,165791 .. );

IN 语句最多可以有 1,000 个值。如您所见，它正在删除首要的关键。但是innodb status输出显示有一个线程在等待锁在甚至不在其删除列表中的记录上(在 IN 子句中)。

显示引擎 INNODB 输出:

------------------------
LATEST DETECTED DEADLOCK
------------------------
140526 15:54:03
*** (1) TRANSACTION:
TRANSACTION A841, ACTIVE 1 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 36 lock struct(s), heap size 2496, 882 row lock(s), undo log entries 70
MySQL thread id 59, OS thread handle 0xa69d9b40, query id 40446 localhost 127.0.0.1 localhost Sending data
DELETE q  FROM table_queue q WHERE q.id IN(165765,165770,165782,165787, ...
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 4653 n bits 216 index `PRIMARY` of table `maildb`.`table_queue` trx id A841 lock_mode X waiting
Record lock, heap no 67 PHYSICAL RECORD: n_fields 20; compact format; info bits 0
 0: len 4; hex 800288fb; asc     ;;
 1: len 6; hex 00000000a82d; asc      -;;
 2: len 7; hex a2000001e83a9e; asc      : ;;
 3: len 4; hex 80000001; asc     ;;
 4: len 4; hex 80000002; asc     ;;
 5: len 11; hex 6361726c6f732b36313535; asc jsmith+6155;;
 6: len 5; hex 536d697468; asc Smith;;
 7: len 22; hex 6361726c6f732b3631353540636f6d6d65722e636f6d; asc jsmith+6155@domain.com;;
 8: len 4; hex 8000180b; asc     ;;
 9: len 8; hex 43617465676f7279; asc Category;;
 10: len 4; hex 80000000; asc     ;;
 11: len 4; hex 80000000; asc     ;;
 12: len 4; hex 80000000; asc     ;;
 13: len 4; hex 80000000; asc     ;;
 14: SQL NULL;
 15: SQL NULL;
 16: len 1; hex 91; asc  ;;
 17: SQL NULL;
 18: SQL NULL;
 19: SQL NULL;

*** (2) TRANSACTION:
TRANSACTION A844, ACTIVE 1 sec starting index read
mysql tables in use 1, locked 1
30 lock struct(s), heap size 2496, 646 row lock(s)
MySQL thread id 60, OS thread handle 0xa69a8b40, query id 40453 localhost 127.0.0.1 localhost Sending data
DELETE q  FROM table_queue q WHERE q.id IN(166139,166146,166162,166166,166171,166175,166177,....
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 4653 n bits 216 index `PRIMARY` of table `maildb`.`table_queue` trx id A844 lock mode S locks rec but not gap
Record lock, heap no 67 PHYSICAL RECORD: n_fields 20; compact format; info bits 0
 0: len 4; hex 800288fb; asc     ;;
 1: len 6; hex 00000000a82d; asc      -;;
 2: len 7; hex a2000001e83a9e; asc      : ;;
 3: len 4; hex 80000001; asc     ;;
 4: len 4; hex 80000002; asc     ;;
 5: len 11; hex 6361726c6f732b36313535; asc jsmith+6155;;
 6: len 5; hex 536d697468; asc Smith;;
 7: len 22; hex 6361726c6f732b3631353540636f6d6d65722e636f6d; asc jsmith+6155@domain.com;;
 8: len 4; hex 8000180b; asc     ;;
 9: len 8; hex 43617465676f7279; asc Category;;
 10: len 4; hex 80000000; asc     ;;
 11: len 4; hex 80000000; asc     ;;
 12: len 4; hex 80000000; asc     ;;
 13: len 4; hex 80000000; asc     ;;
 14: SQL NULL;
 15: SQL NULL;
 16: len 1; hex 91; asc  ;;
 17: SQL NULL;
 18: SQL NULL;
 19: SQL NULL;

Record lock, heap no 74 PHYSICAL RECORD: n_fields 20; compact format; info bits 0
 0: len 4; hex 80028902; asc     ;;
 1: len 6; hex 00000000a82d; asc      -;;
 2: len 7; hex a2000001e83af9; asc      : ;;
 3: len 4; hex 80000001; asc     ;;
 4: len 4; hex 80000001; asc     ;;
 5: len 11; hex 6361726c6f732b36313632; asc jsmith+6162;;
 6: len 5; hex 536d697468; asc Smith;;
 7: len 22; hex 6361726c6f732b3631363240636f6d6d65722e636f6d; asc jsmith+6162@domain.com;;
 8: len 4; hex 80001812; asc     ;;
 9: len 8; hex 43617465676f7279; asc Category;;
 10: len 4; hex 80000000; asc     ;;
 11: len 4; hex 80000000; asc     ;;
 12: len 4; hex 80000000; asc     ;;
 13: len 4; hex 80000000; asc     ;;
 14: SQL NULL;
 15: SQL NULL;
 16: len 1; hex 94; asc  ;;
 17: SQL NULL;
 18: SQL NULL;
 19: SQL NULL;
...
...

据我了解，事务A841是想获取某条记录的独占锁。在这种情况下我可以告诉记录有 id = 166139。查看应用程序日志，我可以看出事务/线程在其 DELETE IN 子句中没有 id 166139 那么为什么它需要对该记录进行锁定？

如果您使用主键明确指定要删除的行，MySQL 应该只锁定那些匹配的 PK 行，我是否误解了？是否有可能锁定其他相邻行出于某种原因？

Answer 1

Innodb 会锁定所有需要扫描的行，以防删除操作。您可以通过阅读以下博客更好地理解:

BLOG LINK