您现在的位置是：网站首页> 编程资料编程资料

Mysql中mvcc各场景理解应用_Mysql_

2023-05-26 555人已围观

简介 Mysql中mvcc各场景理解应用_Mysql_

前言

  • 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 | +----+------+------+-------+ | 
                
                

                

                                            
上一篇：一文解答什么是MySQL的回表_Mysql_

                                                                
下一篇：MySQL性能指标TPS+QPS+IOPS压测_Mysql_

相关内容

点击排行

本栏推荐

猜你喜欢

-六神源码网