技術文章 » mysql事務隔離級別有哪些

mysql事務隔離級別有哪些

2023-01-04 18:00:07

事務隔離級別有四個：1、Read Uncommitted（讀取未提交），允許讀取尚未提交的資料變更，可能造成髒讀、不可重複讀、幻讀。2、Read Committed（讀取已提交），允許讀取並行事務已經提交的資料，可以避免髒讀，但是可能造成不可重複、幻讀。3、Repeatable Read（可重複讀取），對同一欄位多次讀取的結果都是一致。4、Serializable（可序列化）。

本教學操作環境：windows7系統、mysql8版本、Dell G3電腦。

一、什麼是事務？

事務是邏輯上的一組操作，要麼全執行，要麼全不執行。

事務最經典栗子也經常被拿出來的栗子就是銀行轉賬了。比如小明要給小紅轉賬1000元，這個轉賬會涉及到兩個關鍵操作：將小明的餘額減1000元，將小紅的餘額減1000元。萬一這兩個操作之間突然出現錯誤，導致小明餘額減少但是小紅餘額沒有增加，這種情況是肯定不允許的。事務就是保證這兩個關鍵操作要麼都成功，要麼都不成功。

二、事務的特性（ACID）

  • **原子性：**事務最小的執行單位，不允許分割。事務的原子性確保動作要麼全部執行，要麼全部不執行。
  • **一致性：**執行事務的前後，資料保持一致。例如轉賬的業務中，無論事務是否成功，轉賬者和收款人的總額應該是不變的。
  • **隔離性：**並行存取資料庫時，一個使用者的事務不應該被其他事務所影響，各並行事務之間資料庫是獨立的。
  • **永續性：**一個事務被提交後，它對資料庫中資料的改變是持久的，即使資料庫發生故障也不應該對其有影響。

三、並行事務帶來的問題

在典型的應用程式中，多個事務並行執行，經常會操作相同的資料來完成各自的任務（多個使用者對同一資料進行操作）。並行雖然是必須的，但是可能會帶來以下的問題：

  • **髒讀（Dirty read）：**當一個事務正在存取資料並且對其進行了修改，但是還沒提交事務，這時另外一個事務也存取了這個資料，然後使用了這個資料，因為這個資料的修改還沒提交到資料庫，所以另外一個事務讀取的資料就是「髒資料」，這種行為就是「髒讀」，依據「髒資料」所做的操作可能是會出現問題的。
  • 修改丟失（Lost of modify）：是指一個事務讀取一個資料時，另外一個資料也存取了該資料，那麼在第一個事務修改了這個資料之後，第二個事務也修改了這個資料。這樣第一個事務內的修改結果就被丟失，這種情況就被稱為修改丟失。例如：事務1讀取表中資料A=20，事務2也讀取A=20，事務1修改A=A-1，事務2也修改A=A-1，最終結果都是19，但是事務1的修改記錄丟失了。
  • 不可重複讀（Unrepeatableread）：指在一個事務內多次讀取同一資料，在這個事務還沒結束時，另外一個事務也存取了這個資料並對這個資料進行了修改，那麼就可能造成第一個事務兩次讀取的資料不一致，這種情況就被稱為不可重複讀。
  • 幻讀（Phantom read）：幻讀與不可重複讀類似，幻讀是指一個事務讀取了幾行資料，這個事務還沒結束，接著另外一個事務插入了一些資料，在隨後的查詢中，第一個事務讀取到的資料就會比原本讀取到的多，就好像發生了幻覺一樣，所以稱為幻讀。

不可重複讀和幻讀區別：

不可重複讀的重點是修改，幻讀的重點是新增或者刪除。

栗子1（同樣的條件，你讀取過的資料，再次讀取的時候不一樣了）：事務1中的A先生讀取自己的工資是1000的操作還沒結束，事務2的B先生就修改了A先生的工資為2000，A先生再次讀取自己工資的時候就變成2000了，這就是不可重複讀。

栗子2（同樣的條件，第1次和第2次讀取出來的記錄條數不一樣）：假如某工資表中工資大於3000的有4人，事務1讀取了所有工資大於3000的人，總共查詢到4條記錄，這是事務2又查詢了一條工資大於3000的記錄，事務1再次讀取查詢到的記錄就是5條了，這就是幻讀。

四、事務隔離級別

