腳本之家服務(wù)器常用軟件

快捷導(dǎo)航

MySQL的MVCC是否解決幻讀(最新推薦)

更新時(shí)間：2023年07月22日 09:32:14 作者：huchao_lingo

這篇文章主要介紹了MySQL的MVCC是否解決幻讀,本文給大家介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下

MySQL的MVCC是否解決幻讀

MySQL事務(wù)隔離級(jí)別

?讀未提交（ReadUncommitted）：最低的隔離級(jí)別，會(huì)讀取到其他事務(wù)還未提交的內(nèi)容，存在臟讀。

?讀已提交（ReadCommitted）：讀取到的內(nèi)容都是已經(jīng)提交的，可以解決臟讀，但是存在不可重復(fù)讀。

?可重復(fù)讀（RepeatableRead）：在一個(gè)事務(wù)中多次讀取時(shí)看到相同的內(nèi)容，可以解決不可重復(fù)讀，但是存在幻讀。但是在InnoDB中不存在幻讀問(wèn)題，對(duì)于快照讀，InnoDB使用MVCC解決幻讀，對(duì)于當(dāng)前讀，InnoDB通過(guò)gaplocks或next-keylocks解決幻讀。

?串行化（Serializable）：最高的隔離級(jí)別，串行的執(zhí)行事務(wù)，沒(méi)有并發(fā)事務(wù)問(wèn)題。

MySQL默認(rèn)的事務(wù)隔離級(jí)別是可重復(fù)讀（REPEATABLEREAD）

簡(jiǎn)單總結(jié)一下，MySQL的4種事務(wù)隔離級(jí)別對(duì)應(yīng)臟讀、不可重復(fù)讀和幻讀的關(guān)系如下：

事務(wù)隔離級(jí)別	臟讀	不可重復(fù)讀	幻讀
讀未提交（READ UNCOMMITTED）	√	√	√
讀已提交（READ COMMITTED）	×	√	√
可重復(fù)讀（REPEATABLE READ）	×	×	√
串行化（SERIALIZABLE）	×	×	×

不可重復(fù)度和幻讀的區(qū)別

不可重復(fù)讀主要是說(shuō)多次讀取一條記錄,發(fā)現(xiàn)該記錄中某些列值被修改過(guò)。

幻讀主要是說(shuō)多次讀取一個(gè)范圍內(nèi)的記錄(包括直接查詢所有記錄結(jié)果或者做聚合統(tǒng)計(jì)),發(fā)現(xiàn)結(jié)果不一致(標(biāo)準(zhǔn)檔案一般指記錄增多,記錄的減少應(yīng)該也算是幻讀)。(可以參考)

模擬幻讀問(wèn)題

環(huán)境準(zhǔn)備

事務(wù)相關(guān)的命令

# 查看 MySQL 版本
select version();
# 開(kāi)啟事務(wù)
start transaction;
# 提交事務(wù)
commit;
# 回滾事務(wù)
rollback;

MySQL8查詢事務(wù)隔離級(jí)別的命令

select @@global.transaction_isolation,@@transaction_isolation;

通過(guò)以下SQL可以設(shè)置當(dāng)前客戶端的事務(wù)隔離級(jí)別：

set session transaction isolation level 事務(wù)隔離級(jí)別;

事務(wù)隔離級(jí)別的值有4個(gè)：READUNCOMMITTED、READCOMMITTED、REPEATABLEREAD、SERIALIZABLE。

測(cè)試數(shù)據(jù)準(zhǔn)備

創(chuàng)建測(cè)試數(shù)據(jù)庫(kù)和表信息，執(zhí)行SQL如下：

-- 創(chuàng)建數(shù)據(jù)庫(kù)
drop database if exists testdb;
create database testdb;
use testdb;
-- 創(chuàng)建表
create table userinfo(
  id int primary key auto_increment,
  name varchar(250) not null,
  balance decimal(10,2) not null default 0
);
-- 插入測(cè)試數(shù)據(jù)
insert into userinfo(id,name,balance) values(1,'Java',100),(2,'MySQL',200);

創(chuàng)建的表結(jié)構(gòu)和數(shù)據(jù)如下：

