本文共 2168 字，大约阅读时间需要 7 分钟。

本文目的

1、非唯一索引gap锁的阻塞效果

2、on duplicate key update  死锁问题复现。  mysql 5.7.26 已经修复

一、查看mysql引擎

show engines ;show variables like '%storage_engine%' ;show variables like  'innodb_autoinc_lock_mode'  ;set GLOBAL innodb_status_output_locks=ON;

创建表

CREATE TABLE `user` (  `ID` int(11) NOT NULL AUTO_INCREMENT,  `name` varchar(11) COLLATE utf8mb4_bin DEFAULT NULL,  `COMMENT` varchar(11) CHARACTER SET utf8 DEFAULT NULL,  PRIMARY KEY (`ID`),  KEY `user_ix1` (`name`)) ENGINE=InnoDB AUTO_INCREMENT=34 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

插入数据

insert user select 20,20,20; insert user select 25,25,25; insert user select 30,30,30;-- truncate table user ;

 对比唯一索引和普通索引的处理方式 

ALTER TABLE    `study`.`user` ADD CONSTRAINT user_ix2 UNIQUE (`name`);ALTER TABLE    `study`.`user` ADD CONSTRAINT user_ix3 UNIQUE (`COMMENT`)ALTER TABLE    `study`.`user` DROP INDEX `user_ix1`;ALTER TABLE    `study`.`user` ADD INDEX user_ix1 (`name`)

查看锁

select * from information_schema.INNODB_LOCKS

间隙锁

--  SESSION 4  用于触发间隙锁begin;update user set name = '22' where name='20'commit ; -- 实验时先不执行commitrollback ; --  SESSION 1 begin;insert into  user(id , name , user.comment) value(21,'21','21')  ON DUPLICATE KEY UPDATE comment="s1" commit ;  -- 实验时先不执行commitrollback ;

session4先执行， session1中的sql会被gap锁住，因为name是非唯一索引，name=20 会锁住 （20,25）的索引范围

ONDUPLICATE KEY ON 死锁复现 -- mysql 导致

将name字段改为unique  key 

ALTER TABLE    `study`.`user` ADD CONSTRAINT user_ix1 UNIQUE (`name`)

--  SESSION 0begin;insert into user(name , user.comment) value('22','23')  ON DUPLICATE KEY UPDATE comment="s3" commit ;   -- 实验时先不执行commitrollback ;--  SESSION 1 begin;insert into user(name , user.comment) value('21','21')  ON DUPLICATE KEY UPDATE comment="s1" commit ; -- 实验时先不执行commitrollback ; --  SESSION 2 begin;insert into user(name , user.comment) value('21','22')  ON DUPLICATE KEY UPDATE comment="s2" commit ; -- 实验时先不执行commitrollback ;

依次执行 上面3条 insert into .. on duplicate key update  语句， 不要执行commit 或rollcack【这里写出来，只是为了在客户端执行的时候方便】， 

执行完 session 0 ， 1 后，可以看到 1被阻塞了

在执行session2

对session 0 执行 commit ；

session 1 ，执行成功 ， session2 被干掉了【mysql会解决死锁，就是干掉一个】；

这里面我们看到出现了gap锁，理论上不应该出现这个锁【因为这3条语句都是围绕unique key 搞事情，用也是应该是记录锁，所以这是个bug】

转载地址：http://qbdoi.baihongyu.com/