Mysql中mvcc各场景理解应用_Mysql

来源:脚本之家  责任编辑:小易  
目录
前言场景一试验步骤事务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.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> 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> 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底层原理解析MySQL多版本并发控制MVCC深入学习Mysql MVCC机制原理详解mysql的MVCC多版本并发控制的实现MySQL中的乐观锁,悲观锁和MVCC全面解析mysql多版本并发控制MVCC的实现

  • 本文相关:
  • mysql基础入门 轻松学习mysql命令
  • mysql execute、executeupdate、executequery三者的区别
  • 如何使用myisamchk和mysqlcheck工具快速修复损坏的mysql数据库文件
  • mysql存储过程及常用函数代码解析
  • 一文掌握mysql表的创建和约束
  • mysql使用正则表达式过滤数据
  • mysql允许远程连接的方法
  • mysql多版本并发控制mvcc底层原理解析
  • mysql 一个巧用字符函数做数据筛选的题
  • mysql innodb引擎的索引与存储结构详解
  • MySQL全局锁和表锁的深入理解
  • pgsql比mysql好在哪
  • Mysql中事务ACID的实现原理详解
  • 全面了解MySql中的事务
  • MySQL中无过滤条件的count详解
  • MySQL中大对象的多版本并发控制详解
  • 为何Redis用乐观锁,而MySQL数据库却没有
  • 详解MySQL(InnoDB)是如何处理死锁的
  • MySQL InnoDB存储引擎的深入探秘
  • MySQL备份原理详解
  • MySQL问答系列之如何避免ibdata1文件大小暴涨
  • 深入解析MySQL的事务隔离及其对性能产生的影响
  • MySQL存储引擎InnoDB的配置与使用的讲解
  • 网站首页网页制作脚本下载服务器操作系统网站运营平面设计媒体动画电脑基础硬件教程网络安全mssqlmysqlmariadboracledb2mssql2008mssql2005sqlitepostgresqlmongodbredisaccess数据库文摘数据库其它首页mysqlmysql多版本并发控制mvcc底层原理解析mysql多版本并发控制mvcc深入学习mysql mvcc机制原理详解mysql的mvcc多版本并发控制的实现mysql中的乐观锁,悲观锁和mvcc全面解析mysql多版本并发控制mvcc的实现mysql基础入门 轻松学习mysql命令mysql execute、executeupdate、executequery三者的区别如何使用myisamchk和mysqlcheck工具快速修复损坏的mysql数据库文件mysql存储过程及常用函数代码解析一文掌握mysql表的创建和约束mysql使用正则表达式过滤数据mysql允许远程连接的方法mysql多版本并发控制mvcc底层原理解析mysql 一个巧用字符函数做数据筛选的题mysql innodb引擎的索引与存储结构详解mysql安装图解 mysql图文安装教程(详细说明)can""""t connect to mysql servwindows下mysql5.6版本安装及配置过程附有截图和mysql字符串截取函数substring的用法说明mysql提示:the server quit withoumysql之timestamp(时间戳)用法详解mysql创建用户与授权方法mysql——修改root密码的4种方法(以windows为mysql日期数据类型、时间类型使用总结mysql查看版本号的几种方式mysql5.7.19 解压版安装教程详解(附送纯净破解中文版sqlyog)mysql单文件存储删除数据文件容量不会减少的bug与解决方法idea链接mysql报错08001和连接成功后不显示表的问题及解决方法mysql数据库修改添加date格式列的方法mysql数据库表中为什么有索引却没有提高查询速度简单了解mysql union all与union的区别使用limit,offset分页场景时为什么会慢mysql 数据库死锁原因及解决办法mysql常见问题解决mysql 复制表详解及实例代码
    免责声明 - 关于我们 - 联系我们 - 广告联系 - 友情链接 - 帮助中心 - 频道导航
    Copyright © 2017 www.zgxue.com All Rights Reserved