Mysql之锁机制 (2)

举例说明

CREATE TABLE `t` ( `id` int(11) NOT NULL, `c` int(11) DEFAULT NULL, `d` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `c` (`c`) ) ENGINE=InnoDB; insert into t values(0,0,0),(5,5,5), (10,10,10),(15,15,15),(20,20,20),(25,25,25); session A session B
begin;    
select * from t where id=9 for update;    
  begin;  
  select * from t where id=9 for update;  
  insert into t values(9,9,9);  
insert into t values(9,9,9);    

上述语句执行结果如何？出现了死锁，为什么呢？

session A、session B中select for update由于id=9不存在，均加上了(5,10)的间隙锁，这也证明了间隙锁之间不存在冲突。接下来A、B都向这个间隙里插入数据，互相和对方持有的间隙锁冲突，相互等待形成死锁。如果开启了死锁检测，InnoDB会马上发现死锁关系，让A中插入报错返回。

从以上例子也可以看出，由于间隙锁的引入，虽然解决了幻读，可也影响了数据库的并发度。如果实际业务场景不需要保证可重复读，就可以考虑使用读已提交，同时binlog_format=row，保证主从同步的一致性。

加锁规则：两个原则、两个优化、一个bug

原则 1：加锁的基本单位是 next-key lock，前开后闭区间

原则 2：查找过程中访问到的对象才会加锁

优化 1：索引上的等值查询，给唯一索引加锁的时候，匹配上数据，next-key lock 退化为行锁

优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁

一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止

以上规则，其实可以理解为数据查找过程中，扫描到的对象应该加锁，排除逻辑上明显不需要加锁的对象，即为加锁范围

重点：

加锁是分步进行的，例如：c>=10 and c<=11，分解为c=10、c>10 and c<11、c=11依次进行锁申请

间隙由右边的间隙记录，这也导致了不同方向上扫描加锁范围不一样

从扫描到的第一个记录上作为起点，例如：普通索引c取值为[0,5,10,15,20]，c>10和c>=10其分别第一个扫描到的数为15、10，因此第一个间隙锁为(10,15]、(5,10]

读已提交下的应用

在外键场景下有间隙锁，场景待确认

insert intention lock

插入意向锁，仅用于insert语句，表明将在某间隙插入记录，与间隙锁互斥关系如下：

X,GAP S,GAP intention-insert
X,GAP   兼容   兼容   互斥  
S,GAP   兼容   兼容   互斥  
intention-insert   兼容   兼容   唯一键冲突可能互斥  

间隙锁之间不存在互斥关系（X、S表示是什么语句导致的间隙锁）

间隙锁可以堵塞区间内的插入意向锁，但插入意向锁不会堵塞后续的间隙锁

唯一键冲突，如果是主键加记录锁，如果是唯一索引加next-key lock

插入意向锁实验验证

