MySQL的隔離性與隔離級別

本篇內容介紹了“MySQL的隔離性與隔離級別”的有關知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓小編帶領大家學習一下如何處理這些情況吧！希望大家仔細閱讀，能夠學有所成！

隔離性與隔離級別

提到事務，你肯定會想到ACID（Atomicity、Consistency、Isolation、Durability，即原子性、一致性、隔離性、持久性），今天我們就來說說其中Isolation，也就是隔離性。

1. 原子性：一個事務（transaction）中的所有操作，要么全部完成，要么全部不完成，不會結束在中間某個環節。事務在執行過程中發生錯誤，會被回滾（Rollback）到事務開始前的狀態，就像這個事務從來沒有執行過一樣。

2. 一致性：在事務開始之前和事務結束以后，。這表示寫入的資料必須完數據庫的完整性沒有被破壞全符合所有的預設規則，這包含資料的精確度、串聯性以及后續數據庫可以自發性地完成預定的工作。

3. 隔離性：數據庫允許多個并發事務同時對其數據進行讀寫和修改的能力，隔離性可以防止多個事務并發執行時由于交叉執行而導致數據的不一致。事務隔離分為不同級別，包括讀未提交（Read uncommitted）、讀提交（read committed）、可重復讀（repeatable read）和串行化（Serializable）。

4. 持久性：事務處理結束后，對數據的的，即便系統修改就是永久故障也不會丟失。

當數據庫上有多個事務同時執行的時候，就可能出現臟讀（dirty read）、不可重復讀（non-repeatable read）、幻讀（phantom read）的問題，為了解決這些問題，就有了隔離級別的概念。

在談隔離級別之前，你首先要知道，你隔離得越嚴實，效率就會越低。因此很多時候，我們都要在二者之間尋找一個平衡點。SQL標準的事務隔離級別由低到高包括：

• 讀未提交：是指一個事務還沒提交時，它做的變更就能被別的事務看到。

• 讀提交：是指一個事務提交之后，它做的變更才會被其他事務看到。

• 可重復讀(默認級別)：是指一個事務執行過程中看到的數據，總是跟這個事務在啟動時看到的數據是一致的。當然在可重復讀隔離級別下，未提交變更對其他事務也是不可見的。

• 串行化：顧名思義是對于同一行記錄，寫會加寫鎖，讀會加讀鎖。對應一個記錄會加讀寫鎖，出現沖突的時候，后訪問的事務必須等前一個事務執行完成才能繼續執行。

用大白話說下上述的含義：

讀未提交：別人改數據的事務尚未提交，我在我的事務中也能讀到。
讀已提交(Oracle默認)：別人改數據的事務已經提交，我在我的事務中才能讀到。
可重復讀(MySQL默認)：別人改數據的事務已經提交，我在我的事務中也不去讀，以此保證重復讀一致性。
串行：我的事務尚未提交，別人就別想改數據。
這4種隔離級別，并行性能依次降低，安全性依次提高。

其中讀提交和可重復讀比較難理解，所以我用一個例子說明這幾種隔離級別。假設數據表T中只有一列，其中一行的值為1，下面是按照時間順序執行兩個事務的行為。

mysql> create table T(c int) engine=InnoDB;insert into T(c) values(1);

我們來看看在不同的隔離級別下，事務A會有哪些不同的返回結果，也就是圖里面V1、V2、V3的返回值分別是什么。

在實現上，數據庫里面會創建一個視圖，訪問的時候以視圖的邏輯結果為準。

1. 讀未提交隔離級別下直接返回記錄上的最新值，沒有視圖概念

2. 在讀提交隔離級別下，這個視圖(read-view)是在每個SQL語句開始執行的時候創建的.

3. 在可重復讀隔離級別下，這個視圖是在事務啟動時創建的，整個事務存在期間都用這個視圖。

4. 在串行化隔離級別下直接用加鎖的方式來避免并行訪問。

我們可以看到在不同的隔離級別下，數據庫行為是有所不同的。Oracle數據庫的默認隔離級別其實就是讀提交，因此對于一些從Oracle遷移到MySQL(默認可重復讀)的應用，為保證數據庫隔離級別的一致，你一定要記得將MySQL的隔離級別設置為讀提交。

mysql> show variables like 'transaction_isolation';+-----------------------+----------------+| Variable_name | Value |+-----------------------+----------------+| transaction_isolation | READ-COMMITTED |+-----------------------+----------------+

總結來說，存在即合理，哪個隔離級別都有它自己的使用場景，你要根據自己的業務情況來定。我想你可能會問那什么時候需要可重復讀的場景呢？我們來看一個數據校對邏輯的案例。

假設你在管理一個 個人銀行賬戶表。一個表存了每個月月底的余額，一個表存了賬單明細。這時候你要做數據校對，也就是判斷上個月的余額和當前余額的差額，是否與本月的賬單明細一致。你一定希望在校對過程中，即使有用戶發生了一筆新的交易，也不影響你的校對結果。

這時候使用可重復讀隔離級別就很方便。事務啟動時的視圖可以認為是靜態的，不受其他事務更新的影響。

事務隔離的實現

理解了事務的隔離級別，我們再來看看事務隔離具體是怎么實現的。這里我們展開說明可重復讀。

