首页 数据库MySQL正文

Mysql中mvcc各场景理解应用

吾爱主题 阅读：131 2024-04-01 23:20:44 评论：0

前言

  • mysql版本为
?
1 2 3 4 5 6 7 mysql> select version(); + -----------+ | version() | + -----------+ | 8.0.27    | + -----------+ 1 row in set (0.00 sec)
  • 隔离级别
?
1 2 3 4 5 6 7 mysql> show variables like '%isola%' ; + -----------------------+-----------------+ | Variable_name         | Value           | + -----------------------+-----------------+ | transaction_isolation | REPEATABLE - READ | + -----------------------+-----------------+ 1 row in set (0.02 sec)
  • 表结构
?
1 2 3 4 5 6 7 8 9 10 11 12 13 14 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
  • 现有表数据
?
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 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第一步

?
1 2 3 4 5 6 7 8 9 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)

持有锁情况：

?
1 2 3 4 5 6 7 8 9 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执行

?
1 2 3 4 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执行第二步

?
1 2 3 4 5 6 7 8 9 10 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;

场景二

还原数据：

?
1 2 3 4 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第一步

?
1 2 3 4 5 6 7 8 9 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)

持有锁情况：

?
1 2 mysql> select * from performance_schema.data_locks; Empty set (0.00 sec)

事务B执行

?
1 2 3 4 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执行第二步

?
1 2 3 4 5 6 7 8 9 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后续步骤

?
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 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第一步

?
1 2 3 4 5 6 7 8 9 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执行

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

事务A第二步

?
1 2 3 4 5 6 7 8 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第三步

?
1 2 3 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第四步

?
1 2 3 4 5 6 7 8 9 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场景的资料请关注服务器之家其它相关文章！

原文链接：https://juejin.cn/post/7126919738692730893

可以去百度分享获取分享代码输入这里。
声明

1.本站遵循行业规范，任何转载的稿件都会明确标注作者和来源；2.本站的原创文章，请转载时务必注明文章作者和来源，不尊重原创的行为我们将追究责任；3.作者投稿可能会经我们编辑修改或补充。

相关文章
【腾讯云】云服务器产品特惠热卖中
搜索
排行榜
最近发表
标签列表
    关注我们

    了解等多精彩内容