死锁:非唯一索引+RR，相邻key插入发生死锁

# DDL + DATA
DROP TABLE IF EXISTS `tb1`;
CREATE TABLE `tb1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  `c` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_uniq_a` (`a`),
  KEY `idx_b` (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert  into `tb1`(`id`,`a`,`b`,`c`) values (1,1,1,1618),(4,4,2,4131),(7,7,3,5798),(10,10,5,6597),(13,13,6,5592),(16,16,1,8169),(19,19,2,4066),(22,22,3,5824),(25,25,5,6922),(28,28,6,7139),(31,31,1,4926),(34,34,2,3215),(37,37,3,1295),(40,40,5,6829),(43,43,1,259),(46,46,2,807),(49,49,3,3256),(52,52,1,3862),(55,55,2,9539),(58,58,1,6109);


# SELECT id,b FROM tb1 ORDER BY b,id;
a b
1 1
16 1
31 1
43 1
52 1
58 1
4 2
19 2
34 2
46 2
55 2
7 3
22 3
37 3
49 3
10 5
25 5
40 5
13 6
28 6

### test sql
# Thread 1
ROLLBACK;
BEGIN;
SELECT * FROM `tb1` WHERE b=3 FOR UPDATE;

# Thread 2
ROLLBACK;
BEGIN;
SELECT * FROM `tb1` WHERE b=5 FOR UPDATE;

# Thread 1
INSERT INTO `tb1`(id,`a`,`b`) VALUES ( 50, 50, 3);  /* wait gap lock, by th2 hold */

# Thread 2
INSERT INTO `tb1`(id,`a`,`b`) VALUES ( 9, 9, 5);   /* wait gap lock, by th1 hold */


### deadlock
*** (1) TRANSACTION:
TRANSACTION 3967B, ACTIVE 29 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1248, 8 row lock(s), undo log entries 1
MySQL thread id 16, OS thread handle 0x1b10, query id 6501 localhost 127.0.0.1 root update
INSERT  INTO `tb1`(id,`a`,`b`) VALUES (9, 9,5)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 65870 n bits 88 index `idx_b` of table `innodb`.`tb1` trx id 3967B lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0

*** (2) TRANSACTION:
TRANSACTION 3967A, ACTIVE 35 sec inserting, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
5 lock struct(s), heap size 1248, 10 row lock(s), undo log entries 1
MySQL thread id 15, OS thread handle 0x764, query id 6502 localhost 127.0.0.1 root update
INSERT  INTO `tb1`(id,`a`,`b`) VALUES (50,50,3)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 65870 n bits 88 index `idx_b` of table `innodb`.`tb1` trx id 3967A lock_mode X locks gap before rec
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 65870 n bits 88 index `idx_b` of table `innodb`.`tb1` trx id 3967A lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0


疑问:
同一个gap被两个事务同时加X锁？

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26250550/viewspace-1223482/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26250550/viewspace-1223482/