資料庫系列：MySQL慢查詢分析和效能最佳化
資料庫系列：MySQL索引最佳化總結（綜合版）
資料庫系列：高併發下的資料欄位變更
資料庫系列：覆蓋索引和規避回表
資料庫系列：資料庫高可用及無損擴容
資料庫系列：使用高區分度索引列提升效能
資料庫系列：字首索引和索引長度的取捨
資料庫系列：MySQL引擎MyISAM和InnoDB的比較
資料庫系列：InnoDB下實現高併發控制
1 事務概念和必要性
在MySQL中，事務是一個資料庫操作的最小執行單元，它由一個或多個SQL語句組成，這些SQL語句要麼全部執行成功，要麼全部失敗回滾。
所以，事務是一種機制，用來保證一系列操作要麼全部執行成功，要麼全部失敗回滾，從而保持資料庫的一致性和完整性。MySQL中只有使用支援事務的儲存引擎（如InnoDB）才能使用事務功能。
如果資料庫中沒有事務機制，那會怎麼樣呢？
★ 超級典型的金融案例，案例改編自《高效能MySQL》第四版：
假設銀行對兩個使用者賬號進行轉賬：操作使用者賬戶表（包括轉賬源頭 和 轉賬目標）。現在要從使用者A的賬戶轉賬 1000 元到使用者B的賬戶中，那麼需要至少三個步驟：
- 檢查賬戶A的餘額高於 1000 元。
- 從賬戶A餘額中減去 1000 元。
- 在賬戶B的餘額中增加 1000 元。
上述三個步驟的操作必須打包在一個事務中，任何一個步驟失敗,則必須回滾所有的步驟。
可以用 START TRANSACTION 語句開始一個事務，然後要麼使用 COMMIT 提交事務將修改的資料持久保留，要麼使用 ROLLBACK 撤銷所有的修改。
事務SQL的樣本如下:
/* 開始事務 */
START TRANSACTION;
/* 檢查賬戶A（123456）的餘額高於 1000 元 */
SELECT balance FROM acount WHERE customer_id=123456;
/* 從賬戶A（123456）餘額中減去 1000 元 */
UPDATE acount SET balance=balance-1000.00 WHERE customer_id=123456;
/* 在賬戶B（123457）餘額中增加 1000 元 */
UPDATE acount SET balance=balance+1000.00 WHERE customer_id=123457;
/* 提交事務 */
COMMIT;
解讀下這個SQL指令碼：
- 如果執行到第四條語句時伺服器崩潰了，使用者A可能會損失1000元，而使用者B也沒有接收到1000元。
- 如果執行到第三條語句和第四條語句之間時，另外一個程式要消費掉A賬戶的所有餘額，那麼結果可能就是銀行在不知道這個邏輯的情況下白白給了B賬戶1000元。
所以，金融類系統需要有嚴格的ACID測試，ACID是指原子性 (atomicity)、一致性（consistency)、隔離性（isolation)和永續性durability)。一個執行良好的事務處理系統，必須具備這些標準特徵。
2 事務的四個特性（ACID）
一般來說，衡量事務必須滿足四個特性：ACID，即 原子性（Atomicity，或稱不可分割性）、一致性（Consistency）、隔離性（Isolation，又稱獨立性）、永續性（Durability）。
- 原子性（Atomicity）：一個事務（transaction）中的所有操作，要麼全部完成，要麼全部不完成，不會結束在中間某個環節。事務在執行過程中發生錯誤，會被回滾（Rollback）到事務開始前的狀態，就像這個事務從來沒有執行過一樣。
- 一致性（Consistency）：在事務開始之前和事務結束以後，資料庫的完整性沒有被破壞。這表示寫入的資料必須完全符合所有的預設規則，這包含資料的精確度、串聯性以及後續資料庫可以自發性地完成預定的工作。
- 隔離性（Isolation）：資料庫允許多個併發事務同時對其資料進行讀寫和修改的能力，隔離性可以防止多個事務併發執行時由於交叉執行而導致資料的不一致。事務隔離分為不同級別，包括讀未提交（read uncommitted）、讀提交（read committed）、可重複讀（repeatable read）和序列化（Serializable），下面會詳細說明。
- 永續性（Durability）：事務處理結束後，對資料的修改就是永久的，會持久化到硬碟上，即便系統故障也不會丟失。
3 如何保證事務的隔離性
3.1 資料庫併發下事務的三種現象
3.1.1 髒讀
讀取事務未提交資料
髒讀就是指當一個事務A正在訪問資料，並且對資料進行了修改，而這種修改還沒有提交到資料庫中，這時，另外一個事務B也訪問這個資料，讀了未提交事務操作的資料，然後使用了這個髒資料。舉個例子：
| 時間序列 | A事務 | B事務 |
|---|---|---|
| T1 | 開始事務 | |
| T2 | 開始事務 | |
| T3 | 查詢B賬戶有100元餘額★SELECT balance FROM acount WHERE customer_id=123456; |
|
| T4 | B賬戶增加1000元轉賬（未提交）★UPDATE acount SET balance=balance+1000.00 WHERE customer_id=123457; |
|
| T5 | 查詢B賬戶有1100元餘額（讀髒資料） | |
| T6 | 入賬失敗，回滾1000元轉賬款 | |
| T7 | 提交事務★commit; |
3.1.2 不可重複讀
前後多次讀取資料不一致
不可重複讀指的是在事務A中先後多次讀取同一個資料，讀取的結果不一樣，因為另外一個事務也訪問該同一資料，並且可能修改這個資料，這種現象稱為不可重複讀。
髒讀與不可重複讀的區別在於：前者讀到的是其他事務未提交的資料，後者讀到的是其他事務已提交的資料。
| 時間序列 | A事務 | B事務 |
|---|---|---|
| T1 | 開始事務 | |
| T2 | 開始事務 | |
| T3 | 查詢B賬戶有100元餘額★SELECT balance FROM acount WHERE customer_id=123456; |
|
| T4 | B賬戶增加1000元轉賬（未提交）★UPDATE acount SET balance=balance+1000.00 WHERE customer_id=123457; |
|
| T5 | 提交事務★commit; |
|
| T6 | 查詢B賬戶有1100元餘額（不可重複讀） |
按照正確邏輯，事務B前後兩次讀取到的資料應該一致，這邊一次讀到的是100元，一次讀到的是1100元，得到了不同的結果。
3.1.3 幻讀
前後多次讀取，資料不一致
在事務A中按照某個條件先後兩次查詢資料庫，兩次查詢結果的條數不同，這種現象稱為幻讀。不可重複讀與幻讀的區別可以通俗的理解為：前者是資料變了，後者是資料的行數變了。通俗點就是已提交事務B對事務A產生的影響，導致B執行有誤，這個影響叫做“幻讀”。
| 時間序列 | A事務 | B事務 |
|---|---|---|
| T1 | 開始事務 | 開始事務 |
| T2 | 第一次查詢資料庫賬戶表有2條資料，鍵 pay_id是1和2 | |
| T3 | 給賬戶錶轉賬1000元，所以新增一條 pay_id為3的轉賬資料 | |
| T4 | 提交事務成功 | |
| T5 | 因為上面查到的pay_id=2, 所以這邊新增一條pay_id=3的消費資料，insert的時候提示key衝突 |
按照正確邏輯，事務B前後兩次讀取到的資料總量應該一致
3.14 不可重複讀和幻讀的區別
-
不可重複讀是讀取了其他事務更改的資料，針對update操作
★ 解決：使用行級鎖，鎖定該行，事務A多次讀取操作完成後才釋放該鎖，這個時候才允許其他事務更改剛才的資料。 -
幻讀是讀取了其他事務新增的資料，針對insert與delete操作
★ 解決：使用表級鎖，鎖定整張表，事務A多次讀取資料總量之後才釋放該鎖，這個時候才允許其他事務新增資料。
幻讀和不可重複讀都是指的一個事務範圍內的操作受到其他事務的影響了。只不過幻讀是重點在插入和刪除，不可重複讀重點在修改
所以，從上面的幾個案例可以看到，併發的事務可能導致其他事務的問題包括：
- 讀髒資料（最後事務並未提交成功）
- 不可重複讀
- 幻讀
如何解決，隔離性可以防止多個事務併發執行時由於交叉執行而導致資料的不一致。事務隔離分為不同級別，包括
- 讀未提交（read uncommitted）
- 讀提交（read committed）、可重複讀（repeatable read）和序列化（Serializable）
3.2 事務的隔離級別
SQL92標準中事務的隔離性（Isolation）定義了四種隔離級別，並規定了每種隔離級別下上述幾個（髒讀、不可重複讀、幻讀）問題是否被解決。
一般來說，隔離級別越低，系統開銷越低，可支援的併發越高，但隔離性也越差。隔離級別與資料庫讀的3個問題的關係如下：
| 隔離級別 | 髒讀 | 不可重複讀 | 幻讀 |
|---|---|---|---|
| 讀未提交：Read Uncommitted | ✔ | ✔ | × |
| 讀已提交：Read Committed | × | ✔ | × |
| 可重複讀：Repeatable Read | × | × | ✔ |
| 序列化：Serializable | × | × | × |
不同事務的隔離級別，實際上是一致性與併發性的一個權衡與折衷，它本質上也是InnoDB不同的鎖策略(Locking Strategy)產生的效果 。接下來我們對這幾種事務隔離機制詳細介紹下：
3.2.1 讀未提交(Read Uncommitted)
讀未提交情況下，可以讀取到其他事務還未提交的資料，多次讀取結果不一樣，出現了髒讀、不可重複讀的情況。
這種情況下select語句不加鎖：SELECT statements are performed in a nonlocking fashion.，所以這是併發最高，一致性最差的隔離級別。
3.2.2 讀已提交(Read Committed)
這是大多數資料庫系統的預設隔離級別（但不是MySQL預設的）。讀已提交情況下，無法讀取到其他事務還未提交的資料，可以讀取到其他事務已經提交的資料，多次讀取結果不一樣，不會出現髒讀和幻讀，但出現不可重複讀。
這種隔離級別用的比較多，也是網際網路業務最常用的隔離級別，在Read Committed 下：
- 普通讀是快照讀取
- 加鎖的select, update, delete等語句，除了在外來鍵約束檢查(foreign-key constraint checking)以及重複鍵檢查(duplicate-key checking)時會封鎖區間，其他時刻都只使用記錄鎖；
3.2.3 序列化(Serializable)
這種事務的隔離級非常嚴格，在這種序列情況下不存在髒讀、不可重複讀、幻讀的問題了。
所有select請求語句都會被隱式的轉化為：
select ... in share mode.
這可能導致，如果有未提交的事務正在修改某些行，所有讀取這些行的select都會被阻塞住，直到之前的事務執行完成。
這是一致性最好的，但併發性最差的隔離級別。執行順序參考如下：
| 時間 | 視窗A | 視窗B |
|---|---|---|
| T1 | start transaction; | |
| T2 | select * from classes; | |
| T3 | start transaction; | |
| T4 | insert into classes values(9,'初三九班'); | |
| T5 | select * from classes; | |
| T6 | commit; | |
| T7 | commit; |
這個明顯效率太慢了，在大資料量，大併發量的網際網路場景下，基本上是不會使用上述這種隔離級別。
3.2.4 可重複讀(Repeated Read, RR)
RR（可重複讀）是InnoDB預設的隔離級別，在R這種隔離級別下：
-
select操作使用快照讀(snapshot read)，這是一種不加鎖的一致性讀(Consistent Nonlocking Read)，底層使用MVCC來實現，MVCC 參考作者的這篇：資料庫系列：InnoDB下實現高併發控制
-
加鎖的
select(select ... in share mode / select ... for update), update, delete等語句條件的實現，其實是依賴於它們是否在唯一索引(unique index)上使用，如：- 唯一的查詢條件(unique search condition)
- 範圍查詢條件(range-type search condition)
可重複讀情況下，未出現髒讀，未讀取到其他事務已提交的資料，多次讀取結果一致，即可重複讀。但是可能導致“幻讀”。
4 總結
- 認識ACID（原子性、一致性、隔離性、永續性）特性及其實現原理
- 瞭解事務的髒讀、幻讀、不可重複讀
- 瞭解InnoDB實現deSQL92標準中的四種隔離級別
- InnoDB預設的隔離級別是RR，網際網路用得最多的隔離級別是RC，它解決了髒讀和幻讀，保證了資料隔離性和一致性。