【MySQL實戰45講基礎篇】(task3)事務隔離

學習總結

（1）學習了Mysql的事務隔離級別的現象和實現，根據實現原理分析了長事務存在的風險，以及如何用正確的方式避免長事務。

（2）重申幻讀定義：事務a 開啟，查詢符合條件的數據，發現有10條，準備將這10條記錄修改，此時事務b開啟，插入了一條符合事務a查詢條件的記錄。提交事務，回到事務a， a也提交事務，當再次查詢到時候，發現修改了11條…感覺發生了幻覺一樣，此為幻讀。

文章目錄

學習總結

一、隔離性與隔離級別

1.1 不同事務隔離級別的區別

1.2 “讀提交”和“可重復讀”

1.3 隔離級別的配置方法

1.4 可重復讀的場景

二、事務隔離的實現

2.1 事務隔離的實現

2.2 三個為什么

（1）回滾日志什么時候刪除？

（2）什么時候不需要了？

（3）為什么盡量不要使用長事務

三、事務的啟動方式

3.1 啟動方式

3.2 建議使用法一

3.3 如何查詢事務

四、作業

五、課后復習（常見面試題）

1.事務的概念是什么?

2.Mysql的事務隔離級別讀未提交, 讀已提交, 可重復讀, 串行各是什么意思?

3.讀已提交, 可重復讀是怎么通過視圖構建實現的?

4.可重復讀的使用場景舉例?

5.事務隔離是怎么通過read-view(讀視圖)實現的?

6.并發版本控制(MCVV)的概念是什么, 是怎么實現的?

7.使用長事務的弊病? 為什么使用常事務可能拖垮整個庫?

8.事務的啟動方式有哪幾種?

9.commit work and chain的語法是做什么用的?

10.怎么查詢各個表中的長事務?

11.如何避免長事務的出現?

Reference

事務就是要保證一組數據庫操作，要么全部成功，要么全部失敗。

在 MySQL 中，事務支持是在引擎層實現的。你現在知道，MySQL 是一個支持多引擎的系統，但并不是所有的引擎都支持事務。比如 MySQL 原生的 MyISAM 引擎就不支持事務，這也是 MyISAM 被 InnoDB 取代的重要原因之一。

一、隔離性與隔離級別

ACID（Atomicity、Consistency、Isolation、Durability，即原子性、一致性、隔離性、持久性）。當數據庫上有多個事務同時執行的時候，就可能出現臟讀（dirty read）、不可重復讀（non-repeatable read）、幻讀（phantom read）的問題，為了解決這些問題，就有了“隔離級別”的概念。

1.1 不同事務隔離級別的區別

隔離得越嚴實，效率就會越低。所以很多時候，我們都要在二者之間尋找一個平衡點。SQL 標準的事務隔離級別包括：讀未提交（read uncommitted）、讀提交（read committed）、可重復讀（repeatable read）和串行化（serializable ）：

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

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

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

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

1.2 “讀提交”和“可重復讀”

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

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

1

2

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

若隔離級別是“讀未提交”， 則 V1 的值就是 2。這時候事務 B 雖然還沒有提交，但是結果已經被 A 看到了。因此，V2、V3 也都是 2。

若隔離級別是“讀提交”，則 V1 是 1，V2 的值是 2。事務 B 的更新在提交后才能被 A 看到。所以， V3 的值也是 2。

若隔離級別是“可重復讀”，則 V1、V2 是 1，V3 是 2。之所以 V2 還是 1，遵循的就是這個要求：事務在執行期間看到的數據前后必須是一致的。

若隔離級別是“串行化”，則在事務 B 執行“將 1 改成 2”的時候，會被鎖住。直到事務 A 提交后，事務 B 才可以繼續執行。所以從 A 的角度看， V1、V2 值是 1，V3 的值是 2。

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

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

在“讀提交”隔離級別下，這個視圖是在每個 SQL 語句開始執行的時候創建的。

“讀未提交”隔離級別下

直接返回記錄上的最新值，沒有視圖概念

；

而“串行化”隔離級別下直接用加鎖的方式來避免并行訪問。

1.3 隔離級別的配置方法

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

配置的方式是，將啟動參數 transaction-isolation 的值設置成 READ-COMMITTED。可以用 show variables 來查看當前的值。

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

1

2

3

4

5

6

7

8

9

10

11

每種隔離級別都有自己的使用場景，要根據自己的業務情況來定。

1.4 可重復讀的場景

【數據校對邏輯的案例】

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

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

二、事務隔離的實現

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

在 MySQL 中，實際上

每條記錄在更新的時候都會同時記錄一條回滾操作

。記錄上的最新值，通過回滾操作，都可以得到前一個狀態的值。

2.1 事務隔離的實現

假設一個值從 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 對應的事務是不會沖突的。

2.2 三個為什么

（1）回滾日志什么時候刪除？

在不需要的時候才刪除。也就是說，系統會判斷，當沒有事務再需要用到這些回滾日志時，回滾日志會被刪除。

（2）什么時候不需要了？

