MySQL中 LBCC 和 MVCC 的理解，常见问题及示例：

https://blog.csdn.net/qq_37102984/article/details/126764644

​
参考视频及文章：

• b站视频：
  • 美团面试必问–MySQL事务隔离深剖
  • 【Java学姐】Mysql-MVCC多版本并发控制底层原理及实现机制
• csdn博客：浅谈 Mysql 事务 由 ACID 到 MVCC

一、事务

介绍MVCC之前，先介绍下事务：事务是指逻辑上的一组sql操作，要么全部执行成功，要么全部执行失败。事务是为了保证数据库中数据的完整性和一致性。

事务的4个基本要素：

• 原子性（Atomicity）：要么同时成功，要么同时失败。（通过 undo log 回滚日志实现）
• 一致性（Consistency）：一方扣款 xxx 元，另一方收款 xxx 元，符合事物发展的正常逻辑（通过 其他3个特性 来保证）
• 隔离性（Isolation）：此时有多个类似 扣款/收款 事件同时发生，每个事件之间是相互独立的（通过 lock锁 + MVCC 实现）
• 持久性（Durability）：不管数据库宕机或重启，数据最终都落到了磁盘上，下次加载依然可见 （通过 redo log 实现）

二、MVCC初探

• 概念：通过「readview版本视图 + undo log」来控制并发事务访问同一条记录时的行为就叫MVCC。
• 目的：主要为了 提高数据库并发性能。用更好的方式去处理 读/写 冲突，做到即使有 读/写 冲突时，也能做到不加锁，非阻塞并发读。
• 实现：undo log + readview

不同隔离级别下，可能引发的问题：

• 脏读：并发情况下，一方事务读到了另一方事务 “已 update 但未 commit” 的数据，破坏了事务隔离性。
• 不可重复读：并发情况下，一方事务读到了另一方事务 “已 update 或 delete ，并 commit ” 的数据，破坏了事务隔离性。
• 幻读：并发情况下，一方事务读到了另一方事务" insert/delete 并 commit "的数据，导致前后读取记录数不一致（行记录数多了或少了都算是幻读）。

MVCC中的四种事务隔离级别：

可重复读 和 读已提交 的区别？
 
线上业务常用 “读已提交” 的隔离级别，减少死锁的概率（读已提交 无间隙锁），提高事务的并发性能。
 
它们的主要区别在于创建 Read View 版本快照的时机不同，
「读已提交」隔离级别是在「每个语句执行前」都会重新生成一个 Read View 版本快照，而「可重复读」隔离级别是「启动事务时」生成一个 Read View 版本快照，然后整个事务期间都在用这个 Read View。
 
所以，对于 读已提交 的隔离级别，每个语句执行前都会重新生成 Read View 版本快照，可能会发生“不可重复读”的问题（读取到其他事务已提交的记录）。而对于 可重复读 隔离级别来说，一旦开启事务生成好 Read Veiw 版本快照后，该事务执行期间，是不会发生“不可重复读”的问题的，
 
另外，企业中通常使用 RC-读已提交 的隔离级别。因为 RR-可重复读 的隔离级别下，存在间隙锁，在并发场景下容易产生死锁，死锁问题产生的影响比幻读更大！

提问：V1、V2、V3在不同事务隔离级别下读取到的值分别是：

• RU-读未提交 级别：20、20、20（可能发生：脏读、不可重复读）
• RC-读已提交 级别：18、20、20（不可能发生：脏读、可能发生：不可重复度）
• RR-可重复读 级别：18、18、20 （不可能发生：脏读、不可重复读；但是因为事务A已提交，所以V3再次查询时跟事务A是没有隔离性的要求的，因此V3读取到的是20）

三、LBCC & MVCC

• LBCC（Lock-Base Concurrency Control）基于锁的并发控制；
• MVCC（Multiversion Concurrency Control）多版本并发控制；

LBCC 锁相关：

• MySQL 5.5 版本之前，默认的存储引擎是MyISAM，5.5之后默认引擎是Innodb。
• Innodb支持事务，包括：行锁/表锁，MyISAM不支持。