mysql> select * from userinfo;
+----+-------+---------+
| id | name  | balance |
+----+-------+---------+
|  1 | Java  | 100.00  |
|  2 | MySQL | 200.00  |
+----+-------+---------+
2 rows in set (0.00 sec)

幻讀模擬

接下來(lái)會(huì)使用兩個(gè)窗口（兩個(gè)客戶端）來(lái)演示事務(wù)在可重復(fù)讀隔離級(jí)別下的幻讀的問(wèn)題。

幻讀場(chǎng)景1

在一個(gè)事務(wù)中明明沒(méi)有查到主鍵為X的數(shù)據(jù)，但主鍵為X的數(shù)據(jù)就是插入不進(jìn)去，就像某種幻覺(jué)一樣。幻讀演示的執(zhí)行流程如下：

事務(wù)隔離級(jí)別	臟讀	不可重復(fù)讀	幻讀
讀未提交（READ UNCOMMITTED）	√	√	√
讀已提交（READ COMMITTED）	×	√	√
可重復(fù)讀（REPEATABLE READ）	×	×	√
串行化（SERIALIZABLE）	×	×	×

在窗口2中查詢id為3的記錄為空

mysql> set session transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from userinfo where id=3;
Empty set

在窗口1中插入一條id為3的記錄

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into userinfo(id,name,balance) values(3,'Spring',100);
Query OK, 1 row affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

在窗口2中插入id為3的記錄

insert into userinfo(id,name,balance) values(3,'Spring',100);

發(fā)現(xiàn)插入失敗

mysql> insert into userinfo(id,name,balance) values(3,'Spring',100);
1062 - Duplicate entry '3' for key 'userinfo.PRIMARY'

繼續(xù)在窗口2查詢id為3的記錄，發(fā)現(xiàn)查詢不到

mysql> select * from userinfo where id=3;
Empty set

幻讀場(chǎng)景2

在一個(gè)事務(wù)A中一開(kāi)始查詢不到id為X的記錄，但在另一個(gè)事務(wù)B中插入id為X的記錄并提交事務(wù)后，在事務(wù)A中再更新id為X的記錄可以更新到，再次查詢id為X的記錄也可以查詢到。

執(zhí)行步驟	客戶端1（窗口1）	客戶端2（窗口2）	說(shuō)明
第 1 步	set session transaction isolation level repeatable read; start transaction; select * from userinfo where id=4;		設(shè)置事務(wù)隔離級(jí)別為可重復(fù)讀；開(kāi)啟事務(wù)；查詢用戶編號(hào)為 4 的數(shù)據(jù)，查詢結(jié)果為空。
第 2 步		set session transaction isolation level repeatable read; start transaction; select * from userinfo where id=4; insert into userinfo(id,name,balance) values(4,'Redis',100); commit;	開(kāi)啟事務(wù)；添加用戶，用戶編號(hào)為 4；提交事務(wù)。
第 3 步	select * from userinfo where id=4;		查詢用戶編號(hào)為 3 的數(shù)據(jù)，查詢結(jié)果為空。此時(shí)還未出現(xiàn)幻讀問(wèn)題
第 4 步	update userinfo set name='kafka' where id = 4;		更新用戶編號(hào)為4的記錄，更新成功
第 5 步	select * from userinfo where id=4;		查詢到用戶編號(hào)為4的記錄，查詢到數(shù)據(jù)，出現(xiàn)幻讀問(wèn)題

第一步，在窗口1中查詢id為4的記錄，查詢結(jié)果為空

mysql> set session transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from userinfo where id=4;
Empty set

第二步，在窗口2中查詢id為4的記錄，查詢結(jié)果為空。

插入一條id為4的記錄并提交事務(wù)

mysql> set session transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from userinfo where id=4;
Empty set
mysql> insert into userinfo(id,name,balance) values(4,'Redis',100);
Query OK, 1 row affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql>

第三步，在窗口1中查詢id為4的記錄，查詢結(jié)果為空，此時(shí)還沒(méi)有幻讀問(wèn)題

mysql> select * from userinfo where id=4;
Empty set

第四步，在窗口1中更新id為4的記錄，更新成功

mysql> update userinfo set name='kafka' where id = 4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

