前言

上一篇文章講解了MySQL的事務(wù)的相關(guān)概念MySQL的事務(wù)特性概念梳理總結(jié)

文章末尾提出了事務(wù)因并發(fā)出現(xiàn)的問題有哪些？
本篇將著重講述這個問題的前因后果及解決方式。

事務(wù)因并發(fā)出現(xiàn)的問題有哪些 臟讀

概念：一個事務(wù)讀取到其他事務(wù)未提交的數(shù)據(jù)。
用一個圖來講解，在并發(fā)環(huán)境下，多個事務(wù)操作同一對象帶來的問題：

不可重復(fù)讀

概念：一個事務(wù)在一個時間段內(nèi) 前后讀取的數(shù)據(jù)不一致，或者出現(xiàn)了修改/刪除。

幻讀

概念：事務(wù)A 按照查詢條件讀取某個范圍的記錄，其他事務(wù)又在該范圍內(nèi)出入了滿足條件的新記錄，當(dāng)事務(wù)A再次讀取數(shù)據(jù)到時候我們發(fā)現(xiàn)多了滿足記錄的條數(shù)（幻行）

建議大家把幻讀記作幻行，以免和不可重復(fù)讀記混淆

不可重復(fù)讀與幻讀的區(qū)別

前提：兩者都是讀取到已經(jīng)提交的數(shù)據(jù)

不可重復(fù)讀：重點是在于修改，在一個事務(wù)中，同樣的條件，第一次讀取的數(shù)據(jù)與第二次【數(shù)據(jù)不一樣】（因為中間有其他事務(wù)對這個數(shù)據(jù)進行了修改）
幻讀：重點在于新增或者刪除，在一個事務(wù)中，同樣的條件（范圍），第一次讀取和第二讀取【記錄條數(shù)不一樣】（因為中間有其他事務(wù)在這個范圍里插入、刪除了的數(shù)據(jù)）

我們現(xiàn)在已經(jīng)知道，原來事務(wù)并發(fā)會出現(xiàn)，臟讀，不可重復(fù)讀，幻讀的問題。
那這些問題我們都是需要去解決的，怎么解決呢？
有興趣可以看看官網(wǎng)是怎么解釋的
鏈接: 官網(wǎng)地址

事務(wù)并發(fā)的三大問題其實都是數(shù)據(jù)庫讀一致性問題，必須由數(shù)據(jù)庫提供一定的事務(wù)隔離機制來解決。

事務(wù)的四個隔離級別

我們通過事務(wù)的隔離級別來解決不同的問題，那么，不同的隔離級別解決了什么問題呢？

其實sql標(biāo)準(zhǔn)92版 官方都有定義出來

另外，sql標(biāo)準(zhǔn)不是數(shù)據(jù)庫廠商定義出來的，大家不要以為sql語言是什么mysql，sqlserver搞出來的，我們會發(fā)現(xiàn)每個數(shù)據(jù)庫語句的sql語句都是差不多的。sql是獨立于廠商的??！SQL是Structured Query Language的縮寫，本來就屬于一種查詢語言！！

官網(wǎng)支持四種隔離級別：

# 修改當(dāng)前會話的隔離級別
# 讀未提交
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
# 讀已提交
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
# 可重復(fù)讀
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
# 串行化
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

我們也可以通過SQL去查詢當(dāng)前的隔離級別

SHOW GLOBAL VARIABLES LIKE '%isolation%'; //全局隔離級別
SHOW SESSION VARIABLES LIKE '%isolation%';
set SESSION autocommit=0; //關(guān)閉自動提交

InnoDB默認的隔離級別是RR

事務(wù)隔離級別越高，多個事務(wù)在并發(fā)訪問數(shù)據(jù)庫時互相產(chǎn)生數(shù)據(jù)干擾的可能性越低，但是并發(fā)訪問的性能就越差。（相當(dāng)于犧牲了一定的性能去保證數(shù)據(jù)的安全性）

Read UnCommited 讀未提交 RU

多個事務(wù)同時修改一條記錄，A事務(wù)對其的改動在A事務(wù)還沒提交時，在B事務(wù)中就可以看到A事務(wù)對其的改動。

結(jié)論：沒有解決任何問題，存在臟讀，因為他就是讀取最新的數(shù)據(jù)。

Read Commited 讀已提交 RC

多個事務(wù)同時修改一條記錄，A事務(wù)對其的改動在A事務(wù)提交之后，在B事務(wù)中可以看到A事務(wù)對其的改動。

結(jié)論：我就讀取你已經(jīng)提交的事務(wù)就完事，解決臟讀。

Repeatable Read 可重復(fù)讀 RR

