MySQL臟讀幻讀不可重復讀及事務的隔離級別和MVCC、LBCC實現

Posted on by

前言

上一篇文章講解瞭MySQL的事務的相關概念MySQL的事務特性概念梳理總結

文章末尾提出瞭事務因並發出現的問題有哪些？
本篇將著重講述這個問題的前因後果及解決方式。

事務因並發出現的問題有哪些 臟讀

概念：一個事務讀取到其他事務未提交的數據。
用一個圖來講解，在並發環境下，多個事務操作同一對象帶來的問題：

不可重復讀

概念：一個事務在一個時間段內 前後讀取的數據不一致，或者出現瞭修改/刪除。

幻讀

概念：事務A 按照查詢條件讀取某個范圍的記錄，其他事務又在該范圍內出入瞭滿足條件的新記錄，當事務A再次讀取數據到時候我們發現多瞭滿足記錄的條數（幻行）

建議大傢把幻讀記作幻行，以免和不可重復讀記混淆

不可重復讀與幻讀的區別

前提：兩者都是讀取到已經提交的數據

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

我們現在已經知道，原來事務並發會出現，臟讀，不可重復讀，幻讀的問題。
那這些問題我們都是需要去解決的，怎麼解決呢？
有興趣可以看看官網是怎麼解釋的
鏈接: 官網地址

事務並發的三大問題其實都是數據庫讀一致性問題，必須由數據庫提供一定的事務隔離機制來解決。

事務的四個隔離級別

我們通過事務的隔離級別來解決不同的問題，那麼，不同的隔離級別解決瞭什麼問題呢？

其實sql標準92版 官方都有定義出來

另外，sql標準不是數據庫廠商定義出來的，大傢不要以為sql語言是什麼mysql，sqlserver搞出來的，我們會發現每個數據庫語句的sql語句都是差不多的。sql是獨立於廠商的！！SQL是Structured Query Language的縮寫，本來就屬於一種查詢語言！！

官網支持四種隔離級別：

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

我們也可以通過SQL去查詢當前的隔離級別

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

InnoDB默認的隔離級別是RR

事務隔離級別越高，多個事務在並發訪問數據庫時互相產生數據幹擾的可能性越低，但是並發訪問的性能就越差。（相當於犧牲瞭一定的性能去保證數據的安全性）

Read UnCommited 讀未提交 RU

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

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

Read Commited 讀已提交 RC

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

結論：我就讀取你已經提交的事務就完事，解決臟讀。

Repeatable Read 可重復讀 RR

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

結論：RR級別解決瞭臟讀、不可重復讀、幻讀的問題。

Serializable 串行化

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

結論：加鎖排隊讀取，性能最低。

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

undo 版本鏈

undo 版本鏈就是指undo log的存儲在邏輯上的表現形式，它被用於事務當中的回滾操作以及實現MVCC，這裡介紹一下undo log之所以能實現回滾記錄的原理。

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

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

read view

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

  • create_trx_id: 當前事務的
  • idm_idx: 當前正在活躍的所有事務id（id數組），沒有提交的事務的
  • idmin_trx_id: 當前系統中活躍的事務的id最小值
  • max_trx_id: 當前系統中已經創建過的最新事務（id最大）的id+1的值

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

（這個規則永遠成立，這個需要好好理解，對後面講解可重復讀和讀已提交兩個級別的實現密切相關）

  • 如果當前記錄行的row_trx_id小於min_trx_id，表示該版本的記錄在當前事務開啟之前創建，因此可以訪問到
  • 如果當前記錄行的row_trx_id大於等於max_trx_id，表示該版本的記錄創建晚於當前活躍的事務，因此不能訪問到
  • 如果當前記錄行的row_trx_id大於等於min_trx_id且小於max_trx_id，則要分兩種情況：
    • 當前記錄行的row_trx_id在m_idx數組中，則當前事務無法訪問到這個版本的記錄 （除非這個版本的row_trx_id等於當前事務本身的trx_id，本事務當然能訪問自己修改的記錄） ，在m_idx數組中又不是當前事務自己創建的undo版本，表示是並發訪問的其他事務對這條記錄的修改的結果，則不能訪問到。
    • 當前記錄行的row_trx_id不在m_idx數組中，則表示這個版本是當前事務開啟之前，其他事務已經提交瞭的undo版本，當前事務可訪問到。

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

Oracle、Postgres等等其他數據庫都有MVCC的實現。

需要註意，在InnoDB中，MVCC和鎖是協同使用的，這兩種方案並不是互斥的。

