四個案例看懂 MySQL 事務隔離級別

farsun發表於2021-09-09

@[toc]
很多小夥伴對 MySQL 的隔離級別一直心存疑惑,其實這個問題一點都不難,關鍵看怎麼講!單純的看理論,絕對讓你暈頭轉向,但是,如果我們透過幾個實際的 SQL 來演示一些,大家就會發現這玩意原來這麼簡單!

今天松哥想透過幾個簡單的案例,來和大家演示一下 MySQL 中的事務隔離級別問題。

1. 理論

MySQL 中事務的隔離級別一共分為四種,分別如下:

  • 序列化(SERIALIZABLE)
  • 可重複讀(REPEATABLE READ)
  • 提交讀(READ COMMITTED)
  • 未提交讀(READ UNCOMMITTED)

四種不同的隔離級別含義分別如下:

  1. SERIALIZABLE

如果隔離級別為序列化,則使用者之間透過一個接一個順序地執行當前的事務,這種隔離級別提供了事務之間最大限度的隔離。

  1. REPEATABLE READ

在可重複讀在這一隔離級別上,事務不會被看成是一個序列。不過,當前正在執行事務的變化仍然不能被外部看到,也就是說,如果使用者在另外一個事務中執行同條 SELECT 語句數次,結果總是相同的。(因為正在執行的事務所產生的資料變化不能被外部看到)。

  1. READ COMMITTED

READ COMMITTED 隔離級別的安全性比 REPEATABLE READ 隔離級別的安全性要差。處於 READ COMMITTED 級別的事務可以看到其他事務對資料的修改。也就是說,在事務處理期間,如果其他事務修改了相應的表,那麼同一個事務的多個 SELECT 語句可能返回不同的結果。

  1. READ UNCOMMITTED

READ UNCOMMITTED 提供了事務之間最小限度的隔離。除了容易產生虛幻的讀操作和不能重複的讀操作外,處於這個隔離級的事務可以讀到其他事務還沒有提交的資料,如果這個事務使用其他事務不提交的變化作為計算的基礎,然後那些未提交的變化被它們的父事務撤銷,這就導致了大量的資料變化。

在 MySQL 資料庫種,預設的事務隔離級別是 REPEATABLE READ

2. SQL 實踐

接下來透過幾條簡單的 SQL 向讀者驗證上面的理論。

2.1 檢視隔離級別

透過如下 SQL 可以檢視資料庫例項預設的全域性隔離級別和當前 session 的隔離級別:

MySQL8 之前使用如下命令檢視 MySQL 隔離級別:

SELECT @@GLOBAL.tx_isolation, @@tx_isolation;

查詢結果如圖:

圖片描述

可以看到,預設的隔離級別為 REPEATABLE-READ,全域性隔離級別和當前會話隔離級別皆是如此。

MySQL8 開始,透過如下命令檢視 MySQL 預設隔離級別

SELECT @@GLOBAL.transaction_isolation, @@transaction_isolation;

就是關鍵字變了,其他都一樣。

透過如下命令可以修改隔離級別(建議開發者在修改時修改當前 session 隔離級別即可,不用修改全域性的隔離級別):

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

上面這條 SQL 表示將當前 session 的資料庫隔離級別設定為 READ UNCOMMITTED,設定成功後,再次查詢隔離級別,發現當前 session 的隔離級別已經變了,如圖1-2:

圖片描述

注意,如果只是修改了當前 session 的隔離級別,則換一個 session 之後,隔離級別又會恢復到預設的隔離級別,所以我們測試時,修改當前 session 的隔離級別即可。

2.2 READ UNCOMMITTED

2.2.1 準備測試資料

READ UNCOMMITTED 是最低隔離級別,這種隔離級別中存在髒讀、不可重複讀以及幻象讀問題,所以這裡我們先來看這個隔離級別,藉此大家可以搞懂這三個問題到底是怎麼回事。

下面分別予以介紹。

首先建立一個簡單的表,預設兩條資料,如下:

圖片描述

表的資料很簡單,有 javaboy 和 itboyhub 兩個使用者,兩個人的賬戶各有 1000 人民幣。現在模擬這兩個使用者之間的一個轉賬操作。

注意,如果讀者使用的是 Navicat 的話,不同的查詢視窗就對應了不同的 session,如果讀者使用了 SQLyog 的話,不同查詢視窗對應同一個 session,因此如果使用 SQLyog,需要讀者再開啟一個新的連線,在新的連線中進行查詢操作。

2.2.2 髒讀

一個事務讀到另外一個事務還沒有提交的資料,稱之為髒讀。具體操作如下:

  1. 首先開啟兩個SQL操作視窗,假設分別為 A 和 B,在 A 視窗中輸入如下幾條 SQL (輸入完成後不用執行):
START TRANSACTION;
UPDATE account set balance=balance+100 where name='javaboy';
UPDATE account set balance=balance-100 where name='itboyhub';
COMMIT;
  1. 在 B 視窗執行如下 SQL,修改預設的事務隔離級別為 READ UNCOMMITTED,如下:
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
  1. 接下來在 B 視窗中輸入如下 SQL,輸入完成後,首先執行第一行開啟事務(注意只需要執行一行即可):
START TRANSACTION;
SELECT * from account;
COMMIT;
  1. 接下來執行 A 視窗中的前兩條 SQL,即開啟事務,給 javaboy 這個賬戶新增 100 元。

  2. 進入到 B 視窗,執行 B 視窗的第二條查詢 SQL(SELECT * from user;),結果如下:

圖片描述

可以看到,A 視窗中的事務,雖然還未提交,但是 B 視窗中已經可以查詢到資料的相關變化了。

這就是髒讀問題。

2.2.3 不可重複讀

不可重複讀是指一個事務先後讀取同一條記錄,但兩次讀取的資料不同,稱之為不可重複讀。具體操作步驟如下(操作之前先將兩個賬戶的錢都恢復為1000):

  1. 首先開啟兩個查詢視窗 A 和 B ,並且將 B 的資料庫事務隔離級別設定為 READ UNCOMMITTED。具體 SQL 參考上文,這裡不贅述。

  2. 在 B 視窗中輸入如下 SQL,然後只執行前兩條 SQL 開啟事務並查詢 javaboy 的賬戶:

START TRANSACTION;
SELECT * from account where name='javaboy';
COMMIT;

前兩條 SQL 執行結果如下:

圖片描述

  1. 在 A 視窗中執行如下 SQL,給 javaboy 這個賬戶新增 100 塊錢,如下:
START TRANSACTION;
UPDATE account set balance=balance+100 where name='javaboy';
COMMIT;

4.再次回到 B 視窗,執行 B 視窗的第二條 SQL 檢視 javaboy 的賬戶,結果如下:

圖片描述

javaboy 的賬戶已經發生了變化,即前後兩次檢視 javaboy 賬戶,結果不一致,這就是不可重複讀

和髒讀的區別在於,髒讀是看到了其他事務未提交的資料,而不可重複讀是看到了其他事務已經提交的資料(由於當前 SQL 也是在事務中,因此有可能並不想看到其他事務已經提交的資料)。

2.2.4 幻象讀

幻象讀和不可重複讀非常像,看名字就是產生幻覺了。

我舉一個簡單例子。

在 A 視窗中輸入如下 SQL:

START TRANSACTION;
insert into account(name,balance) values('zhangsan',1000);
COMMIT;

然後在 B 視窗輸入如下 SQL:

START TRANSACTION;
SELECT * from account;
delete from account where name='zhangsan';
COMMIT;