在MySQL中，實際上每條記錄在更新的時候都會同時記錄一條回滾操作。記錄上的最新值，通過回滾操作，都可以得到前一個狀態的值。

假設一個值從1被按順序改成了2、3、4，在回滾日志里面就會有類似下面的記錄。
當前值是4，但是在查詢這條記錄的時候，不同時刻啟動的事務會有不同的read-view。如圖中看到的，在視圖A、B、C里面，這一個記錄的值分別是1、2、4，同一條記錄在系統中可以存在多個版本，就是數據庫的多版本并發控制（MVCC）。對于read-view A，要得到1，就必須將當前值依次執行圖中所有的回滾操作得到。

同時你會發現，即使現在有另外一個事務正在將4改成5，這個事務跟read-view A、B、C對應的事務是不會沖突的。

你一定會問，回滾日志總不能一直保留吧，什么時候刪除呢？答案是，在不需要的時候才刪除。也就是說，系統會判斷，當沒有事務再需要用到這些回滾日志時，回滾日志會被刪除。

什么時候才不需要了呢？就是當系統里沒有比這個回滾日志更早的read-view的時候。

基于上面的說明，我們來討論一下為什么建議你盡量不要使用長事務。

長事務意味著系統里面會存在很老的事務視圖。由于這些事務隨時可能訪問數據庫里面的任何數據，所以這個事務提交之前，數據庫里面它可能用到的回滾記錄都必須保留，這就會導致大量占用存儲空間。

在MySQL 5.5及以前的版本，回滾日志是跟數據字典一起放在ibdata文件里的，即使長事務最終提交，回滾段被清理，文件也不會變小。我見過數據只有20GB，而回滾段有200GB的庫。最終只好為了清理回滾段，重建整個庫。

除了對回滾段的影響，長事務還占用鎖資源，也可能拖垮整個庫，這個我們會在后面講鎖的時候展開。

事務的啟動方式

如前面所述，長事務有這些潛在風險，我當然是建議你盡量避免。其實很多時候業務開發同學并不是有意使用長事務，通常是由于誤用所致。MySQL的事務啟動方式有以下幾種：

• 顯式啟動事務語句， begin 或 start transaction。配套的提交語句是commit，回滾語句是rollback。

• set autocommit=0，這個命令會將這個線程的自動提交關掉。意味著如果你只執行一個select語句，這個事務就啟動了，而且并不會自動提交。這個事務持續存在直到你主動執行commit 或 rollback 語句，或者斷開連接。

有些客戶端連接框架會默認連接成功后先執行一個set autocommit=0的命令。這就導致接下來的查詢都在事務中，如果是長連接，就導致了意外的長事務。

因此，我會建議你總是使用set autocommit=1, 通過顯式語句的方式來啟動事務。

但是有的開發同學會糾結多一次交互的問題。對于一個需要頻繁使用事務的業務，第二種方式每個事務在開始時都不需要主動執行一次 begin，減少了語句的交互次數。如果你也有這個顧慮，我建議你使用commit work and chain語法。

在autocommit為1的情況下，用begin顯式啟動的事務，如果執行commit則提交事務。如果執行 commit work and chain則是提交事務并自動啟動下一個事務，這樣也省去了再次執行begin語句的開銷。同時帶來的好處是從程序開發的角度明確地知道每個語句是否處于事務中。

你可以在information_schema庫的innodb_trx這個表中查詢長事務，比如下面這個語句，用于查找持續時間超過60s的事務。

select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started)) > 60

如何避免長事務對業務的影響

你現在知道了系統里面應該避免長事務，如果你是業務開發負責人同時也是數據庫負責人，你會有什么方案來避免出現或者處理這種情況呢？

這個問題，我們可以從應用開發端和數據庫端來看。

首先，從應用開發端來看：

1. 確認是否使用了set autocommit=0。這個確認工作可以在測試環境中開展，把MySQL的general_log開起來，然后隨便跑一個業務邏輯，通過general_log的日志來確認。一般框架如果會設置這個值，也就會提供參數來控制行為，你的目標就是把它改成1。

2. 確認是否有不必要的只讀事務。有些框架會習慣不管什么語句先用begin/commit框起來。我見過有些是業務并沒有這個需要，但是也把好幾個select語句放到了事務中。這種只讀事務可以去掉。

3. 業務連接數據庫的時候，根據業務本身的預估，通過SET MAX_EXECUTION_TIME命令，來控制每個語句執行的最長時間，避免單個語句意外執行太長時間。（為什么會意外？在后續的文章中會提到這類案例）

其次，從數據庫端來看：

1. 監控 information_schema.Innodb_trx表，設置長事務閾值，超過就報警/或者kill；

2. Percona的pt-kill這個工具不錯，推薦使用；

3. 在業務功能測試階段要求輸出所有的general_log，分析日志行為提前發現問題；

4. 如果使用的是MySQL 5.6或者更新版本，把innodb_undo_tablespaces設置成2（或更大的值）。如果真的出現大事務導致回滾段過大，這樣設置后清理起來更方便。

“MySQL的隔離性與隔離級別”的內容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業相關的知識可以關注億速云網站，小編將為大家輸出更多高質量的實用文章！