配合使用read view和undo log版本鏈就能實現事務之間並發訪問相同記錄時，可以根據事務id不同，獲取同一行的不同undo log版本（多版本並發控制）。

MVCC（Multi-Version Concurrent Control ）多版本並發控制

多版本並發控制，是什麼意思呢？版本控制，我們在進行查詢的時候是有版本的，後續在同一個事務裡查詢的時候，我們都是使用我們當初創建的快照版本。
比如說嘛，快照，你10歲20歲30歲40歲去照相，你隻能看到你之前照相的模樣，但是不能看到你未來的模樣。

MVCC怎麼去實現？
每個事務都有一個事務ID，並且是遞增，我們後續MVCC的原理都是基於它去完成。
效果：建立一個快照，同一個事務無論查詢多少次都是相同的數據。

一個事務能看見的版本：

  • 第一次查詢之前已經提交的版本
  • 本事務的修改

一個事務不能看見的版本：

  • 在本事務第一次查詢之後創建的事務（事務ID比我大）
  • 活躍中的（未提交）的時候的修改。

下面通過模擬並發訪問的兩個事務操作，介紹MVCC的實現（具體來說就是可重復讀和讀已提交兩個隔離級別的實現）

可重復讀實現

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

事務A的read view：

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

事務B的read view：

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

（ps. 這裡因為AB事務是並發執行，因此兩個事務創建的read view的max_trx_id = 103）

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

你可能有疑問，在A事務第二次查詢的時候，B事務已經完成提交瞭，那麼A事務的read view的m_idx數組應該移除102才對啊，它存的不是當前活躍的事務的id嗎？·

註意：在可重復讀隔離級別下，當事務sql執行的時候，會生成一個read view快照，且在本事務周期內一直使用這個read view，雖然102確實應該從A事務的read view中移除，但是因為read view在可重復讀隔離級別下隻會在第一條SQL執行時創建一次，並始終保持不變直到事務結束。

那麼也就明白瞭，在可重復讀隔離級別下，因為read view隻在第一條SQL執行時創建，因此並發訪問的其他事務提交前改動的臟數據、以及並發訪問的其他事務提交的改動數據都對當前事務是透明的（盡管確實是記錄在瞭undo log版本鏈中） ，這就解決瞭臟讀和不可重復讀（即使其他事務提交的修改，對A事務來說前後查詢結果相同）的問題！

讀已提交實現

還是借助上面事務處理的例子，所有的事務處理流程不變，隻是將隔離級別調整為讀已提交，讀已提交依舊遵守read view和undo log版本鏈機制，它和可重復讀級別的區別在於，每次執行sql，都會創建一個read view，獲取最新的事務快照。 而因為這個區別，讀已提交產生瞭不可重復讀的問題，下面來分析一下原因：

事務A第一次查詢創建的read view：

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

事務B的read view：

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

事務A第二次查詢創建的read view：

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

（ps. 這裡因為AB事務是並發執行，因此兩個事務創建的read view的max_trx_id = 103）

這裡重點觀察A事務的第二次查詢，之前你可能就意識到瞭，在事務B完成提交後，當前系統中活躍的事務id應該移除102，但是因為在可重復讀隔離級別下，A事務的read view隻會在第一個SQL執行時創建，而在讀已提交隔離級別下，每次執行SQL都會創建最新的read view，且此時 m_idx數組中移除瞭102，那麼事務A在追溯undo log版本鏈的時候，最新版本記錄的trx_id = 102，102不在A事務的m_idx數組中，且101 = min_trx_id <= 102 < max_trx_id = 103，因此可以訪問到B事務的提交結果。

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

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

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

LBCC

鎖的作用是什麼?它跟Java裡面的鎖是一樣的，是為瞭解決資源競爭的問題，Java裡面的資源是對象，數據庫的資源就是數據表或者數據行。

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

本文著重講InnoDB引擎

  • 基於鎖的屬性：共享鎖和排它鎖
  • 基於鎖的狀態：意向共享鎖和意向排它
  • 基於鎖的粒度：表鎖、頁鎖、行鎖 鎖的粒度

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

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

為什麼支持行鎖會成為InnoDB的優勢?表鎖和行鎖的區別到底在哪?

  • 鎖定粒度：表鎖 > 行鎖
  • 加鎖效率：表鎖 > 行鎖
  • 沖突概率：表鎖 > 行鎖
  • 並發性能：表鎖 < 行鎖

