mysql怎么使用間隙鎖 mysql間隙鎖在什么隔離級(jí)別

mysql 間隙鎖和臨鍵鎖原理

以下面的表為例子進(jìn)行說(shuō)明

創(chuàng)新互聯(lián)建站是專(zhuān)業(yè)的武夷山網(wǎng)站建設(shè)公司，武夷山接單;提供成都網(wǎng)站建設(shè)、網(wǎng)站制作,網(wǎng)頁(yè)設(shè)計(jì),網(wǎng)站設(shè)計(jì),建網(wǎng)站,PHP網(wǎng)站建設(shè)等專(zhuān)業(yè)做網(wǎng)站服務(wù);采用PHP框架,可快速的進(jìn)行武夷山網(wǎng)站開(kāi)發(fā)網(wǎng)頁(yè)制作和功能擴(kuò)展;專(zhuān)業(yè)做搜索引擎喜愛(ài)的網(wǎng)站,專(zhuān)業(yè)的做網(wǎng)站團(tuán)隊(duì),希望更多企業(yè)前來(lái)合作!

間隙鎖的產(chǎn)生來(lái)自于 InnboDB 引擎在可重復(fù)讀的級(jí)別基礎(chǔ)上執(zhí)行當(dāng)前讀時(shí)出現(xiàn)的幻讀問(wèn)題。下面來(lái)分析一下幻讀的例子， 假如沒(méi)有間隙鎖的話 ，那么會(huì)出現(xiàn)下面的現(xiàn)象：

如上表如示，是基于沒(méi)有間隙鎖的假設(shè)，sessionA 事務(wù)內(nèi)執(zhí)行兩次相同的當(dāng)前讀返回的數(shù)據(jù)不一樣，出現(xiàn)幻讀的現(xiàn)象。因?yàn)?2,2,10)這條記錄在原本的數(shù)據(jù)并不存在，行鎖就鎖不住，因此誕生間隙鎖。

首先看看sessionA的執(zhí)行計(jì)劃，發(fā)現(xiàn)用到覆蓋索引

場(chǎng)景 1：插入到索引 a 時(shí)，要插入是索引是(11,5)，屬于(a=10,id=10)和(a=30,id=30)之間的鎖范圍，所以阻塞

場(chǎng)景 2、3、4 同理分析得出結(jié)論

本例子跟《查詢(xún)條件走二級(jí)索引例子》區(qū)別在于 sessionA 是 select * ，因此需要回到主鍵索引查詢(xún)所有字段，掃描了主鍵索引，所以也會(huì)在掃描到的索引進(jìn)行加 next-key lock。該語(yǔ)句回表一次，掃描到是行是 id=10，所以加鎖是(0,10],(10,20)，因此 sessionA 一共加了鎖是索引 a 的(10,30)和主鍵索引的（0,20）。

間隙鎖和行鎖加鎖規(guī)則

表和數(shù)據(jù)

規(guī)則總結(jié)（這個(gè)規(guī)則只限于截止到現(xiàn)在的最新版本，即 5.x 系列 =5.7.24，8.0 系列 =8.0.13。）：

原則 1：加鎖的基本單位是 next-key lock。希望你還記得，next-key lock 是前開(kāi)后閉區(qū)間。

原則 2：查找過(guò)程中訪問(wèn)到的對(duì)象才會(huì)加鎖。

優(yōu)化 1：索引上的等值查詢(xún)，給唯一索引加鎖的時(shí)候，next-key lock 退化為行鎖。

優(yōu)化 2：索引上的等值查詢(xún)，向右遍歷時(shí)且最后一個(gè)值不滿(mǎn)足等值條件的時(shí)候，next-key lock 退化為間隙鎖。

一個(gè) bug：唯一索引上的范圍查詢(xún)會(huì)訪問(wèn)到不滿(mǎn)足條件的第一個(gè)值為止。

案例一：等值查詢(xún)間隙鎖

案例二：非唯一索引等值鎖

根據(jù)原則1，優(yōu)化2，鎖的范圍是(0,5],(5,10)，但是根據(jù)原則2，只有訪問(wèn)到的對(duì)象才加鎖，這個(gè)查詢(xún)使用了覆蓋索引，并不訪問(wèn)主鍵索引，所以主鍵上沒(méi)加鎖。

