mysql 最后行加汇总_[mysql系列]6.幻读和间隙锁

最新推荐文章于 2023-08-09 18:28:17 发布

weixin_39926311

最新推荐文章于 2023-08-09 18:28:17 发布

阅读量81

点赞数

文章标签： mysql 最后行加汇总

本文链接：https://blog.csdn.net/weixin_39926311/article/details/111278711

版权

本文探讨了InnoDB数据库中的幻读现象，通过实例解释了间隙锁和next-keylock如何避免幻读。讲解了原则如等值查询间隙锁、覆盖索引锁级和主键范围锁，并给出了死锁案例。重点在于理解这些概念在实际操作中的应用和如何优化事务隔离以提升并发性能。

摘要由CSDN通过智能技术生成

幻读就是在同一个事务里面，前后两次查一个区间内的数据，看到了第一次查询看不到的行。

幻读会导致一个是锁语义上的问题，另外一个是数据不一致的问题。

语义上，我们看下面三个session的执行：

在time1时刻，先对d=5的所有行加行锁，然而time2的sessionb 还有time4的sessionC都没有依照sessionA的语义，所以time5的select产生了幻读的现象。

另外一个就是数据一致性的问题。在多个会话的情况下，会导致在binlog下的语句执行序列和预估的不一致，这样复制到备库也会出问题。因为语句只锁住行，所以没办法应对insert新的行进去，导致数据也不一致。

那么怎么解决幻读的问题呢？这里就要引进一个概念，间隙锁，锁住更改的行的间隙，这也是innodb的默认幻读现象解决方案，只在可重复读的事务隔离级别情况下。另外间隙锁之间并不会互斥，他表达的是一种保护作用，保护这个间隙，不被别的数据插入。

间隙锁和行锁合称为next-key lock,这种next-key lock解决了幻读的问题，但是也影响了并发程度，锁的粒度加大了。下面结合一些原则和案例分析一下：

先说下原则：1.加锁的基本单位是next-key lock,他是前开后闭区间

2.在select的时候，只有访问到的数据才会加锁

3.索引上等值查询，唯一索引加锁的时候，next-key 就是行锁。

4.索引上等值查询，向右方向遍历的时候，直到最后一个值不满足等值查询的时候才停下来，体现了间隙锁的特性。

我们先建立一个sql表和插入一些数据：

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);

基于这个表，结合上面的原则，举几个例子：

案例一等值查询的间隙锁
为什么会出现sessionb和c的现象呢？结合上述提到的原则。根据间隙锁的左开右闭原则，sessionA的语句锁的是(5,10],另外这个是一个等职查询，最后一个值不满足不满足查询条件。所以最终锁(5,10)，就出现了上叙现象。
案例二覆盖索引加锁
share in mode因为覆盖索引的原因，只锁了c的那条B+树(根据上面的原则，只锁访问到的对象，id=5没有访问到，所有id的主键b+树没有锁，可以正常update)。但是如果是for update就粒度会包括id主键。
因为是普通索引，根据上面的原则，知道查到一个不满足等值的数才停下来，加间隙锁，锁住了(5,10)。所以sessionC被阻塞了。
案例三主键索引的范围锁
先看下两个语义相同的sql语句
mysql> select * from t where id=10 for update;
mysql> select * from t where id>=10 and id<11 for update;
语义相同，但是锁的逻辑就不相同了，执行效果如图
按照惯例，依据前面的四大原则分析：id是唯一索引，next-key lock退化为行锁，所以对id=10的这一行加锁。然后范围查询，直到id=15这一行数据，停下来了。所以锁加在[10,15)。
而如果是非唯一索引，也执行相同的操作，比如：
那么将会全部被阻塞，因为将锁住(5,10]还有(10,15)因为他并不会退化成行锁，所以有两个间隙区间锁。
limit语句加锁
下面还是举一个例子，也是语义一致，但是加锁的粒度不同。
加了limit。虽然c=10的数据也不超过2条，逻辑上加不加没啥区别，但是锁的粒度就不一样了。
加了limit的语句，在找到第二条数据后就停止了新区间的加锁，但是如果没有limit会到下个数据不满足等值才停止加锁，那么c在(10，15)也是加锁状态，这条insert语句就会不成功，而limit，这条insert语句是成功的。
next key lock死锁
1.session A 启动事务后执行查询语句加 lock in share mode，在索引 c 上加了 next-key lock(5,10] 和间隙锁 (10,15)，这个前面有例子。
2.session B 的 update 语句也要在索引 c 上加 next-key lock(5,10] ，进入锁等待
3.所以再insert进去就死锁了。这个时候innodb就会进行事务回滚。