多個事務(wù)同時修改一條記錄，這條記錄在A事務(wù)執(zhí)行期間是不變的（別的事務(wù)對這條記錄的修改不被A事務(wù)感知）。

結(jié)論：RR級別解決了臟讀、不可重復(fù)讀、幻讀的問題。

Serializable 串行化

多個事務(wù)同時訪問一條記錄（CRUD），讀加讀鎖，寫加寫鎖，完全退化成了串行的訪問，自然不會收到任何其他事務(wù)的干擾，性能最低。

結(jié)論：加鎖排隊讀取，性能最低。

可以看出，RU與串行化都沒啥實用意義，主要還是看RC和RR，那么Mysql是怎么實現(xiàn)這兩種隔離級別的呢？
我們要先學(xué)習(xí)Mysql的兩種機制，undo 版本鏈機制以及read view快照讀機制，讀已提交和可重復(fù)讀隔離級別的實現(xiàn)都是建立在這兩個核心機制之上。

undo 版本鏈

undo 版本鏈就是指undo log的存儲在邏輯上的表現(xiàn)形式，它被用于事務(wù)當(dāng)中的回滾操作以及實現(xiàn)MVCC，這里介紹一下undo log之所以能實現(xiàn)回滾記錄的原理。

對于每一行記錄，會有兩個隱藏字段：row_trx_id和roll_pointer
row_trx_id表示更新（改動）本條記錄的全局事務(wù)id （每個事務(wù)創(chuàng)建都會分配id，全局遞增，因此事務(wù)id區(qū)別對某條記錄的修改是由哪個事務(wù)作出的）
roll_pointer是回滾指針，指向當(dāng)前記錄的前一個undo log版本，如果是第一個版本則roll_pointer指向null，這樣如果有多個事務(wù)對同一條記錄進行了多次改動，則會在undo log中以鏈的形式存儲改動過程。

在上圖中，最下方的undo log中記錄了當(dāng)前行的最新版本，而該條記錄之前的版本則以版本鏈的形式可追溯，這也是事務(wù)回滾所做的事。那undo log版本鏈和事務(wù)的隔離性有什么關(guān)系呢？那就要引入另一個核心機制：read view。

read view

read view表示讀視圖，這個快照讀會記錄四個關(guān)鍵的屬性：

• create_trx_id: 當(dāng)前事務(wù)的
• idm_idx: 當(dāng)前正在活躍的所有事務(wù)id（id數(shù)組），沒有提交的事務(wù)的
• idmin_trx_id: 當(dāng)前系統(tǒng)中活躍的事務(wù)的id最小值
• max_trx_id: 當(dāng)前系統(tǒng)中已經(jīng)創(chuàng)建過的最新事務(wù)（id最大）的id+1的值

當(dāng)一個事務(wù)讀取某條記錄時會追溯undo log版本鏈，找到第一個可以訪問的版本，而該記錄的某一個版本是否能被這個事務(wù)讀取到遵循如下規(guī)則：

（這個規(guī)則永遠成立，這個需要好好理解，對后面講解可重復(fù)讀和讀已提交兩個級別的實現(xiàn)密切相關(guān)）

• 如果當(dāng)前記錄行的row_trx_id小于min_trx_id，表示該版本的記錄在當(dāng)前事務(wù)開啟之前創(chuàng)建，因此可以訪問到
• 如果當(dāng)前記錄行的row_trx_id大于等于max_trx_id，表示該版本的記錄創(chuàng)建晚于當(dāng)前活躍的事務(wù)，因此不能訪問到
• 如果當(dāng)前記錄行的row_trx_id大于等于min_trx_id且小于max_trx_id，則要分兩種情況：
  • 當(dāng)前記錄行的row_trx_id在m_idx數(shù)組中，則當(dāng)前事務(wù)無法訪問到這個版本的記錄 （除非這個版本的row_trx_id等于當(dāng)前事務(wù)本身的trx_id，本事務(wù)當(dāng)然能訪問自己修改的記錄） ，在m_idx數(shù)組中又不是當(dāng)前事務(wù)自己創(chuàng)建的undo版本，表示是并發(fā)訪問的其他事務(wù)對這條記錄的修改的結(jié)果，則不能訪問到。
  • 當(dāng)前記錄行的row_trx_id不在m_idx數(shù)組中，則表示這個版本是當(dāng)前事務(wù)開啟之前，其他事務(wù)已經(jīng)提交了的undo版本，當(dāng)前事務(wù)可訪問到。

RR中 Read View是事務(wù)第一次查詢的時候建立的。RC的Read View是事務(wù)每次查詢的時候建立的。

Oracle、Postgres等等其他數(shù)據(jù)庫都有MVCC的實現(xiàn)。

需要注意，在InnoDB中，MVCC和鎖是協(xié)同使用的，這兩種方案并不是互斥的。

