Mysql 锁的测试

本文目的

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 ; -- 实验时先不执行commit
rollback ;

 --  SESSION 1 
begin;
insert into  user(id , name , user.comment) value(21,'21','21')  ON DUPLICATE KEY UPDATE comment="s1" 
commit ;  -- 实验时先不执行commit
rollback ;

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 0
begin;
insert into user(name , user.comment) value('22','23')  ON DUPLICATE KEY UPDATE comment="s3" 
commit ;   -- 实验时先不执行commit
rollback ;

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

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

依次执行 上面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】