我們執行步驟如下:

  1. 首先執行 B 視窗的前兩行,開啟一個事務,同時查詢資料庫中的資料,此時查詢到的資料只有 javaboy 和 itboyhub。
  2. 執行 A 視窗的前兩行,向資料庫中新增一個名為 zhangsan 的使用者,注意不用提交事務。
  3. 執行 B 視窗的第二行,由於髒讀問題,此時可以查詢到 zhangsan 這個使用者。
  4. 執行 B 視窗的第三行,去刪除 name 為 zhangsan 的記錄,這個時候刪除就會出問題,雖然在 B 視窗中可以查詢到 zhangsan,但是這條記錄還沒有提交,是因為髒讀的原因才看到了,所以是沒法刪除的。此時就產生了幻覺,明明有個 zhangsan,卻無法刪除。

這就是幻讀

看了上面的案例,大家應該明白了髒讀不可重複讀以及幻讀各自是什麼含義了。

2.3 READ COMMITTED

和 READ UNCOMMITTED 相比,READ COMMITTED 主要解決了髒讀的問題,對於不可重複讀和幻象讀則未解決。

將事務的隔離級別改為 READ COMMITTED 之後,重複上面關於髒讀案例的測試,發現已經不存在髒讀問題了;重複上面關於不可重複讀案例的測試,發現不可重複讀問題依然存在。

上面那個案例不適用於幻讀的測試,我們換一個幻讀的測試案例。

還是兩個視窗 A 和 B,將 B 視窗的隔離級別改為 READ COMMITTED

然後在 A 視窗輸入如下測試 SQL:

START TRANSACTION;
insert into account(name,balance) values('zhangsan',1000);
COMMIT;

在 B 視窗輸入如下測試 SQL:

START TRANSACTION;
SELECT * from account;
insert into account(name,balance) values('zhangsan',1000);
COMMIT;

測試方式如下:

  1. 首先執行 B 視窗的前兩行 SQL,開啟事務並查詢資料,此時查到的只有 javaboy 和 itboyhub 兩個使用者。
  2. 執行 A 視窗的前兩行 SQL,插入一條記錄,但是並不提交事務。
  3. 執行 B 視窗的第二行 SQL,由於現在已經沒有了髒讀問題,所以此時查不到 A 視窗中新增的資料。
  4. 執行 B 視窗的第三行 SQL,由於 name 欄位唯一,因此這裡會無法插入。此時就產生幻覺了,明明沒有 zhangsan 這個使用者,卻無法插入 zhangsan。

2.4 REPEATABLE READ

和 READ COMMITTED 相比,REPEATABLE READ 進一步解決了不可重複讀的問題,但是幻象讀則未解決。

REPEATABLE READ 中關於幻讀的測試和上一小節基本一致,不同的是第二步中執行完插入 SQL 後記得提交事務。

由於 REPEATABLE READ 已經解決了不可重複讀,因此第二步即使提交了事務,第三步也查不到已經提交的資料,第四步繼續插入就會出錯。

注意,REPEATABLE READ 也是 InnoDB 引擎的預設資料庫事務隔離級別

2.5 SERIALIZABLE

SERIALIZABLE 提供了事務之間最大限度的隔離,在這種隔離級別中,事務一個接一個順序的執行,不會發生髒讀、不可重複讀以及幻象讀問題,最安全。

如果設定當前事務隔離級別為 SERIALIZABLE,那麼此時開啟其他事務時,就會阻塞,必須等當前事務提交了,其他事務才能開啟成功,因此前面的髒讀、不可重複讀以及幻象讀問題這裡都不會發生。

3. 總結

總的來說,隔離級別和髒讀、不可重複讀以及幻象讀的對應關係如下:

隔離級別 髒讀 不可重複讀 幻象讀
READ UNCOMMITTED 允許 允許 允許
READ COMMITED 不允許 允許 允許
REPEATABLE READ 不允許 不允許 允許
SERIALIZABLE 不允許 不允許 不允許

效能關係如圖:

圖片描述

好了,這篇文章就和小夥伴們先說這麼多,大家不妨寫幾行 SQL 試一試。

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/4289/viewspace-2796617/,如需轉載,請註明出處,否則將追究法律責任。

相關文章