Mysql中mvcc各场景理解应用
作者:王者之峰 发布时间:2024-01-24 19:29:07
前言
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级别下读快照如果中间出现修改版本链内容还是会出现幻读(很合理,但是不容易发现这个原因),如果真的要想做到不幻读还是要通过加锁(当然要有索引,没有的话就锁表了)。
来源:https://juejin.cn/post/7126919738692730893
猜你喜欢
- 这篇文章主要介绍了基于python调用psutils模块过程解析,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值
- Python中多线程使用到Threading模块。Threading模块中用到的主要的类是Thread,我们先来写一个简单的多线程代码:#
- 一种很常见的写法: document.write('<scr'+'ipt src=&quo
- 介绍pandas数据聚合和重组的相关知识,仅供参考。1GroupBy技术1.1简介简介:根据一个或多个键进行分组,每一组应用函数,再进行合并
- 1 简介孤立森林(isolation Forest)是一种高效的异常检测算法,它和随机森林类似,但每次选择划分属性和划分点(值)时都是随机的
- 1.对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。 2.应尽量避免在 where
- Oracle数据库先创建一个表和添加一些数据1.先在Oracle数据库中创建一个student表:create table student(
- 数据库分类早期数据库模型有三种,分别为层次式数据库、网络式数据库和关系型数据库。而在当今的互联网中,通常把数据库分为两类,即关系型数据库和非
- 1.string.maketrans设置字符串转换规则表(translation table) allchars = string.make
- 事件调度sched 模块内容很简单,只定义了一个类。它用来最为一个通用的事件调度模块。class sched.scheduler(timef
- 【原文地址】 Fixes for Common VS 2008 and .NET 3.5 Beta2 Issu
- 对于大多数web应用来说,数据库都是一个十分基础性的部分。如果你在使用PHP,那么你很可能也在使用MySQL—LAMP系列中举足轻重的一份子
- 1、代码from aip import AipFaceimport cv2import timeimport base64from PIL
- 通常情况下:from threading import Threadglobal_num = 0def func1(): global gl
- 前言这篇博文的目的是演示如何使用 OpenCV、Python 和面部标志对齐人脸。给定一组面部标志(输入坐标),我们的目标是将图像扭曲并转换
- 一、需求分析最近同事用网上提供扫描软件进行扫描识别文字,每天上线只能够做两次扫描,请求我研发一个小工具帮助解决识别图片的中文字。二、方案选择
- 本文实例为大家分享了python语音整点报时的具体代码,供大家参考,具体内容如下主要的技术特殊点在于PyS60的定时器最多只能定2147秒。
- 1. 首先介绍pip常用命令pip安装命令: pip install package_name pip升级命令:pip install –u
- 一、软件包a) freetds-stable.gzb) php-5.2.12.tar.gz二、安装步骤a) tar zxvf freetds
- 需求:web系统有包含以下5个url,分别对于不同资源;1、stu/add_stu/2、stu/upload_homework/3、stu/