需要注意，在這個(gè)例子中，lock in share mode 只鎖覆蓋索引，但是如果是 for update 就不一樣了。 執(zhí)行 for update 時(shí)，系統(tǒng)會(huì)認(rèn)為你接下來(lái)要更新數(shù)據(jù)，因此會(huì)順便給主鍵索引上滿(mǎn)足條件的行加上行鎖。

案例三：主鍵索引范圍鎖

案例四：非唯一索引范圍鎖

這次 session A 用字段 c 來(lái)判斷，加鎖規(guī)則跟案例三唯一的不同是：在第一次用 c=10 定位記錄的時(shí)候，索引 c 上加了 (5,10] 這個(gè) next-key lock 后，由于索引 c 是非唯一索引，沒(méi)有優(yōu)化規(guī)則，也就是說(shuō)不會(huì)蛻變?yōu)樾墟i，因此最終 sesion A 加的鎖是，索引 c 上的 (5,10] 和 (10,15] 這兩個(gè) next-key lock。

案例五：唯一索引范圍鎖 bug

案例六：非唯一索引上存在"等值"的例子

表中插入一條mysql insert into t values(30,10,30);

這時(shí)，session A 在遍歷的時(shí)候，先訪問(wèn)第一個(gè) c=10 的記錄。同樣地，根據(jù)原則 1，這里加的是 (c=5,id=5) 到 (c=10,id=10) 這個(gè) next-key lock。

然后，session A 向右查找，直到碰到 (c=15,id=15) 這一行，循環(huán)才結(jié)束。根據(jù)優(yōu)化 2，這是一個(gè)等值查詢(xún)，向右查找到了不滿(mǎn)足條件的行，所以會(huì)退化成 (c=10,id=10) 到 (c=15,id=15) 的間隙鎖。

案例七：limit 語(yǔ)句加鎖

索引 c 上的加鎖范圍變成了從（c=5,id=5) 到（c=10,id=30) 這個(gè)前開(kāi)后閉區(qū)間，如下圖所示：

案例八：一個(gè)死鎖的例子

在讀提交情況下，語(yǔ)句執(zhí)行過(guò)程中加上的行鎖，在語(yǔ)句執(zhí)行完成后，就要把“不滿(mǎn)足條件的行”上的行鎖直接釋放了，不需要等到事務(wù)提交。

費(fèi)解的問(wèn)題

1.由于是 order by c desc，第一個(gè)要定位的是索引 c 上“最右邊的”c=20 的行，所以會(huì)加上間隙鎖 (20,25) 和 next-key lock (15,20]。

2.在索引 c 上向左遍歷，要掃描到 c=10 才停下來(lái)，所以 next-key lock 會(huì)加到 (5,10]，這正是阻塞 session B 的 insert 語(yǔ)句的原因。

3.在掃描過(guò)程中，c=20、c=15、c=10 這三行都存在值，由于是 select *，所以會(huì)在主鍵 id 上加三個(gè)行鎖。

參考：

深入理解MySQL的間隙鎖

因?yàn)樾墟i只能鎖住行，但是新插入記錄這個(gè)動(dòng)作，要更新的是記錄之間的“間隙”。為了解決幻讀問(wèn)題，InnoDB 只好引入新的鎖，也就是間隙鎖 (Gap Lock)。

間隙鎖，鎖的就是兩個(gè)值之間的空隙，不允許兩個(gè)值之間再插一個(gè)值。

比如初始化插入了 6 個(gè)記錄，這就產(chǎn)生了 7 個(gè)間隙。分別是 (-∞,0)、(0,5)、(5,10)、(10,15)、(15,20)、(20, 25)、(25, +supremum)，間隙鎖都是開(kāi)區(qū)間

和行鎖不一樣的是，跟間隙鎖存在沖突關(guān)系的，是“往這個(gè)間隙中插入一個(gè)記錄”這個(gè)操作。間隙鎖之間都不存在沖突關(guān)系。

缺點(diǎn)：可能會(huì)導(dǎo)致同樣的語(yǔ)句鎖住更大的范圍，影響了并發(fā)度。