意向锁

• 意向共享锁/读锁（IS，表锁类型，无法手动创建）：当需要对数据加行级读锁时，MySQL 会先向整个表加意向读锁。 MySQL 中语法： lock in share mode
• 意向排它锁/写锁（IX，表锁类型，无法手动创建）：当需要对数据加行级写锁时，MySQL 会先向整个表加意向写锁。MySQL 中语法： for update

常见问题：为什么要加入意向锁？
  意向锁并不是真正用来锁定数据的，而是用来告诉你当前表中是否已经有了被 共享锁/排它锁 锁定的数据行。如果有就没必要再去加无用的表锁了，起到一个标识作用，提高加表锁的效率（相当于高铁洗手间门上方是否有人正在使用的 “指示灯”）。
  目的：当Innodb需要对一个表上一个“表级别”的S锁和X锁时，可以快速判断表是否被上行锁，以避免用遍历的方式检验是否上行锁。

1. 如果意向锁是行锁，则需要遍历每一行数据去确认；
2. 如果意向锁是表锁，则只需要判断一次即可知道有没数据行被锁定，提升性能。

记录锁（Record Lock）、间隙锁（Gap Lock）、临键锁（Next-Key Lock）：

• 介绍：临键锁 = 记录锁 + 间隙锁，是 RR-可重复读 隔离级别下独有的
• 目的：间隙锁的出现就是为了解决可重复读隔离级别下的幻读问题

问题：如图示：执行此sql语句（先开启事务）：BEGIN; SELECT * FROM tbl WHERE id > 15 FOR UPDATE; ，以下两个sql语句可以执行成功吗？

• INSERT INTO tbl VALUES (20, 20);
• INSERT INTO tbl VALUES (12, 12);
  以上两句sql都是不会执行成功的，因为触发了 间隙锁-Gap lock，实际上锁住的范围是 (11, +∞)。实际上锁定范围是根据数据库当前表的记录来划分不同范围段的锁，例如上述例子中：最后一段就锁定的是 (11, +∞)。
  因此，走索引是行锁，不走索引是表锁。所以为了避免两个事务同时修改一张表的不同记录会导致表锁的问题，建议加上具体索引，这样就只是行锁，而不会升级为表锁！

MVCC底层实现详解：

• 快照读（实际上为读相关的操作）：读取的是记录的可见版本 (有可能是历史版本)，不用加锁。可以理解为将当前数据库的数据复制一份副本。在当前事务中，之后不管进行多少次 SELECT 查询，都是在副本中去取数据，所以不管数据库中后来是否又对数据进行了改变，都不会影响当前已经 BEGIN 的事务数据的读取。
  • 简单的 SELECT 操作，属于快照读，不加锁。
    • SELECT * FROM user WHERE ?
• 当前读（实际上为写相关的操作）：在事务中操作数据前，还要去MySQL中 重新读取一遍该数据对应最新版本的记录，并且 当前读 返回的记录都会加上锁，保证其他事务不会再并发修改这条记录。以下两种方式都属于当前读，需要加锁：
  • 特殊读 (加锁读): SELECT * FROM user WHERE id = xxx LOCK IN SHARE MODE;
  • INSERT / UPDATE / DELETE / SELECT ... FOR UPDATE 等写操作。

问题：在 RR-可重复读 的默认隔离级别下，假设起始的age为18，那么Q1和Q2对应的age分别是多少呢？

• 针对 “事务B” 分析：因为存在 UPDATE 写操作，触发了 当前读，所以要先去读最新提交的版本号记录（即：事务C UPDATE 后提交的记录），然后事务B再去执行自己的 UPDATE 操作。也就是要先去读事务C提交的最新数据为19，然后事务B自身再 UPDATE 加1最终变为20。
• 针对 “事务A” 分析：因为事务A本身是没有任何的写操作，仅仅是 SELECT 查询操作，触发 快照读。所以事务A只认准事务 BEGIN 开始之前记录的 最新最后提交的版本号，其记录值也就是初始的18。
  
