面試官:MySQL的幻讀是怎麼被解决的?

小林coding 2021-09-18 13:52:10 阅读数:158

mysql 解决

大家好,我是小林。

我之前寫過一篇數據庫事務的文章「 事務、事務隔離級別和MVCC」,這篇我說過什麼是幻讀。

在這裏插入圖片描述在這裏插入圖片描述

然後前幾天有比特讀者跟我說,我這個幻讀例子不是已經被「可重複讀」隔離級別解决了嗎?為什麼還要有 next-key 呢?

他有這個質疑,是因為他做了這個實驗。

實驗的數據庫錶 t_stu 如下,其中 id 為主鍵。

在這裏插入圖片描述在這裏插入圖片描述
然後在可重複讀隔離級別下,有兩個事務的執行順序如下:

 

在這裏插入圖片描述在這裏插入圖片描述

從這個實驗結果可以看到,即使事務 B 中途插入了一條記錄,事務 A 前後兩次查詢的結果集都是一樣的,並沒有出現所謂的幻讀現象。

讀者做的實驗之所以看不到幻讀現象,是因為在可重複讀隔離級別下,普通的查詢是快照讀,是不會看到別的事務插入的數據的

可重複讀隔離級是由 MVCC(多版本並發控制)實現的,實現的方式是啟動事務後,在執行第一個查詢語句後,會創建一個視圖,然後後續的查詢語句都用這個視圖,「快照讀」讀的就是這個視圖的數據,視圖你可以理解為版本數據,這樣就使得每次查詢的數據都是一樣的。

MySQL 裏除了普通查詢是快照度,其他都是當前讀,比如update、insert、delete,這些語句執行前都會查詢最新版本的數據,然後再做進一步的操作。

這很好理解,假設你要 update 一個記錄,另一個事務已經 delete 這條記錄並且 提交事務了,這樣不是會產生沖突嗎,所以 update 的時候肯定要知道最新的數據。

另外,select ... for update 這種查詢語句是當前讀,每次執行的時候都是讀取最新的數據。

因此,要討論「可重複讀」隔離級別的幻讀現象,是要建立在「當前讀」的情况下。

接下來,我們假設select ... for update當前讀是不會加鎖的(實際上是會加鎖的),在做一遍讀者那個實驗。

這時候,事務 B 插入的記錄,就會被事務 A 的第二條查詢語句查詢到(因為是當前讀),這樣就會出現前後兩次查詢的結果集合不一樣,這就出現了幻讀。

所以,Innodb 引擎為了解决「可重複讀」隔離級別使用「當前讀」而造成的幻讀問題,就引出了 next-key 鎖,就是記錄鎖和間隙鎖的組合。

  • 記錄鎖,鎖的是記錄本身;
  • 間隙鎖,鎖的就是兩個值之間的空隙,以防止其他事務在這個空隙間插入新的數據,從而避免幻讀現象。

比如,執行這條語句的時候,會鎖住,然後期間如果有其他事務在這個鎖住的範圍插入數據就會被阻塞。

next-key 鎖的加鎖規則其實挺複雜的,在一些場景下會退化成記錄鎖或間隙鎖,我之前也寫一篇加鎖規則,詳細可以看這篇「我做了一天的實驗!

需要注意的是,next-key lock 鎖的是索引,而不是數據本身,所以如果 update 語句的 where 條件沒有用到索引列,那麼就會全錶掃描,在一行行掃描的過程中,不僅給行加上了行鎖,還給行兩邊的空隙也加上了間隙鎖,相當於鎖住整個錶,然後直到事務結束才會釋放鎖。

所以在線上千萬不要執行沒有帶索引的 update 語句,不然會造成業務停滯,我有個讀者就因為幹了這個事情,然後被老板教育了一波,詳細可以看這篇「完蛋,公司被一條 update 語句幹趴了!


好了,這次就聊到這啦,學到的點個贊呀!

版权声明:本文为[小林coding]所创,转载请带上原文链接,感谢。 https://gsmany.com/2021/09/20210918135210363I.html