間隙鎖和行鎖合稱(chēng) next-key lock，每個(gè) next-key lock 是前開(kāi)后閉區(qū)間。如果用 select * from t for update 要把整個(gè)表所有記錄鎖起來(lái)，就形成了 7 個(gè) next-key lock，分別是 (-∞,0]、(0,5]、(5,10]、(10,15]、(15,20]、(20, 25]、(25, +supremum]。

和間隙鎖的最大區(qū)別是，next-key lock 為前開(kāi)后閉區(qū)間，這樣所有的next-key lock就可以把所有記錄鎖起來(lái)。

加鎖規(guī)則里面，包含了兩個(gè)“原則”、兩個(gè)“優(yōu)化”和一個(gè)“bug”

MySQL從入門(mén)到精通(九) MySQL鎖，各種鎖

鎖是計(jì)算機(jī)協(xié)調(diào)多個(gè)進(jìn)程或線程并發(fā)訪問(wèn)某一資源的機(jī)制，在數(shù)據(jù)庫(kù)中，除傳統(tǒng)的計(jì)算資源（CPU、RAM、I/O）爭(zhēng)用外，數(shù)據(jù)也是一種供許多用戶(hù)共享的資源，如何保證數(shù)據(jù)并發(fā)訪問(wèn)的一致性，有效性是所有數(shù)據(jù)庫(kù)必須解決的一個(gè)問(wèn)題，鎖沖突也是影響數(shù)據(jù)庫(kù)并發(fā)訪問(wèn)性能的一個(gè)重要因素，從這個(gè)角度來(lái)說(shuō)，鎖對(duì)數(shù)據(jù)庫(kù)而言是尤其重要，也更加復(fù)雜。MySQL中的鎖，按照鎖的粒度分為:1、全局鎖，就鎖定數(shù)據(jù)庫(kù)中的所有表。2、表級(jí)鎖，每次操作鎖住整張表。3、行級(jí)鎖，每次操作鎖住對(duì)應(yīng)的行數(shù)據(jù)。

全局鎖就是對(duì)整個(gè)數(shù)據(jù)庫(kù)實(shí)例加鎖，加鎖后整個(gè)實(shí)例就處于只讀狀態(tài)，后續(xù)的DML的寫(xiě)語(yǔ)句，DDL語(yǔ)句，已經(jīng)更新操作的事務(wù)提交語(yǔ)句都將阻塞。其典型的使用場(chǎng)景就是做全庫(kù)的邏輯備份，對(duì)所有的表進(jìn)行鎖定，從而獲取一致性視圖，保證數(shù)據(jù)的完整性。但是對(duì)數(shù)據(jù)庫(kù)加全局鎖是有弊端的，如在主庫(kù)上備份，那么在備份期間都不能執(zhí)行更新，業(yè)務(wù)會(huì)受影響，第二如果是在從庫(kù)上備份，那么在備份期間從庫(kù)不能執(zhí)行主庫(kù)同步過(guò)來(lái)的二進(jìn)制日志，會(huì)導(dǎo)致主從延遲。

解決辦法是在innodb引擎中，備份時(shí)加上--single-transaction參數(shù)來(lái)完成不加鎖的一致性數(shù)據(jù)備份。

添加全局鎖: flush tables with read lock; 解鎖 unlock tables。

表級(jí)鎖,每次操作會(huì)鎖住整張表.鎖定粒度大,發(fā)送鎖沖突的概率最高,并發(fā)讀最低,應(yīng)用在myisam、innodb、BOB等存儲(chǔ)引擎中。表級(jí)鎖分為: 表鎖、元數(shù)據(jù)鎖(meta data lock, MDL)和意向鎖。

表鎖又分為: 表共享讀鎖 read lock、表獨(dú)占寫(xiě)鎖write lock

語(yǔ)法: 1、加鎖 lock tables 表名 ... read/write

2、釋放鎖 unlock tables 或者關(guān)閉客戶(hù)端連接

注意: 讀鎖不會(huì)阻塞其它客戶(hù)端的讀，但是會(huì)阻塞其它客戶(hù)端的寫(xiě)，寫(xiě)鎖既會(huì)阻塞其它客戶(hù)端的讀，又會(huì)阻塞其它客戶(hù)端的寫(xiě)。大家可以拿一張表來(lái)測(cè)試看看。

