一个死锁问题

表结构：

CREATE TABLE `test` (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增id',
  `a` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '唯一健',
  `b` BIGINT(20) UNSIGNED NOT NULL DEFAULT '0' COMMENT '',
  `c` TINYINT UNSIGNED DEFAULT  0 COMMENT '',
  `d` bigint(20) unsigned NOT NULL DEFAULT '0'COMMENT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_a` (`a`),
  KEY `idx_b_c` (`b`, `c`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='测试表';

---

---

插入语句：

insert test(a,b) values(1,1);

insert test(a,b) values(2,2);

insert test(a,b) values(3,2);

insert test(a,b) values(4,4);

insert test(a,b) values(5,5);

 

 

事务一：

update test set d=1 where a = '2' and b= 2 and c in (0);

 

事务二：

update test set d=1 where a = '3' and b= 2 and c in (0);

 

发生了锁等待：

MySQL [gifshow]> select * from information_schema.innodb_locks\G;

*************************** 1. row ***************************

 lock_id: 458673016:562:5:3

 lock_trx_id: 458673016

  lock_mode: X

  lock_type: RECORD

 lock_table: `gifshow`.`test`

 lock_index: idx_b_c

 lock_space: 562

  lock_page: 5

   lock_rec: 3

  lock_data: 2, 0, 2

*************************** 2. row ***************************

  lock_id: 458672622:562:5:3

 lock_trx_id: 458672622

  lock_mode: X

  lock_type: RECORD

 lock_table: `gifshow`.`test`

 lock_index: idx_b_c

 lock_space: 562

  lock_page: 5

   lock_rec: 3

  lock_data: 2, 0, 2

 

疑问点？

explain  update test set d=1 where a = '2' and b= 2 and c in (0);

+----+-------------+-------+------------+-------------+----------------+----------------+---------+------+------+----------+----------------------------------------------+

| id | select_type | table | partitions | type        | possible_keys  | key            | key_len | ref  | rows | filtered | Extra                                        |

+----+-------------+-------+------------+-------------+----------------+----------------+---------+------+------+----------+----------------------------------------------+

|  1 | UPDATE      | test  | NULL       | index_merge | uniq_a,idx_b_c | idx_b_c,uniq_a | 10,402  | NULL |    1 |   100.00 | Using intersect(idx_b_c,uniq_a); Using where |

+----+-------------+-------+------------+-------------+----------------+----------------+---------+------+------+----------+----------------------------------------------+

update 语句为啥有唯一索引，为啥还要用到  index merge ？ 以及什么情况下会用到index merge?

 

同时做了个实验：

如果用到了索引 idx_b_c  那  不应该加间隙锁吗？

事务一：

update test set d=1 where a = '2' and b= 2 and c in (0);

事务二：

insert test(a,b) values("1.5",2);

但是执行事务二并没有发生锁等待？