配合使用read view和undo log版本鏈就能實現(xiàn)事務(wù)之間并發(fā)訪問相同記錄時，可以根據(jù)事務(wù)id不同，獲取同一行的不同undo log版本（多版本并發(fā)控制）。

MVCC（Multi-Version Concurrent Control ）多版本并發(fā)控制

多版本并發(fā)控制，是什么意思呢？版本控制，我們在進行查詢的時候是有版本的，后續(xù)在同一個事務(wù)里查詢的時候，我們都是使用我們當(dāng)初創(chuàng)建的快照版本。
比如說嘛，快照，你10歲20歲30歲40歲去照相，你只能看到你之前照相的模樣，但是不能看到你未來的模樣。

MVCC怎么去實現(xiàn)？
每個事務(wù)都有一個事務(wù)ID，并且是遞增，我們后續(xù)MVCC的原理都是基于它去完成。
效果：建立一個快照，同一個事務(wù)無論查詢多少次都是相同的數(shù)據(jù)。

一個事務(wù)能看見的版本：

• 第一次查詢之前已經(jīng)提交的版本
• 本事務(wù)的修改

一個事務(wù)不能看見的版本：

• 在本事務(wù)第一次查詢之后創(chuàng)建的事務(wù)（事務(wù)ID比我大）
• 活躍中的（未提交）的時候的修改。

下面通過模擬并發(fā)訪問的兩個事務(wù)操作，介紹MVCC的實現(xiàn)（具體來說就是可重復(fù)讀和讀已提交兩個隔離級別的實現(xiàn)）

可重復(fù)讀實現(xiàn)

下面模擬兩個并發(fā)訪問同一條記錄的事務(wù)AB的行為，假設(shè)這條記錄初始時id=1，a=0，該記錄兩個隱藏字段row_trx_id = 100，roll_pointer = null
注意：在可重復(fù)讀隔離級別下，當(dāng)事務(wù)sql執(zhí)行的時候，會生成一個read view快照，且在本事務(wù)周期內(nèi)一直使用這個read view，下面給出了并發(fā)訪問同一條記錄的兩個事務(wù)AB的具體執(zhí)行過程，并解釋可重復(fù)讀是如何實現(xiàn)的（解決了臟讀和不可重復(fù)讀）。

事務(wù)A的read view：

create_trx_id = 101｜ m_idx = [101, 102]｜min_trx_id = 101｜max_trx_id = 103

事務(wù)B的read view：

create_trx_id = 102｜ m_idx = [101, 102]｜min_trx_id = 101｜max_trx_id = 103

（ps. 這里因為AB事務(wù)是并發(fā)執(zhí)行，因此兩個事務(wù)創(chuàng)建的read view的max_trx_id = 103）

這里要注意的是，每次對一條記錄發(fā)生修改，就會記錄一個undo log的版本，則在A事務(wù)中第二次查詢id=1的記錄的a的值的時候，B事務(wù)對該記錄的修改已經(jīng)添加到版本鏈上了，此時這個undo log的trx_id = 102，在A事務(wù)的read view的m_idx數(shù)組中且不等于A事務(wù)的trx_id = 101，因此無法訪問到，需要在向前回溯，這里找到trx_id = 100的記錄版本（小于A事務(wù)read view的min_trx_id屬性，因此可以訪問到），故A事務(wù)第二次查詢依舊得到a = 0，而不是B事務(wù)修改的a = 1。

你可能有疑問，在A事務(wù)第二次查詢的時候，B事務(wù)已經(jīng)完成提交了，那么A事務(wù)的read view的m_idx數(shù)組應(yīng)該移除102才對啊，它存的不是當(dāng)前活躍的事務(wù)的id嗎？·

注意：在可重復(fù)讀隔離級別下，當(dāng)事務(wù)sql執(zhí)行的時候，會生成一個read view快照，且在本事務(wù)周期內(nèi)一直使用這個read view，雖然102確實應(yīng)該從A事務(wù)的read view中移除，但是因為read view在可重復(fù)讀隔離級別下只會在第一條SQL執(zhí)行時創(chuàng)建一次，并始終保持不變直到事務(wù)結(jié)束。

那么也就明白了，在可重復(fù)讀隔離級別下，因為read view只在第一條SQL執(zhí)行時創(chuàng)建，因此并發(fā)訪問的其他事務(wù)提交前改動的臟數(shù)據(jù)、以及并發(fā)訪問的其他事務(wù)提交的改動數(shù)據(jù)都對當(dāng)前事務(wù)是透明的（盡管確實是記錄在了undo log版本鏈中） ，這就解決了臟讀和不可重復(fù)讀（即使其他事務(wù)提交的修改，對A事務(wù)來說前后查詢結(jié)果相同）的問題！