元數(shù)據(jù)鎖,在加鎖過(guò)程中是系統(tǒng)自動(dòng)控制的，無(wú)需顯示使用，在訪問(wèn)一張表的時(shí)候會(huì)自動(dòng)加上，MDL鎖主要作用是維護(hù)表元數(shù)據(jù)的數(shù)據(jù)一致性，在表上有活動(dòng)事務(wù)的時(shí)候，不可以對(duì)元數(shù)據(jù)進(jìn)行寫(xiě)入操作。為了避免DML和DDL沖突，保證讀寫(xiě)的正確性。

在MySQL5.5中引入了MDL，當(dāng)對(duì)一張表進(jìn)行增刪改查的時(shí)候，加MDL讀鎖(共享)；當(dāng)對(duì)表結(jié)構(gòu)進(jìn)行變更操作時(shí)，加MDL寫(xiě)鎖(排他).

查看元數(shù)據(jù)鎖:

select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema_metadata_locks;

意向鎖，為了避免DML在執(zhí)行時(shí)，加的行鎖與表鎖的沖突，在innodb中引入了意向鎖，使得表鎖不用檢查每行數(shù)據(jù)是否加鎖，使用意向鎖來(lái)減少表鎖的檢查。意向鎖分為,意向共享鎖is由語(yǔ)句select ... lock in share mode添加。意向排他鎖ix,由insert，update，delete，select。。。for update 添加。

select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_lock;

行級(jí)鎖，每次操作鎖住對(duì)應(yīng)的行數(shù)據(jù)，鎖定粒度最小，發(fā)生鎖沖突的概率最高，并發(fā)讀最高，應(yīng)用在innodb存儲(chǔ)引擎中。

innodb的數(shù)據(jù)是基于索引組織的，行鎖是通過(guò)對(duì)索引上的索引項(xiàng)加鎖來(lái)實(shí)現(xiàn)的，而不是對(duì)記錄加的鎖，對(duì)于行級(jí)鎖，主要分為以下三類(lèi):

1、行鎖或者叫record lock記錄鎖，鎖定單個(gè)行記錄的鎖，防止其他事物對(duì)次行進(jìn)行update和delete操作，在RC,RR隔離級(jí)別下都支持。

2、間隙鎖Gap lock,鎖定索引記錄間隙(不含該記錄)，確保索引記錄間隙不變，防止其他事物在這個(gè)間隙進(jìn)行insert操作，產(chǎn)生幻讀，在RR隔離級(jí)別下都支持。

3、臨鍵鎖Next-key-lock,行鎖和間隙鎖組合，同時(shí)鎖住數(shù)據(jù)，并鎖住數(shù)據(jù)前面的間隙Gap,在RR隔離級(jí)別下支持。

innodb實(shí)現(xiàn)了以下兩種類(lèi)型的行鎖

1、共享鎖 S: 允許一個(gè)事務(wù)去讀一行，阻止其他事務(wù)獲得相同數(shù)據(jù)集的排他鎖。

2、排他鎖 X: 允許獲取排他鎖的事務(wù)更新數(shù)據(jù)，阻止其他事務(wù)獲得相同數(shù)據(jù)集的共享鎖和排他鎖。

insert 語(yǔ)句 排他鎖 自動(dòng)添加的

update語(yǔ)句 排他鎖 自動(dòng)添加

delete 語(yǔ)句 排他鎖 自動(dòng)添加

select 正常查詢(xún)語(yǔ)句 不加鎖 。。。

select 。。。lock in share mode 共享鎖 需要手動(dòng)在select 之后加lock in share mode

select 。。。for update 排他鎖 需要手動(dòng)在select之后添加for update

默認(rèn)情況下，innodb在repeatable read事務(wù)隔離級(jí)別運(yùn)行，innodb使用next-key鎖進(jìn)行搜索和索引掃描，以防止幻讀。

間隙鎖唯一目的是防止其它事務(wù)插入間隙，間隙鎖可以共存，一個(gè)事務(wù)采用的間隙鎖不會(huì)阻止另一個(gè)事務(wù)在同一間隙上采用的間隙鎖。

