事務(wù)就是要保證一組數(shù)據(jù)庫操作，要么全部成功，要么全部失敗。在 MySQL 中，事務(wù)支持是在引擎層實現(xiàn)的。MySQL 是一個支持多引擎的系統(tǒng)，但并不是所有的引擎都支持事務(wù)。比如 MySQL 原生的 MyISAM 引擎就不支持事務(wù)，這也是 MyISAM 被 InnoDB 取代的重要原因之一

隔離性與隔離級別

ACID

• Atomicity，原子性
• Consistency，一致性
• Isolation，隔離性
• Durability，持久性

當數(shù)據(jù)庫上有多個事務(wù)同時執(zhí)行的時候，就可能出現(xiàn)臟讀（dirty read）、不可重復讀（non-repeatable read）、幻讀（phantom read）的問題，為了解決這些問題，就有了“隔離級別”的概念

SQL 標準的事務(wù)隔離級別

• 讀未提交（read uncommitted）:一個事務(wù)還沒提交時，它做的變更就能被別的事務(wù)看到
• 讀提交（read committed）:一個事務(wù)提交之后，它做的變更才會被其他事務(wù)看到，新聞線上數(shù)據(jù)庫都是READ-COMMITTED
• 可重復讀（repeatable read）：一個事務(wù)執(zhí)行過程中看到的數(shù)據(jù)，總是跟這個事務(wù)在啟動時看到的數(shù)據(jù)是一致的。當然在可重復讀隔離級別下，未提交變更對其他事務(wù)也是不可見的。MySQL默認是REPEATABLE-READ
• 串行化（serializable ）：對于同一行記錄，“寫”會加“寫鎖”，“讀”會加“讀鎖”。當出現(xiàn)讀寫鎖沖突的時候，后訪問的事務(wù)必須等前一個事務(wù)執(zhí)行完成，才能繼續(xù)執(zhí)行

隔離的實現(xiàn)

事務(wù)啟動的時候會創(chuàng)建一個視圖，訪問的時候以視圖的邏輯結(jié)果為準

• 在“可重復讀”隔離級別下，這個視圖是在事務(wù)啟動時創(chuàng)建的，整個事務(wù)存在期間都用這個視圖
• 在“讀提交”隔離級別下，這個視圖是在每個 SQL 語句開始執(zhí)行的時候創(chuàng)建的
• 在“讀未提交”隔離級別下直接返回記錄上的最新值，沒有視圖概念
• 而“串行化”隔離級別下直接用加鎖的方式來避免并行訪問

隔離示例

假設(shè)數(shù)據(jù)表 T 中只有一列，其中一行的值為 1，下面是按照時間順序執(zhí)行兩個事務(wù)的行為，在不同的隔離級別下，事務(wù) A 會有哪些不同的返回結(jié)果，也就是圖里面 V1、V2、V3 的返回值分別是多少：

• 若隔離級別是“讀未提交”， 則 V1 的值就是 2。這時候事務(wù) B 雖然還沒有提交，但是結(jié)果已經(jīng)被 A 看到了。因此，V2、V3 也都是 2
• 若隔離級別是“讀提交”，則 V1 是 1，V2 的值是 2。事務(wù) B 的更新在提交后才能被 A 看到。所以， V3 的值也是 2
• 若隔離級別是“可重復讀”，則 V1、V2 是 1，V3 是 2。之所以 V2 還是 1，遵循的就是這個要求：事務(wù)在執(zhí)行期間看到的數(shù)據(jù)前后必須是一致的
• 若隔離級別是“串行化”，則在事務(wù) B 執(zhí)行“將 1 改成 2”的時候，會被鎖住。直到事務(wù) A 提交后，事務(wù) B 才可以繼續(xù)執(zhí)行。所以從 A 的角度看， V1、V2 值是 1，V3 的值是 2

隔離的應(yīng)用

假設(shè)你在管理一個個人銀行賬戶表
一個表存了每個月月底的余額，一個表存了賬單明細
候你要做數(shù)據(jù)校對，也就是判斷上個月的余額和當前余額的差額，是否與本月的賬單明細一致
你一定希望在校對過程中，即使有用戶發(fā)生了一筆新的交易，也不影響你的校對結(jié)果, 這時候使用“可重復讀”隔離級別就很方便
事務(wù)啟動時的視圖可以認為是靜態(tài)的，不受其他事務(wù)更新的影響

