Mysql中mvcc各场景理解应用

2024-08-01 13:02:16

前言

mysql版本为

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.27    |
+-----------+
1 row in set (0.00 sec)

隔离级别

mysql> show variables like '%isola%';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.02 sec)

表结构

mysql> show create table test;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                                                 |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test  | CREATE TABLE `test` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` char(32) NOT NULL COMMENT '用户姓名',
  `num` int DEFAULT NULL,
  `phone` char(11) DEFAULT '' COMMENT '手机号',
  PRIMARY KEY (`id`),
  KEY `idx_name_phone` (`name`,`phone`)
) ENGINE=InnoDB AUTO_INCREMENT=108 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='test表'           |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec

现有表数据

mysql> select * from test;
+-----+---------------+---------+-------+
| id  | name          | num     | phone |
+-----+---------------+---------+-------+
|   1 | 执行业        | 1234567 |       |
|   2 | 执行业务1     |    NULL |       |
|   3 | a             |    NULL |       |
|   4 | a             |    NULL |       |
|   5 | a             |    NULL |       |
|   6 | b             |       1 |       |
|   7 | wdf           |    NULL |       |
|  10 | dd            |       1 |       |
|  11 | hello         |    NULL |       |
|  15 | df            |    NULL |       |
|  16 | e             |    NULL |       |
|  20 | e             |    NULL |       |
|  21 | 好的          |    NULL |       |
|  25 | g             |       1 |       |
| 106 | hello         |    NULL |       |
| 107 | a             |    NULL |       |
+-----+---------------+---------+-------+
16 rows in set (0.00 sec)

场景一

事务A:select * from test where id in (7,15) for update;。
事务B:update test set name='d' where id=10;和insert into test(id,name) values(8,'hello');。
事务A:select * from test where id in (7,8,10,15);。第二步是否阻塞。第三步是否能读到事务B执行的更新。

试验步骤

事务A第一步

mysql> begin;select * from test where id in (7,15) for update;
Query OK, 0 rows affected (0.00 sec)
+----+------+------+-------+
| id | name | num  | phone |
+----+------+------+-------+
|  7 | wdf  | NULL |       |
| 15 | df   | NULL |       |
+----+------+------+-------+
2 rows in set (0.01 sec)

持有锁情况：

mysql> select * from performance_schema.data_locks;
+--------+-----------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+
| ENGINE | ENGINE_LOCK_ID              | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE     | LOCK_STATUS | LOCK_DATA |
+--------+-----------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+
| INNODB | 4974808984:1063:4890706744  |                 46666 |        50 |      123 | my_test       | test        | NULL           | NULL              | NULL       |            4890706744 | TABLE     | IX            | GRANTED     | NULL      |
| INNODB | 4974808984:2:4:7:4915866136 |                 46666 |        50 |      123 | my_test       | test        | NULL           | NULL              | PRIMARY    |            4915866136 | RECORD    | X,REC_NOT_GAP | GRANTED     | 15        |
| INNODB | 4974808984:2:4:9:4915866136 |                 46666 |        50 |      123 | my_test       | test        | NULL           | NULL              | PRIMARY    |            4915866136 | RECORD    | X,REC_NOT_GAP | GRANTED     | 7         |
+--------+-----------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+
3 rows in set (0.00 sec)

发现7,15持有了行锁。

事务B执行

mysql> update test set name = 'sds' where id=10;insert into test(id,name) values(8,'hello');
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
Query OK, 1 row affected (0.00 sec)

事务A执行第二步

mysql> select * from test where id in (7,8,10,15);
+----+-------+------+-------+
| id | name  | num  | phone |
+----+-------+------+-------+
|  7 | wdf   | NULL |       |
|  8 | hello | NULL |       |
| 10 | sds   |    1 |       |
| 15 | df    | NULL |       |
+----+-------+------+-------+
4 rows in set (0.01 sec)

结果

步骤二执行了，事务A读到了事务B提交的数据。下面我们来看看正常的select;

场景二

还原数据：

mysql> update test set name = 'dd' where id=10;delete from test where id=8;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
Query OK, 1 row affected (0.00 sec)

事务A:select * from test where id in (7,15);。
事务B:update test set name='d' where id=10;和insert into test(id,name) values(8,'hello');。
事务A:select * from test where id in (7,8,10,15);。第二步是否阻塞。第三步是否能读到事务B执行的更新。

试验步骤

事务A第一步

mysql> begin;select * from test where id in (7,15);
Query OK, 0 rows affected (0.00 sec)
+----+------+------+-------+
| id | name | num  | phone |
+----+------+------+-------+
|  7 | wdf  | NULL |       |
| 15 | df   | NULL |       |
+----+------+------+-------+
2 rows in set (0.00 sec)

持有锁情况：

mysql&gt; select * from performance_schema.data_locks;
Empty set (0.00 sec)

事务B执行

mysql> update test set name = 'sds' where id=10;insert into test(id,name) values(8,'hello');
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
Query OK, 1 row affected (0.00 sec)

事务A执行第二步

mysql> select * from test where id in (7,8,10,15);
+----+------+------+-------+
| id | name | num  | phone |
+----+------+------+-------+
|  7 | wdf  | NULL |       |
| 10 | dd   |    1 |       |
| 15 | df   | NULL |       |
+----+------+------+-------+
3 rows in set (0.00 sec)

结果

步骤二执行了，事务A没读到了事务B提交的数据。笔者猜测for update加锁之后会清除readview或者没开启readview，所以后面会读到事务B的。

所以我们来看看到底是清除还是没开启。

事务A后续步骤

mysql> select * from test where id in (7,15) for update;
+----+------+------+-------+
| id | name | num  | phone |
+----+------+------+-------+
|  7 | wdf  | NULL |       |
| 15 | df   | NULL |       |
+----+------+------+-------+
2 rows in set (0.00 sec)
mysql> select * from test where id in (7,8,10,15);
+----+------+------+-------+
| id | name | num  | phone |
+----+------+------+-------+
|  7 | wdf  | NULL |       |
| 10 | dd   |    1 |       |
| 15 | df   | NULL |       |
+----+------+------+-------+
3 rows in set (0.00 sec)

可以发现重新执行了场景一的步骤后结果没变。

所以应该是没开启，应该是当前读不会开启readview。

笔者找了下资料没找到，找到的笔者可以留言。

不过我们可以使用继续实验验证下。

场景三

事务A:update test set name = 'dgf' where id in (7,15);。
事务B:update test set name='d' where id=10;和insert into test(id,name) values(8,'hello');。
事务A:select * from test where id in (7,8,10,15);。第二步是否阻塞。第三步是否能读到事务B执行的更新。

这个场景就不搞实验步骤了，结果是和笔者的猜想一样的 ”当前读不会开启readview，第一个快照读才会开启“

场景四

事务A:select * from test where id in (7,15);。
事务B:insert into test(id,name) values(8,'hello');。
事务A:select * from test where id in (7,8,15);。
事务A:update test set name ='cv' where id =8;。
事务A:select * from test where id in (7,8,15);。

事务A第一步

mysql> begin;select * from test where id in (7,15);
Query OK, 0 rows affected (0.00 sec)
+----+------+------+-------+
| id | name | num  | phone |
+----+------+------+-------+
|  7 | wdf  | NULL |       |
| 15 | df   | NULL |       |
+----+------+------+-------+
2 rows in set (0.00 sec)

开启了事务，浅读一下。

事务B执行

insert into test(id,name) values(8,'hello');

事务A第二步

mysql&gt; select * from test where id in (7,8,15);
+----+------+------+-------+
| id | name | num  | phone |
+----+------+------+-------+
|  7 | wdf  | NULL |       |
| 15 | df   | NULL |       |
+----+------+------+-------+
2 rows in set (0.00 sec)

检验一下是否读的到，发现读不到。

事务A第三步

mysql> update test set name ='cv' where  id =8;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

对插入的进行更新。

事务A第四步

mysql> select * from test where id in (7,8,15);
+----+------+------+-------+
| id | name | num  | phone |
+----+------+------+-------+
|  7 | wdf  | NULL |       |
|  8 | cv   | NULL |       |
| 15 | df   | NULL |       |
+----+------+------+-------+
3 rows in set (0.00 sec)

发现可以读到了。

原因

能读到的原因是因为本事务对版本链内容进行了修改，所以就读到了。

这个场景可能会出现在实际开发中，会比较懵，当然“事务A第三步”是笔者随便模拟的，实际生产中直接拿大不到刚刚插入的id，所以应该是模糊(没有确定行)update。所以在生产中还是要确定行去进行修改，避免出现这种比较难理解的场景。

虽然也可以使用lock in share mode或者for update读当前借助next-key去实现不幻读（第二次读到第一次没有读到的行），还是需要根据具体业务选择。

总结

根据以上的场景，我们可以知道：

readview是第一个select的时候才会创建的。
rr级别下读快照如果中间出现修改版本链内容还是会出现幻读（很合理，但是不容易发现这个原因），如果真的要想做到不幻读还是要通过加锁（当然要有索引，没有的话就锁表了）。

以上就是Mysql中mvcc各场景理解的详细内容，更多关于Mysql mvcc场景的资料请关注我们其它相关文章！

MySQL多版本并发控制MVCC深入学习

MVCC MVCC(Multi-Version Concurrency Control),即多版本并发控制.是 innodb 实现事务并发与回滚的重要功能.锁机制可以控制并发操作,但是其系统开销较大,而MVCC可以在大多数情况下代替行级锁,使用MVCC,能降低其系统开销. 具体实现是在数据库的每一行中,额外添加三个字段: DB_TRX_ID : 记录插入或更新该行的最后一个事务的事务ID DB_ROLL_PTR : 指向改行对应undolog 的指针 DB_ROW_ID : 单调递增的ID,他就
MySQL多版本并发控制MVCC底层原理解析

目录 1 事务并发中遇到的问题 1.1 脏读 1.2 不可重复读 1.3 幻读 2 隔离级别 3 版本链 4 ReadView 4.1 ReadView 定义 4.2 访问控制 4.3 再谈隔离 4.3.1 READ COMMITTED(读已提交) 4.3.2 REPEATABLE READ(可重读) 5 幻读 6 总结 1 事务并发中遇到的问题 1.1 脏读当一个事务读取到了另外一个事务修改但未提交的数据,被称为脏读. 1.2 不可重复读当事务内相同的记录被检索两次,且两次得到的结果不同时
MySQL中的乐观锁,悲观锁和MVCC全面解析

前言在数据库的实际使用过程中,我们常常会遇到不希望数据被同时写或者读的情景,例如秒杀场景下,两个请求同时读到系统还有库存1个,然后又先后把库存更新为0,这时候就会出现超卖的情况,这时候货物的实际库存和我们的记录就会对应不上了. 为了解决这种资源竞争导致的数据不一致等问题,我们需要有一种机制来进行保证数据的正确访问和修改,而在数据库中,这种机制就是数据库的并发控制.其中乐观并发控制,悲观并发控制和多版本并发控制是数据库并发控制主要采用的技术手段. 悲观并发控制本质维基百科:在关系数据库管理系
mysql的MVCC多版本并发控制的实现

1 什么是MVCC MVCC全称是: Multiversion concurrency control,多版本并发控制,提供并发访问数据库时,对事务内读取的到的内存做处理,用来避免写操作堵塞读操作的并发问题. 举个例子,程序员A正在读数据库中某些内容,而程序员B正在给这些内容做修改(假设是在一个事务内修改,大概持续10s左右),A在这10s内则可能看到一个不一致的数据,在B没有提交前,如何让A能够一直读到的数据都是一致的呢? 有几种处理方法,第一种: 基于锁的并发控制,程序员B开始修改数据时,
mysql多版本并发控制MVCC的实现

事务隔离级别设置 set global transaction isolation level read committed; //全局的 set session transaction isolation level read committed; //当前会话修改事务提交方式(是否自动提交,mysql默认自动提交) SET AUTOCOMMIT = 1; //自动提交,为0手动提交不同数据库引擎MVCC模式各不相同,典型有乐观和悲观并发控制. innodb 说明: InnoDB的MVCC
Mysql MVCC机制原理详解

什么是MVCC MVCC,全称Multi-Version Concurrency Control,即多版本并发控制.MVCC是一种并发控制的方法,一般在数据库管理系统中,实现对数据库的并发访问,在编程语言中实现事务内存. 我们知道,一般情况下我们使用mysql数据库的时候使用的是Innodb存储引擎,Innodb存储引擎是支持事务的,那么当多线程同时执行事务的时候,可能会出现并发问题.这个时候需要一个能够控制并发的方法,MVCC就起到了这个作用. Mysql的锁和事务隔离级别在理解MVCC机制
Mysql中mvcc各场景理解应用

目录前言场景一试验步骤事务A第一步事务B执行事务A执行第二步结果场景二试验步骤事务A第一步事务B执行事务A执行第二步结果事务A后续步骤场景三场景四事务A第一步事务B执行事务A第二步事务A第三步事务A第四步原因总结前言 mysql版本为 mysql> select version(); +-----------+ | version() | +-----------+ | 8.0.27 | +-----------+ 1 row in set (0.
深入理解MySQL中MVCC与BufferPool缓存机制

目录一.MVCC机制 undo日志版本链与read-view机制版本链比对规则二.BufferPool机制三.总结一.MVCC机制 MVCC(Multi Version Concurrency Control),MySQL(默认)RR隔离级别就是通过该机制来保证的,对一行数据的读与写两个操作默认是不会通过加锁互斥来保证隔离性的串行化隔离级别是为了保证较高的隔离性,是通过将所有操作加锁互斥来实现的 MySQL在RC隔离级别和RR隔离级别下都实现了MVCC机制 RC每次查询都会创建一个r
MySQL中MVCC机制的实现原理

目录前言什么是当前读和快照读? MVCC的实现原理前言 MVCC全称为Multi Version Concurrency Control,直译为多版本并发控制,是MySQL中一种并发控制的方法,他主要是为了提高数据库的读写性能,用更好的方式去处理读写冲突. 什么是当前读和快照读? 在讲述MVCC机制实现原理之前,我们先了解一下当前读和快照读当前读: 其实也很容易理解,当前读指的就是读取的是最新的记录,读取的时候还需要保证其他事务不能修改当前记录,所以会对读取的记录进行加锁处理例如:se
MySQL中group_concat函数深入理解

本文通过实例介绍了MySQL中的group_concat函数的使用方法,比如select group_concat(name) . MySQL中group_concat函数完整的语法如下: group_concat([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator '分隔符']) 基本查询 mysql> select * from aa; +------+------+ | id| name | +------+------+ |1 |
mysql中的mvcc 原理详解

目录简介前言一.mysql 数据写入磁盘流程二.redo log 1.redolog 的整体流程 2.为什么需要 redo log 三.undo log 1.undo log 特点 2.undo log 类型 3.undo log 生成过程 4.undo log 回滚过程 5.undo log的删除四.mvcc 1.什么是MVCC 2.MVCC组成 3.快照读与当前读快照读当前读五.mvcc操作演示 1.READ COMMITTED 隔离级别 2.REPEATABLE READ
MySQL中 LBCC 和 MVCC 的理解及常见问题示例

目录 1. 事务 2. MVCC初探 3. LBCC & MVCC 总结 1. 事务介绍MVCC之前,先介绍下事务:事务是为了保证数据库中数据的完整性和一致性. 事务的4个基本要素: 原子性(Atomicity):要么同时成功,要么同时失败.(通过undo log回滚日志实现) 一致性(Consistency):一方扣款 xxx 元,另一方收款 xxx 元,符合事物发展的正常逻辑(通过lock锁实现) 隔离性(Isolation):此时有多个类似扣款/收款事件同时发生,每个事件之间是相互独
一文带你理解MySql中explain结果filtered

MySql explain语句的返回结果中,filtered字段要怎么理解? MySql5.7官方文档中描述如下: The filtered column indicates an estimated percentage of table rows filtered by the table condition. The maximum value is 100, which means no filtering of rows occurred. Values decreasing from
MySQL中索引失效的常见场景与规避方法

前言之前有看过许多类似的文章内容,提到过一些sql语句的使用不当会导致MySQL的索引失效.还有一些MySQL"军规"或者规范写明了某些sql不能这么写,否则索引失效. 绝大部分的内容笔者是认可的,不过部分举例中笔者认为用词太绝对了,并没有说明其中的原由,很多人不知道为什么.所以笔者绝对再整理一遍MySQL中索引失效的常见场景,并分析其中的原由供大家参考. 当然请记住,explain是一个好习惯! MySQL索引失效的常见场景在验证下面的场景时,请准备足够多的数据量,因为数据量少时
深入理解r2dbc在mysql中的使用

简介 mysql应该是我们在日常工作中使用到的一个非常普遍的数据库,虽然mysql现在是oracle公司的,但是它是开源的,市场占有率还是非常高的. 今天我们将会介绍r2dbc在mysql中的使用. r2dbc-mysql的maven依赖要想使用r2dbc-mysql,我们需要添加如下的maven依赖: <dependency> <groupId>dev.miku</groupId> <artifactId>r2dbc-mysql</artifact