针对数据库事务的学习来到了锁的算法实现模块了,整理下我们之前学习到的知识:

1. 数据库隔离机制(postgresql),将就着用

隔离机制

2. 数据库特性及保证机制(mysql-InnoDB)

四大特性ACID

3. 数据库索引结构

索引结构  B+树结构 B树结构 索引分类

4. InnoDB锁介绍

锁介绍

以上是一些前期知识准备,了解多方面对于锁的算法理解会很方便。

因为我学习的是Mysql-InnoDB详解,所以很多知识都是针对InnoDB的,而InnoDB下默认的隔离机制是RR(可重复读)。

隔离机制简称:RC(读已提交),RR(可重复读),Se(序列化读)。

锁算法简称:RL(Record Lock锁住单行),GL(Gap Lock间隙锁,不包含当前记录),NKL(Next-Key Lock间隙锁,包含当前记录)

RR(InnoDB默认隔离级别):

1.锁是在索引基础上实现的。(前文叙述过)

2.锁住的是区间,并且包含当前记录。(前文叙述过)

3.降级问题,如果是唯一索引时,且查询条件得到的结果是1条记录,此时会降级成RL。(本文新内容)

4.上锁不仅仅是对主键索引加锁,对辅助索引也会加(文本新内容)

此篇主要关注的还是3,4两点,主键索引,辅助索引,唯一索引,普通索引等等情况。

本文主要参考地址如下:跟着大神的思路去一步一步探究锁内部的加锁机制,毕竟大神的博客也是说没就没的。

SQL1: select * from table where id = 10;

SQL2: delete from table where id = 10;

问:sql1和sql2加了哪些锁?

一般回答:

1. sql1不加锁,因为mysql存在MVCC机制,因此读的是快照,不加锁。

2. sql2加X锁,因为根据id删除了,先入为主id是主键了。

但是大神也说了,没有前提这个也没法回答,就单凭这个sql语句没法知道采用了哪种锁,缺少一些条件。

前提一:id是不是主键?

前提二:隔离机制是什么?

前提三:id如果不是主键,那么有索引么?是唯一索引么?

前提四:执行计划是什么?全表还是索引?

这些前提其实也就是我们在分析一个sql的加锁流程具体什么样的。那么我们假设一些场景去学习吧。根据隔离机制分为两组,

根据隔离机制和索引来分场景:

RC隔离级别:

组合一:id是主键。

组合二:id是唯一索引。

组合三:id是普通索引(不唯一)。

组合四:id没有索引。

RR隔离级别:

组合五:id是主键。

组合六:id是唯一索引。

组合七:id是普通索引(不唯一)。

组合八:id没有索引。

Serializable隔离级别

分析部分:

对于组合1-8来说,普通的select语句其实都是基于MVCC保证的,因此都是快照读,不加锁,主要针对delete分析,update其实也一个道理。

组合一:


结论:主键索引时只在当前记录上加锁

组合二:


因为id是唯一索引,因此在sql执行时会首先执行到id的索引,找到10的记录后,加X锁,然后再通过主键索引找到真实的记录,加上X锁,如果不在主键索引上加上X锁,那么如果另一条并发语句通过主键去修改的话,它并没有通过唯一索引id找到,而是通过主键索引找到的,就不知道此时是存在X锁的,会造成脏数据。

结论:如果id是唯一索引,需要加两个锁,一个存在于唯一索引结构中的记录,一个对应主键索引结构中的记录。

组合三:


因为id不是唯一索引了,因此会存在很多id=10的记录,但是这些记录的主键值肯定是不同的。因此在主键索引内会将所有满足的记录都加上索引。

结论:若id是非唯一索引,那么对应的记录都会被加锁,同时主键索引对应的所有记录也都会加锁。

组合四:

对于无索引的情况,不去做实验了,因为大神的博客说的很清楚了已经。

结论:若id上无索引,那么mysql会走全表扫描,对所有记录加上X锁,但是Mysql Server端会做一些优化,将不满足的记录的锁进行解锁操作。

组合五:

这时候隔离机制是RR级别了,更高一层。

结论:加锁情况和组合一一致,在主键索引上加。

组合六:

因为还是唯一索引,所以情况和组合二一样。

结论:在唯一索引处加锁,并且在主键索引处加索引。

组合七:

我们在前一篇文章读InnoDb书时知道,在RR级别下,mysql解决了幻读的问题,解决幻读的根本措施就是使用了NKL技术,NKL中的GL将范围内的都加上锁,免的读出多一条记录的问题,那么这刚好就是去理解组合七中的依据。


对于非唯一索引,那么根据一个id去读的话,会读出多个记录,因此会加GAP锁。

因为我们删除的id=10的记录。那么我们在执行时,哪些地方会导致产生另外的id=10的记录呢?图中我用紫色线标注出间隙了,三个地方(7,10-5],(10-5,10-8],(10-8,17-11],比如我插入的是(10-4)数据,就会产生在(7-10)之间。

结论:对于非唯一索引的情况,RR级别会锁住数据本身和可能出现的间隙,避免出现幻读。

组合八:

组合四RC隔离级别中已经表明会对全部记录加锁,然后去做优化。那么对于RR隔离级别呢?


结论:在RR级别下,没有Mysql Server的优化操作,会锁定所有的主键和主键之间的间隙,10个X锁和11个GAP锁。如果是万级别的数据呢?加锁太多了,需要注意。也是有缓解方案的,但是不建议使用~~大神的建议。

组合九:

对于Se的级别,sql2的操作和RR一致,但是sql1的操作不是快照读,而是当前读。MVCC并发控制会降级为Lock-Based CC(不懂,以后再学习)。

最后总结一下:


发布评论

分享到:

IT虾米网

微信公众号号:IT虾米 (左侧二维码扫一扫)欢迎添加!

MyISAM和InnoDB的区别详解
你是第一个吃螃蟹的人
发表评论

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。