事務(wù)的啟動

• 事務(wù)的啟動方式

  • 隱式啟動事務(wù)語句，set autocommit = 1, 在 autocommit 為 1 的情況下，用 begin 顯式啟動的事務(wù)，如果執(zhí)行 commit 則提交事務(wù)。如果執(zhí)行 commit work and chain，則是提交事務(wù)并自動啟動下一個事務(wù)，這樣也省去了再次執(zhí)行 begin 語句的開銷。同時帶來的好處是從程序開發(fā)的角度明確地知道每個語句是否處于事務(wù)中
  • 顯式啟動事務(wù)語句， begin 或 start transaction。配套的提交語句是 commit，回滾語句是 rollback。begin/start transaction 命令并不是一個事務(wù)的起點，在執(zhí)行到它們之后的第一個操作 InnoDB 表的語句，事務(wù)才真正啟動。
  • 如果你想要馬上啟動一個事務(wù)，可以使用 start transaction with consistent snapshot 這個命令。上邊這種啟動方式，一致性視圖是在第執(zhí)行第一個快照讀語句時創(chuàng)建的； 這種啟動方式，一致性視圖是在執(zhí)行 start transaction with consistent snapshot 時創(chuàng)建的

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

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

• MySQL默認是autocommit = on

• 你可以在 information_schema 庫的 innodb_trx 這個表中查詢長事務(wù)，比如下面這個語句，用于查找持續(xù)時間超過 60s 的事務(wù)
  select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60

事務(wù)隔離的實現(xiàn)

在 MySQL 中，每條記錄在更新的時候都會同時記錄一條回滾操作(undo log)。記錄上的最新值，通過回滾操作，都可以得到前一個狀態(tài)的值，這樣同一條記錄在系統(tǒng)中可以存在多個版本，就是數(shù)據(jù)庫的多版本并發(fā)控制（MVCC）

多版本是咋么實現(xiàn)的

InnoDB 里面每個事務(wù)有一個唯一的事務(wù) ID，叫作 transaction id。它是在事務(wù)開始的時候向 InnoDB 的事務(wù)系統(tǒng)申請的，是按申請順序嚴格遞增的。 而每行數(shù)據(jù)也都是有多個版本的。每次事務(wù)更新數(shù)據(jù)的時候，都會生成一個新的數(shù)據(jù)版本，并且把 transaction id 賦值給這個數(shù)據(jù)版本的事務(wù) ID，記為 row trx_id。同時，舊的數(shù)據(jù)版本要保留，并且在新的數(shù)據(jù)版本中，能夠有信息可以直接拿到它。 也就是說，數(shù)據(jù)表中的一行記錄，其實可能有多個版本 (row)，每個版本有自己的 row trx_id

下圖是一個記錄被多個事務(wù)連續(xù)更新后的狀態(tài)：

• 圖中虛線框里是同一行數(shù)據(jù)的 4 個版本，當前最新版本是 V4，k 的值是 22，它是被 transaction id 為 25 的事務(wù)更新的，因此它的 row trx_id 也是 25
• 圖 2中的三個虛線箭頭，就是 undo log；而 V1、V2、V3 并不是物理上真實存在的，而是每次需要的時候根據(jù)當前版本和 undo log 計算出來的。比如，需要 V2 的時候，就是通過 V4 依次執(zhí)行 U3、U2 算出來

事務(wù)隔離是咋么實現(xiàn)的