當系統里沒有比這個回滾日志更早的 read-view 的時候。

（3）為什么盡量不要使用長事務

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

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

除了對回滾段的影響，長事務還占用鎖資源，也可能拖垮整個庫。

三、事務的啟動方式

3.1 啟動方式

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

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

3.2 建議使用法一

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

使用 set autocommit=1, 通過顯式語句的方式來啟動事務

。

但是有的開發同學會糾結“多一次交互”的問題。對于一個需要頻繁使用事務的業務，

第二種方式每個事務在開始時都不需要主動執行一次 “begin”，減少了語句的交互次數。如果有這個顧慮，建議使用 commit work and chain 語法。

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

3.3 如何查詢事務

可以在 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。

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

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

其次，從數據庫端來看：

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

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

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

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

五、課后復習（常見面試題）

1.事務的概念是什么?

事務是對數據庫中數據操作的保證數據邏輯一致的最小操作單位。

2.mysql的事務隔離級別讀未提交, 讀已提交, 可重復讀, 串行各是什么意思?

讀未提交：一個事務讀取到了其他事務未提交的操作。

讀已提交：一個事務讀取到了其他事務已經提交的操作。

可重復讀：一個事務從它開始到結束整個生命周期中，所能讀取到的數據內容和它啟動的時候所能讀到的數據內容是相同的。不會出現在事務運行的整個過程中，不同的時間點讀取到的數據不一樣的情況。

串行化：所有的事務都進行排隊執行，事務之間不存才并發的情況。讀有讀鎖，寫有寫鎖。讀、讀之間不影響，讀、寫和寫、寫之間互相排斥，當遇到排斥的情況后，后發生的事務需要等待先發生的事務執行完成后才可以執行。

3.讀已提交, 可重復讀是怎么通過視圖構建實現的?

讀已提交：會在事務中的每一個SQL語句執行的時候都為對應的SQL創建一個一致性視圖。此時這個SQL能讀取到已經提交的事務對數據的操作。

可重復讀：會在事務啟動的時候，為整個事務創建一個一致性視圖，這個視圖會貫穿到這個事務執行結束。在整個事務執行過程中，都使用這個視圖中的數據作為一致性讀的依據。

4.可重復讀的使用場景舉例?

對賬的時候應該很有用

庫管盤貨

5.事務隔離是怎么通過read-view(讀視圖)實現的?

每一行數有多個版本，當我們要去讀取數據的時候，要判斷這個數據的版本號，對當前事務而言，是否可見，如果不可見，則要根據undolog計算得到上一個版本。如果上一個版本也不符合要求，則要找到再上一個版本，

直到找到對應正確的數據版本。

6.并發版本控制(MCVV)的概念是什么, 是怎么實現的?

待補充。

https://database.51cto.com/art/202101/641019.htm

https://blog.csdn.net/SnailMann/article/details/94724197

7.使用長事務的弊病? 為什么使用常事務可能拖垮整個庫?

長事務導致表空間持續增長，即便是事務提交或者回滾后，回滾表空間被是否后，表空間大小仍然不會被縮小。

長事務的存在導致鎖發生沖突或等待的幾率大大增加。

如果某個應用有發生鎖等待后嘗試重新建立連接的機制，那么在發生鎖等待或沖突的時候，應用就會不斷地發起新的連接，導致MySQL的連接數被占用爆滿。MySQL不能在提供連接服務，就掛掉了。

8.事務的啟動方式有哪幾種?

begin;–一致性讀的視圖不會馬上創建，而是在執行begin后面的第一個操作innodb表的SQL語句時生成。這個SQL可以是select,update,delete,insert。事務ID也是此時被分配的

start transaction;–和begin的功能效果一樣。

start transaciton with consistent snapshot;–該語句執行后，會馬上創建一致性讀的視圖。這個是它和begin的區別。事務ID也是此時被分配的。

9.commit work and chain的語法是做什么用的?

提交上一個事務，并且再開啟一個新的事務。它的功能等效于：commit + begin。

10.怎么查詢各個表中的長事務?

select * from information_schema.innodb_trx;

這個表中記錄了所有正在運行的事務信息，里面有事務的開始時間。可以從這里看出哪些事務運行的時間比較長。

11.如何避免長事務的出現?

從數據庫方面：

a.設置autocommit=1，不要設置為0。

b.寫腳本監控information_schemal.innodb_trx表中數據內容，發現長事務，kill掉它。

c.配置SQL語句所能執行的最大運行時間，如果查過最大運行時間后，中斷這個運行事情長的SQL語句。

d.設置回滾表空單獨存放，便于回收表空間。

從業務代碼方面：

a.確認是否使用了autocommit=0的配置，如果有關閉它，然后再業務代碼中手動的使用begin;commit來操作。

b.檢查業務邏輯代碼，能拆分為小事務的不要用大事務。

c.檢查代碼，把沒有必要的select語句被事務包裹的情況去掉。

Reference

（1）《MySQL實戰45講》林曉斌

（2）https://www.cnblogs.com/luoahong/p/10396719.html

MySQL 數據庫

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。