返回
首页
业界
电商
创业
访谈
手机
移动
报告
运营
建站
互联网+
系统
教程
搜索

Mysql中mvcc各场景理解应用

2022-08-04 10:50:20
目录
前言场景一试验步骤事务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>。事务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>

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

              事务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>。事务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>。事务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>或者for update读当前借助next-key去实现不幻读（第二次读到第一次没有读到的行），还是需要根据具体业务选择。

                  总结

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

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

                    以上就是Mysql中mvcc各场景理解的详细内容，更多关于Mysql>

相关文章 大家在看