讀已提交實現(xiàn)

還是借助上面事務(wù)處理的例子，所有的事務(wù)處理流程不變，只是將隔離級別調(diào)整為讀已提交，讀已提交依舊遵守read view和undo log版本鏈機制，它和可重復(fù)讀級別的區(qū)別在于，每次執(zhí)行sql，都會創(chuàng)建一個read view，獲取最新的事務(wù)快照。 而因為這個區(qū)別，讀已提交產(chǎn)生了不可重復(fù)讀的問題，下面來分析一下原因：

事務(wù)A第一次查詢創(chuàng)建的read view：

create_trx_id = 101｜ m_idx = [101, 102]｜min_trx_id = 101｜max_trx_id = 103

事務(wù)B的read view：

create_trx_id = 102｜ m_idx = [101, 102]｜min_trx_id = 101｜max_trx_id = 103

事務(wù)A第二次查詢創(chuàng)建的read view：

create_trx_id = 101｜ m_idx = [101]｜min_trx_id = 101｜max_trx_id = 103

（ps. 這里因為AB事務(wù)是并發(fā)執(zhí)行，因此兩個事務(wù)創(chuàng)建的read view的max_trx_id = 103）

這里重點觀察A事務(wù)的第二次查詢，之前你可能就意識到了，在事務(wù)B完成提交后，當(dāng)前系統(tǒng)中活躍的事務(wù)id應(yīng)該移除102，但是因為在可重復(fù)讀隔離級別下，A事務(wù)的read view只會在第一個SQL執(zhí)行時創(chuàng)建，而在讀已提交隔離級別下，每次執(zhí)行SQL都會創(chuàng)建最新的read view，且此時 m_idx數(shù)組中移除了102，那么事務(wù)A在追溯undo log版本鏈的時候，最新版本記錄的trx_id = 102，102不在A事務(wù)的m_idx數(shù)組中，且101 = min_trx_id <= 102 < max_trx_id = 103，因此可以訪問到B事務(wù)的提交結(jié)果。

那么對A事務(wù)來說，在事務(wù)過程中讀取同一條記錄第一次得到a=0，第二次得到a=1，所以出現(xiàn)了不可重復(fù)讀的問題（這里B不提交的話A如果就進行了第二次查詢，則102不會從A事務(wù)的read view移除，則A事務(wù)依舊訪問不到B事務(wù)未提交的修改，因此臟讀還是可以避免的！）

MVCC多版本并發(fā)控制的實現(xiàn)可以理解成讀已提交、可重復(fù)讀兩種隔離級別的實現(xiàn)，通過控制read view的創(chuàng)建時機（其訪問機制是不變的），配合undo log版本鏈可以實現(xiàn)事務(wù)之間對同一條記錄的并發(fā)訪問，并獲得不同的結(jié)果。

但是，大家有沒有想過，剛才的一切都是對A提供便利，對B呢？
而且，MVCC 是適合用于處查詢的時候使用，能提供很高的性能，我們的事務(wù)不僅僅
是只有讀，我們還有寫情況，剛才介紹的情況，B的事務(wù)是不是會被直接覆蓋掉？這不就造成了事務(wù)丟失了嘛
針對寫的情況，Mysql還有另一種基于鎖的機制

LBCC

鎖的作用是什么?它跟Java里面的鎖是一樣的，是為了解決資源競爭的問題，Java里面的資源是對象，數(shù)據(jù)庫的資源就是數(shù)據(jù)表或者數(shù)據(jù)行。

基于鎖的方式起始比較簡單，就是一個事務(wù)在進行數(shù)據(jù)查詢時，不允許其他事務(wù)修改。也就是說，基于鎖的機制就使得數(shù)據(jù)庫無法支持并發(fā)事務(wù)的讀寫操作，這種方案在一定程度上影響了操作數(shù)據(jù)的效率。

本文著重講InnoDB引擎

• 基于鎖的屬性：共享鎖和排它鎖
• 基于鎖的狀態(tài)：意向共享鎖和意向排它
• 基于鎖的粒度：表鎖、頁鎖、行鎖 鎖的粒度

在之前講MySQL存儲引擎的時候，我們知道了 InnoDB和MylSAM支持的鎖 的類型是不同的。InnoDB同時支持表鎖和行鎖，而MylSAM只支持表鎖，用lock table的語法加鎖。

lock tables xxx read;
lock tables xxx write;
unlock tables ;

為什么支持行鎖會成為InnoDB的優(yōu)勢?表鎖和行鎖的區(qū)別到底在哪?

• 鎖定粒度：表鎖 > 行鎖
• 加鎖效率：表鎖 > 行鎖
• 沖突概率：表鎖 > 行鎖
• 并發(fā)性能：表鎖 < 行鎖

