我的问题是:为什么事务1持有主锁，为什么事务2需要主锁？我在 mysql 手册中找不到有关此锁的任何信息。

关于这个死锁的信息:

事务 1:

1988266681 Query        BEGIN
1988266681 Query        INSERT IGNORE INTO `tab1`
                            (`sn`, `is_fetch`, `is_done`, `add_time`)
                            VALUES ('4287', 0, 0, 1403186277)
1988266681 Query        COMMIT

交易 2:

1988212988 Query        BEGIN
1988212988 Query        SELECT sn FROM tab1 WHERE is_fetch = 0
                            LIMIT 200 FOR UPDATE
1988212988 Query        UPDATE `tab1` SET `is_fetch` = 1
                            WHERE sn in ('4287', '4387', '4487', '4587', '4687',
                            '4787', '4887', '4987')
1988212988 Query        COMMIT

架构信息:

CREATE TABLE `tab1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`sn` varchar(20) NOT NULL,
`is_fetch` tinyint(1) NOT NULL DEFAULT '0' ,
`is_done` tinyint(1) NOT NULL DEFAULT '0' ,
`add_time` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `sn` (`sn`),
KEY `is_fetch` (`is_fetch`),
KEY `is_done` (`is_done`)
) ENGINE=InnoDB AUTO_INCREMENT=4387619 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

死锁信息:

------------------------
LATEST DETECTED DEADLOCK
------------------------
140617 23:25:36
*** (1) TRANSACTION:
TRANSACTION 36E4099DA, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1248, 2 row lock(s), undo log entries 1
MySQL thread id 1937033606, OS thread handle 0x2ae3b0040700, query id 18031163883 192.168.1.65 db1 update
INSERT IGNORE INTO `tab1` (`sn`, `is_fetch`, `is_done`, `add_time`) VALUES ('1887', 0, 0, 1403018736)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 625 page no 5 n bits 1616 index `is_fetch` of table `db1`.`tab1` trx id 36E4099DA lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 1476 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 81; asc  ;;
1: len 4; hex 80410669; asc  A i;;

*** (2) TRANSACTION:
TRANSACTION 36E4099D7, ACTIVE 0 sec fetching rows, thread declared inside InnoDB 458
mysql tables in use 1, locked 1
6 lock struct(s), heap size 3112, 51 row lock(s), undo log entries 7
MySQL thread id 1937007092, OS thread handle 0x2ae8b5a26700, query id 18031163880 192.168.1.130 db1 Updating
UPDATE `tab1` SET `is_fetch` = 1 WHERE sn in ('1187', '1287', '1387', '1487', '1587', '1687', '1787')
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 625 page no 5 n bits 1616 index `is_fetch` of table `db1`.`tab1` trx id 36E4099D7 lock_mode X locks gap before rec
Record lock, heap no 1476 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 1; hex 81; asc  ;;
1: len 4; hex 80410669; asc  A i;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 625 page no 3 n bits 440 index `PRIMARY` of table `db1`.`tab1` trx id 36E4099D7 lock_mode X waiting
Record lock, heap no 168 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
0: len 4; hex 80411598; asc  A  ;;
1: len 6; hex 00036e4099da; asc   n@  ;;
2: len 7; hex e80000c0060110; asc        ;;
3: len 14; hex 3134303631373338343331383837; asc 1887;;
4: len 1; hex 80; asc  ;;
5: len 1; hex 80; asc  ;;
6: len 4; hex d3a05df0; asc   ] ;;

最佳答案

问:这是怎么发生的？

事务2获得了对'is_fetch`索引中记录的独占锁，并试图获得对表主键中记录的锁。

事务1获取了表PRIMARY key中记录的独占锁，并试图获取is_fetch索引中记录的锁。

InnoDB 自动检测到两个事务都无法继续，因为每个事务都持有另一个所需的资源。 InnoDB 终止其中一个事务，以便另一个事务可以继续。

请注意，INSERT 语句可以获得唯一索引中记录的“间隙”锁。导致死锁的不仅仅是 INSERT，它是并发运行的事务的组合。

InnoDB 记录锁定记录在 MySQL 引用手册中:

http://dev.mysql.com/doc/refman/5.5/en/innodb-record-level-locks.html

关于mysql - 插入导致 InnoDB 中出现死锁。这怎么发生的？，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/24327317/