史上最易懂的mysql锁 、mvvc分析

1 mysql中的锁类型：

1) 表锁

表共享锁(S):表级别的读锁，表共享锁之间是兼容的。
表排他锁(X): 表级别的写锁，表排他锁和任何锁(包括表排他锁)都不兼容(不包括意向锁)。
意向排他锁(IX): 获取行排他锁之前必须获取的意向排他锁，这个锁是用来快速指示当前表中是否存在行排他锁，而不用去表中遍历判断每行数据是否有行锁。
意向共享锁(IS): 获取行共享锁之前必须获取得意向共享锁，这个锁是用来快速指示当表中前是否存在行共享锁，而不用在表中遍历每行数据判断当前行是否有行排他锁。

2) 行锁

行锁(R)主要是针对唯一索引(包括主键)，行锁也分行共享锁(S)、行排他锁(X)。

3) 间隙锁

间隙锁(GAP):指锁定一个范围区间，主要用来接解决幻读问题。
插入意向锁(INSERT_INTENTION):它不是意向锁，意向锁是表锁，它是一种特殊的间隙锁，在数据插入的时候需要先获取插入意向锁。

4) 临键锁

临键锁(NEXT-KEY) : 它可以看成一种组合锁，相当于行锁+间隙锁。普通的索引列(非唯一索引)就是加临键锁。间隙锁是用来锁住一个区间的，防止这个区间内插入其他数据，普通索引是可以重复的，需要锁住自身，所以还需要行锁，而唯一索引本身是有唯一性的，不能插入重复数据，只需要锁住间隙就可以了，不需要锁住自己本身。

2 具体验证

现在有一个表stu，其中的age字段有索引，现在针对age字段做锁实验。stu表中目前有如下这些数据，( 注意:下面sql实验的隔离级别都是’可重复读’，且开启了自动提交事务，每次写数据后都要恢复成下面的初始化数据, )。

CREATE TABLE `stu` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `age` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `age_index` (`age`)
) ENGINE=InnoDB AUTO_INCREMENT=125 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

select * from stu;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | NULL |   10 |
|  3 | 23   |   24 |
|  6 | NULL |   32 |
|  7 | 23   |   45 |
+----+------+------+

1) 间隙锁with间隙锁

间隙锁和间隙锁之间是兼容的，即可以同时同一个区间加锁，不会有锁竞争。

先在会话1中开启一个事务，更新age=24的记录，此时会获取到[10,32)这个区间的插入间隙锁，此时先不提交事务，然后在新会话2中尝试更新[10,32)区间的一条数据age=25的记录，此时会话2中sql脚本执行正常(尽管此数据不存在)、没有被阻塞。

// 会话1
COMMIT;
update stu set `name` ='hi' WHERE age=24

//会话2
update stu set `name` ='hi' WHERE age=25

2) 插入意向锁with插入意向锁

插入意向锁和插入意向锁(也是一种间隙锁)之间是兼容的，只要不是唯一索引(包括主键)冲突，可以直接插入，不会有锁竞争。

先在会话1中开启一个事务，插入一条age=15的记录，此时会获取到[10,24)这个区间的插入意向锁，此时先不提交事务，然后在新会话2中尝试插入[10,24)区间的一条数据age=16的记录，此时会话2中数据插入成功、没有被阻塞。

//会话1
begin;
INSERT stu (`name`,age) values ('he',15);

//会话2
INSERT stu (`name`,age) values ('he',16);

3) 间隙锁with插入意向锁

间隙锁和插入意向锁之间有锁竞争，但只有在先有间隙锁再申请插入意向锁时才会不兼容，有锁竞争；反之在先有插入意向锁再申请间隙锁是不会有锁竞争的，是兼容的。
(1)先在会话1中开启一个事务，删除记录age=25的记录(尽管此数据不存在)，此时会获取到[32,45)这个区间的间隙锁，此时先不提交事务，然后在新会话2中尝试插入[32,45)区间的一条数据age=36的记录，此时会话2中数据一直被阻塞，直到会话1的事务提交。这充分证明了先有间隙锁再申请插入意向锁，锁不兼容.

//会话1
BEGIN;
DELETE from stu WHERE age=35;
//会话2
INSERT stu (`name`,age) values ('he',36); //被阻塞

(2) 其他很多技术贴都说间隙锁的锁区间是左开右闭，但我实验的结果是左闭右开。
先在会话1中开启一个事务，删除记录age=25的记录(尽管此数据不存在)，此时会获取到[32,45)这个区间的间隙锁，此时先不提交事务，然后在新会话2中尝试插入[32,45)区间的一条数据age=32的记录，此时插入失败、被阻塞，然而在新会话3尝试插入另一条数据age=45的记录则成功插入、没被阻塞。按照那些技术贴的说法，应该age=45的数据插入失败被阻塞，age=32的数据插入成功，但事实却不是如此。

//会话1
BEGIN;
DELETE from stu WHERE age=35;
//会话2
INSERT stu (`name`,age) values ('he',32); //被阻塞
//会话3
INSERT stu (`name`,age) values ('he',45); //成功插入

(3)先在会话1中开启一个事务，插入一条记录age=27的记录，此时会获取到[25,32)这个区间的插入意向锁，此时先不提交事务，然后在新会话2中尝试在[25,32)区间更新的一条数据age=29的记录(尽管此数据不存在)，此时会话2中sql脚本正常返回、没有被阻塞。这充分证明了先有插入意向锁再申请间隙锁，锁兼容.

//会话1
BEGIN;
INSERT stu (`name`,age) values ('he',32); 

//会话2
UPDATE stu set `name`='haha' WHERE age=29;//正常返回、不阻塞

跟undo log相关的支持，undo log 分TRX_UNDO_INSERT 和TRX_UNDO_UPDATE 。TRX_UNDO_INSERT在事务提交后，可以直接删除，因为他只在自己的事务中可见，而TRX_UNDO_UPDATE在事务提交后，还不能直接删除，它可能会被mvvc使用到。

update 和delete

update 时如果更新条件是二级索引(包括唯一索引)，先获取这个二级索引的锁，再获取对应主键的行锁。
delete时需要获取对应行上的所有索引列的的锁

3 mvvc

网上对mvvc的解释太复杂了，我这里简单说下的行为结果。
mvvc中读提交 、可重复读这两种隔离级别中有不同的行为。
读提交 :每次的读都是当前读，即每次都读当前最新的已提交数据。
可重复：可重复读是快照读，第一次读和当前读一样，读此时的最新的已提交数据，不同点在于之后的第2~n次读。第一次之后的第2~n次读出的数据不会再变化，这时读出的数据是第一次读的快照，它和第一次读的数据始终是一样的，不再关心第一次读之后的已提交数据。每次读数是一样的，这就是名副其实的可重复读。
在可重复读隔离级别下， 普通的select 语句是快照读，而update 、delete 、insert (如果把update delete insert判断是否能写数据的过程看成读的话) 、select (for update)/(lock on share mode) 都是当前读。