鎖的類型

我們可以看到，官網(wǎng)把鎖分成了8類。我們把前面的兩個行級別的鎖(Shared andExclusive Locks)，和兩個表級別的鎖(Intention Locks）稱為鎖的基本模式。

• 鎖的基本模式： （Shared And Exclusive Locks）行級別鎖 和 （Intention Locks）表級別鎖
• 后面三個：Record Locks、Gap Locks、Next-Key Locs ，我們稱為鎖的算法，也就是說在什么情況下鎖定什么范圍。
• 插入意向鎖（Insert Intention Locks）：是一個特殊的間隙鎖。間隙鎖不允許插入數(shù)據(jù)，但是插入意向鎖允許 多個事務(wù)同時插入數(shù)據(jù)到同一個范圍。比如(4,7), —個事務(wù)插入5, —個事務(wù)插入6,不 會發(fā)生鎖等待。
• 自增鎖(AUTO-INC Locks)：是一種特殊的表鎖，用來防止自增字段重復(fù)，數(shù)據(jù)插入以后就會釋放，不需要等到事務(wù)提交才釋放。如果需要選擇更快的自增值生成速度或者更加連續(xù)的自增值，就要通過修改自增鎖的模式改變。

show variables like 'innodb_autoinc_lock_mode';
--0: traditonal(每次都會產(chǎn)生表鎖)
--1: consecutive(會產(chǎn)生一個輕量鎖,simple insert 會獲得批量的鎖，保證連續(xù)插入，默認值)
--2: interleaved(不會鎖表，來一個處理一個，并發(fā)最高)

空間索引的謂詞鎖：Predicate Locks for Spatial Indexes是5.7版本里面新增的空間索引的謂詞鎖。

共享鎖

第一個行級別的鎖就是我們在官網(wǎng)看到的Shared Locks(共享鎖)，我們獲取了一行數(shù)據(jù)的讀鎖以后，可以用來讀取數(shù)據(jù)，所以它也叫做讀鎖，注意不要在加上了讀鎖以后去寫數(shù)據(jù)，不然的話可能會出現(xiàn)死鎖的情況。而且多個事務(wù)可以共享一把讀鎖。

共享鎖的作用：因為共享鎖會阻塞其他事務(wù)的修改，所以可以用在不允許其他事務(wù)修改數(shù)據(jù)的情況。
那怎么給一行數(shù)據(jù)加上讀鎖呢?
我們可以用select… lock in share mode;的方式手工加上一把讀鎖。
釋放鎖有兩種方式，只要事務(wù)結(jié)束，鎖就會自動事務(wù)，包括提交事務(wù)和結(jié)束事務(wù)。

排它鎖

第二個行級別的鎖叫做Exclusive Locks(排它鎖)，它是用來操作數(shù)據(jù)的，所以又叫做寫鎖。只要一個事務(wù)獲取了一行數(shù)據(jù)的排它鎖，其他的事務(wù)就不能再獲取這一行數(shù)據(jù)的共享鎖和排它鎖。

排它鎖的加鎖方式有兩種
第一種是自動加排他鎖，可能是同學(xué)們沒有注意到的:我們在操作數(shù)據(jù)的時候，包括增刪改，都會默認加上一個排它鎖。
第二種是手工加鎖，我們用一個FOR UPDATE給一行數(shù)據(jù)加上一個排它鎖，這個無論是在我們的代碼里面還是操作數(shù)據(jù)的工具里面，都比較常用。
釋放鎖的方式跟前面是一樣的。

這個是兩個行鎖，接下來就是兩個表鎖。

意向鎖

意向鎖是什么呢?我們好像從來沒有聽過，也從來沒有使用過，其實他們是由數(shù)據(jù)庫自己維護的。

也就是說：

• 當(dāng)我們給一行數(shù)據(jù)加上共享鎖之前，數(shù)據(jù)庫會自動在這張表上面加一個意向共享鎖。
• 當(dāng)我們給一行數(shù)據(jù)加上排他鎖之前，數(shù)據(jù)庫會自動在這張表上面加一個意向排他鎖。

反過來:

• 如果一張表上面至少有一個意向共享鎖，說明有其他的事務(wù)給其中的某些數(shù)據(jù)行加上了共享鎖。

意向鎖跟意向鎖是不沖突的，意向鎖跟行鎖也不沖突。

那么這兩個表級別的鎖存在的意義是什么呢?

如果說沒有意向鎖的話，當(dāng)我們準(zhǔn)備給一張表加上表鎖的時候，我們首先要做什么?是不是必須先要去判斷有沒其他的事務(wù)鎖定了其中了某些行?如果有的話，肯定不能加上表鎖。那么這個時候我們就要去掃描整張表才能確定能不能成功加上一個表鎖，如果數(shù)據(jù)量特別大，比如有上千萬的數(shù)據(jù)的時候，加表鎖的效率是不是很低?
但是我們引入了意向鎖之后就不一樣了。我只要判斷這張表上面有沒有意向鎖，如果有，就直接返回失敗。如果沒有，就可以加鎖成功。所以InnoDB里面的表鎖，我們可以把它理解成一個標(biāo)志。就像火車上衛(wèi)生間有沒有人使用的燈，讓你不用去推門，是用來提高加鎖的效率的。

所以鎖是用來解決事務(wù)對數(shù)據(jù)的并發(fā)訪問的問題的。那么，鎖到底鎖住了什么呢?
當(dāng)一個事務(wù)鎖住了一行數(shù)據(jù)的時候，其他的事務(wù)不能操作這一行數(shù)據(jù)，那它到底是鎖住了這一行數(shù)據(jù)，還是鎖住了這一個字段，還是鎖住了別的什么東西呢?

行鎖的原理

沒有索引的表

首先我們有三張表，一張沒有索引的t1，一張有主鍵索引的t2，一張有唯一索引的t3。
我們先假設(shè) InnoDB的行鎖 鎖住的是一行數(shù)據(jù)或者一條記錄。
我們假設(shè)t1的表結(jié)構(gòu)，它有兩個字段, int類型的id和varchar類型的name。里面有4條數(shù)據(jù)，1、2、3、4。

我們在兩個會話里面手工開啟兩個事務(wù)。
在第一個事務(wù)里面，我們通過 where id =1鎖住第一行數(shù)據(jù)。
在第二個事務(wù)里面，我們嘗試給id=3的這一行數(shù)據(jù)加鎖，能成功嗎?

很遺憾，我們看到紅燈亮起，這個加鎖的操作被阻塞了。這就有點奇怪了，第一個事務(wù)鎖住了id=1的這行數(shù)據(jù)，為什么我不能操作id=3的數(shù)據(jù)呢?
我們再來操作一條不存在的數(shù)據(jù)，插入 id=5。它也被阻塞了。實際上這里整張表都被鎖住了。所以，我們的第一個猜想被推翻了，InnoDB的行鎖鎖住的應(yīng)該不是Record。
那為什么在沒有索引或者沒有用到索引的情況下，會鎖住整張表?這個問題我們先留在這里。

有主鍵索引的表

我們假設(shè)t2的表結(jié)構(gòu)。字段和t1是一樣的，不同的地方是id上創(chuàng)建了一個主鍵索引。里面的數(shù)據(jù)是1、4、7、10。

第一種情況，使用相同的id值去加鎖，沖突;使用不同的id 加鎖，可以加鎖成功。那么，既然不是鎖定一行數(shù)據(jù)，有沒有可能是鎖住了id 的這個字段呢?

有唯一索引的表（上面假設(shè)鎖住了字段）

我們假設(shè)t3的表結(jié)構(gòu)字段還是一樣的, id上創(chuàng)建了一個主鍵索引,name 上創(chuàng)建了一個唯一索引。里面的數(shù)據(jù)是1、4、7、10。

在第一個事務(wù)里面，我們通過name字段去鎖定值是4的這行數(shù)據(jù)。
在第二個事務(wù)里面，嘗試獲取一樣的排它鎖，肯定是失敗的，這個不用懷疑。
在這里我們懷疑InnoDB的行鎖鎖住的是字段，所以這次我換一個字段，用id=4去給這行數(shù)據(jù)加鎖，能成功嗎?

很遺憾，又被阻塞了，說明行鎖鎖住的是字段的這個推測也是錯的，否則就不會出現(xiàn)第一個事務(wù)鎖住了name，第二個字段鎖住id失敗的情況。

既然鎖住的不是record，也不是column,，行列都沒鎖，那InnoDB的行鎖鎖住的到底是什么呢?在這三個案例里面，我們要去分析一下他們的差異在哪里，也就是這三張表的結(jié)構(gòu)，是什么區(qū)別導(dǎo)致了加鎖的行為的差異?其實答案就是索引。InnoDB的行鎖，就是通過鎖住索引來實現(xiàn)的。

那么我們還有兩個問題沒有解決:

1、為什么表里面沒有索引的時候，鎖住一行數(shù)據(jù)會導(dǎo)致鎖表?或者說，如果鎖住的是索引，一張表沒有索引怎么辦?

所以，一張表有沒有可能沒有索引?

• 1)如果我們定義了主鍵(PRIMARY KEY)，那么InnoDB會選擇主鍵作為聚集索引。
• 2)如果沒有顯式定義主鍵，則InnoDB會選擇第一個不包含有NULL值的唯一索引作為主鍵索引。
• 3）如果也沒有這樣的唯一索引，則InnoDB會選擇內(nèi)置6字節(jié)長的 ROWID（每一行都有的內(nèi)置，或者說隱藏的列）作 為隱藏的聚集索引，它會隨著行記錄的寫入而主鍵遞增。