mysql> CREATE TABLE `t` ( `id` int(11) NOT NULL, `c` int(11) DEFAULT NULL, `d` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `c` (`c`) ) ENGINE=InnoDB; mysql> insert into t values(0,0,0),(5,5,5), (10,10,10),(15,15,15),(20,20,20),(25,25,25); -- 开启事务A mysql> begin; /** 在事务A中执行修改语句，id=7不存在，添加(5,10)的间隙锁，LOCK_MODE=X,GAP LOCK_DATA=10可以验证两个观点： 1、间隙锁是加在右边间隙上的 2、此处X并不代表10上加行锁，仅代表什么语句造成的，若改为select * from t where id=7 lock in share mode，LOCK_MODE就变为S,GAP **/ mysql> update t set d=d+1 where id=7; -- 在事务B中插入id=6的数据，需要申请插入意向锁，进入堵塞状态 mysql> insert into t values(6,6,6); /** 事务A中已经添加了间隙锁，相同间隙的插入意向锁堵塞，LOCK_MODE=X,GAP,INSERT_INTENTION，LOCK_STATUS=WAITING v8.0.11时，LOCK_MODE=X,GAP，INSERT_INTENTION标识是高版本新加的(此处使用的是8.0.21)，插入意向锁是一种特殊的间隙锁 **/ mysql> select THREAD_ID,OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA from performance_schema.data_locks; +-----------+---------------+-------------+------------+-----------+------------------------+-------------+-----------+ | THREAD_ID | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA | +-----------+---------------+-------------+------------+-----------+------------------------+-------------+-----------+ | 54 | demo | t | NULL | TABLE | IX | GRANTED | NULL | | 54 | demo | t | PRIMARY | RECORD | X,GAP | GRANTED | 10 | | 53 | demo | t | NULL | TABLE | IX | GRANTED | NULL | | 53 | demo | t | PRIMARY | RECORD | X,GAP,INSERT_INTENTION | WAITING | 10 | +-----------+---------------+-------------+------------+-----------+------------------------+-------------+-----------+ -- 开启事务C mysql> begin; -- 在事务C中插入id=16的数据，由于该间隙上没有间隙锁，申请插入意向锁成功 mysql> insert into t values(16,16,16); /** 查询当前加锁情况，并没有发现插入意向锁，为什么？ 插入意向锁是为了配合间隙锁解决幻读问题，在有间隙锁的情况下进行堵塞。此时没有间隙锁，不需要堵塞，所以就不用加插入意向锁吗？ 但其他事务中相同行插入会产生冲突，说明这里还是有其他约束的，只是不用堵塞的插入意向锁转换成另外一种约束了 **/ mysql> select THREAD_ID,OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA from performance_schema.data_locks; +-----------+---------------+-------------+------------+-----------+-----------+-------------+-----------+ | THREAD_ID | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA | +-----------+---------------+-------------+------------+-----------+-----------+-------------+-----------+ | 53 | demo | t | NULL | TABLE | IX | GRANTED | NULL | +-----------+---------------+-------------+------------+-----------+-----------+-------------+-----------+ /** 证明其他约束的存在，新启一个事务，同样执行insert into t values(16,16,16)，可以看到申请S锁堵塞，正常上一个事务中的插入有其他约束 这里需要进行唯一约束验证，获取id=16的读锁 **/ mysql> select THREAD_ID,OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA from performance_schema.data_locks; +-----------+---------------+-------------+------------+-----------+---------------+-------------+-----------+ | THREAD_ID | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA | +-----------+---------------+-------------+------------+-----------+---------------+-------------+-----------+ | 53 | demo | t | NULL | TABLE | IX | GRANTED | NULL | | 56 | demo | t | NULL | TABLE | IX | GRANTED | NULL | | 56 | demo | t | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 16 | | 56 | demo | t | PRIMARY | RECORD | S,REC_NOT_GAP | WAITING | 16 | +-----------+---------------+-------------+------------+-----------+---------------+-------------+-----------+ -- 开启事务D mysql> begin; -- 在事务D中插入id=10， mysql> insert into t values(10,10,10); 1062 - Duplicate entry '10' for key 'PRIMARY' -- 在事务E中插入id=9 mysql> insert into t values(9,9,9); (blocked) /**V8.0.11 查看当前加锁情况，事务D插入语句检测到唯一冲突后在id=10上加了一个S锁 事务E中插入id=9，等待插入意向锁，没有间隙锁冲突，为什么会堵塞呢？ 唯一键冲突加的应该不是一个记录S锁，应该是一个next-key lock (5,10]，因为已经存在间隙锁，所以插入意向锁才会堵塞 这是MySQL的一个bug，在V8.0.16已经修复，事务E中插入不会堵塞（主键唯一冲突就是一个单纯的记录锁） https://bugs.mysql.com/bug.php?id=93806 **/ mysql> select THREAD_ID,OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA from performance_schema.data_locks; +--------+-----------+---------------+-------------+------------+-----------+-----------+-------------+-----------+ | ENGINE | THREAD_ID | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA | +--------+-----------+---------------+-------------+------------+-----------+-----------+-------------+-----------+ | INNODB | 109 | demo | t | NULL | TABLE | IX | GRANTED | NULL | | INNODB | 109 | demo | t | PRIMARY | RECORD | S | GRANTED | 10 | | INNODB | 108 | demo | t | NULL | TABLE | IX | GRANTED | NULL | | INNODB | 108 | demo | t | PRIMARY | RECORD | X,GAP | WAITING | 10 | +--------+-----------+---------------+-------------+------------+-----------+-----------+-------------+-----------+ 加锁检测

等MDL锁

-- 事务A lock table test_data write; -- 由于事务A加了表锁，事务B堵塞 select * from test_data;