• 按照可重復讀的定義，一個事務(wù)啟動的時候，能夠看到所有已經(jīng)提交的事務(wù)結(jié)果。但是之后，這個事務(wù)執(zhí)行期間，其他事務(wù)的更新對它不可見。 因此，一個事務(wù)如果一個數(shù)據(jù)版本是在他啟動之前生成的，就認；如果是他啟動以后才生成的，就不認，必須通過undo log找到它的上一個版本”。 當然，如果“上一個版本”也不可見，那就得繼續(xù)往前找
• 還有，如果是這個事務(wù)自己更新的數(shù)據(jù)，它自己還是要認的
• 在實現(xiàn)上， InnoDB 為每個事務(wù)構(gòu)造了一個數(shù)組，用來保存這個事務(wù)啟動瞬間，當前正在“活躍”的所有事務(wù) ID?！盎钴S”指的就是，啟動了但還沒提交
• 數(shù)組里面事務(wù) ID 的最小值記為低水位 low-water-mark，相對于已提交的事務(wù)就是up-limit-id，當前系統(tǒng)里面已經(jīng)創(chuàng)建過的事務(wù) ID 的最大值加 1 記為高水位 high-water-mark 相對于未開始的事務(wù)就是low-limit-id
• 這個視圖數(shù)組和高水位，就組成了當前事務(wù)的一致性視圖（read-view）。 而數(shù)據(jù)版本的可見性規(guī)則，就是基于數(shù)據(jù)的 row trx_id 和這個一致性視圖的對比結(jié)果得到的

這個視圖數(shù)組把所有的 row trx_id 分成了幾種不同的情況

對于當前事務(wù)的啟動瞬間來說，一個數(shù)據(jù)版本的 row trx_id，有以下幾種可能：

• 如果落在綠色部分，表示這個版本是已提交的事務(wù)或者是當前事務(wù)自己生成的，這個數(shù)據(jù)是可見的
• 如果落在紅色部分，表示這個版本是由將來啟動的事務(wù)生成的，是肯定不可見的
• 如果落在黃色部分，那就包括兩種情況
  • 若 row trx_id 在數(shù)組中，表示這個版本是由還沒提交的事務(wù)生成的，不可見
  • 若 row trx_id 不在數(shù)組中，表示這個版本是已經(jīng)提交了的事務(wù)生成的，可見

比如，對于上圖中的數(shù)據(jù)來說，如果有一個事務(wù)，它的低水位是 18，那么當它訪問這一行數(shù)據(jù)時，就會從 V4 通過 U3 計算出 V3，所以在它看來，這一行的值是 11。有了這個聲明后，系統(tǒng)里面隨后發(fā)生的更新，就跟這個事務(wù)看到的內(nèi)容無關(guān)了，因為之后的更新，生成的版本一定屬于上面的 2 或者 3(1) 的情況，而對它來說，這些新的數(shù)據(jù)版本是不存在的，所以這個事務(wù)的快照，就是“靜態(tài)”的了

小插曲

MySQL中的兩個視圖

一個是 view。它是一個用查詢語句定義的虛擬表，在調(diào)用的時候執(zhí)行查詢語句并生成結(jié)果。創(chuàng)建視圖的語法是 create view … ，而它的查詢方法與表一樣
另一個是 InnoDB 在實現(xiàn) MVCC 時用到的一致性讀視圖，即 consistent read view，用于支持 RC（Read Committed，讀提交）和 RR（Repeatable Read，可重復讀）隔離級別的實現(xiàn)。

回滾日志總不能一直保留吧，什么時候刪除呢？

答案是，在不需要的時候才刪除。也就是說，系統(tǒng)會判斷，當沒有事務(wù)再需要用到這些回滾日志時，回滾日志會被刪除就是當系統(tǒng)里沒有比這個回滾日志更早的 read-view 的時候

為什么建議你盡量不要使用長事務(wù)

長事務(wù)意味著系統(tǒng)里面會存在很老的事務(wù)視圖
由于這些事務(wù)隨時可能訪問數(shù)據(jù)庫里面的任何數(shù)據(jù)，所以這個事務(wù)提交之前，數(shù)據(jù)庫里面它可能用到的回滾記錄都必須保留，這就會導致大量占用存儲空間
在 MySQL 5.5 及以前的版本，回滾日志是跟數(shù)據(jù)字典一起放在 ibdata 文件里的，即使長事務(wù)最終提交，回滾段被清理，文件也不會變小。見過數(shù)據(jù)只有 20GB，而回滾段有 200GB 的庫。最終只好為了清理回滾段，重建整個庫
除了對回滾段的影響，長事務(wù)還占用鎖資源，也可能拖垮整個庫