• BEGIN 事务开始的时候会创建一个快照，并为对应事务分配一个事务id，即 TRX_ID
• 开启事务之前最后的版本号为：up_limit_id=999，对应 age=18
• 事务B和事务C都有 UPDATE 操作（当前读），所以 row_trx_id 为自身的 TRX_ID 的值，分别是1001和1002。而事务A没有 UPDATE 操作（快照读），所以只认准事务A在 事务开始前 最后的版本号 up_limit_id=999，其 age=18。

---

但是要注意：RR的默认隔离级别下，不能完全解决幻读的问题。
比如下面的例子：参考 大佬文章

事务1 可重复读&事务手动提交	事务2 可重复读&事务手动提交
begin;
select * from student where id > 0;	begin;
	select * from student where id > 0;
	insert into student (id, name, sex, age) values(3, ‘tom’, ‘男’, 66) ;
select * from student where id > 0;
	select * from student where id > 0;
	commit;
select * from student where id > 0;（快照读：读取历史数据）
update student set sex = ‘男’ where id > 0;（当前读：重新读取一次最新数据）
select * from student where id > 0;
commit;

注意：事务1能正常SELECT、UPDATE 的前提是：事务2已 COMMIT。
注意：如果事务1不执行 UPDATE 操作，那么在最后 SELECT 时，是查询不到事务2新 ISNERT 的记录的。

• 幻读原因：因为事务1中的 update 操作更新了事务2最新插入的 id=3（name=‘tom’） 的数据，所以事务1在执行 update student set sex = '男' where id > 0;（当前读） 后再去 select * from student where id > 0;会查询出事务2新插入并提交的数据，也就是幻读。如果事务1的 update 操作没有涉及到事务2新插入的数据，那么后续 select 是不会出现幻读的。
• 底层原因：对于使用InnoDB存储引擎的表来说，它的聚簇索引记录中都包含下面这两个必要的隐藏列（row_id并不是必要的：在创建的表中有主键时，或者有不允许为NULL的UNIQUE键时，都不会包含row_id列）。
  • trx_id：一个事务每次对某条聚簇索引记录进行改动时，都会把该事务的事务id赋值给trx_id隐藏列。
  • roll_pointer：每次对某条聚族索引记录进行改动时，都会把旧的版本写入到undo日志中，这个隐藏列就相当于一个指针，可以通过它找到该记录修改前的信息。
  • 在默认RR的隔离级别下，事务1第一次执行普通 SELECT 语句时生成了一个ReadView版本链，之后事务2向 student 表中新插入一条id=3（name=‘Tom’）的记录并提交。ReadView并不能阻止事务1执行 UPDATE/DELETE 语句来改动这个事务2新插入的记录（由于事务2已经 COMMIT，因此改动该记录并不会造成阻塞）。但是这样一来，这条新记录的 trx_id 隐藏列的值就变成了事务1的事务id，之后事务1再使用普通的 SELECT 语句去查询这条记录时就可以看到这条记录了，也就可以把这条记录返回给客户端。因为这个特殊现象的存在，我们也可以认为InnoDB中的MVCC并不能完全禁止幻读。
    也就是说，事务1将新插入数据的事务号修改的小于等于原先ReadView对应的事务版本号了，相当于扩充了ReadView的范围，从而导致事务1在最后一次 SELECT 时，能够查询到 id=3（name=‘Tom’）记录。
• 解决方法：事务1可以在一开始 SELECT 时就使用 FOR UPDATE 加锁，这样其他事务，比如事务2就不能 INSERT 或 UPDATE 数据。而且事务1还可以执行任意的 INSERT 或 UPDATE 当前读类型语句，并且事务1最后查询的结果集也不会因为幻读而产生新的数据行记录了。
• 示例总结：如果事务中都使用快照读，那么就不会产生幻读现象；但是如果快照读和当前读混用，就会产生幻读。
• 另外，补充一张 “在RR的默认隔离级别下，不能完全解决幻读的问题” 的说明图：

---

四、总结

