概述
文章有點長喲，需要耐心看喲
數據庫系統是現代商業世界有序穩定運行的基石，數據和數據承載的交易事件的結果不會因系統故障而損傷。數據庫系統的核心技術是事務處理機制，也是有別于其他系統的關鍵特質。事務處理機制以其ACID特性保證了在各種環境下的數據一致性。同時：數據庫系統又是多用戶處理系統，為了提高數據庫的性能，數據庫常常會并發執行多個事務，事務的并發執行，雖然提高了性能，但是卻帶來了其他的數據一致性問題，因此，合適的并發控制技術是必須的，本文主要介紹MySQL的事務處理和并發控制技術。
1. 數據庫事務系統概述
數據庫管理系統是位于用戶與操作系統之間的一層數據管理軟件，數據庫的事務處理機制是數據庫的基石，那么為什么需要事務處理機制呢？
1.1 為什么需要事務處理機制
我們先看一個問題：賬戶A轉賬50元到賬戶B，過程如下：
賬戶轉賬示例
事務處理機制就是要通過實現事務的ACID特性來保證用戶對數據的操作是安全的一致的，不管在什么環境下，都必須是安全的一致的。事務概念為應用程序開發人員提供了一個非常好的抽象，同時保證了數據的安全一致性。
1.2 什么是事務呢？具有哪些特性
前面我們通過例子簡單的認識了事務處理的必要性，那么事務到底是什么呢？它的ACID特性該如何理解呢？
事務是數據庫管理系統執行過程中的一個邏輯單位，由一個有限的數據庫操作序列構成，事務是無法被分割的操作，事務必須作為一個完整的單元成功或失敗，不可能存在部分完成的事務。
事務具有如下特性：
- 原子性（Atomicity）：事務作為一個整體被執行，包含在其中的對數據庫的操作要么全部被執行，要么都不執行。
- 一致性（Consistency）：事務應確保數據庫的狀態從一個一致狀態轉變為另一個一致狀態。一致狀態的含義是數據庫中的數據應滿足完整性約束。
- 隔離性（Isolation）：多個事務并發執行時，一個事務的執行不應影響其他事務的執行。
- 持久性（Durability）：已被提交的事務對數據庫的修改應該永久保存在數據庫中。
那么該如何理解這四個特性呢？
- 1.原子性：ACID的原子性描述了當客戶想進行多次寫入，但在一些寫操作處理完之后出現故障的情況。例如進程崩潰，網絡連接中斷，磁盤變滿或者某種完整性約束被違反。如果這些寫操作被分組到一個原子事務中，并且該事務由于錯誤而不能完成（提交），則該事務將被中止，并且數據庫必須丟棄或撤消該事務中迄今為止所做的任何寫入。如果沒有原子性，在多處更改進行到一半時發生錯誤，很難知道哪些更改已經生效，哪些沒有生效。該應用程序可以再試一次，但冒著進行兩次相同變更的風險，可能會導致數據重復或錯誤的數據。原子性簡化了這個問題：如果事務被中止（abort），應用程序可以確定它沒有改變任何東西，所以可以安全地重試。ACID原子性的定義特征是：能夠在錯誤時中止事務，丟棄該事務進行的所有寫入變更的能力。即：可中止的。
- 2.一致性：ACID一致性的概念是，對數據的一組特定陳述必須始終成立。例如我們之前舉例的轉賬。一致性更多的強調的是數據庫狀態的變遷，即：數據庫必須從一個有序狀態變遷到另一個有序狀態，那么什么是有序的? 答案是：數據在事務的操作下，始終符合用戶定義的所有完整性約束。可見：原子性，隔離性和持久性是數據庫的屬性，而一致性（在ACID意義上）是應用程序的屬性。應用可能依賴數據庫的原子性、隔離性和持久性來實現數據額一致性。
- 3.隔離性：大多數數據庫都會同時被多個客戶端訪問。如果它們各自讀寫數據庫的不同部分，這是沒有問題的，但是如果它們訪問相同的數據庫記錄，則可能會遇到并發問題（競爭條件（race conditions））。如下圖顯然違背了隔離性：
ACID意義上的隔離性意味著，同時執行的事務是相互隔離的：它們不能相互冒犯。傳統的數據庫教科書將隔離性形式化為可串行化（Serializability），這意味著每個事務可以假裝它是唯一在整個數據庫上運行的事務。數據庫確保當事務已經提交時，結果與它們按順序運行（一個接一個）是一樣的，盡管實際上它們可能是并發運行的。不過在實際生產環境中，很少使用可串行化，因為這會使得性能大幅降低。
- 4.持久性：數據庫系統的目的是，提供一個安全的地方存儲數據，而不用擔心丟失。持久性 是一個承諾，即一旦事務成功完成，即使發生硬件故障或數據庫崩潰，寫入的任何數據也不會丟失。當然了，沒有絕對的持久性，如果你的數據所在的磁盤損壞了呢?備份也損壞了呢？因此沒有絕對的持久性安全性，增加副本只能增加安全性的幾率，但是絕對達不到100%。
以上介紹了數據庫的四大特性，接下來看看為什么需要并發控制呢？
1.3 為什么需要并發控制
我們已經知道，數據庫會同時被多個用戶（多個事務）訪問，如果多個事務訪問不同的數據，那么事務之間不會有任何干擾，如果多個事務操作相同的數據，那么各個事務之前就會互相干擾違背事務的隔離性，導致數據不一致。因此，為了滿足隔離性，使得各個并發的事務互不干擾，數據庫系統必須對并發的事務進行控制，即：并發控制。
那么不進行并發控制會有哪些問題呢？
事務之間的關系有如下三種：
- 1、讀--讀：如果多個事務都是只讀操作，那么這些事務可以并發，不會互相干擾（因為沒有數據更新）。
- 2、讀--寫：如果讀寫都存在，則會存在臟讀、不可重復度、幻讀等問題。
- 3、寫--寫：如果多個事務并發的寫同一數據，如果不加并發控制也會帶來數據異常。
接下來看看三種常見的讀異常：
讀數據異常
如上圖：
臟讀：如果沒有并發控制機制，事務T2在Time1處讀到了無效的數據（因為事務T1在Time2時中止了）
不可重復讀：事務T1在Time3處發生了不可重復讀（同一個事務，在事務執行的過程中，同一行數據，讀到了不同的值）
幻讀：事務T1在Time3處發生了幻讀。
沒有并發控制的情況下，讀--寫操作不僅會造成三種讀異常，寫寫操作也會造成寫異常，下邊我們看看寫-寫操作導致的異常：
寫數據異常
如上圖： 臟寫：事務T1在time3時刻回滾掉了不是自己修改的數據，即事務T1在time3出發生了臟寫。
丟失更新：事務T1在time3處覆蓋掉了不是自己修改的數據，即T1在time3出發生了丟失更新異常，如果是T2在time3時提交，道理也是一樣的，只不過在T2上發生了丟失更新。
除了以上讀寫異常，還存在一種**語義約束（事務的特性是要保證語義約束的）**引發的數據異常--寫偏序：為了理解寫偏序，我們先看一個例子（該例子來自《數據密集型應用系統設計》一書）：
- 你正在為醫院寫一個醫生輪班管理程序。醫院通常會同時要求幾位醫生待命，但底線是至少有一位醫生在待命。醫生可以放棄他們的班次（例如，如果他們自己生病了），只要至少有一個同事在這一班中繼續工作?，F在想象一下，Alice和Bob是兩位值班醫生。兩人都感到不適，所以他們都決定請假。不幸的是，他們恰好在同一時間點擊按鈕下班。下圖說明了接下來的事情：
寫偏序
在兩個事務中，應用首先檢查是否有兩個或以上的醫生正在值班；如果是的話，它就假定一名醫生可以安全地休班。由于兩次檢查都返回 2 ，所以兩個事務都進入下一個階段。Alice更新自己的記錄休班了，而Bob也做了一樣的事情。兩個事務都成功提交了，現在沒有醫生值班了。違反了**至少有一名醫生在值班（數據庫的一致性）**的要求。
寫偏序的特征：
這種異常稱為寫偏序。它既不是臟寫，也不是丟失更新，因為這兩個事務正在更新兩個不同的對象（Alice和Bob各自的待命記錄）。
由上可見：如果不進行并發控制，會使得多個并發執行的事務造成各種各樣的問題，因此需要并發控制技術。
那么是不是只要簡單的對各個事務進行排隊，并一個接一個的執行就可以了呢？是的，這樣的確可以，但是，這樣會使得數據庫的性能非常差，為了提高數據庫的性能，SQL標準定義了四種隔離級別，這四種隔離級別分別在三種數據讀異常前后插入，對應的解決了讀異常的發生。
1.4 在正確性和效率之間做權衡
為了提高數據庫的效率，同時兼顧正確性，SQL標準定義了四種隔離級別，分別解決各種讀數據異常：
這四種隔離級別如下：
- 1 未提交讀（READ UNCOMMITTED）：一個事務在執行過程中可以看到其他事務“尚未提交”的修改（包含更新和插入），該隔離級別允許臟讀，在實際應用中沒有實用價值。
- 2 已提交讀（READ COMMITTED）：一個事務在執行過程中可以看到其他事務“已經提交”的修改（包含更新和插入）。該隔離級別下，允許不可重復讀發生，不允許臟讀。
- 3 可重復性讀（REPEATABLE READ）：一個事務在執行過程中可以看到其他事務已經提交的新插入的元組，但是不能看到其他事務對已有元組的更新。允許幻讀（范圍查詢時會出現）。
- 4 可串行化（SERIALIZABLE）：看上去就是一個事務接著一個事務的串行執行，不會出現任何不一致。
實際應用中，已提交讀和可重復度比較常用（MySQL默認情況下為可重復性讀）。
四種隔離級別都不允許臟寫和丟失更新發生。
2 ACID的實現技術
在第一節，學習了數據庫的基本概念，特別是事務及其特性，本節梳理一下實現事務和ACID特性的技術。
2.1 實現原子性的技術
- 事務管理
begin; rollback; commit;
- 并發控制，避免并發事務之間的干擾
- 日志技術
- redo日志：用于系統故障發生后的崩潰恢復。
- undo日志：用于故障恢復。保證被aborted的事務一定是被aborted的。
2.2 實現隔離性的技術
并發控制技術。主要包含：
- 加鎖
- MVCC
2.3 實現 持久性的技術
日志技術
- redo日志：用于系統故障發生后的崩潰恢復。
- undo日志：用于故障恢復。保證被aborted的事務一定是被aborted的。
3 MySQL事務處理
3.1 MySQL事務模型
MySQL本質上是一個數據庫軟件的框架，支持插件化存儲引擎，MySQL的事務處理也與具體的存儲引擎相關，MySQL只是定義了事務處理的框架具體實現由存儲引擎完成。因此MySQL是否支持事務，是與存儲引擎相關的。從mysql-5.5.5開始,InnoDB作為默認存儲引擎，該存儲引擎是支持事務的，同時，InnoDB也是MySQL做為OLTP場景標準的存儲引擎，這里主要研究InnoDB的事務模型。
MySQL的事務管理語句如下：
START TRANSACTION
[transaction_characteristic [, transaction_characteristic] ...]
transaction_characteristic:
WITH CONSISTENT SNAPSHOT
| READ WRITE
| READ ONLY
BEGIN [WORK]
COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
SET autocommit = {0 | 1}
以上這些語句提供了對事務的控制：
START TRANSACTION或BEGIN開始新的事務。- COMMIT 提交當前事務，使其更改持久化。
- ROLLBACK 回滾當前事務，取消其更改。
- SET autocommit 禁用或啟用當前會話的默認自動提交模式。
- 默認情況下，MySQL在啟用自動提交模式的情況下運行 。這意味著只要執行更新（修改）語句，MySQL就會將更新存儲在磁盤上以使其永久保存。更改無法回滾。即：如果沒有顯示使用begin開啟一個事務，MySQL會隱式開啟事務，并自動提交（SQL成功）或回滾（SQL失?。Ｒ獮閱蝹€sql語句隱式禁用自動提交，請使用以下START TRANSACTION 語句：
START TRANSACTION; SELECT @A:=SUM(salary) FROM table1 WHERE type=1; UPDATE table2 SET summary=@A WHERE type=1; COMMIT;
mysql看到START TRANSACTION，將會禁用自動提交，直到用COMMIT或者ROLLBACK結束事務，結束之后，自動提交模式將恢復到之前的狀態。
為了要顯式禁用自動提交模式，請使用以下語句：
SET autocommit=0;
通過將autocommit變量設置為零禁用自動提交模式后 ，對事務安全型表（例如InnoDB或NDB表）的更改不會立即生效。你必須使用COMMIT將更改存儲到磁盤或ROLLBACK忽略更改。autocommit是一個會話變量，必須為每個會話設置。要為每個新連接禁用自動提交模式，需要修改系統變量。
一些語句不能回滾。通常包括數據定義語言（DDL）語句。
MySQL事務有三種提交模式：
- 1 自動提交；
- 2 顯示執行commit；
- 3 因執行DDL，觸發之前的SQL語句自動提交。
MySQL事務有三種回滾模式:
- 1 顯示執行rollback；
- 2 因參數 innodb_lock_wait_timeout (表示鎖等待發生多久)事務中的語句進行語句級回滾；
- 3 因參數innodb_rollback_on_timeout 超時導致整個事務回滾(默認情況下，InnoDB僅回滾事務超時的最后一個語句。如果指定了--innodb-rollback-on-timeout為ON，則事務超時會導致InnoDB回滾整個事務。)
3.2 InnoDB日志技術
日志技術是實現數據庫原子性和持久性的核心技術，InnoDB通過UNDO LOG、REDO LOG、doublewrite buffer實現事務的原子性和持久性。
3.2.1 REDO LOG
重做日志是在崩潰恢復期間使用的基于磁盤的數據結構，用于糾正由于寫入不完整導致數據錯誤，從而保證已提交事務的原子性和持久性。
為什么需要redo log？
為了提高數據庫的性能，MySQL 中使用了大量的內存 Cache（Innodb 的buffer poll） ，對數據的修改操作會先修改內存中的 Page，但這些頁不會立刻同步磁盤，這時內存中的數據已經和磁盤上的不一致了，我們稱這種 Page 為臟頁。試想一下這時候如果數據庫宕機了，內存中這部分被修改的數據記錄就丟失了，重啟后也沒辦法恢復。因此需要redo log，即使數據庫不用緩存，也需要使redo log，比如：一個事務寫磁盤寫了一半，數據庫重啟了，重啟之后數據庫根本無法知道事務進行到何種程度了。
因此為了保證數據的安全性，在修改內存中的 Page 之后 InnoDB 會寫 redo log，因為 redo log 是順序寫入的，而眾所周知磁盤的順序讀寫的速度遠大于隨機讀寫，因此這部分日志寫操作對性能影響較小。然后，InnoDB 會在事務提交前將 redo log 保存到磁盤中（可以通過innodb_flush_log_at_trx_commit來控制重做日志刷新到磁盤的策略。該參數默認值為1，表示事務提交必須進行一次fsync操作，還可以設置為0和2。0表示事務提交時不進行寫入重做日志操作，該操作只在主線程中完成，2表示提交時寫入重做日志，但是只寫入文件系統緩存，不進行fsync操作。由此可見，設置為0時，性能最高，但是喪失了事務的一致性）。這里所說的 redo log 是物理日志而非邏輯日志，記錄的是數據頁的物理修改（比如：將某個頁面的某個偏移量處的值加2），而不是某一行或某幾行修改成怎樣怎樣，它用來恢復提交后的物理數據頁（恢復數據頁，且只能恢復到最后一次提交的位置）。
當數據庫意外重啟時，會根據 redo log 進行數據恢復，如果 redo log 中有事務提交，則進行事務提交修改數據。
3.2.2 UNDO LOG
與 redo log 不同，undo log 一般是邏輯日志，根據每行記錄進行記錄。例如當 DELETE 一條記錄時，undo log 中會記錄一條對應的 INSERT 記錄，反之亦然當 UPDTAE 一條記錄時，它記錄一條對應反向 UPDATE 記錄。
當數據被修改時除了會記錄 redo log 還會記錄 undo log，通過 undo log 一方面可以實現事務回滾，另一方面可以根據 undo log 回溯到某個特定的版本的數據，InnoDB實現 MVCC 的功能時就會用到undo log。
3.2.3 雙寫（doublewrite buffer）
有了redo log可以用來恢復數據，那么doublewrite buffer是做什么的呢？doublewrite buffer也是用于實現數據的持久性的。
因為InnoDB并不會將整個頁面的內容寫入重做日志，而是記錄的對頁面的操作，例如將某個偏移量處的值加2，使用重做日志進行恢復的基礎是表空間中的實際數據頁面在內部是完整的一致的，它是哪個頁面版本無關緊要 ，但是如果頁面不一致，則無法繼續恢復，因為你的基礎數據就是不一致的。為此引入了Doublewrite Buffer來解決問題。
但是為什么會出現“表空間的數據頁面不完整呢？” 這是因為會出現InnoDB部分頁面寫入的情況：InnoDB中，在將緩沖池中的數據刷新到磁盤時是以頁面（InnoDB的頁面，通常為16KB）為單位的，這時可能會出現部分頁面寫入的問題。所謂部分頁面寫入是指向操作系統提交的頁面寫入請求僅部分完成。例如，在16K 的Innodb頁面中，只有第一個4KB（文件系統的塊通常為4KB）的塊被寫入磁盤，其他部分保持原來的狀態。最常見的部分頁面寫入一般在發生電源故障時發生。也可能發生在操作系統崩潰時。另外，如果使用軟件RAID，頁面可能會出現在需要多個IO請求的條帶邊界上。如果硬件RAID沒有電池備份，電源故障時也會發生這種情況。如果對磁盤本身發出單個寫入，即使電源掉電，在理論上也應完成寫入，因為驅動器內部應該有足夠的電源來完成它。但是真的很難檢查是否總是這樣，因為它不是部分頁面寫入的唯一原因。
理解了為什么需要Doublewrite Buffer，也就不難理解Doublewrite Buffer如何工作了。具體來說就是：你可以將Doublewrite Buffer視為系統表空間中的一個短期日志文件，它包含100個頁的空間。當Innodb從Innodb緩沖池中刷新頁面時，InnoDB首先會將頁面寫入雙寫緩沖區（順序），然后調用fsync()以確保它們保存到磁盤，然后將頁面寫入真正的數據文件并第二次調用fsync()）。現在Innodb恢復的時候會檢查表空間中數據頁面的內容和Doublewrite Buffer中頁面的內容。如果在雙寫緩沖區中的頁面不一致，則簡單地丟棄它，如果表空間中的數據頁面不一致，則從雙寫緩沖區中恢復。
4 MySQL并發控制
前面我們已經探討了需要并發控制技術的原因，本節介紹InnoDB使用的并發控制技術。
InnoDB的并發控制技術包含兩種：
- 1 基于鎖的并發控制；
- 2 MVCC（多版本并發控制）。
其中，鎖是并發控制的基礎，在此基礎上實現了MVCC機制，改善了基于鎖的方式帶來的效率低的問題。使得讀寫之間互補阻塞，提高了單純的基于鎖的并發效率。
4.1 基于鎖的并發
Innodb提供四種粒度的鎖：
- S：共享鎖，也稱為讀鎖；
- X：排他鎖，也稱為寫鎖；
- IS：意向共享鎖，也稱為意向讀鎖；
- IX：意向排他鎖，也稱為意向寫鎖。
InnoDB鎖的想容性矩陣如下：
InnoDB的加鎖機制符合SS2PL（增強兩階段封鎖協議），即：
每個事務分為兩個階段：
- 增長階段：事務只能獲得鎖，但不能釋放鎖；
- 縮減階段：事務可以釋放鎖，但不可獲取鎖。
- 事務提交之前不能釋放任何鎖。
不過不同粒度的鎖的施加時機和持鎖時間與隔離級別和MVCC相關。“串行化”隔離級別下，MVCC不發生作用，InnoDB嚴格遵循SS2PL。
可以通過：
show status like 'innodb_row_lock%'; select * from information_schema.innodb_locks; -- 5.7即以前 select * from performance_schema.data_locks; -- 8.0即以后
來查看行鎖的爭用情況。更多信息，可以參考MySQL官方手冊。
意向鎖作用解讀
InnoDB 支持多粒度鎖（multiple granularity locking），它允許行級鎖與表級鎖共存，而意向鎖就是其中的一種表鎖。
意向鎖分為兩種： 意向共享鎖（intention shared lock, IS）：事務有意向對表中的某些行加共享鎖（S鎖）
-- 事務要獲取某些行的 S 鎖，必須先獲得表的 IS 鎖。 SELECT column FROM table ... LOCK IN SHARE MODE;
意向排他鎖（intention exclusive lock, IX）：事務有意向對表中的某些行加排他鎖（X鎖）
-- 事務要獲取某些行的 X 鎖，必須先獲得表的 IX 鎖。 SELECT column FROM table ... FOR UPDATE;
即：意向鎖是有數據引擎自己維護的，用戶無法手動操作意向鎖，在為數據行加共享/排他鎖之前，InooDB 會先獲取該數據行所在在數據表的對應意向鎖。換言之，數據庫引擎看到某表上有事務T持有IX，就可以得知事務T必然持有該表中某些數據行的排他鎖。共享鎖同理。后面會看到，這個對性能提升意義非凡。
意向鎖可以提高性能，例如：
事務 A 獲取了某一行的排他鎖，并未提交：
SELECT * FROM users WHERE id = 6 FOR UPDATE;
此時 users 表存在兩把鎖：users 表上的意向排他鎖與 id 為 6 的數據行上的排他鎖。
事務 B 想要獲取 users 表的共享鎖：
LOCK TABLES users READ;
此時事務 B 檢測事務 A 持有 users 表的意向排他鎖，就可以得知事務 A 必然持有該表中某些數據行的排他鎖，那么事務 B 對 users 表的加鎖請求就會被排斥（阻塞），而無需去檢測表中的每一行數據是否存在排他鎖(如果是一張大表，這個檢查的性能是非常低的)。
4.1.1 鎖的種類
InnoDB為了實現基于鎖的并發控制，實現了多種類型的鎖，包括在聚集索引上實現的行級記錄鎖，間隙鎖，記錄鎖+間隙鎖組合成的范圍鎖，插入意象鎖。這些鎖的實現依賴于InnoDB的聚集索引額封鎖機制。
- 1 記錄鎖：行級鎖，依據InnoDB的聚集索引實現，針對索引加的鎖。
- 2 間隙鎖：兩個索引項之間的間隔稱為間隙。把這個間隙視為一個對象，然后在此對象上加鎖，就是間隙鎖。
- 3 Next-key lock：由記錄鎖和此記錄前的間隙上的間隙鎖組成。用于在重復讀的隔離級別下解決幻讀問題。
- 4 插入意象鎖：基于間隙鎖，專門用于插入操作。目的是為了提高并發度。
4.2 基于MVCC的并發控制
InnoDB在鎖的基礎上實現了MVCC，增加了并發度。
概括來說就是當一個事務開始時，它被賦予一個唯一的，永遠增長的事務ID（ txid ）。每當事務向數據庫寫入任何內容時，它所寫入的數據都會被標記上寫入者的事務ID，一個事務只能看到在他之前提交的事務修改的數據。
- 在可重復讀的隔離級別下，InnoDB會在第一個select操作時創建一個數據快照。在一致性讀（快照讀）的查詢下，通過快照實現了可重復讀，解決了幻讀。不過對于非一致性讀（當前讀）的情況下，需要通過范圍鎖才能解決幻讀。
- 對于未提交讀和已提交讀，事務塊內的select會分別創建自己額快照，因此每次讀的都不同。后邊的select可以讀到本次select之前提交的數據。
4.3 InnoDB的四種隔離級別
InnoDB實現了四種隔離級別，InnoDB的四種隔離級別的語義除了可重復讀之外與標準隔離級別一致。默認情況下為可重復性讀，不同之處在于InnoDB的可重復讀通過MVCC和范圍鎖（Next-Key lock）避免了幻讀。不同的場景下避免幻讀的機制不一樣。
- 對于SELECT ... FROM 語句，為一致性讀（快照讀），這種場景下，InnoDB通過MVCC技術，在SELECT的時候創建一個表的一致性快照，該技術下讀寫不阻塞。
- 對于SELECT ... FROM FOR UPDATE（當前讀--讀取的是最新版本, 并且對讀取的記錄加鎖, 不讓其他事務同時去改）：則會通過范圍鎖來實現可重復性讀，并且避免幻讀。
5、隔離級別與數據異常
本節演示一下各種隔離級別下的數據異常。
分別啟動兩個客戶端client1，client2連接到mysql服務器，client1為主事務端，在此改變隔離級別。
創建表如下：
create table bluesea(c1 int, c2 varchar(32), c3 int, primary key(c1));
5.1 可串行化隔離級別
可串行化
client2執行insert后，阻塞，這時在client3查看加鎖情況如下：
mysql> select * from data_locks; +--------+--------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+------------------------+ | ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA | +--------+--------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+------------------------+ | INNODB | 18262823:7872 | 18262823 | 50 | 18 | test | bluesea | NULL | NULL | NULL | 139693811345432 | TABLE | IX | GRANTED | NULL | | INNODB | 18262823:6815:4:1 | 18262823 | 50 | 18 | test | bluesea | NULL | NULL | PRIMARY | 139693811342392 | RECORD | X | WAITING | supremum pseudo-record | | INNODB | 421168866928480:7872 | 421168866928480 | 49 | 209 | test | bluesea | NULL | NULL | NULL | 139693811339480 | TABLE | IS | GRANTED | NULL | | INNODB | 421168866928480:6815:4:1 | 421168866928480 | 49 | 209 | test | bluesea | NULL | NULL | PRIMARY | 139693811336440 | RECORD | S | GRANTED | supremum pseudo-record | +--------+--------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+------------------------+ 4 rows in set (0.00 sec)
5.2 可重復讀隔離級別
不存在幻讀，mysql通過間隙鎖解決了幻讀問題，sql標準在這個隔離級別下允許幻讀。
可重復讀
client2的事務在更新的時候會加鎖，client1的不加鎖：
select * from data_locks; +--------+-------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+-----------+ | ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA | +--------+-------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+-----------+ | INNODB | 18262831:7872 | 18262831 | 50 | 24 | test | bluesea | NULL | NULL | NULL | 139693811345432 | TABLE | IX | GRANTED | NULL | | INNODB | 18262831:6815:4:2 | 18262831 | 50 | 25 | test | bluesea | NULL | NULL | PRIMARY | 139693811342392 | RECORD | X | GRANTED | 1 | +--------+-------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+-----------+ 2 rows in set (0.00 sec)
5.3 已提交讀隔離級別
存在幻讀，不可重復讀
已提交讀
5.4 未提交讀隔離級別
存在臟讀
未提交讀
5.5 寫偏序再討論
寫偏序一般遵循如下模式：
- 1 一個 SELECT 查詢找出符合條件的行，并檢查是否符合一些要求。（例如：至少有兩名醫生在值班）。
- 2 按照第一個查詢的結果，應用代碼決定是否繼續。（可能會繼續操作，也可能中止并報錯）
- 3 如果應用決定繼續操作，就執行寫入（插入、更新或刪除），并提交事務。
這個寫入的效果改變了步驟2 中的先決條件。換句話說，如果在提交寫入后，重復執行一次步驟1 的SELECT查詢，將會得到不同的結果。因為寫入改變了符合搜索條件的行集（現在少了一個醫生值班）
MySQL的可重復讀避免不了寫偏序，為了避免寫偏序，需要使用可串行化隔離級別或者在可重復讀隔離級別下使用(select ... for update)：
BEGIN TRANSACTION; SELECT count(*) FROM doctors WHERE on_call = TRUE AND shift_id = 1234 FOR UPDATE; UPDATE doctors SET on_call = FALSE WHERE name = 'Alice' AND shift_id = 1234; COMMIT;
5.5.1 寫偏序異常舉例
首先構造數據：
我們這里舉出的例子還是接著之前的醫生值班的例子。
CREATE TABLE `docts` ( `id` int(11) NOT NULL, `name` varchar(16) DEFAULT NULL, `status` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB; insert into docts values(1, 'alice', 1); insert into docts values(2, 'bob', 1); insert into docts values(3, 'test', 0); mysql> select * from docts; +----+-------+--------+ | id | name | status | +----+-------+--------+ | 1 | alice | 1 | | 2 | bob | 1 | | 3 | test | 0 | +----+-------+--------+ 3 rows in set (0.00 sec) select @@transaction_isolation,@@global.transaction_isolation; +-------------------------+--------------------------------+ | @@transaction_isolation | @@global.transaction_isolation | +-------------------------+--------------------------------+ | REPEATABLE-READ | REPEATABLE-READ | +-------------------------+--------------------------------+
status == 1表示在值班，0表示未值班
接下來看看在可重復讀下寫偏序的發生：
寫偏序
set @v1=(select count(*) from docts where `status`=1 );的加鎖情況 mysql> select THREAD_ID, OBJECT_SCHEMA, OBJECT_NAME, LOCK_MODE,LOCK_STATUS from data_locks; +-----------+---------------+-------------+-----------+-------------+ | THREAD_ID | OBJECT_SCHEMA | OBJECT_NAME | LOCK_MODE | LOCK_STATUS | +-----------+---------------+-------------+-----------+-------------+ | 49 | test | docts | IS | GRANTED | | 49 | test | docts | S | GRANTED | | 49 | test | docts | S | GRANTED | | 49 | test | docts | S | GRANTED | | 49 | test | docts | S | GRANTED | +-----------+---------------+-------------+-----------+-------------+ 5 rows in set (0.00 sec)
5.5.2 寫偏序異常解決
避免寫偏序
set @v1=(select count(*) from docts where `status`=1 for update);的加鎖情況 mysql> select THREAD_ID, OBJECT_SCHEMA, OBJECT_NAME, LOCK_MODE,LOCK_STATUS from data_locks; +-----------+---------------+-------------+-----------+-------------+ | THREAD_ID | OBJECT_SCHEMA | OBJECT_NAME | LOCK_MODE | LOCK_STATUS | +-----------+---------------+-------------+-----------+-------------+ | 49 | test | docts | IX | GRANTED | | 49 | test | docts | X | GRANTED | | 49 | test | docts | X | GRANTED | | 49 | test | docts | X | GRANTED | | 49 | test | docts | X | GRANTED | +-----------+---------------+-------------+-----------+-------------+ 5 rows in set (0.00 sec)