事務(wù)隔離下的查詢&更新

建表語句：

mysql> CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `k` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;
insert into t(id, k) values(1,1),(2,2);

3個事務(wù)的執(zhí)行時機：

假設(shè)

• .事務(wù) A 開始前，系統(tǒng)里面只有一個活躍事務(wù) ID 是 99
• 事務(wù) A、B、C 的版本號分別是 100、101、102，且當前系統(tǒng)里只有這四個事務(wù)
• 三個事務(wù)開始前，(1,1）這一行數(shù)據(jù)的 row trx_id 是 90

這樣，事務(wù) A 的視圖數(shù)組就是 [99,100], 事務(wù) B 的視圖數(shù)組是 [99,100,101], 事務(wù) C 的視圖數(shù)組是 [99,100,101,102]

查詢

事務(wù) A 的語句返回的結(jié)果，為什么是 k=1?
為了簡化分析，只畫出跟事務(wù) A 查詢邏輯有關(guān)的操作：

從圖中可以看到

• 第一個有效更新是事務(wù) C，把數(shù)據(jù)從 (1,1) 改成了 (1,2)。這時候，這個數(shù)據(jù)的最新版本的 row trx_id 是 102，而 90 這個版本已經(jīng)成為了歷史版本
• 第二個有效更新是事務(wù) B，把數(shù)據(jù)從 (1,2) 改成了 (1,3)。這時候，這個數(shù)據(jù)的最新版本（即 row trx_id）是 101，而 102 又成為了歷史版本
• 在事務(wù) A 查詢的時候，其實事務(wù) B 還沒有提交，但是它生成的 (1,3) 這個版本已經(jīng)變成當前版本了。但這個版本對事務(wù) A 必須是不可見的，否則就變成臟讀了

現(xiàn)在事務(wù) A 要來讀數(shù)據(jù)了，它的視圖數(shù)組是 [99,100]。當然了，讀數(shù)據(jù)都是從當前版本讀起的。所以，事務(wù) A 查詢語句的讀數(shù)據(jù)流程是這樣的：

• 找到 (1,3) 的時候，判斷出 row trx_id=101，比高水位大，處于紅色區(qū)域，不可見
• 接著，找到上一個歷史版本，一看 row trx_id=102，比高水位大，處于紅色區(qū)域，不可見
• 再往前找，終于找到了（1,1)，它的 row trx_id=90，比低水位小，處于綠色區(qū)域，可見

這樣執(zhí)行下來，雖然期間這一行數(shù)據(jù)被修改過，但是事務(wù) A 不論在什么時候查詢，看到這行數(shù)據(jù)的結(jié)果都是一致的，所以我們稱之為一致性讀

更新

事務(wù) B 的 update 語句，如果按照一致性讀，好像結(jié)果不對。 你看圖 5 中，事務(wù) B 的視圖數(shù)組是先生成的，之后事務(wù) C 才提交，不是應(yīng)該看不見 (1,2) 嗎，怎么能算出 (1,3) 來？

• 如果事務(wù) B 在更新之前查詢一次數(shù)據(jù)，這個查詢返回的 k 的值確實是 1
• 但是，當它要去更新數(shù)據(jù)的時候，就不能再在歷史版本上更新了，否則事務(wù) C 的更新就丟失了
• 因此，事務(wù) B 此時的 set k=k+1 是在（1,2）的基礎(chǔ)上進行的操作
• 這里就用到了這樣一條規(guī)則：更新數(shù)據(jù)都是先讀后寫的，而這個讀，只能讀當前的值，稱為“當前讀”（current read）
• 因此，在更新的時候，當前讀拿到的數(shù)據(jù)是 (1,2)，更新后生成了新版本的數(shù)據(jù) (1,3)，這個新版本的 row trx_id 是 101
• 所以，在執(zhí)行事務(wù) B 查詢語句的時候，一看自己的版本號是 101，最新數(shù)據(jù)的版本號也是 101，是自己的更新，可以直接使用，所以查詢得到的 k 的值是 3

這里我們提到了一個概念，叫作當前讀。其實，除了 update 語句外，select 語句如果加鎖，也是當前讀

