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

1. 事务

介绍MVCC之前，先介绍下事务：事务是为了保证数据库中数据的完整性和一致性。

事务的4个基本要素：

• 原子性（Atomicity）：要么同时成功，要么同时失败。（通过undo log回滚日志实现）

• 一致性（Consistency）：一方扣款 xxx 元，另一方收款 xxx 元，符合事物发展的正常逻辑（通过lock锁实现）

• 隔离性（Isolation）：此时有多个类似 扣款/收款 事件同时发生，每个事件之间是相互独立的（通过 lock锁 + MVCC实现）

• 持久性（Durability）：不管数据库宕机或重启，数据最终都落到了磁盘上，下次加载依然可见 （通过 redo log实现）

2. MVCC初探

目的：主要是为了 提高数据库并发性能。用更好的方式去处理 读/写 冲突，做到即使有 读/写 冲突时，也能做到不加锁，非阻塞并发读。

不同隔离级别下，可能引发的问题： 脏读：并 * 况下，一方事务读到了另一方事务 “已 update 但未 commit” 的数据，破坏了事务隔离性。不可重复读：并 * 况下，一方事务读到了另一方事务 “已 update 或 delete ，并 commit ” 的数据，破坏了事务隔离性。幻读：并 * 况下，一方事务读到了另一方事务" insert 并 commit "的数据，导致前后读取结果不一致。

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

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

• RU-读未提交 级别：20、20、20（可能发生：脏读、不可重复读）

• RC-读已提交 级别：18、20、20（不可能发生：脏读、可能发生：不可重复度）

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

3. LBCC & MVCC

•  LBCC（Lock-Base Concurrency Control）基于锁的并发控制；

• MVCC（Multiversion Concurrency Control）多版本并发控制；

LBCC 锁相关：

MySQL 5.5 版本之前，默认的存储引擎是MyISAM，5.5之后默认引擎是Innodb。Innodb支持事务，包括：行锁/表锁，MyISAM不支持。 意向锁 意向共享锁/读锁（表锁类型，无法手动创建），mysql 中语法： lock in share mode意向排它锁/写锁（表锁类型，无法手动创建），mysql 中语法： for update

常见问题：为什么要加入意向锁？

意向锁并不是真正用来锁定数据的，而是用来告诉你当前表中是否已经有了被 共享锁/排它锁
锁定的数据行。如果有就没必要再去加无用的表锁了，起到一个标识作用，提高加表锁的效率（相当于高铁洗手间门上方是否有人正在使用的 “指示灯”）。

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

• 介绍：临键锁 = 记录锁 + 间隙锁，是 RR 可重复读-隔离级别下独有的，

• 目的：间隙锁的出现就是为了解决可重复读隔离级别下的幻读问题

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

MVCC底层实现详解：

快照读（实际上为读相关的操作）：读取的是记录的可见版本 (有可能是历史版本)，不用加锁。

简单的 SELECT 操作，属于快照读，不加锁。

SELECT * FROM user WHERE ?

当前读（实际上为写相关的操作）：在事务中，update 数据前，还要去MySQL中重新读取一遍该数据对应最新版本的记录，并且 当前读 返回的记录都会加上锁，保证其他事务不会再并发修改这条记录。以下两种方式都属于当前读，需要加锁：

• 特殊读 (加锁读): SELECT * FROM user WHERE id = xxx LOCK IN SHARE MODE;

• INSERT / UPDATE / DELETE 等写操作。

问题：在 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。

• 行锁升级为表锁

• 当数据量达到顶峰的时候，可能会造成“主键冲突”，int的取值范围为2^32 -1，当未声明主键时，达到最大值范围时，id会再次重新从0开使自增，这时候可能会出现覆盖之前row_id记录的情况，造成数据丢失。相反的，如果声明主键的话，那么当id达到上限时，再次insert时会报“主键冲突”错误，这时候可以将之前的int 类型的id改为big int。

• MySQL会自动声明一个“隐藏主键 row_id”，占6字节。而你自己声明int类型的主键时，只会消耗4字节。因此这是一种资源的浪费！

来源：https://blog.csdn.net/qq_37102984/article/details/126764644