• 事务：事务是指逻辑上的一组sql操作，要么全部执行成功，要么全部执行失败。其是为了保证数据库中数据的完整性和一致性。事务的4个特性：ACID。
  • A 原子性：通过 “undo log” 实现
  • C 一致性：通过 “其他3个特性” 实现
  • I 隔离性： 通过 “lock锁 + MVCC” 实现
  • D 持久性：通过 “redo log” 实现
• MVCC 基础：
  • 概念：通过 “readview版本视图 + undo log” 来控制并发事务访问同一条记录时的行为。
  • 好处：提高数据库并发性能。用更好的方式去处理 读/写 冲突，做到即使有 读/写 冲突 时，也能做到不加锁，非阻塞并发读。
  • 四种隔离级别 ：RU-读未提交、RC-读已提交（线上业务常用的隔离级别，因为“读已提交”无间隙锁，能减少死锁的概率，提高事务的并发性能）、RR-可重复读（MySQL默认级别）、S-串行化。
  • MVCC事务隔离级别中，常见的三种问题：脏读、不可重复读、幻读（前后select的结果集条数不一致）。
• 快照读 & 当前读：
  • 快照读：在RR的默认隔离级别下，单纯的 SELECT 只触发 “快照读” 。
  • 当前读：而当你包含 INSERT / UPDATE / DELETE / SELECT ... FOR UPDATE / SELECT LOCK IN SHARE MODE 等 写操作 时，这时就会触发 当前读。也就是在事务中，当前读会在相关写操作之前会再去读取一次其他事务的最后提交记录。
  • 关键点：这里的关键在于你事务中的sql是单纯的 SELECT 语句（快照读），还是你事务在的sql是包含了INSERT / UPDATE / DELETE / SELECT ... FOR UPDATE / SELECT ... LOCK IN SHARE MODE 等 写操作（当前读）。
• 幻读：
  • 解决：依靠 间隙锁 和 临键锁（临键锁 = 记录锁 + 间隙锁） 来解决部分幻读问题。
  • 注意：但 在RR的默认隔离级别下，不能完全解决幻读的问题：也就是 事务中如果都使用快照读，那就不会产生幻读现象；但是如果快照读和当前读混用，还是会产生幻读。
  • 在触发 INSERT / UPDATE / DELETE / SELECT ... FOR UPDATE / SELECT ... LOCK IN SHARE MODE 等当前读的情况下，表明此时需要看到或操作最新的数据（包含其他事务已提交的数据）。所以在后续再次查询到的结果集中，可能会包含过程中其他事务新提交的记录，导致前后查询的结果集条数不一致。
• 行锁&表锁：没有建立索引或索引失效，行锁会升级为表锁，因为找不到对应行记录。所以为了避免两个事务同时修改一张表的不同记录会导致表锁的问题，建议加上索引，这样就只是行锁，而不会升级为表锁！
• 意向锁（表级锁）：
  • 概念：意向锁并不是真正用来锁定数据的，而是用来告诉你当前表中是否已经有了被 共享锁/排它锁 锁定的数据行。
  • 目的：**为了快速判断表里是否有记录被加锁。**如果有就没必要再去加无用的表锁了，起到一个标识作用，提高加表锁的效率（相当于高铁洗手间门上方是否有人正在使用的 “指示灯”）。

最后，补充一个问题点：

如果不声明的创建主键，会有哪些危害？ 比如你的id（假设int类型）没有声明为主键，并且也没有声明唯一索引（当未声明主键时，唯一索引会被取代为主键）

1. 行锁升级为表锁
2. 当数据量达到顶峰的时候，可能会造成“主键冲突”，int的取值范围为2^32 -1，当未声明主键时，达到最大值范围时，id会再次重新从0开始自增，这时候可能会出现覆盖之前row_id记录的情况，造成数据丢失。相反的，如果声明主键的话，那么当id达到上限时，再次insert时会报“主键冲突”错误，这时候可以将之前的 int 类型的id改为bigint。
3. MySQL会自动声明一个“隐藏主键 row_id”，占6字节。而你自己声明int类型的主键时，只会消耗4字节。因此这是一种资源的浪费！