鎖的類型

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

  • 鎖的基本模式： （Shared And Exclusive Locks）行級別鎖 和 （Intention Locks）表級別鎖
  • 後面三個：Record Locks、Gap Locks、Next-Key Locs ，我們稱為鎖的算法，也就是說在什麼情況下鎖定什麼范圍。
  • 插入意向鎖（Insert Intention Locks）：是一個特殊的間隙鎖。間隙鎖不允許插入數據，但是插入意向鎖允許 多個事務同時插入數據到同一個范圍。比如(4,7), —個事務插入5, —個事務插入6,不 會發生鎖等待。
  • 自增鎖(AUTO-INC Locks)：是一種特殊的表鎖，用來防止自增字段重復，數據插入以後就會釋放，不需要等到事務提交才釋放。如果需要選擇更快的自增值生成速度或者更加連續的自增值，就要通過修改自增鎖的模式改變。
show variables like 'innodb_autoinc_lock_mode';
--0: traditonal(每次都會產生表鎖)
--1: consecutive(會產生一個輕量鎖,simple insert 會獲得批量的鎖，保證連續插入，默認值)
--2: interleaved(不會鎖表，來一個處理一個，並發最高)

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

共享鎖

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

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

排它鎖

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

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

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

意向鎖

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

也就是說：

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

反過來:

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

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

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

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

所以鎖是用來解決事務對數據的並發訪問的問題的。那麼，鎖到底鎖住瞭什麼呢?
當一個事務鎖住瞭一行數據的時候，其他的事務不能操作這一行數據，那它到底是鎖住瞭這一行數據，還是鎖住瞭這一個字段，還是鎖住瞭別的什麼東西呢?

行鎖的原理

沒有索引的表

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

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

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

有主鍵索引的表

我們假設t2的表結構。字段和t1是一樣的，不同的地方是id上創建瞭一個主鍵索引。裡面的數據是1、4、7、10。

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

有唯一索引的表（上面假設鎖住瞭字段）

我們假設t3的表結構字段還是一樣的, id上創建瞭一個主鍵索引,name 上創建瞭一個唯一索引。裡面的數據是1、4、7、10。

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

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

既然鎖住的不是record，也不是column,，行列都沒鎖，那InnoDB的行鎖鎖住的到底是什麼呢?在這三個案例裡面，我們要去分析一下他們的差異在哪裡，也就是這三張表的結構，是什麼區別導致瞭加鎖的行為的差異?其實答案就是索引。InnoDB的行鎖，就是通過鎖住索引來實現的。

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

1、為什麼表裡面沒有索引的時候，鎖住一行數據會導致鎖表?或者說，如果鎖住的是索引，一張表沒有索引怎麼辦?

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

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

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

2、為什麼通過唯一索引給數據行加鎖，主鍵索引也會被鎖住?

大傢還記得在InnoDB裡面，當我們使用輔助索引（二級索引）的時候，它是怎麼檢索數據的嗎?輔助索引的葉子節點存儲的是什麼內容?
在輔助索引裡面，索引存儲的是二級索引和主鍵的值。比如name=4,存儲的是name的索引和主鍵id 的值4。
而主鍵索引裡面除瞭索引之外，還存儲瞭完整的數據。所以我們通過輔助索引鎖定一行數據的時候，它跟我們檢索數據的步驟是一樣的，會通過主鍵值找到主鍵索引，然後也鎖定。

本質上是因為鎖定的是同一行數據，是相互沖突的。

InnoDB中LBCC要解決的問題

問題1-幻讀問題(InnoDB)

范圍查詢的時候，多次查詢結果的數據行數一致

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

問題二， for update 實現瞭排他鎖(行鎖)

--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，這張表有一個主鍵索引,前面我們已經見過瞭。我們插入瞭4行數據，主鍵id分別是1、4、7、10。
為瞭讓大傢真正理解這三種行鎖算法的區別，我也來花一點時間給大傢普及一下這三種范圍的概念。
因為我們用主鍵索引加鎖，我們這裡的劃分標準就是主鍵索引的值。

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

任何一個字符集，都有相應的排序規則:

在這裡插入圖片描述

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

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

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

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

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

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

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

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

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

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

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

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

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

為什麼要鎖住下一個左開右閉的區間?——就是為瞭解決幻讀的問題。

小結

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

最後我們來總結一下四個事務隔離級別:

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

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

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

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

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

事務隔離級別怎麼選？

RU和Serializable肯定不能用

RC和RR主要有幾個區別：

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

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

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

到此這篇關於MySQL臟讀幻讀不可重復讀及事務的隔離級別和MVCC、LBCC實現的文章就介紹到這瞭,更多相關MySQL臟讀幻讀 內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！

推薦閱讀：