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

快捷導(dǎo)航

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

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

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

MySQL的MVCC是否解決幻讀

MySQL事務(wù)隔離級別

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

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

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

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

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

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

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

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

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

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

模擬幻讀問題

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

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

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

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

select @@global.transaction_isolation,@@transaction_isolation;

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

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

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

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

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

-- 創(chuàng)建數(shù)據(jù)庫
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
);
-- 插入測試數(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)

幻讀模擬

接下來會使用兩個窗口（兩個客戶端）來演示事務(wù)在可重復(fù)讀隔離級別下的幻讀的問題。

幻讀場景1

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

事務(wù)隔離級別	臟讀	不可重復(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

幻讀場景2

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

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

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)幻讀問題

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

幻讀場景3

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

執(zhí)行步驟	客戶端1（窗口1）	客戶端2（窗口2）	說明
第 1 步	set session transaction isolationlevel repeatable read; start transaction; select * from userinfo;		設(shè)置事務(wù)隔離級別為可重復(fù)讀；開啟事務(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;	開啟事務(wù)；添加用戶，用戶編號為 5；提交事務(wù)。
第 3 步	select * from userinfo for update;		執(zhí)行當(dāng)前讀for update，查詢出了id為5的記錄，出現(xià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)了幻讀問題。

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

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

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

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

您可能感興趣的文章: