Mysql中mvcc各场景理解应用
作者：王者之峰 发布时间：2024-01-24 19:29:07
标签：Mysql,mvcc,场景
前言
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
0
投稿猜你喜欢
简单定义图轴：import numpy as npimport matplotlib.pyplot as plt创建一个简单的matplot- 本文实例讲述了Python运维自动化之nginx配置文件对比操作。分享给大家供大家参考，具体如下：文件差异对比diff.py#!/usr/b
- 本文实例为大家分享了python实现桌面壁纸切换功能的具体实现方法，供大家参考，具体内容如下大体分为两个部分一、利用爬虫爬取壁纸第一部分爬取
- 引言如果你不确定TypeScript是什么，它基本上是一种建立在JavaScript之上的类型化语言。所有的JavaScript都是有效的T
- 最近的一些疫情信息很让人揪心，为了方便大家掌握疫情信息，在空闲之余做了一个关于 nCoV 的疫情监控小助手。主要的功能是通过企业微信的 We
- 本文实例讲述了Python使用回溯法子集树模板解决爬楼梯问题。分享给大家供大家参考，具体如下：问题某楼梯有n层台阶，每步只能走1级台阶，或2
- 本文实例讲述了php中使用key,value,current,next和prev函数遍历数组的方法。分享给大家供大家参考。具体分析如下：ph
- 这篇文章主要介绍了Python assert关键字原理及实例解析,文中通过示例代码介绍的非常详细，对大家的学习或者工作具有一定的参考学习价值
- 工欲善其事，必先利其器，开发工具这个东西觉得折腾下还是有好处的。但常常感觉专门抽出时间搞这个浪费时间，更常见的现象是已经明显感觉到当前的开发
- 如下所示：# coding: utf-8import paramikoimport MySQLdbdef main(): connectio
- 如下代码：<div id="vue_det"> <
- 依赖安装Python安装git安装Django1.去Python官网下载Python3.6并配置好环境变量 Python下载地址：https
- 本文实例讲述了Python面向对象类的继承。分享给大家供大家参考，具体如下：一、概述面向对象编程 (OOP) 语言的一个主要功能就是“继承”
- 如下所示：>>> item={} ; items=[] #先声明一个字典和一个列表，字典用来添加到列表里面&g
- 突如其来想知道一下 python 如何修改文件的属性（创建、修改、访问时间），于是就去网上搜集了可行方案，也就有了这篇博客方案一from w
- 最近做拍卖小程序，里面有一个需求是监控拍卖时间，需要对时间进行动态的倒计时显示从构思开始，做这个倒计时也花了我4个小时多，也遇到了很多问题，
- 当程序出现错误时，系统会自动引发异常。除此之外，Python 也允许程序自行引发异常，自行引发异常使用 raise 语句来完成。很多时候，系
- MySQL GUI工具很多，本文就常用的Navicat for MySQL与MySQL-Front的特色功能做一个详细介绍与比较。(一)My
- 接着上篇文章《解析SQL 表结构信息查询 含主外键、自增长》里面提到了INFORMATION_SCHEMA视图，其实到了SQL 2005微软
- 版本选择因为MySql的版本越来越多，而作为中小网站者可能没有足够的经济去购买商业版本，所以一般选择免费版，而且功能也是足够使用的。有钱任性