所以，為什么鎖表，是因為查詢沒有使用索引，會進行全表掃描，然后把每一個隱藏的聚集索引都鎖住了。

2、為什么通過唯一索引給數(shù)據(jù)行加鎖，主鍵索引也會被鎖住?

大家還記得在InnoDB里面，當(dāng)我們使用輔助索引（二級索引）的時候，它是怎么檢索數(shù)據(jù)的嗎?輔助索引的葉子節(jié)點存儲的是什么內(nèi)容?
在輔助索引里面，索引存儲的是二級索引和主鍵的值。比如name=4,存儲的是name的索引和主鍵id 的值4。
而主鍵索引里面除了索引之外，還存儲了完整的數(shù)據(jù)。所以我們通過輔助索引鎖定一行數(shù)據(jù)的時候，它跟我們檢索數(shù)據(jù)的步驟是一樣的，會通過主鍵值找到主鍵索引，然后也鎖定。

本質(zhì)上是因為鎖定的是同一行數(shù)據(jù)，是相互沖突的。

InnoDB中LBCC要解決的問題

問題1-幻讀問題(InnoDB)

范圍查詢的時候，多次查詢結(jié)果的數(shù)據(jù)行數(shù)一致

select * from table where id >=1 and id<=4 //鎖定2，3 [解決幻讀問題]