第五步，在窗口1中查詢id為4的記錄，查詢到更新后的數(shù)據(jù)，出現(xiàn)幻讀問(wèn)題

mysql> select * from userinfo where id=4;
+----+-------+---------+
| id | name  | balance |
+----+-------+---------+
|  4 | kafka | 100.00  |
+----+-------+---------+
1 row in set (0.00 sec)

幻讀場(chǎng)景3

在事務(wù)A中查詢id為X的記錄查詢不到，在事務(wù)B中插入一條id為X的記錄后，再到事務(wù)A中對(duì)表執(zhí)行當(dāng)前讀forupdate可以查詢到id為X的記錄。

執(zhí)行步驟	客戶端1（窗口1）	客戶端2（窗口2）	說(shuō)明
第 1 步	set session transaction isolationlevel repeatable read; start transaction; select * from userinfo;		設(shè)置事務(wù)隔離級(jí)別為可重復(fù)讀；開(kāi)啟事務(wù)；查詢用戶表所有數(shù)據(jù)，查詢結(jié)果為4條。
第 2 步		set session transaction isolation level repeatable read; start transaction; select * from userinfo where id=5; insert into userinfo(id,name,balance) values(5,'Pulsar',100); commit;	開(kāi)啟事務(wù)；添加用戶，用戶編號(hào)為 5；提交事務(wù)。
第 3 步	select * from userinfo for update;		執(zhí)行當(dāng)前讀for update，查詢出了id為5的記錄，出現(xiàn)幻讀問(wèn)題。

第一步，在窗口1中查詢用戶表的所有記錄，查詢結(jié)果為4條

mysql> set session transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from userinfo;
+----+--------+---------+
| id | name   | balance |
+----+--------+---------+
|  1 | Java   | 100.00  |
|  2 | MySQL  | 200.00  |
|  3 | Spring | 100.00  |
|  4 | kafka  | 100.00  |
+----+--------+---------+
4 rows in set (0.00 sec)

第二步，在窗口2中查詢id為5的記錄，查詢結(jié)果為空。

插入一條id為5的記錄并提交事務(wù)

mysql> set session transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from userinfo where id=5;
Empty set
mysql> insert into userinfo(id,name,balance) values(5,'Pulsar',100);
Query OK, 1 row affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

第三步，窗口1執(zhí)行當(dāng)前讀forupdate，查詢出了id為5的記錄，出現(xiàn)了幻讀問(wèn)題。

mysql> select * from userinfo for update;
+----+--------+---------+
| id | name   | balance |
+----+--------+---------+
|  1 | Java   | 100.00  |
|  2 | MySQL  | 200.00  |
|  3 | Spring | 100.00  |
|  4 | kafka  | 100.00  |
|  5 | Pulsar | 100.00  |
+----+--------+---------+
5 rows in set (0.00 sec)

結(jié)論

不可重復(fù)讀的重點(diǎn)在于update和delete,而幻讀著重強(qiáng)調(diào)insert操作,當(dāng)用戶第一次讀取時(shí)，存在結(jié)果集a,b,c，第二次讀取時(shí)結(jié)果集為a,b,c,d即多出來(lái)的一條數(shù)據(jù)d表示發(fā)送幻讀現(xiàn)象。

不可重復(fù)讀的現(xiàn)象是用戶第一次讀取，返回結(jié)果集a,而第二次讀取返回結(jié)果a’,即數(shù)據(jù)發(fā)生了變更.而a’數(shù)據(jù)確實(shí)由一個(gè)已經(jīng)committed的事務(wù)修改.不可重復(fù)讀現(xiàn)象需要隔離級(jí)別為RepeatableRead來(lái)規(guī)避.在InnoDB中使用MVCC機(jī)制實(shí)現(xiàn)。

通常來(lái)說(shuō),MVCC的多版本控制并不能保證在RR隔離級(jí)別下完全避免幻讀,但I(xiàn)nnoDB通過(guò)MVCC+Nextkeylock的方式來(lái)保證在RR隔離級(jí)別下避免部分場(chǎng)景下幻讀。

到此這篇關(guān)于MySQL的MVCC是否解決幻讀的文章就介紹到這了,更多相關(guān)MySQL的MVCC解決幻讀內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: