MySQL在讀已提交和可重復(fù)讀這兩個(gè)不同事務(wù)隔離級(jí)別下幻讀的區(qū)別及說(shuō)明
1. 前 言
在正式開(kāi)始之前，先簡(jiǎn)單回顧一下并發(fā)事務(wù)存在的問(wèn)題以及事務(wù)的隔離級(jí)別等內(nèi)容。
1.1 并發(fā)事務(wù)存在的問(wèn)題
當(dāng)兩個(gè)或者兩個(gè)以上事務(wù)同時(shí)開(kāi)啟去處理同一個(gè)表的數(shù)據(jù)時(shí)，可能會(huì)存在以下的問(wèn)題：
- 丟失修改
- 臟讀
- 不可重復(fù)讀
- 幻讀
丟失修改
丟失修改是指當(dāng)兩個(gè)或多個(gè)事務(wù)更新同一行記錄，產(chǎn)生更新丟失的現(xiàn)象，事務(wù)回滾覆蓋和事務(wù)提交覆蓋都會(huì)導(dǎo)致這種現(xiàn)象的產(chǎn)生。
臟讀
一個(gè)事務(wù)能讀取到另一個(gè)事務(wù)已經(jīng)修改但還沒(méi)有提交的數(shù)據(jù)。
不可重復(fù)讀
在一個(gè)事務(wù)中多次執(zhí)行同一條查詢(xún)語(yǔ)句，讀取到的數(shù)據(jù)內(nèi)容前后不一致。
幻讀
在一個(gè)事務(wù)中多次執(zhí)行同一條查詢(xún)語(yǔ)句，讀取到的數(shù)據(jù)記錄在數(shù)量上前后不一致，可能多了幾條記錄也可能少了幾條記錄。
1.2 事務(wù)的隔離級(jí)別
為了解決并發(fā)事務(wù)存在的問(wèn)題，大佬們想到了一個(gè)手段，那就是對(duì)事務(wù)進(jìn)行隔離，最好是做到各個(gè)事務(wù)各干各的，互不干涉，但理想很豐滿(mǎn)，現(xiàn)實(shí)很骨感，哪可能一步到位呢。
為了應(yīng)對(duì)不同的需要，解決不同的問(wèn)題，于是決定將事務(wù)的隔離分成四個(gè)級(jí)別，分別是：
- 讀未提交
- 讀已提交
- 可重復(fù)讀
- 串行化
它們各自能解決的并發(fā)事務(wù)問(wèn)題如下表所示：
| 隔離級(jí)別 \ 事務(wù)問(wèn)題 | 事務(wù)回滾覆蓋 | 臟讀 | 不可重復(fù)讀 | 事務(wù)提交覆蓋 | 幻讀 |
|---|---|---|---|---|---|
| 讀未提交 | 能解決 | 不能解決 | 不能解決 | 不能解決 | 不能解決 |
| 讀已提交 | 能解決 | 能解決 | 不能解決 | 不能解決 | 不能解決 |
| 可重復(fù)讀 | 能解決 | 能解決 | 能解決 | 能解決 | 不能完全解決，可能發(fā)生 |
| 串行化 | 能解決 | 能解決 | 能解決 | 能解決 | 能解決 |
1.3 快照讀和當(dāng)前讀
快照讀
快照讀是基于 MVCC 和 undo log 來(lái)實(shí)現(xiàn)的，讀取數(shù)據(jù)的歷史版本，得到一個(gè) ReadView (事務(wù)視圖) ，不對(duì)數(shù)據(jù)加鎖，適用于簡(jiǎn)單 select 語(yǔ)句。
這里提一句，所謂 MVCC 并發(fā)版本控制，就是靠 ReadView (事務(wù)視圖) 來(lái)實(shí)現(xiàn)的，多個(gè) readView 組成 undo log（回滾日志）。
當(dāng)前讀
當(dāng)前讀是基于行鎖 + 間隙鎖來(lái)實(shí)現(xiàn)的，讀取數(shù)據(jù)的最新版本，并對(duì)數(shù)據(jù)進(jìn)行加鎖，適用于 insert，update，delete，select ... for update， select ... lock in share mode語(yǔ)句，以及加鎖了的 select 語(yǔ)句。
在更新數(shù)據(jù)時(shí)，都是先讀后寫(xiě)，而這個(gè)讀，就是指當(dāng)前讀，意味讀取數(shù)據(jù)時(shí)，讀到的是該條數(shù)據(jù)最新生成的 ReadView。
2. 不同事務(wù)隔離級(jí)別下幻讀的區(qū)別
在上面的表格中，我們能看到，在讀已提交這個(gè)隔離級(jí)別下，幻讀是不能被解決的，也就是說(shuō)會(huì)發(fā)生；而在可重復(fù)讀這個(gè)隔離級(jí)別下，幻讀則是沒(méi)有完全被解決，只是解決了部分，意味著仍然有可能發(fā)生。
那它們分別是怎么產(chǎn)生的？各自又有什么表現(xiàn)？有何區(qū)別？下面我們舉個(gè)例子來(lái)探究一下。
假設(shè)現(xiàn)在有兩個(gè)事務(wù)，分別是事務(wù) A 和 事務(wù) B ，同時(shí)有一張學(xué)生表 student（表中只有一條記錄，stu_name 為王大） ，我們用這兩個(gè)事務(wù)來(lái)操作這張表。
2.1 讀已提交下的幻讀
在讀已提交這種事務(wù)隔離級(jí)別下，兩個(gè)事務(wù)的操作順序如下：
| 事務(wù)A | 事務(wù)B |
|---|---|
| begin; | begin; |
| 開(kāi)始第一次查詢(xún)：select * from student where stu_id > 0; | insert into student(stu_name) values(‘李二’); |
| 開(kāi)始第二次查詢(xún)：select * from student where stu_id > 0; | commit; |
- 事務(wù) A 第一次查詢(xún)，得到的數(shù)據(jù)記錄是 stu_name 為王大這一條
- 事務(wù) B 中途往表里插入了一條 stu_name 為李二的記錄，并提交自身的事務(wù)
- 事務(wù) A 用第一次查詢(xún)的 SQL 語(yǔ)句進(jìn)行第二次查詢(xún)時(shí)，發(fā)現(xiàn)得到的數(shù)據(jù)記錄成了兩條，出現(xiàn)幻讀
表現(xiàn)
普通的查詢(xún)語(yǔ)句，能看到明顯幻讀現(xiàn)象。
原因剖析
事務(wù) A 中用的是普通的 select 語(yǔ)句，因此采用的是快照讀，但由于事務(wù)隔離級(jí)別為讀已提交，在讀已提交下，每次 select 操作，都會(huì)重新獲取最新版本的數(shù)據(jù)，也正是因?yàn)檫@個(gè)原因，導(dǎo)致事務(wù) A 中兩次查詢(xún)得到的結(jié)果在數(shù)量不一致，產(chǎn)生幻讀。
解決方式
將事務(wù)隔離級(jí)別升級(jí)為可重復(fù)讀
2.2 可重復(fù)讀下的幻讀
2.2.1 情況一，無(wú)幻讀
在可重復(fù)讀這種事務(wù)隔離級(jí)別下，兩個(gè)事務(wù)的操作順序如下：
| 事務(wù)A | 事務(wù)B |
|---|---|
| begin; | begin; |
| 開(kāi)始第一次查詢(xún)：select * from student where stu_id > 0; | insert into student(stu_name) values(‘李二’); |
| 開(kāi)始第二次查詢(xún)：select * from student where stu_id > 0; | commit; |
像上面這樣的事務(wù)操作，雖然在讀已提交事務(wù)隔離級(jí)別下會(huì)產(chǎn)生幻讀，但在可重復(fù)讀事務(wù)隔離級(jí)別下，卻不會(huì)產(chǎn)生幻讀。
原因：在可重復(fù)讀事務(wù)隔離級(jí)別下，針對(duì)普通的 select 語(yǔ)句，采用的是快照讀，只會(huì)在第一次查詢(xún)的時(shí)候獲取一次數(shù)據(jù)的版本，往后繼續(xù)做相同的 select 操作，不會(huì)重新獲取，會(huì)延用前面得到的數(shù)據(jù)版本。
細(xì)心的你有沒(méi)有發(fā)現(xiàn)，在可重復(fù)讀事務(wù)隔離級(jí)別下，對(duì)于快照讀（普通的 select 操作），這不就是使用 MVCC 解決幻讀問(wèn)題嗎？你沒(méi)看錯(cuò)，是這樣的。
2.2.2 情況二，有幻讀
在可重復(fù)讀這種事務(wù)隔離級(jí)別下，兩個(gè)事務(wù)的操作順序如下：
| 事務(wù)A | 事務(wù)B |
|---|---|
| begin; | begin; |
| 開(kāi)始第一次查詢(xún)：select * from student where stu_id > 0; | insert into student(stu_name) values(‘李二’); |
| 開(kāi)始第二次查詢(xún)：select * from student where stu_id > 0 for update; | commit; |
上面的事務(wù)操作，和前面不同的地方在于，第二次查詢(xún)加上了 for update ，也就是采用當(dāng)前讀。
表現(xiàn)
能看到明顯的幻讀現(xiàn)象
原因剖析
由于事務(wù) A 第二次查詢(xún)?cè)?SQL 語(yǔ)句末尾加上了 for update ，表示采用當(dāng)前讀的方式，獲取數(shù)據(jù)的最新版本，那么自然而然會(huì)把事務(wù) B 中途插入的數(shù)據(jù)給查出來(lái)，從而出現(xiàn)幻讀。
解決方式
用 next-key lock 解決，如果是走索引，會(huì)鎖住索引本身的行鎖；如果是范圍，就會(huì)成為一個(gè)行鎖+間隙鎖，導(dǎo)致范圍內(nèi)的無(wú)法插入；如果是無(wú)索引的，直接全表加上了間隙鎖，無(wú)法插入，阻塞。
具體的操作方式如下：
| 事務(wù)A | 事務(wù)B |
|---|---|
| begin; | begin; |
| 開(kāi)始第一次查詢(xún)：select * from student where stu_id > 0 for update; | insert into student(stu_name) values(‘李二’); |
| 開(kāi)始第二次查詢(xún)：select * from student where stu_id > 0 for update; | commit; |
事務(wù) A 的每次查詢(xún)都加上 for update，這樣就不會(huì)出現(xiàn)幻讀，原因是事務(wù) B 的 insert 操作會(huì)被阻塞，無(wú)法將數(shù)據(jù)插入到表中，從而避免幻讀的出現(xiàn)。
2.2.3 情況三，有幻讀
在可重復(fù)讀這種事務(wù)隔離級(jí)別下，兩個(gè)事務(wù)的操作順序如下：
| 事務(wù)A | 事務(wù)B |
|---|---|
| begin; | begin; |
| 開(kāi)始第一次查詢(xún)：select * from student where stu_id > 0; | insert into student(stu_name) values(‘李二’); |
| update student set stu_class = ‘03’ where stu_id is not null; | commit; |
| 開(kāi)始第二次查詢(xún)：select * from student where stu_id > 0; |
在上面的事務(wù)操作下，事務(wù) B 能將數(shù)據(jù)記錄正常插入到表中，而事務(wù) A 做了一次 update 操作，從上面的介紹我們可以知道，update 操作是當(dāng)前讀，會(huì)獲取到數(shù)據(jù)的最新版本，自然也能拿到事務(wù) B 的提交記錄。
表現(xiàn)
能明顯看到幻讀現(xiàn)象
原因剖析
事務(wù) A 的 update 操作采用的是當(dāng)前讀，會(huì)獲取數(shù)據(jù)的最新版本，將事務(wù) B 提交的結(jié)果讀取出來(lái)，后面事務(wù) A 再做普通的 select 操作，采用快照讀，由于延用 update 操作時(shí)得到的數(shù)據(jù)歷史版本，因而產(chǎn)生幻讀。
解決方式
同 2.2.2 中的解決方式一樣，具體操作如下：
| 事務(wù)A | 事務(wù)B |
|---|---|
| begin; | begin; |
| 開(kāi)始第一次查詢(xún)：select * from student where stu_id > 0 for update; | insert into student(stu_name) values(‘李二’); |
| update student set stu_class = ‘03’ where stu_id is not null; | commit; |
| 開(kāi)始第二次查詢(xún)：select * from student where stu_id > 0 for update; |
最簡(jiǎn)單的解決方式，就是再將事務(wù)隔離級(jí)別升級(jí)，改為串行化，畢竟串行化本身就解決所有事務(wù)問(wèn)題，當(dāng)然，這會(huì)犧牲效率。
總結(jié)
并發(fā)事務(wù)中，對(duì)于普通的 select 語(yǔ)句，在讀已提交下，能明顯看到幻讀的現(xiàn)象，而在可重復(fù)讀下，看不到幻讀現(xiàn)象。
并發(fā)事務(wù)中，對(duì)于事務(wù)執(zhí)行語(yǔ)句里含有當(dāng)前讀的情況，得具體問(wèn)題具體分析，可能可以看到幻讀，也可能由于加了 for update ，看不到幻讀。
以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。
相關(guān)文章
mysql導(dǎo)入導(dǎo)出數(shù)據(jù)的示例詳解
本文主要介紹了MySQL 導(dǎo)出和導(dǎo)入數(shù)據(jù)的幾種實(shí)現(xiàn)方式,文中通過(guò)示例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2024-05-05
sql server自動(dòng)編號(hào)的三種方法
自增列是最簡(jiǎn)單和常見(jiàn)的方法,適用于大多數(shù)情況,本文介紹了SQL Server中三種常見(jiàn)的自動(dòng)編號(hào)方法：自增列、序列和觸發(fā)器,具有一定的參考價(jià)值,感興趣的可以了解一下2023-10-10
在高并發(fā)數(shù)據(jù)庫(kù)系統(tǒng)中，需要保證事務(wù)與事務(wù)之間的隔離性，還有事務(wù)本身的一致性。所以需要解決幻讀問(wèn)題，本文就來(lái)介紹一下，感興趣的可以了解一下2021-08-08
mysql如何根據(jù).frm和.ibd文件恢復(fù)數(shù)據(jù)表
這篇文章主要介紹了mysql根據(jù).frm和.ibd文件恢復(fù)數(shù)據(jù)表的操作方法，本文給大家介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值,需要的朋友可以參考下2023-07-07
MySQL group_concat函數(shù)使用方法詳解
GROUP_CONCAT函數(shù)用于將GROUP BY產(chǎn)生的同一個(gè)分組中的值連接起來(lái)，返回一個(gè)字符串結(jié)果，接下來(lái)就給大家簡(jiǎn)單的介紹一下MySQL group_concat函數(shù)的使用方法,需要的朋友可以參考下2023-07-07
Mysql Error Code : 1436 Thread stack overrun
I meet with the error while calling stored procedures from the MySql in my Mac system server. It similar as the description below2011-07-07
Mysql事務(wù)并發(fā)臟讀+不可重復(fù)讀+幻讀詳解
MySQL 8.0數(shù)據(jù)字典的初始化與啟動(dòng)流程