• 如果把事務(wù) A 的查詢語句 select * from t where id=1 修改一下，加上 lock in share mode 或 for update，也都可以讀到版本號是 101 的數(shù)據(jù)，返回的 k 的值是 3(會進行所等待)

下面這兩個 select 語句，就是分別加了讀鎖（S 鎖，共享鎖）和寫鎖（X 鎖，排他鎖）

mysql> select k from t where id=1 lock in share mode;
mysql> select k from t where id=1 for update;

假設(shè)事務(wù) C 不是馬上提交的，而是變成了下面的事務(wù) C’，會怎么樣呢

事務(wù) C的不同是，更新后并沒有馬上提交，在它提交前，事務(wù) B 的更新語句先發(fā)起了。雖然事務(wù) C’還沒提交，但是 (1,2) 這個版本也已經(jīng)生成了，并且是當前的最新版本。那么，事務(wù) B 的更新語句會怎么處理呢？

• 這個時候，“兩階段鎖協(xié)議”就要上場了。
• C沒提交，也就是說 (1,2) 這個版本上的寫鎖還沒釋放
• 而事務(wù) B 是當前讀，必須要讀最新版本，而且必須加鎖，因此就被鎖住了，必須等到事務(wù) C’釋放這個鎖，才能繼續(xù)它的當前讀

到這里，一致性讀、當前讀和行鎖就串起來了

讀提交下的事務(wù)

上邊說的例子是可重復讀情況下的，讀提交隔離級別下呢

先總結(jié)下可重復讀

• 可重復讀的核心就是一致性讀（consistent read）
• 務(wù)更新數(shù)據(jù)的時候，只能用當前讀
• 當前的記錄的行鎖被其他事務(wù)占用的話，就需要進入鎖等待

讀提交的邏輯和可重復讀的邏輯類似，最主要的區(qū)別是：

• 在可重復讀隔離級別下，只需要在事務(wù)開始的時候創(chuàng)建一致性視圖，之后事務(wù)里的其他查詢都共用這個一致性視圖
• 在讀提交隔離級別下，每一個語句執(zhí)行前都會重新算出一個新的視圖

在讀提交隔離級別下，事務(wù) A 和事務(wù) B 的查詢語句查到的 k，分別應(yīng)該是多少呢

下面是讀提交時的狀態(tài)圖，可以看到這兩個查詢語句的創(chuàng)建視圖數(shù)組的時機發(fā)生了變化，就是圖中的 read view 框。（注意：這里，我們用的還是事務(wù) C 的邏輯直接提交，而不是事務(wù) C’）

• 這時，事務(wù) A 的查詢語句的視圖數(shù)組是在執(zhí)行這個語句的時候創(chuàng)建的，時序上 (1,2)、(1,3) 的生成時間都在創(chuàng)建這個視圖數(shù)組的時刻之前。
• 但是，在這個時刻： (1,3) 還沒提交，屬于情況 1，不可見； (1,2) 提交了，屬于情況 3，可見
• 所以，這時候事務(wù) A 查詢語句返回的是 k=2。 顯然地，事務(wù) B 查詢結(jié)果 k=3

內(nèi)容有點多，總結(jié)一下

• InnoDB 的行數(shù)據(jù)有多個版本，每個數(shù)據(jù)版本有自己的 row trx_id，每個事務(wù)或者語句有自己的一致性視圖
• 普通查詢語句是一致性讀，一致性讀會根據(jù) row trx_id 和一致性視圖確定數(shù)據(jù)版本的可見性。
• 對于可重復讀，查詢只承認在事務(wù)啟動前就已經(jīng)提交完成的數(shù)據(jù)
• 對于讀提交，查詢只承認在語句啟動前就已經(jīng)提交完成的數(shù)據(jù)；
• 而當前讀，總是讀取已經(jīng)提交完成的最新版本，讀未提交隔離用的就是當前讀

"start transaction with consistent snapshot; "

意思是從這個語句開始，創(chuàng)建一個持續(xù)整個事務(wù)的一致性快照。所以，在讀提交隔離級別下，這個用法就沒意義了，等效于普通的 start transaction