【数据库】一篇文章搞懂数据库隔离级别那些事（LBCC,MVCC）
MySQL 事務(wù)
文章比較長，建議分段閱讀
后續(xù)如果有改動會在 Junebao.top
之前對事務(wù)的了解僅限于知道要么全部執(zhí)行，要么全部不執(zhí)行，能背出 ACID 和隔離級別，知其然但不知其所以然，現(xiàn)在覺得非常有必要系統(tǒng)學(xué)一下，關(guān)于事務(wù)，關(guān)于 LBCC，關(guān)于 MVCC，關(guān)于死鎖 ……
并發(fā)的問題
所謂 事務(wù) 是用戶定義的一個 數(shù)據(jù)庫操作序列， 這些操作要么全做，要么全不做，是一個不可分割的工作單位，在關(guān)系型數(shù)據(jù)庫中，一個事務(wù)可以是一條 SQL 語句，一組 SQL 語句或者是整個程序，事務(wù)的開始和結(jié)束由用戶顯示控制，如果用戶沒有顯式定義事務(wù)，則由 DBMS 按默認規(guī)定自動劃分事務(wù)，如在 MySQL 中默認 autocommit 為 ON 則開啟事務(wù)自動提交，每條沒有顯式定義事務(wù)的 SQL 語句都會被當(dāng)作一個單獨的事務(wù)并自動提交：
mysql> show session variables like 'autocommit'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | autocommit | ON | +---------------+-------+ 1 row in set, 1 warning (0.00 sec)事務(wù)有四個特性，即 ACID：
- A（Atomicity）: 原子性 ，事務(wù)是數(shù)據(jù)庫的基本工作單位，事務(wù)中包含的諸操作要么都做，要么都不做。
- C（Consistency）: 一致性，事務(wù)的執(zhí)行結(jié)果必須使數(shù)據(jù)庫從一個一致性狀態(tài)轉(zhuǎn)換到另一個一致性狀態(tài)，所謂一致性狀態(tài)是指數(shù)據(jù)庫中因該只包含成功事務(wù)執(zhí)行的結(jié)果，如果一個事務(wù)在執(zhí)行過程中被迫中斷，但這個未完成的事務(wù)對數(shù)據(jù)庫的部分修改已經(jīng)寫入物理數(shù)據(jù)庫，這時數(shù)據(jù)庫就處于一種 “不正確” 的狀態(tài)，或者稱處于 “不一致” 的狀態(tài)。
- I （Isolation）: 隔離性，一個事務(wù)的執(zhí)行不能被其他事務(wù)所干擾，即一個事務(wù)內(nèi)部操作所使用的數(shù)據(jù)對其他并發(fā)的事務(wù)應(yīng)該是隔離的，所有并發(fā)執(zhí)行的事物之間不能相互干擾。
- D（Durability）：持久性，一個事務(wù)一旦提交，他對數(shù)據(jù)的操作就應(yīng)該是持久的，接下來的其他操作或故障不應(yīng)該對他有任何影響。
只有保證了事務(wù)的 ACID 特性，對數(shù)據(jù)庫的操作才能是安全的，因此，不管是 LDCC 還是 MVCC，其核心目的都是保證事務(wù)的 ACID 特性?？赡芷茐氖聞?wù) ACID 特性的因素包括：
上面兩個因素分別對應(yīng)事務(wù)處理的兩類技術(shù)：并發(fā)控制和數(shù)據(jù)庫恢復(fù)技術(shù)，事務(wù)被強制終止一般由事物內(nèi)部故障，系統(tǒng)故障等造成，發(fā)生類似故障時，一般采用日志文件恢復(fù)等方法使數(shù)據(jù)庫恢復(fù)到上一個一致性狀態(tài)，這里著重研究由并發(fā)導(dǎo)致的 ACID 被破壞的情況和解決方案。
并發(fā)導(dǎo)致的數(shù)據(jù)不一致包括：丟失修改，臟讀，不可重復(fù)讀，幻讀。
丟失修改
如上圖，兩個并發(fā)的事務(wù) T1, T2 同時讀表中的某條記錄 total 得到 16， 并且對其進行修改，最終造成后提交的事務(wù) T2 的修改結(jié)果覆蓋了先提交的事務(wù) T1 的修改結(jié)果，這種現(xiàn)象叫做丟失修改。
針對丟失修改，還有一種情況，就是如果 T2 在修改 total 之后發(fā)生異常進行了回滾，就會導(dǎo)致 total 值重置為 16，這種現(xiàn)象被稱為 回滾覆蓋，而第一種情況被稱為提交覆蓋
不可重復(fù)讀和幻讀
事務(wù) T1 讀取數(shù)據(jù)后，T2 對該數(shù)據(jù)進行了更新操作， 導(dǎo)致 T1 無法再次讀到前一次讀取的結(jié)果，這種現(xiàn)象叫做不可重復(fù)讀，導(dǎo)致不可重復(fù)讀的原因包括下面三種情況：
后兩種情況也被常常稱為 幻讀， 幻讀與第一種情況的不同在于：
為了方便，不可重復(fù)讀一般只指第一種情況，幻讀指后兩種情況。
臟讀
事務(wù) T1 修改了某條記錄，T2 讀取到了 T1 未提交的這條記錄，但 T1 由于某些原因被回滾了，這就導(dǎo)致了 T2 讀取到的數(shù)據(jù)與數(shù)據(jù)庫中的數(shù)據(jù)不一致，即臟數(shù)據(jù)。
對比
| 臟讀 | 不可重讀 | ||
| 幻讀 | 丟失修改 |
導(dǎo)致上述四種不一致的原因就是破壞了事務(wù)的隔離性，進而導(dǎo)致一致性被破壞，而保證數(shù)據(jù)隔離性的方法就是使用正確的方式調(diào)度并發(fā)操作， 但有的時候，為了性能，我們有允許犧牲一部分隔離性，比如對有些數(shù)據(jù)量很大，少量臟數(shù)據(jù)對結(jié)果影響很小或影響可以接收時，我們可以降低一致性要求以減少系統(tǒng)開銷，這就是數(shù)據(jù)庫的隔離級別。
| 讀未提交 Read uncommitted | 可能 | 可能 | 可能 |
| 讀已提交 Read committed | 可能 | 可能 | |
| 可重復(fù)讀 Repeatable read | 可能 | ||
| 串行化 Serializable |
并發(fā)控制技術(shù)
隔離級別只是定義了在不同的級別下應(yīng)該保證哪些一致性，具體實現(xiàn)這些隔離級別的方法有很多，如傳統(tǒng)的基于鎖的并發(fā)控制（LBCC），還有一些無鎖并發(fā)控制方案，如時間戳（timestamp), 樂觀控制法（scheduler），多版本并發(fā)控制（MVCC）等，我們主要探索基于鎖的并發(fā)控制（LBCC）和多版本并發(fā)控制（MVCC）
基于鎖的并發(fā)控制（LBCC）
所謂封鎖就是事務(wù)在某個數(shù)據(jù)對象進行操作之前先申請鎖，對該對象加鎖后，該事務(wù)就擁有了一定的對該對象的控制，在該事務(wù)釋放該鎖前，其他事務(wù)不能操作此數(shù)據(jù)對象。
從鎖的模式來看，鎖可以分為共享鎖和排它鎖，共享鎖又稱為讀鎖（S 鎖），排它鎖又稱為寫鎖（X鎖）。
- X 鎖：若事務(wù) T 對數(shù)據(jù)對象 A 加上了 X 鎖，則只允許 T 讀取和修改 A， 其他任何事務(wù)不得再對 A加任何類型的鎖，直到 T 釋放鎖，。
- S 鎖：若事務(wù) T 對數(shù)據(jù)對象 A 加上了 S 鎖，則 T 和其他事務(wù)都可以可以讀 A，同時其他事務(wù)可以繼續(xù)申請 A 的 S 鎖，但是直到所有事務(wù)都釋放 A 的 S 鎖為止（所有事務(wù)并不包括自己），A 是不允許修改的。這就意味著如果只有一個事務(wù)對 A 添加了 S 鎖，那他自己是可以修改數(shù)據(jù)的。
封鎖協(xié)議
- 一級封鎖協(xié)議： 事務(wù) T 在修改數(shù)據(jù) R之前必須對其加 X 鎖，直到事務(wù)提交或回滾才釋放鎖，一級封鎖協(xié)議可以防止丟失修改，一級封鎖協(xié)議只在寫數(shù)據(jù)時加鎖，讀數(shù)據(jù)時并不需要獲取鎖，所以它無法解決臟讀，幻讀，不可重復(fù)讀。
- **二級封鎖協(xié)議：**要求在一級封鎖協(xié)議的基礎(chǔ)上，讀數(shù)據(jù)前必須加 S 鎖，讀完即可立刻釋放 S 鎖。因為對數(shù)據(jù) A 加 X 鎖后，不允許其他事務(wù)再申請其他鎖，所以事務(wù)要想獲得 S 鎖讀，就必須等持有 X 鎖的事務(wù)寫完提交或回滾，這樣就可以避免臟讀，但由于二級封鎖協(xié)議允許讀完后立刻釋放 S 鎖，無法保證下一次讀時數(shù)據(jù)不被修改，所以所以它不能保證可重復(fù)讀（包括幻讀）。
- **三級封鎖協(xié)議：**要求在一級封鎖協(xié)議的基礎(chǔ)上，讀數(shù)據(jù)前必須加 S 鎖，直到事務(wù)結(jié)束才釋放。該協(xié)議可以解決不可重復(fù)讀（包括幻讀）的問題。
死鎖，活鎖
死鎖和活鎖是使用 LBCC 解決一致性問題時必須考慮的問題：
- 活鎖：如果 T1 封鎖了 R，T2 請求 R 的鎖，這時 T2 應(yīng)該等待，然后 T3 也請求 R 并等待，這時 T1 釋放了 R 的鎖，但該鎖被 T3 獲得，在這過程中， T4 也請求 R， T3 釋放鎖后鎖又被 T4 獲得…… 這導(dǎo)致 T2 一直無法獲得鎖，這種某個事務(wù)陷入饑餓的狀態(tài)現(xiàn)象叫做活鎖，避免活鎖的簡單策略是先來先服務(wù)。
- 死鎖：如果 T1 封鎖了 R1, T2 封鎖了 R2, 然后 T1 又來申請 R2, T2 又來申請 R1, 這會造成 T1 T2 相互等待永遠無法結(jié)束的局面，形成死鎖。
預(yù)防死鎖
死鎖出現(xiàn)的原因是兩個事務(wù)都已經(jīng)封鎖了一些數(shù)據(jù)對象，然后都去請求已經(jīng)被對方鎖定的數(shù)據(jù)對象，預(yù)防死鎖就是要想辦法破壞死鎖產(chǎn)生的條件，通常使用一次封鎖法和順序封鎖法解決：
- 一次封鎖法：一個事務(wù)必須一次性將所有要使用的數(shù)據(jù)全部加鎖，否則就不能繼續(xù)執(zhí)行。但由于數(shù)據(jù)庫中的數(shù)據(jù)并不是一塵不變的，我們往往很難準確知道要用哪寫數(shù)據(jù)，為了實現(xiàn)一次封鎖，就不得不擴大封鎖范圍，將可能的數(shù)據(jù)全部加鎖，這會造成系統(tǒng)并發(fā)度降低，影響性能。
- 順序封鎖法：預(yù)先對數(shù)據(jù)對象規(guī)定一個封鎖順序，所有事物按這個順序?qū)嵤┓怄i，但這樣同樣存在問題：
- 數(shù)據(jù)庫中的數(shù)據(jù)對象很多，要維護這么多對象的封鎖順序并不容易。
- 誰應(yīng)該先被封鎖，誰應(yīng)該后被封鎖，關(guān)于這個順序很難得到一個最優(yōu)解。
解除死鎖
上面?zhèn)€兩種預(yù)防死鎖的辦法在現(xiàn)實中可行性很低，所以DBS通常采用診斷并解除死鎖的辦法解決死鎖問題。
診斷死鎖：
解除死鎖：
- 發(fā)現(xiàn)死鎖后，我們往往會選擇一個處理死鎖代價最小的事務(wù)，將其撤銷以釋放他持有的所有鎖，當(dāng)然，對撤銷事務(wù)的數(shù)據(jù)修改操作必須加以修復(fù)。
可串行化調(diào)度和兩段鎖協(xié)議
可串行化調(diào)度：
- 如果多個事務(wù)并發(fā)執(zhí)行的結(jié)果和按某一次序串行執(zhí)行這些事務(wù)的結(jié)果是一樣的，就說這種調(diào)度是可串行化調(diào)度，他是并發(fā)事務(wù)正確調(diào)度的準則。
兩段鎖協(xié)議（2PL）：
- 兩段鎖協(xié)議用來保證調(diào)度是可串行化的
- 兩段鎖協(xié)議是指所有事務(wù)必須分兩個階段對數(shù)據(jù)項進行加鎖和解鎖操作。
- 在對一個數(shù)據(jù)項進行讀寫操作前，必須申請并獲得該數(shù)據(jù)項的封鎖。（拓展階段）
- 釋放完一個封鎖后，事務(wù)不再去申請或獲取任何其他鎖。（收縮階段）
封鎖粒度
加鎖時，你可以選擇對一個屬性值，關(guān)系，索引項，整個索引甚至整個數(shù)據(jù)庫加鎖，加鎖對象的大小叫做鎖的粒度，一般來說，粒度越大，并發(fā)度越小，系統(tǒng)開銷也越小，封鎖粒度越小，并發(fā)度越高，系統(tǒng)開銷也就越大。一個 DBS 應(yīng)該盡可能兼顧并發(fā)度和系統(tǒng)開銷，這樣顯然不能只支持某一粒度的封鎖，這種提供多種封鎖粒度供不同事務(wù)選擇的封鎖方法叫做多粒度封鎖。
多粒度封鎖可以由多粒度樹描述，如下：
多粒度樹的根節(jié)點是整個數(shù)據(jù)庫，表示最大的數(shù)據(jù)粒度，葉子節(jié)點表示最小的封鎖粒度，多粒度封鎖協(xié)議 允許粒度樹的每一個節(jié)點可以被單獨加鎖，某一個節(jié)點加鎖意味著該節(jié)點的所有子節(jié)點也被加同樣的鎖，對該節(jié)點來說，這個鎖屬于顯式加鎖，對于其子節(jié)點來說，屬于隱式加鎖，他們的效果是一樣的。
顯式和隱式加鎖看起來是理所當(dāng)然的，但這會導(dǎo)致一個問題，在對某一數(shù)據(jù)項加鎖時，我們必須保證當(dāng)前要加的這把鎖與其顯示假的鎖不沖突，同時還要保證與其隱式假的鎖不沖突，為此，在加鎖前，我們必須要：
這樣一來，每次加鎖我們不得不遍歷整個粒度樹，這種效率是非常低下的，為此，我們引入了一種新鎖：意向鎖
意向鎖用于提升加鎖效率，無法手動創(chuàng)建，它的含義是如果對一個節(jié)點加意向鎖，則說明該節(jié)點的下層節(jié)點正在被加鎖，對任意節(jié)點加鎖時，必須先對它的上層節(jié)點加意向鎖。有三種常用的意向鎖，他們分別是：意向共享鎖（IS 鎖），意向排它鎖（IX鎖），共享意向排它鎖（SIX鎖）：
- IS 鎖：表示其子節(jié)點準備加 S 鎖
- IX 鎖：表示其子節(jié)點準備加 X 鎖
- SIX 鎖：如果對一個數(shù)據(jù)對象加 SIX 鎖，表示對他加 S 鎖，在家 IX 鎖，例如對某表加 SIX 鎖，表示該事務(wù)要通過 S 鎖讀整個表，同時還要更新個別元組（IX鎖）。
根據(jù)上面的描述，我們可以得出以下的鎖強度偏序關(guān)系圖和數(shù)據(jù)鎖的相容矩陣：
鎖強度表示一個鎖對其他鎖的排斥程度
| X | F | F | F | F | F | T |
| SIX | F | T | T | F | T | T |
| IX | F | T | T | F | F | T |
| S | F | F | F | T | T | T |
| IS | F | T | T | T | T | T |
| - | T | T | T | T | T | T |
規(guī)律：對于意向鎖來說使用強度更高的鎖來替換強度低的鎖是安全的。
意向鎖如何提高加鎖效率？
如果某一時刻，數(shù)據(jù)庫中數(shù)據(jù)對象的鎖持有情況如下圖粒度樹所示，對表 T1 加了 S 鎖，其父節(jié)點對應(yīng)加了 IS 鎖，T1 的子節(jié)點也隱式獲得了 S 鎖（當(dāng)然所有節(jié)點也隱式獲得了 IS 鎖，所有隱式鎖都未畫出）
如果現(xiàn)在我們希望更新記錄 R1，根據(jù)封鎖協(xié)議，就必須對 R1 添加 X 鎖，對其父節(jié)點加 IX 鎖，這時只需要檢查 T1 和 DB 的鎖是否與之不相容，T1 持有 S 鎖，與 X 鎖不相容，調(diào)度器會阻止加鎖。
相反，如果我們只是想對 R1 加 S 鎖進行讀操作，就需要先對父節(jié)點加 IS 鎖，T1 持有 S 鎖，與 IS 鎖相容，再檢查 R1 持有的鎖是否與 S 鎖相容，相容，允許加鎖。
加入意向鎖后，我們不需要再去遍歷所有子節(jié)點便可以判斷能否未數(shù)據(jù)項加鎖，可以提高系統(tǒng)并發(fā)度，減少加鎖解鎖開銷。
多版本并發(fā)控制（MVCC）
通過 LBCC， 我們可以解決所有的并發(fā)不一致問題，那為什么還會有其他并發(fā)控制方案呢？歸根結(jié)底還是基于性能的考慮， LBCC 只是實現(xiàn)了允許并發(fā)讀，但對于并發(fā)讀寫，寫寫操作只能串行執(zhí)行，在讀寫都很頻繁的場景下，并發(fā)性能將大大降低，因此，人們才提出各種無鎖并發(fā)控制方案，MVCC 就屬于其中一種。
MVCC 的大概思路是每一個事務(wù)都有一個唯一的ID，當(dāng)某一個事務(wù)要修改某行數(shù)據(jù)時，先將這一行原來的數(shù)據(jù)做一個快照保存下來，當(dāng)有其他并發(fā)事務(wù)也要操作這個事務(wù)時，可以操作之前的版本，這樣，最新的版本只被寫事務(wù)維持，不會干擾到讀事務(wù)，以此實現(xiàn)隔離，MVCC 并沒有一個統(tǒng)一的標準，不同 DBS 的實現(xiàn)也不盡相同，下面以 MySQL InnoDB 引擎為例說一下 MVCC 的一個具體流程。
InnoDB 中的 MVCC
既然是 MVCC， 那最重要的就是舊版本的數(shù)據(jù)要存在，在 MySQL InnoDB 中，這些數(shù)據(jù)會以 回滾段 （rollback segment）的形式保存在表空間中，更具體來說，他們會被保存在 undo log 中。
其次，InnoDB 會在所有表中加兩個隱藏列 DB_TRX_ID 和 DB_ROLL_PTR，前者占 6 字節(jié)，表示插入或更新該行的最后一個事務(wù)的事務(wù)標識符；后者占 7 個字節(jié)，稱為回滾指針，指向回滾段的 undo log 記錄。
InnoDB 插入的隱藏列還有一個 DB_ROW_ID，會隨著新行的插入會單調(diào)遞增，如果使用了默認自增ID的聚簇索引，索引中就會包含這個列。
每當(dāng)我們插入或更新一行數(shù)據(jù)（刪除被認為是更新的一種），InnoDB 會為這個事務(wù)分配一個唯一單調(diào)遞增的事務(wù)ID，這個 ID會記錄在這一行的 DB_TRX_ID 中，表示這一行數(shù)據(jù)的最新版本。
如下圖，設(shè)有一張表 t, 包含兩個字段 id 和 name, 它的初始狀態(tài)如下：
表示最后插入或修改這條記錄的事務(wù) ID 是 100，現(xiàn)在如果有一個新的事務(wù)要修改這條記錄，設(shè)其 ID 為 200，則現(xiàn)在表的狀態(tài)就會變成下面這樣：
灰色行表示歷史版本，被記錄在 undo log 中，從最新版本的回滾指針可以找到這條記錄的歷史版本，這條鏈表被叫做版本鏈，
除此之外，當(dāng)一個事務(wù)第一次執(zhí)行讀操作時，會為該事務(wù)生成一個一致性視圖 read-view, 這個數(shù)據(jù)結(jié)構(gòu)包含此刻所有活躍著的（未提交的）寫事務(wù)的事務(wù)ID列表和此時DBS分配出去的最大事務(wù)號，有了 read-view 就可以判斷出哪寫事務(wù)是已經(jīng)提交了的，哪寫事務(wù)是未提交的，具體判斷邏輯如下：
從活躍事務(wù)列表中找到最小的事務(wù)ID，記為 min_t_id, 讀取到某一版本的事務(wù)ID記為 row_t_id, 系統(tǒng)分配的最大事務(wù)ID 記為 max_t_id
有了版本鏈和 read-view，一個事務(wù)就可以根據(jù) read-view 順著版本鏈依照上面的規(guī)則一直往下直到找到一個可見的版本，
以上面的例子為例，如果事務(wù)的并發(fā)時序圖如下：
當(dāng)事務(wù)二更新操作執(zhí)行后，版本鏈變?yōu)?#xff1a;
事務(wù)三第一次執(zhí)行查詢語句時，生成的 read-view 為：
| active list | max | +-------------------+--------+ | 200, 300 | 300 | +-------------------+--------+read view根據(jù) read-view 得到 max_t_id = 300, min_t_id = 200, 從最新版本開始遍歷，
如下圖，如果在第五時刻，事務(wù) 2 提交，第六時刻事務(wù)三再次讀取：
版本鏈并沒有發(fā)生改變，但如果在 RC 的隔離界別下，事務(wù)三的第二條查詢語句會重新生成 read-view, 這時活躍事務(wù)只有 200，最大事務(wù)為 300，按上面的規(guī)則，row_t_id = 300 時，滿足條件三但不在活躍事務(wù)列表中，所以這條記錄是可見的，這就會讀出 wangwu, 導(dǎo)致不可重復(fù)讀。
但如果使用 InnoDB 默認的 RR 隔離界別，read-view 只會在事務(wù)執(zhí)行第一條查詢語句時生成，后續(xù)所有查詢語句使用同一個 read-view, 由此避免不可重復(fù)讀。
一些其他問題
InnoDB 中的鎖
在上面我們介紹了 LBCC，這里再簡單介紹一下 InnoDB 中幾個具體的鎖，他們以鎖粒度劃分。
記錄鎖
Record Locks，也叫行鎖，加在索引記錄上的鎖。例如，SELECT c1 FROM t WHERE c1 = 10 For UPDATE, 防止任何其他事務(wù)插入、更新或刪除 t.c1 值為10的行, 行鎖加在索引上而不是記錄上，因為innodb一定會有一個聚簇索引，因此最終的行鎖都會落到聚簇索引上。
間隙鎖
gap Locks, 是對索引記錄之間間隙加的鎖，或者是對第一個索引記錄之前或最后一個索引記錄之后的間隙的鎖。例如：
SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE這樣可以防止其他事務(wù)插入 c1 值在 10 - 20 之間的數(shù)據(jù)。
這里的間隙可以是兩個索引值之間的間隙，也可以是從無窮到單個索引值之間的間隙
使用唯一索引檢索唯一行時不會使用間隙鎖，但是如果檢索條件僅包含多列唯一索引的一些列時，仍然會使用間隙鎖鎖定，例如一個簡單的 SELECT 語句：
SELECT * FROM child WHERE id = 100;如果 id 使用唯一索引，則該語句只會加記錄鎖。
但如果 id 未建立索引或建立了非唯一索引，那么從負無窮到100的間隙將會被間隙鎖鎖定。
還有一個需要值得注意的問題，在一個間隙上，不同的事務(wù)可以持有相互沖突的鎖，這是因為如果某條記錄被從索引中清除，那我們必須合并由不同事務(wù)保存在記錄上的間隙鎖。所以說間隙鎖的唯一目的是為了防止記錄被插入間隙， 一個事務(wù)進行的間隙鎖定不會阻止另一事務(wù)對相同的間隙進行間隙鎖定。共享和專用間隙鎖之間沒有區(qū)別。 它們彼此不沖突，并且執(zhí)行相同的功能。
間隙鎖定可以顯式禁用。 如果將事務(wù)隔離級別更改為READ COMMITTED或啟用innodb_locks_unsafe_for_binlog系統(tǒng)變量（現(xiàn)已棄用），則會發(fā)生這種情況。 在這種情況下，將禁用間隙鎖定來進行搜索和索引掃描，并且間隙鎖定僅用于外鍵約束檢查和重復(fù)鍵檢查。
使用 READ COMMITTED 隔離級別或啟用innodb_locks_unsafe_for_binlog還有其他效果。MySQL 在計算完 WHERE 條件后，將釋放不匹配行的記錄鎖。 對于 UPDATE 語句，InnoDB 進行“半一致”讀取，以便將最新的提交版本返回給 MySQL，以便MySQL可以確定該行是否與UPDATE的WHERE條件匹配。
間隙鎖總結(jié)
臨鍵鎖
臨鍵鎖是間隙鎖和記錄鎖的結(jié)合，臨鍵鎖使得一個會話如果在某個索引記錄上建立了共享或排它鎖，其他會話不能在該索引記錄前面的間隙插入數(shù)據(jù)。
假設(shè)某個表的索引包含值10，11，13，20，則其臨鍵鎖可能包含以下間隔：
其實只需要記住臨鍵鎖鎖定的是一個左開右閉的區(qū)間即可
臨鍵鎖總結(jié)
插入意向鎖
insert intention lock， 是在插入新的記錄之前通過 INSERT 操作設(shè)置的一種間隙鎖，該鎖以這樣一種方式發(fā)出插入意圖的信號，即如果多個事務(wù)要插入的數(shù)據(jù)在同一間隙內(nèi)但不是相同的位置，那這些事務(wù)就不需要相互等待。比如假設(shè)有值為4和7的索引記錄。嘗試分別插入值5和6的單獨事務(wù)在獲得插入行上的獨占鎖之前，每個事務(wù)都使用插入意圖鎖鎖定4和7之間的間隙，但不會互相阻塞，因為行不沖突。
自增鎖
AUTO-INC鎖是一種特殊的表級鎖，如果一個表中有 AUTO_INCREMENT列，則要插入該表的事務(wù)在插入之前會先獲取該鎖，該鎖是表級鎖，但不是事務(wù)級鎖，插入語句執(zhí)行完后就會立刻釋放，不會等待事務(wù)提交才釋放。自增鎖也具有不同的模式，可以使用 innodb_autoinc_lock_mode 選項（0， 1， 2）控制自增鎖遞增算法，以謀求效率和安全性的要求.
在默認狀態(tài)下，該選項設(shè)置為 1，在該模式下：
- “批量插入”使用特殊的AUTO-INC表級鎖并將其保持到語句結(jié)束。 這適用于所有INSERT … SELECT，REPLACE … SELECT和LOAD DATA語句。 一次只能執(zhí)行一條持有AUTO-INC鎖的語句。 如果批量插入操作的源表與目標表不同，則在對源表中選擇的第一行進行共享鎖之后，將對目標表執(zhí)行AUTO-INC鎖。 如果批量插入操作的源和目標是同一表，則在對所有選定行進行共享鎖之后，將獲取AUTO-INC鎖。
- “簡單插入”（預(yù)先知道要插入的行數(shù)）通過在互斥量（輕型鎖）的控制下獲得所需數(shù)量的自動增量值來避免表級AUTO-INC鎖定 僅在分配過程的整個過程中才保留，直到語句完成為止。 除非另一個事務(wù)持有AUTO-INC鎖，否則不使用表級AUTO-INC鎖。 如果另一個事務(wù)持有AUTO-INC鎖，則“簡單插入”將等待AUTO-INC鎖，就好像它是“批量插入”一樣。
- 混合模式插入”，如果用戶為多行“簡單插入”中的某些行 (但不是所有行) 的AUTO_INCREMENT列提供顯式值，InnoDB分配的自動增量值會多于要插入的行數(shù)。但是，自動分配的所有值都是由最近執(zhí)行的上一條語句生成的自動增量值連續(xù)生成的，因此“多余的”號碼就會丟失。
其他模式可以參考 官方文檔
關(guān)于幻讀
網(wǎng)上對幻讀的定義各種各樣，有人把幻讀囊括在不可重復(fù)中（比如我們的教材），有人說對某一范圍的數(shù)據(jù)執(zhí)行刪除或插入會導(dǎo)致幻讀，有人說只有插入導(dǎo)致的才叫幻讀，實際上在 SQL 92 標準里明確定義了什么是幻讀：
P3 ("Phantom"): SQL-transaction T1 reads the set of rows N that satisfy some <search condition>. SQL-transaction T2 then executes SQL-statements that generate one or more rows that satisfy the <search condition> used by SQL-transaction T1. If SQL-transaction T1 then repeats the initial read with the same <search condition>, it obtains a different collection of rows.當(dāng)一個事務(wù) T1 讀到滿足某些條件的行集合后，事務(wù) T2 向表中插入了滿足這些條件的一行或多行數(shù)據(jù)，如果 T1 使用相同的條件重復(fù)讀取，它將得到不同的結(jié)果，這叫幻讀，而對于刪除的情況，92 標準也明確說了這屬于不可重復(fù)讀。
P2 ("Non-repeatable read"): SQL-transaction T1 reads a row. SQL-transaction T2 then modifies or deletes that row and performs a COMMIT. If T1 then attempts to reread the row, it may receive the modified value or discover that the row has been deleted. P2(“不可重復(fù)讀取”)：SQL-Transaction T1讀取一行。然后，SQL-Transaction T2修改或刪除該行并執(zhí)行提交。如果T1隨后嘗試重新讀取該行，它可能會收到修改后的值或發(fā)現(xiàn)該行已被刪除。在 InnoDB 的文檔中，也可以看見：
Also, a deletion is treated internally as an update where a special bit in the row is set to mark it as deleted.
所以對于 MVCC 解決不可重復(fù)讀中刪除情況的問題和修改其實是一樣的。
還有一個問題是 InnoDB 的 RR 隔離級別有沒有解決幻讀的問題，這在文檔里面也清晰的寫了：解決了，解決方式就是上面的臨鍵鎖。
關(guān)于丟失修改
上面說到并發(fā)導(dǎo)致的問題時提到了兩類丟失修改的問題：提交覆蓋和回滾覆蓋，但似乎在平時說到并發(fā)問題時大家只提臟讀幻讀不可重復(fù)讀，在 SQL 92 標準里也沒有發(fā)現(xiàn)對所謂丟失修改問題的描述，事實上，這兩種情況都是在讀的結(jié)果上進行了修改，對于回滾覆蓋，InnoDB 的任何隔離級別下他都不會發(fā)生，因為回滾到的是針對數(shù)據(jù)庫的上一個已提交的版本，而不是針對該事務(wù)的，而對于提交覆蓋，我們完全可以在應(yīng)用程序?qū)用媸褂弥T如 CAS 等技術(shù)手段避免這類問題，網(wǎng)上也有使用悲觀鎖或樂觀鎖避免的方案，可以參考：事務(wù)的隔離級別以及Mysql事務(wù)的使用
關(guān)于 InnoDB 中的死鎖
在 LBCC 那一節(jié)也簡單說了一下死鎖和活鎖，這里再詳細了解一下 MySQL InnoDB 的死鎖問題。先看一個官方文檔給出的死鎖的例子：
CREATE TABLE t (i INT) ENGINE = InnoDB; INSERT INTO t (i) VALUES(1);事務(wù)一先對數(shù)據(jù)加 S 鎖執(zhí)行讀操作(未提交)：
START TRANSACTION; SELECT * FROM t WHERE i = 1 LOCK IN SHARE MODE;然后再開啟一個事務(wù)二嘗試刪除該記錄：
START TRANSACTION; DELETE FROM t WHERE i = 1;由于刪除會對數(shù)據(jù)加 X 鎖，這與事務(wù)一加的 S 鎖并不相容，所以事務(wù)二會等待事務(wù)一釋放 S 鎖，像下面這樣：
而等待一段時間后，會提示獲取鎖超時并終止事務(wù)：
DELETE FROM t WHERE i = 1 > 1205 - Lock wait timeout exceeded; try restarting transaction > 時間: 51.473s這樣并不存在死鎖的問題，但如果在事務(wù)二等待的期間，事務(wù)一執(zhí)行了對這條記錄的刪除操作：
DELETE FROM t WHERE i = 1;就會發(fā)生死鎖：
DELETE FROM t WHERE i = 1 > 1213 - Deadlock found when trying to get lock; try restarting transaction > 時間: 12.216s原因是事務(wù)一如果想要刪除就必須要一個 X 鎖，但 X 鎖已經(jīng)被事務(wù)二請求了，不能授予事務(wù)一，且由于事務(wù)二事先請求 X 鎖，事務(wù)一持有的 S 鎖也不能升級為 X 鎖，這就會導(dǎo)致死鎖，出現(xiàn)這個問題時，InnoDB 會回滾一個小事務(wù)（事務(wù)的大小由插入、更新或刪除的行數(shù)決定）并拋出上面的錯誤來解除死鎖。
在高并發(fā)系統(tǒng)上，當(dāng)多個線程等待相同的鎖時，死鎖檢測會導(dǎo)致速度變慢。 所以有時當(dāng)發(fā)生死鎖時，禁用死鎖檢測而依靠innodb_lock_wait_timeout 設(shè)置進行事務(wù)回滾可能會更有效。 可以使用 innodb_deadlock_detect 配置選項禁用死鎖檢測。
InnoDB使用自動行級鎖定。即使在僅插入或刪除單行的事務(wù)中，也可能會遇到死鎖。這是因為這些操作并不是真正的“原子”操作；它們會自動對插入或刪除的行的（可能是多個）索引記錄設(shè)置鎖定。
您可以使用以下技術(shù)來處理死鎖并減少發(fā)生死鎖的可能性：
-
使用 SHOW ENGINE INNODB STATUS命令以確定最近死鎖的原因。這可以幫助您調(diào)整應(yīng)用程序以避免死鎖。
-
如果頻繁出現(xiàn)死鎖警告引，請通過啟用innodb_print_all_deadlocks 配置選項來收集更廣泛的調(diào)試信息 。有關(guān)每個死鎖的信息，而不僅僅是最新的死鎖，都記錄在MySQL error log 中。完成調(diào)試后，請禁用此選項。
-
如果事務(wù)由于死鎖而失敗，在任何時候，請重試一遍，死鎖并不可怕。
-
請保持插入或更新事務(wù)足夠小，避免鎖被一個事務(wù)長時間占用，以此減少沖突概率。
-
進行一系列相關(guān)更改后立即提交事務(wù)，以減少沖突的發(fā)生。特別是，不要長時間未提交事務(wù)而使交互式 mysql會話保持打開狀態(tài)。
-
如果您使用鎖定讀取（SELECT ... FOR UPDATE或 SELECT ... LOCK IN SHARE MODE），請嘗試使用較低的隔離級別，例如 READ COMMITTED。
-
在事務(wù)中修改處于多個表或同一表中的不同行集時，每次都要以一致的順序去執(zhí)行這些操作。這樣事務(wù)會形成定義明確的隊列而不會導(dǎo)致死鎖。例如，將數(shù)據(jù)庫操作組織到應(yīng)用程序內(nèi)的函數(shù)中，而不是在不同位置編碼多個類似的INSERT，UPDATE和DELETE語句序列。
-
對表中的數(shù)據(jù)建立合適索引，這樣您的查詢將會使用更少的索引記錄，同樣也會使用更少的鎖。使用EXPLAIN SELECT以確定MySQL認為哪些索引最適合您的查詢。
-
如果可以，請盡量少的使用鎖，以允許 SELECT從一個舊的快照返回數(shù)據(jù)，不要添加條款FOR UPDATE或LOCK IN SHARE MODE給它。在READ COMMITTED這里使用隔離級別是件好事，因為同一事務(wù)中的每個一致性讀取均從其自己的新快照讀取。
-
如果沒有其他辦法，可以使用表級鎖序列化事務(wù)。對事務(wù)表（例如InnoDB表）使用LOCK TABLES的正確方法是：SET autocommit = 0（not START TRANSACTION）后跟來開始事務(wù)，直到明確提交事務(wù)后才對LOCK TABLES調(diào)用 UNLOCK TABLES。例如，如果您需要寫表 t1和從表中讀取數(shù)據(jù) t2，則可以執(zhí)行以下操作：
SET autocommit=0; LOCK TABLES t1 WRITE, t2 READ, ...; ... do something with tables t1 and t2 here ... COMMIT; UNLOCK TABLES;表級鎖可防止對表的并發(fā)更新，從而避免死鎖，但代價是對繁忙系統(tǒng)的響應(yīng)速度較慢。
-
序列化事務(wù)的另一種方法是創(chuàng)建一個僅包含一行的輔助“信號量”表。 在訪問其他表之前，讓每個事務(wù)更新該行。 這樣，所有事務(wù)都以串行方式發(fā)生。 注意，在這種情況下，InnoDB即時死鎖檢測算法也適用，因為序列化鎖是行級鎖。 對于MySQL表級鎖，必須使用超時方法來解決死鎖。
參考
MySQL 官方文檔 innodb-multi-versioning
美團技術(shù)文章 - Innodb中的事務(wù)隔離級別和鎖的關(guān)系
解決死鎖之路 - 學(xué)習(xí)事務(wù)與隔離級別
總結(jié)
以上是生活随笔為你收集整理的【数据库】一篇文章搞懂数据库隔离级别那些事（LBCC,MVCC）的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： Mac版本QQ消息防撤回
- 下一篇： Tomcat7与Tomcat8的差异详细