Mysql中mvcc各場景理解應用

更新時間：2022年08月04日 08:42:05 作者：王者之峰

這篇文章主要為大家介紹了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)

表結(jié)構(gòu)

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

現(xiàn)有表數(shù)據(jù)

mysql> select * from test;
+-----+---------------+---------+-------+
| id  | name          | num     | phone |
+-----+---------------+---------+-------+
|   1 | 執(zhí)行業(yè)        | 1234567 |       |
|   2 | 執(zhí)行業(yè)務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執(zhí)行的更新。

試驗步驟

事務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)

發(fā)現(xiàn)7,15持有了行鎖。

事務B執(zhí)行

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執(zhí)行第二步

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)

結(jié)果

步驟二執(zhí)行了，事務A讀到了事務B提交的數(shù)據(jù)。下面我們來看看正常的select;

場景二

還原數(shù)據(jù)：

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執(zhí)行的更新。

試驗步驟

事務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&gt; select * from performance_schema.data_locks;
Empty set (0.00 sec)

事務B執(zhí)行

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執(zhí)行第二步

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)

結(jié)果

步驟二執(zhí)行了，事務A沒讀到了事務B提交的數(shù)據(jù)。筆者猜測for update加鎖之后會清除readview或者沒開啟readview，所以后面會讀到事務B的。

所以我們來看看到底是清除還是沒開啟。

事務A后續(xù)步驟

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)

可以發(fā)現(xiàn)重新執(zhí)行了場景一的步驟后結(jié)果沒變。

所以應該是沒開啟，應該是當前讀不會開啟readview。

筆者找了下資料沒找到，找到的筆者可以留言。

不過我們可以使用繼續(xù)實驗驗證下。

場景三

事務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執(zhí)行的更新。

這個場景就不搞實驗步驟了，結(jié)果是和筆者的猜想一樣的 ”當前讀不會開啟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執(zhí)行

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

事務A第二步

mysql&gt; 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)

檢驗一下是否讀的到，發(fā)現(xiàn)讀不到。

事務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)

發(fā)現(xiàn)可以讀到了。