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。

总结

• 事务：是为了保证数据库中数据的完整性和一致性。事务的4个特性：ACID。
• MVCC的好处：提高数据库并发性能。用更好的方式去处理 读/写 冲突，做到即使有 读/写 冲突时，也能做到不加锁，非阻塞并发读。
• MVCC四种隔离级别 ：读未提交、读已提交、可重复读（MySQL默认级别）、串行化。
• MVCC事务隔离级别中，常见的三种问题：脏读、幻读、不可重复读。在RR的默认隔离级别下，单纯的 SELECT 只触发 “快照读” 。而当你包含 INSERT / UPDATE / DELETE 等 写操作 时，这时就会触发 当前读，也就是在事务中，在相关写操作之前会再去读取一次其他事务的最后提交记录。这里的关键在于你事务中的sql是单纯的 SELECT 语句（快照读），还是你事务在的sql是包含了INSERT / UPDATE / DELETE 等 写操作（当前读）。
• 没有建立索引或索引失效，行锁会升级为表锁，因为找不到对应行记录。所以为了避免两个事务同时修改一张表的不同记录会导致表锁的问题，建议加上索引，这样就只是行锁，而不会升级为表锁！
• 幻读的解决关键在于 间隙锁 和 临键锁（临键锁 = 记录锁 + 间隙锁） 。

最后，补充一个问题点：

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

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

到此这篇关于MySQL中 LBCC 和 MVCC 的理解及常见问题示例的文章就介绍到这了,更多相关MySQL中LBCC和 MVCC内容请搜索服务器之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持服务器之家！

原文链接：https://blog.csdn.net/qq_37102984/article/details/126764644