MySQL锁算法

锁的类型

Mysql提供了两种标准的行级锁：共享锁和排他锁。

共享锁和排他锁可以理解成读锁和写锁，读写锁之间只有读锁和读锁之间不互斥，写锁和写锁，读锁和写锁之间都会互斥。

查看MySQL中的锁情况，可以通过库INFORMATION_SCHEMA中的INNODB_TRX、INNODB_LOCKS、INNODB_LOCK_WAITS表来查看具体锁情况。包括当前事务的状态，事务的ID，事务的sql，线程id，等待事务的锁id，事务开始时间，事务锁定的索引，事务锁定的行数量，数据锁定的页数量，事务锁定的主键值等

一致性非锁定读（快照读）

在事务中简单的SELECT操作就是快照读，不包括SELECT…FOR UPDATE和SELECT…LOCK IN SHARE MODE方式，这两种会触触发当前读，下文会讲到

一致性非锁定读的实现是采用多版本并发控制MVCC (Multi Version Concurrency Control)来实现，其基本思想是对于不同的事务创建不同的快照，每个事务读取对应的快照，从而隔离事务之间对记录修改的影响。

MVCC方法作用于Read committed和Repeatable read这两种隔离级别上，在不同隔离级别上快照建立的时间不同。对于Read committed，每次select会创建一个快照，而对于Repeatable read是当事务中的第一个select时才会创建快照。

MVCC的具体实现

innodb中聚簇索引包含了两个隐藏的值，trx_id和roll_pointer两个字段，trx_id记录了对记录进行修改的事务ID，每新建一个事务，该事务的trx_id会自增1。roll_pointer记录了修改之前的记录的指针，之前版本的记录会放进undo日志当中，roll_pointer指向的就是undo日志老版本记录的位置。

Read committed和Repeatable read在MVCC上使用的不同就是创建快照的时间不同，这里的快照记录了当前活动的事务的id，通过以下判断当前记录的trx_id和快照中的活动事务ID列表来决定读取的记录

要访问的记录trx_id比快照中的活动事务ID最小值都要小时，说明该记录在之前的事务已经提交了，可以访问
要访问的记录trx_id在照中的活动事务ID最小值和最大值之间，则需要判断trx_id是否在活动事务列表中，如果在则说明事务还未提交，无法访问，需要通过roll_pointer寻找上一条记录。如果不在，则说明事务已经提交，可以访问
要访问的记录trx_id比快照中的活动事务ID最大值都要大时，说明该记录是创建快照之后生成的，不能访问，需要通过roll_pointer寻找上一条记录。

不断通过trx_id和活动事务列表判断直到寻找到可以访问的记录。

一致性锁定读（当前读）

当前读,读取的是最新的记录, 同时会对记录增加锁，阻塞其他事务修改记录。SELECT…FOR UPDATE和SELECT…LOCK IN SHARE MODE就是当前锁的操作，前一个会获得排他锁，后一个是共享锁。

SELECT…FOR UPDATE能够很好的解决丢失更新的问题，丢失更新的问题如下：

1、事务A查询一行数据，保存在本地进行修改，还未update

2、事务B查询相同的一行数据，保存在本地进行修改，还未update

3、事务A update本地修改好的数据并提交

3、事务B update本地修改好的数据并提交

这种情况理论是没有问题的，但是在逻辑上事务B会覆盖事务A的提交，事务A发生了丢失更新

事务A中执行SELECT…FOR UPDATE会获取排他锁，阻塞其余事务修改操作，这时事务Aupdate并提交才会释放锁，因此在事务A中是不会出现丢失更新的，其他事务在事务A不提交会阻塞在查询记录的sql上。

一致性锁定读是基于next-key lock实现的，next-key lock是包括Record Lock和gap Lock的，Record Lock是行锁，锁定具体一行记录，gap Lock是间隙锁，锁定两行记录之间的间隙。什么是间隙和行记录呢？如下

表A，有字段a,b,c,primary key为a,b列上建立普通索引
a			b			c
1			2			3
2			5			8
3			6			9

则表A有三条记录，Record Lock是锁在这些记录上的，字段b的间隙有(-$\infty$, 2),(2 5),(5, 6),(6,+$\infty$)，间隙就是排序后字段各个值的中间，间隙锁主要是用于锁住间隙，这样插入间隙之间字段值就需要获得间隙锁才能执行。间隙锁只有在Read Repeatable、Serializable隔离级别，能够使用间隙锁很好的防止幻读。

一致性锁定读使用next-key lock来实现。

例如在上述表A，在事务A中SELECT * FROM A WHER a = 2 FOR UPDATE，会使用next-key lock来锁住，由于字段a是主键，在唯一索引上，并且where 条件=或者in，next-key lock会降级成Record Lock，因为可以确定唯一的记录。
如果是在事务A中执行SELECT * FROM A WHER b = 2 FOR UPDATE或SELECT * FROM A WHER b > 2 and b < 5 FOR UPDATE，当对索引（不包括唯一索引）上进行wherr时，会对命中的记录加Record Lock，包括的范围（间隙(2，5)）加上gap Lock，同时对下一个间隙（(5，6)）也要加上gap Lock。此时其他事务对b=2的记录更新或者inser into字段b的值范围在2到5都会阻塞，因为b=2的记录和间隙(2 5),(5, 6)都被锁住了
如果是在事务A中执行SELECT * FROM A WHER c = 2 FOR UPDATE，则会多全表进行gap Lock，因为字段c上没有索引，进行当前读会在全表上使用间隙锁，这个尽量避免

幻读解决

通过MVCC和next-key lock看起来已经很好解决了幻读问题，其实mysql的Repeatable read可以说是解决了幻读问题，但是也可以说做的还不够，官方给出的回答是

1
2
3

只要在一个事务中，第二次select多出了row就算幻读。
a事务先select，b事务insert确实会加一个gap锁，但是如果b事务commit，这个gap锁就会释放（释放后a事务可以随意dml操作），a事务再select出来的结果在MVCC下还和第一次select一样，接着a事务不加条件地update，这个update会作用在所有行上（包括b事务新加的），a事务再次select就会出现b事务中的新行，并且这个新行已经被update修改了，实测在RR级别下确实如此。
如果这样理解的话，Mysql的RR级别确实防不住幻读

参考

https://baijiahao.baidu.com/s?id=1629409989970483292&wfr=spider&for=pc

《MySQL技术内幕-InnoDB存储结构》