服务器之家:专注于VPS、云服务器配置技术及软件下载分享
分类导航

Mysql|Sql Server|Oracle|Redis|MongoDB|PostgreSQL|Sqlite|DB2|mariadb|Access|数据库技术|

服务器之家 - 数据库 - Mysql - MySQL中 LBCC 和 MVCC 的理解及常见问题示例

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

2022-11-10 16:23dreamer'~ Mysql

这篇文章主要介绍了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中的四种事务隔离级别:

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

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

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

  • 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中 LBCC 和 MVCC 的理解及常见问题示例

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 可重复读-隔离级别下独有的,
  • 目的:间隙锁的出现就是为了解决可重复读隔离级别下的幻读问题

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

问题:如图示:执行此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分别是多少呢?

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

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

  • 针对 “事务B” 分析:因为存在 UPDATE 操作,触发了 当前读,所以要先去读最新提交的版本号记录(即:事务C UPDATE 后提交的记录),然后事务B再去执行自己的 UPDATE 操作。也就是要先去读事务C提交的最新数据为19,然后事务B自身再 UPDATE 加1最终变为20。
  • 针对 “事务A” 分析:因为事务A本身是没有任何的操作,仅仅是 SELECT 查询操作,触发 快照读。所以事务A只认准事务 BEGIN 开始之前记录的 最新最后提交的版本号,其记录值也就是初始的18。

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

  • 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

延伸 · 阅读

精彩推荐
  • MysqlMySQL中查询当前时间间隔前1天的数据

    MySQL中查询当前时间间隔前1天的数据

    实际项目中我们都会遇到分布式定时任务执行的情况,今天通过本文给大家分享MySQL中查询当前时间间隔前1天的数据,查询sql语句给大家介绍的非常详细,...

    人无名,则可专心练剑7522022-01-22
  • Mysqlmysql查询慢的原因和解决方案

    mysql查询慢的原因和解决方案

    最近发现公司网站后台查询的时候比较慢,可能因为大量的like查询导致,这里为大家分享一下方法,需要的朋友可以参考下...

    mysql教程网3672020-12-07
  • MysqlLinux下MySQL5.7.18二进制包安装教程(无默认配置文件my_default.cnf)

    Linux下MySQL5.7.18二进制包安装教程(无默认配置文件my_default.cnf)

    这篇文章主要介绍了Linux下MySQL5.7.18二进制包安装教程(无默认配置文件my_default.cnf) ,需要的朋友可以参考下...

    MSSQL1237802020-07-27
  • MysqlMySQL内存表的特性与使用介绍

    MySQL内存表的特性与使用介绍

    临时表和内存表都可以人工创建,但临时表更多的作用是系统自己创建后,组织数据以提升性能,如子查询,临时表在多个连接之间不能共享。这里只讨论...

    MYSQL教程网3432019-12-19
  • Mysqlmysql 5.7.13 winx64安装配置教程

    mysql 5.7.13 winx64安装配置教程

    这篇文章主要为大家分享了windows下mysql 5.7.13winx64安装配置教程,总结在安装mysql5.7时遇到的坑,感兴趣的朋友可以参考一下 ...

    Admin97895562020-07-04
  • MysqlMysql 忘记root密码的完美解决方法

    Mysql 忘记root密码的完美解决方法

    通常在使用Mysql数据库时,如果长时间没有登陆,或者由于工作交接完成度不高,会导致数据库root登陆密码忘记,本文给大家介绍一种当忘记mysql root密码时...

    MR.QiGao5002020-07-07
  • Mysqlmysql5.7.18安装时mysql服务启动失败的解决方法

    mysql5.7.18安装时mysql服务启动失败的解决方法

    这篇文章主要为大家详细介绍了mysql5.7.18安装时mysql服务启动失败的解决方法,具有一定的参考价值,感兴趣的小伙伴们可以参考一下 ...

    chasing_the_sun5272020-08-28
  • MysqlMySQL中的唯一性约束与NULL详解

    MySQL中的唯一性约束与NULL详解

    这里记录的是很久之前的一个 bug 了,主要给大家介绍了关于MySQL中唯一性约束与NULL的相关资料,文中介绍的非常详细,对大家具有一定的参考学习价值,...

    Giraffe3622020-07-28