問題二， for update 實現(xiàn)了排他鎖(行鎖)

--transaction1 
select * from table where id=1 for update; //查詢主鍵id=1 （行 鎖，只鎖定行）
--transaction2
 update table set name='111' where id=1; //阻塞 
 update table set name='222' where name =''; //阻塞

基于索引來決定的，如果where是索引，那么這個時候，直接加行鎖.

問題三， 鎖定整個表

select * from table for update; //表鎖
update table set name='111' where id=1; //阻塞

鎖的算法

我們先來看一下我們測試用的表,t2，這張表有一個主鍵索引,前面我們已經(jīng)見過了。我們插入了4行數(shù)據(jù)，主鍵id分別是1、4、7、10。
為了讓大家真正理解這三種行鎖算法的區(qū)別，我也來花一點時間給大家普及一下這三種范圍的概念。
因為我們用主鍵索引加鎖，我們這里的劃分標(biāo)準(zhǔn)就是主鍵索引的值。

這些數(shù)據(jù)庫里面存在的主鍵值，我們把它叫做Record（記錄），那么這里我們就有4個Record。
根據(jù)主鍵，這些存在的Record隔開的數(shù)據(jù)不存在的區(qū)間，我們把它叫做Gap（間隙），它是一個左開右開的區(qū)間。
假設(shè)我們有N個Record，那么所有的數(shù)據(jù)會被劃分成多少個Gap 區(qū)間?答案是N+1，就像我們把一條繩子砍N刀，它最后肯定是變成N+1段。
最后一個，間隙(Gap）連同它左邊的記錄(Record)，我們把它叫做臨鍵的區(qū)間，它是一個左開右閉的區(qū)間。再重復(fù)一次，是左開右閉。
整型的主鍵索引，它是可以排序，所以才有這種區(qū)間。如果我的主鍵索引不是整形，是字符怎么辦呢?

任何一個字符集，都有相應(yīng)的排序規(guī)則:

Record Lock (記錄鎖) [鎖定的是索引]

第一種情況，當(dāng)我們對于唯一性的索引(包括唯一索引和主鍵索引)使用等值查詢，精準(zhǔn)匹配到一條記錄的時候，這個時候使用的就是記錄鎖。

顧名思義，記錄鎖就是為某行記錄加鎖，它封鎖該行的索引記錄，并不是真正的數(shù)據(jù)記錄，鎖的是索引的鍵值對。

-- 記錄鎖：id 列為主鍵列或唯一索引列 
SELECT * FROM user WHERE id = 1 FOR UPDATE;
--意味著id=1的這條記錄會被鎖住

Gap Lock（間隙鎖 鎖定索引區(qū)間，不包括record lock）

第二種情況，當(dāng)我們查詢的記錄不存在，沒有命中任何一個record，無論是用等值查詢還是范圍查詢的時候，它使用的都是間隙鎖。
還有個情況，假如我們只命中間隙的一邊，另一邊無法命中怎么辦？
這種情況下，會鎖住另一邊的無限空間

顧名思義 鎖間隙，不鎖記錄。
重復(fù)一遍，當(dāng)查詢的記錄不存在的時候，使用間隙鎖。
注意，間隙鎖主要是阻塞插入insert。相同的間隙鎖之間不沖突。
間隙鎖是基于非唯一索引，它鎖定一段范圍內(nèi)的索引記錄，比如下面這個查詢

 SELECT * FROM user WHERE id BETWEN 1 AND 4 FOR UPDATE;

那么意味著所有在(1,4)區(qū)間內(nèi)的記錄行都會被鎖住，它是一個左右開區(qū)間的范圍，意味著在這種情況下， 會鎖住id為2，3的索引，但是1、4不會被鎖定

next Key Lock（臨鍵鎖 鎖定索引區(qū)間，包括record lock）

第三種情況，當(dāng)我們使用了范圍查詢，不僅僅命中了Record記錄，還包含了Gap間隙，在這種情況下我們使用的就是臨鍵鎖，它是MySQL里面默認的行鎖算法，相當(dāng)于記錄鎖加上間隙鎖。

唯一性索引，等值查詢匹配到一條記錄的時候，退化成記錄鎖。
沒有匹配到任何記錄的時候，退化成間隙鎖。

next Key Lock 可以理解為一種特殊的間隙鎖，也可以理解為一種特殊的算法，每個數(shù)據(jù)行上的非唯一索引列上都會存在一把臨鍵鎖，當(dāng)某個事務(wù)持有該數(shù)據(jù)行的臨鍵鎖時，會鎖住一段左開右閉區(qū)間的數(shù)據(jù)。

為什么要鎖住下一個左開右閉的區(qū)間?——就是為了解決幻讀的問題。

小結(jié)

所以，我們再回過頭來看下這張圖片，為什么InnoDB的RR級別能夠解決幻讀的問題，就是用臨鍵鎖實現(xiàn)的。
我們再回過頭來看下這張圖片,這個就是MySQL InnoDB里面事務(wù)隔離級別的實現(xiàn)。

最后我們來總結(jié)一下四個事務(wù)隔離級別:

Read Uncommited
RU隔離級別:不加鎖。Serializable
Serializable 所有的select語句都會被隱式的轉(zhuǎn)化為select … in share mode，會和update、delete互斥。

這兩個很好理解，一般也不用，主要是RR和RC的區(qū)別?

Repeatable Read：RR隔離級別下，普通的select使用快照讀(snapshot read)，底層使用MVCC來實
現(xiàn)。
加鎖的select(select … in share mode / select … for update)以及更新操作update, delete等語句使用當(dāng)前讀(current read)，底層使用記錄鎖、或者間隙鎖、臨鍵鎖。

Read Commited：RC隔離級別下，普通的select 都是快照讀，使用MVCC 實現(xiàn)。加鎖的select都使用記錄鎖，因為沒有Gap Lock。

除了兩種特殊情況——外鍵約束檢查(foreign-key constraint checking)以及重復(fù)鍵檢查(duplicate-key checking)時會使用間隙鎖封鎖區(qū)間。
所以RC會出現(xiàn)幻讀的問題。

事務(wù)隔離級別怎么選？

RU和Serializable肯定不能用

RC和RR主要有幾個區(qū)別：

• 1、 RR的間隙鎖會導(dǎo)致鎖定范圍的擴大。
• 2、 條件列未使用到索引, RR鎖表，RC鎖行。
• 3、 RC的"半一致性”(semi-consistent)讀可以增加update操作的并發(fā)性。

在RC中，一個update語句，如果讀到一行已經(jīng)加鎖的記錄，此時 InnoDB返回記錄最近提交的版本，由MySQL上層判斷此版本是否滿足update的where 條件。若滿足(需要更新)，則MySQL會重新發(fā)起一次讀操作，此時會讀取行的最新版本(并加鎖)。

實際上，如果能夠正確地使用鎖(避免不使用索引去枷鎖),只鎖定需要的數(shù)據(jù)，用默認的RR級別就可以了
在我們使用鎖的時候，有一個問題是需要注意和避免的，我們知道，排它鎖有互斥的特性。一個事務(wù)或者說一個線程持有鎖的時候，會阻止其他的線程獲取鎖，這個時候會造成阻塞等待，如果循環(huán)等待，會有可能造成死鎖。
死鎖的相關(guān)信息，可以看我的下一篇博客，MySQL死鎖的解析
鏈接: MySQL死鎖使用詳解及檢測和避免方法

到此這篇關(guān)于MySQL臟讀幻讀不可重復(fù)讀及事務(wù)的隔離級別和MVCC、LBCC實現(xiàn)的文章就介紹到這了,更多相關(guān)MySQL臟讀幻讀 內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論