Mysql中mvcc各場(chǎng)景理解應(yīng)用

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

這篇文章主要為大家介紹了Mysql中mvcc各場(chǎng)景理解應(yīng)用，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進(jìn)步，早日升職加薪

前言

mysql版本為

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.27    |
+-----------+
1 row in set (0.00 sec)

隔離級(jí)別

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 '手機(jī)號(hào)',
  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è)務(wù)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)

場(chǎng)景一

事務(wù)A:select * from test where id in (7,15) for update;。
事務(wù)B:update test set name='d' where id=10;和insert into test(id,name) values(8,'hello');。
事務(wù)A:select * from test where id in (7,8,10,15);。第二步是否阻塞。第三步是否能讀到事務(wù)B執(zhí)行的更新。

試驗(yàn)步驟

事務(wù)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持有了行鎖。

事務(wù)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)

事務(wù)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í)行了，事務(wù)A讀到了事務(wù)B提交的數(shù)據(jù)。下面我們來(lái)看看正常的select;

場(chǎng)景二

還原數(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)

事務(wù)A:select * from test where id in (7,15);。
事務(wù)B:update test set name='d' where id=10;和insert into test(id,name) values(8,'hello');。
事務(wù)A:select * from test where id in (7,8,10,15);。第二步是否阻塞。第三步是否能讀到事務(wù)B執(zhí)行的更新。

試驗(yàn)步驟

事務(wù)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)

事務(wù)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)

事務(wù)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í)行了，事務(wù)A沒讀到了事務(wù)B提交的數(shù)據(jù)。筆者猜測(cè)for update加鎖之后會(huì)清除readview或者沒開啟readview，所以后面會(huì)讀到事務(wù)B的。

所以我們來(lái)看看到底是清除還是沒開啟。

事務(wù)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í)行了場(chǎng)景一的步驟后結(jié)果沒變。

所以應(yīng)該是沒開啟，應(yīng)該是當(dāng)前讀不會(huì)開啟readview。

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

不過(guò)我們可以使用繼續(xù)實(shí)驗(yàn)驗(yàn)證下。

場(chǎng)景三

事務(wù)A:update test set name = 'dgf' where id in (7,15);。
事務(wù)B:update test set name='d' where id=10;和insert into test(id,name) values(8,'hello');。
事務(wù)A:select * from test where id in (7,8,10,15);。第二步是否阻塞。第三步是否能讀到事務(wù)B執(zhí)行的更新。

這個(gè)場(chǎng)景就不搞實(shí)驗(yàn)步驟了，結(jié)果是和筆者的猜想一樣的 ”當(dāng)前讀不會(huì)開啟readview，第一個(gè)快照讀才會(huì)開啟“

場(chǎng)景四

事務(wù)A:select * from test where id in (7,15);。
事務(wù)B:insert into test(id,name) values(8,'hello');。
事務(wù)A:select * from test where id in (7,8,15);。
事務(wù)A:update test set name ='cv' where id =8;。
事務(wù)A:select * from test where id in (7,8,15);。

事務(wù)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)

開啟了事務(wù)，淺讀一下。

事務(wù)B執(zhí)行

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

事務(wù)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)

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

事務(wù)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

對(duì)插入的進(jìn)行更新。

事務(wù)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)可以讀到了。

原因

能讀到的原因是因?yàn)楸臼聞?wù)對(duì)版本鏈內(nèi)容進(jìn)行了修改，所以就讀到了。

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

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

總結(jié)

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

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

以上就是Mysql中mvcc各場(chǎng)景理解的詳細(xì)內(nèi)容，更多關(guān)于Mysql mvcc場(chǎng)景的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！

您可能感興趣的文章: