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)

表結構

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)

結果

步驟二執(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)

結果

步驟二執(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í)行了場景一的步驟后結果沒變。

所以應該是沒開啟，應該是當前讀不會開啟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í)行的更新。

這個場景就不搞實驗步驟了，結果是和筆者的猜想一樣的 ”當前讀不會開啟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)可以讀到了。

原因

能讀到的原因是因為本事務對版本鏈內容進行了修改，所以就讀到了。

這個場景可能會出現(xiàn)在實際開發(fā)中，會比較懵，當然“事務A第三步”是筆者隨便模擬的，實際生產(chǎn)中直接拿大不到剛剛插入的id，所以應該是模糊(沒有確定行)update。所以在生產(chǎn)中還是要確定行去進行修改，避免出現(xiàn)這種比較難理解的場景。

雖然也可以使用lock in share mode或者for update讀當前借助next-key去實現(xiàn)不幻讀（第二次讀到第一次沒有讀到的行），還是需要根據(jù)具體業(yè)務選擇。

總結

根據(jù)以上的場景，我們可以知道：

readview是第一個select的時候才會創(chuàng)建的。
rr級別下讀快照如果中間出現(xiàn)修改版本鏈內容還是會出現(xiàn)幻讀（很合理，但是不容易發(fā)現(xiàn)這個原因），如果真的要想做到不幻讀還是要通過加鎖（當然要有索引，沒有的話就鎖表了）。

以上就是Mysql中mvcc各場景理解的詳細內容，更多關于Mysql mvcc場景的資料請關注腳本之家其它相關文章！

您可能感興趣的文章:

$安裝mysql-8.0.19-winx64遇到的問題：Can''t create directory ''xxxx\Database\''$
安裝mysql-8.0.19-winx64遇到的問題：Can''t create directory ''xxxx\Da
這篇文章主要介紹了安裝mysql-8.0.19-winx64遇到的坑，Can't create directory 'xxxx\Database\',非常不錯，具有一定的參考借鑒價值，需要的朋友可以參考下
2020-02-02
mysql 5.7.21 winx64免安裝版配置方法圖文教程
這篇文章主要為大家詳細介紹了mysql 5.7.21 winx64免安裝版配置方法圖文教程，具有一定的參考價值，感興趣的小伙伴們可以參考一下
2018-02-02
一次MySQL啟動導致的事故實戰(zhàn)記錄
這篇文章主要給大家介紹了一次MySQL啟動導致的事故實戰(zhàn)記錄,記錄了MySQL 啟動成功但未監(jiān)聽端口的解決方法,文中給出了詳細的解決方法,需要的朋友可以參考下
2021-09-09
關于MySql數(shù)據(jù)庫Update批量更新不同值的實現(xiàn)方法
這篇文章主要介紹了關于MySql數(shù)據(jù)庫Update批量更新不同值的實現(xiàn)方法，數(shù)據(jù)庫管理系統(tǒng)可以通過SQL管理數(shù)據(jù)庫，定義和操作數(shù)據(jù)，維護數(shù)據(jù)的完整性和安全性,需要的朋友可以參考下
2023-05-05
MySQL數(shù)據(jù)庫多表聯(lián)合查詢代碼示例
所謂聯(lián)合就是把多個表的記錄往一起合并,一起進行查詢,也叫多表查詢,這篇文章主要給大家介紹了關于MySQL數(shù)據(jù)庫多表聯(lián)合查詢的相關資料,需要的朋友可以參考下
2024-01-01
MySQL雙層游標嵌套循環(huán)實現(xiàn)方法
要實現(xiàn)逐行獲取數(shù)據(jù),需要用到MySQL中的游標,一個游標相當于一個for循環(huán),這里需要用到2個游標,如何在MySQL中實現(xiàn)游標雙層循環(huán)呢,下面小編給大家分享MySQL雙層游標嵌套循環(huán)方法,感興趣的朋友跟隨小編一起看看吧
2024-05-05
mysql數(shù)據(jù)庫刪除重復數(shù)據(jù)只保留一條方法實例
這篇文章主要給大家介紹了關于mysql數(shù)據(jù)庫刪除重復數(shù)據(jù)，只保留一條的相關資料，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧
2021-03-03
MySQL分表實現(xiàn)上百萬上千萬記錄分布存儲的批量查詢設計模式詳解
本篇文章是對使用MySQL分表實現(xiàn)上百萬上千萬記錄分布存儲的批量查詢設計模式進行了詳細的分析介紹，需要的朋友參考下
2013-06-06
MySQL筆記之函數(shù)查詢的使用
本篇文章介紹了mysql中函數(shù)查詢的使用。需要的朋友參考下
2013-05-05
Navicat使用報2059錯誤的兩種解決方案
Navicat是一款流行的數(shù)據(jù)庫管理工具,而MySQL則是其中的一種數(shù)據(jù)庫軟件,下面這篇文章主要給大家介紹了關于Navicat使用報2059錯誤的兩種解決方案,需要的朋友可以參考下
2023-11-11