mysql - 莫名其妙的 InnoDB 死锁

Question

这个 InnoDB 死锁真的让我抓狂。据我所知:

1. 事务 (1) 正在等待“应用程序”上的 PRIMARY

2. 后者已被 (2) 获取用于一些相当长时间运行的更新(SELECT * FROM applications WHERE ID = xxxx FOR UPDATE)

到目前为止，一切顺利 - 人们会期望 (1) 等待锁定，然后继续其工作。

但是，一旦 (2) 准备好保存它的工作(并提交事务)，它就会因死锁而失败，因为出于某种原因 (1) 已经设法锁定了某个二级索引。 如果 PRIMARY 被 (2) 持有，(1) 到底是如何设法获得该行的任何锁的。

如果 (2) 最初获得了 PRIMARY 锁 (SELECT * FROM applications WHERE ID = xxxx FOR UPDATE)，那么它也会在所有二级索引上设置锁。如果 tasked==NULL 是否有可能它不会锁定“tasked”索引，从而允许(1)在锁定 PRIMARY 之前获取“tasked”的锁？

我没有运气复制这个场景..

谢谢!

劳里

------------------------
LATEST DETECTED DEADLOCK
------------------------
130428 17:04:06
*** (1) TRANSACTION:
TRANSACTION A369A8C, ACTIVE 1 sec fetching rows
mysql tables in use 3, locked 3
LOCK WAIT 217 lock struct(s), heap size 31160, 636 row lock(s)
MySQL thread id 13310554, OS thread handle 0x7f06cc2d7700, query id 177699568 217.146.78.151 shard67 Sending data
SELECT `applications`.* FROM `applications`
 LEFT JOIN `applicants` ON applicants.ID = applications.applicant_ID
 LEFT JOIN `regions` ON regions.ID = applicants.region_ID WHERE (status <> 'Blank') AND (status <> 'Closed') AND (revised < 1367154245) AND (tasked IS NULL OR tasked < 1367147045) AND (commence_year >= '2013') AND (regions.instance_ID = '1') ORDER BY `tasked` ASC, `ID` ASC LIMIT 20 FOR UPDATE
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 20021 page no 1192 n bits 80 index `PRIMARY` of table `dream-shard67`.`applications` trx id A369A8C lock_mode X locks rec but not gap waiting
*** (2) TRANSACTION:
TRANSACTION A369A87, ACTIVE 1 sec updating or deleting
mysql tables in use 1, locked 1
16 lock struct(s), heap size 3112, 22 row lock(s), undo log entries 5
MySQL thread id 13310563, OS thread handle 0x7f06cc151700, query id 177699599 217.146.76.127 shard67
UPDATE `applications` SET `revised` = '1367157846', `tasked` = '1367157846', `revision_ID` = '140649', `xml` = 'Zms6\noMmI$%[v....snipped binary data
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 20021 page no 1192 n bits 72 index `PRIMARY` of table `dream-shard67`.`applications` trx id A369A87 lock_mode X locks rec but not gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 20021 page no 292 n bits 1280 index `tasked` of table `dream-shard67`.`applications` trx id A369A87 lock_mode X locks rec but not gap waiting
*** WE ROLL BACK TRANSACTION (2)

Answer 1

确保你做了其中的大部分:
http://dev.mysql.com/doc/refman/5.0/en/innodb-deadlocks.html