mysql 的鎖以及間隙鎖

mysql 為并發(fā)事務(wù)同時(shí)對(duì)一條記錄進(jìn)行讀寫(xiě)時(shí)，提出了兩種解決方案：

1）使用 mvcc 的方法，實(shí)現(xiàn)多事務(wù)的并發(fā)讀寫(xiě)，但是這種讀只是“快照讀”，一般讀的是歷史版本數(shù)據(jù)，還有一種是“當(dāng)前讀”，一般加鎖實(shí)現(xiàn)“當(dāng)前讀”，或者 insert、update、delete 也是當(dāng)前讀。

2）使用加鎖的方法，鎖分為共享鎖（讀鎖），排他鎖（寫(xiě)鎖）

快照讀：就是select

當(dāng)前讀：特殊的讀操作，插入/更新/刪除操作，屬于當(dāng)前讀，處理的都是當(dāng)前的數(shù)據(jù)，需要加鎖。

mysql 在 RR 級(jí)別怎么處理幻讀的呢？一般來(lái)說(shuō)，RR 級(jí)別通過(guò) mvcc 機(jī)制，保證讀到低于后面事務(wù)的數(shù)據(jù)。但是 select for update 不會(huì)觸發(fā) mvcc，它是當(dāng)前讀。如果后面事務(wù)插入數(shù)據(jù)并提交，那么在 RR 級(jí)別就會(huì)讀到插入的數(shù)據(jù)。所以，mysql 使用 行鎖 + gap 鎖（簡(jiǎn)稱(chēng) next-key 鎖）來(lái)防止當(dāng)前讀的時(shí)候插入。

Gap Lock在InnoDB的唯一作用就是防止其他事務(wù)的插入操作，以此防止幻讀的發(fā)生。

Innodb自動(dòng)使用間隙鎖的條件：

MySQL白菜教程（Level 10 - 意向鎖&記錄鎖&間隙鎖）

意向鎖（Intention Locks; table-level lock）

意向鎖是一種特殊的表級(jí)鎖，意向鎖是為了讓 InnoDB 多粒度的鎖能共存而設(shè)計(jì)的。取得行的共享鎖和排他鎖之前需要先取得表的意向共享鎖（IS）和意向排他鎖（IX），意向共享鎖和意向排他鎖都是系統(tǒng)自動(dòng)添加和自動(dòng)釋放的，整個(gè)過(guò)程無(wú)需人工干預(yù)

意向鎖就是指未來(lái)的某一個(gè)時(shí)刻事務(wù)可能要加共享鎖或者排它鎖，提前聲明一個(gè)意向，分為兩種：

意向共享鎖（Intention Shared Lock） IS

事務(wù)有意向?qū)Ρ碇械哪承┬屑庸蚕礞i（S鎖）

意向排它鎖（Intention Exclusive Lock）IX

事務(wù)有意向?qū)Ρ碇械哪承┬屑优潘i（X鎖）

記錄鎖（Record Locks）

官方原文

SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE; 這一行則是使用了記錄鎖，不允許其他事務(wù)進(jìn)行增，刪，改

但是 SELECT c1 FROM t WHERE c1 = 10; 是沒(méi)有鎖的，走的是快照讀，上文已經(jīng)闡明過(guò)了

記錄鎖本身不是鎖定記錄數(shù)據(jù)本身而是鎖定索引記錄，如果要鎖的列沒(méi)有索引，則會(huì)進(jìn)行全表記錄加鎖

間隙鎖（Gap Locks）

官方原文

比如 SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE ;

插入 c1 為 15 的記錄會(huì)被鎖定不可執(zhí)行

這種默認(rèn)存在于可重復(fù)讀的事務(wù)隔離級(jí)別中的鎖，鎖定被圈定的范圍不允許 insert，防止不可重復(fù)讀，上文說(shuō)了我們的事務(wù)隔離級(jí)別都是讀已提交，默認(rèn)會(huì)產(chǎn)生不可重復(fù)讀的問(wèn)題

分享標(biāo)題：mysql怎么使用間隙鎖 mysql間隙鎖在什么隔離級(jí)別
分享路徑：http://m.jiaotiyi.com/article/hhejgj.html