SQL標準定義了四個隔離級別：

  • **讀取未提交（READ-UNCOMMITTED）：**最低的隔離級別，允許讀取尚未提交的資料變更，可能造成髒讀、不可重複讀、幻讀。
  • **讀取已提交（READ-COMMITTED）：**允許讀取並行事務已經提交的資料，可以避免髒讀，但是可能造成不可重複、幻讀。
  • **可重複讀（REPEATABLE-READ）：**對同一欄位多次讀取的結果都是一致的，除非本身事務修改，可以避免髒讀和不可重複讀，但是可能造成幻讀。
  • **可序列化（SERIALIZABLE）：**最高的隔離級別，完全服從ACID的隔離級別，所有的事務依次執行，可以避免髒讀、不可重複讀、幻讀。
隔離級別髒讀不可重複讀幻讀
讀取未提交√√√
讀取已提交×√√
可重複讀××√
可序列化×××

MySQL InnoDB儲存引擎預設的事務隔離級別是可重複讀（REPEATABLE-READ），可以通過命令select @@tx_isolation;語句來檢視，MySQL 8.0 該語句改為SELECT @@transaction_isolation;

mysql> SELECT @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
登入後複製

MySQL InnoDB儲存引擎的可重複讀並不能避免幻讀，需要應用使用加鎖讀來保證，這加鎖讀使用到的機制就是Next-Key Locks。

因為隔離級別越低，事務請求的鎖越少，所以大部分資料庫系統的隔離級別都是讀取已提交（READ-COMMITTED），InnoDB 儲存引擎預設使用 REPEATABLE-READ（可重讀） 並不會有任何效能損失。

InnoDB儲存引擎在分散式事務的情況下一般會用到可序列化隔離級別。

? 拓展一下(以下內容摘自《MySQL 技術內幕：InnoDB 儲存引擎(第 2 版)》7.7 章)：

InnoDB儲存引擎提供了對XA事務的支援，並通過XA事務來支援分散式事務的實現。分散式事務指的是允許多個獨立的事務資源參與到一個全域性的事務中。事務資源通常是關係型資料庫系統，但也可以是其他型別的資源。全域性事務要求在其中的所有參與的事務要麼都提交，要麼都回滾，這對事務的原有ACID要求又有了提高。另外，在使用分散式事務時，InnoDB 儲存引擎的事務隔離級別必須設定為 SERIALIZABLE。

四、實際情況演示

MySQL命令列的預設設定中事務都是自動提交的，即執行SQL語句就會馬上執行COMMIT操作。可以用命令START TRANSACTION開始一個事務。

我們可以通過下面命令設定事務隔離級別。

SET [SESSION|GLOBAL] TRANSACTION ISOLATION LEVEL [READ UNCOMMITTED|READ COMMITTED|REPEATABLE READ|SERIALIZABLE]
登入後複製

我們再來看一下我們在實際操作中使用到的一些並行控制語句：

  • START TRANSACTION | BEGIN ：顯示的開啟一個事務。
  • COMMIT：提交事務，使得對資料庫做的所有修改成為永久性。
  • ROLLBACK：回滾到結束使用者的事務，並復原正在進行的所有未提交的修改。

（髒讀）讀取未提交

（避免髒讀）讀取已提交

不可重複讀

還是剛才上面的讀已提交的圖，雖然避免了讀未提交，但是卻出現了，一個事務還沒有結束，就發生了 不可重複讀問題。

可重複讀

[外連圖片轉存失敗,源站可能有防盜鏈機制,建議將圖片儲存下來直接上傳(img-ysjbfC4b-1651149978452)(https://qtspace.cn/contentimg/81.jpg)]

幻讀

演示幻讀出現的情況

sql 指令碼 1 在第一次查詢工資為 500 的記錄時只有一條，sql 指令碼 2 插入了一條工資為 500 的記錄，提交之後；sql 指令碼 1 在同一個事務中再次使用當前讀查詢發現出現了兩條工資為 500 的記錄這種就是幻讀。

幻讀和不可重複讀有些相似之處 ，但是不可重複讀的重點是修改，幻讀的重點在於新增或者刪除。

解決幻讀的方法

  • 將事務隔離級別調整為 SERIALIZABLE。
  • 在可重複讀的事務級別下，給事務操作的這張表新增表鎖。
  • 在可重複讀的事務級別下，給事務操作的這張表新增 Next-Key Locks。

說明：Next-Key Locks 相當於 行鎖 + 間隙鎖

【相關推薦：】

以上就是mysql事務隔離級別有哪些的詳細內容，更多請關注TW511.COM其它相關文章！