MySQL 之 InnoDB Lock 淺談

Q: MySQL 加鎖時鎖的是索引還是數(shù)據(jù) ?

如果別人問你這個問題的話,你是成竹在胸還是不知所措? 當(dāng)然蹦狂,這都不是重點击蹲,重點是之后你只有一個答案那就是胸有成竹询刹。

InnodB-Lock-Overview.png

鎖類型

鎖的名稱真的有很多種莽红,但從資源的使用來說就是兩種竭业,共享鎖(Shared-Lock)和排它鎖(Exclusive-Lock)。共享鎖通常簡寫為 S, 而排它鎖的簡寫卻是 X (為什么是 X 而不是 E,希望知道答案的你與我分享 mailto: sftjun@outlook.com )国葬。

InnoDB 引擎默認(rèn)對普通查詢不加鎖贤徒,所以示例代碼以手動加鎖形式給出

共享鎖

共享鎖也稱為讀鎖,因為讀并不改變數(shù)據(jù)汇四,所以把鎖共享可以使資源的利用最大化接奈。若事務(wù) T1 對數(shù)據(jù)加上了 S 鎖,T1 在該事務(wù)期間只能讀取數(shù)據(jù)而不能修改數(shù)據(jù)船殉。與此同時事務(wù) T2 也只能夠?qū)?shù)據(jù)加上 S 鎖而不能加上 X 鎖鲫趁。在該事務(wù)期間加任何的 X 鎖都會失敗斯嚎,只有當(dāng)該數(shù)據(jù)的所有 S 鎖釋放之后利虫,加 X 鎖才會成功。

select * from table_name where ... lock in share mode;

排它鎖

排它鎖也稱為寫鎖堡僻,當(dāng)事務(wù) T1 對數(shù)據(jù)加 X 鎖之后糠惫,該事務(wù)就具備了對應(yīng)的數(shù)據(jù)操作權(quán)限,可以對數(shù)據(jù)進(jìn)行修改(update钉疫、delete etc)硼讽。 此時如果有事務(wù) T2 想對加了 X 鎖的數(shù)據(jù)進(jìn)行加鎖時,則不會成功(S鎖也不行)牲阁,必須得事務(wù) T1 釋放 X 鎖之方可加鎖(X固阁、S 都可以)。

select * from table_name for update;

查看鎖

紙上來得終覺淺,絕知此事要躬行

  • 關(guān)閉事務(wù)自動提交
  • 開啟事務(wù)
  • 加鎖
  • 查看鎖狀態(tài)
set autocommit = 0;
start transaction;
select * from table_name ... for update; // X 鎖
select * from table_name ... lock in share mode; // S 鎖

操作的時候開啟多個終端城菊,每個終端為一個客戶端連接到 MySQL 的服務(wù)端备燃,按上面的步驟進(jìn)行操作。為 T1S 鎖凌唬,然后再 T2X 鎖并齐,顯示如下的信息。

...省略...
------------
TRANSACTIONS
------------
Trx id counter 6418
Purge done for trx's n:o < 6415 undo n:o < 0 state: running but idle
History list length 2
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 422149355321168, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 6417, ACTIVE 136 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 5 row lock(s)
MySQL thread id 13, OS thread handle 140674203023104, query id 168 localhost root Sending data
select * from repl_tb1 for update
------- TRX HAS BEEN WAITING 4 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 25 page no 3 n bits 72 index PRIMARY of table `repldb`.`repl_tb1` trx id 6417 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
...省略...

從上面信息中我們可以看到事務(wù)的信息客税,有多少鎖在使用中况褪,比如 X 鎖等待了多長時間等,這些信息有助于排查問題,優(yōu)化數(shù)據(jù)庫等更耻。

鎖級別

根據(jù)資源使用類型來對鎖分類的話只有共享鎖與排它鎖测垛,在數(shù)據(jù)庫的鎖分類里面還有按鎖的級別進(jìn)行分類,或者說是按鎖的粒度進(jìn)行分類秧均,我們最常說的就是表級鎖(Table-Level)和行級鎖(Row-Level)食侮,其實還有頁面鎖(Page-Level)脊奋,但是這個鎖被提及的情況沒有像前面兩種那樣廣泛。

  • 表級鎖: 鎖定整張表
  • 行級鎖: 鎖定特定行
  • 頁面鎖: 鎖定數(shù)據(jù)頁

表疙描、行诚隙、頁面鎖也分 XS 鎖,X 鎖期間仍然禁止其它事務(wù)的一切操作, S 鎖期間允許加 S 鎖進(jìn)行數(shù)據(jù)的讀取操作起胰。

鎖的不同級別相應(yīng)的開鎖和處理能力也各不相同久又,如下表所示:

類型 開銷 速度 死鎖 粒度 并發(fā)
表級鎖
行級鎖
頁面鎖

表中的大小、快慢等均是以三個級別相較而言效五,但是它們之間的差距根據(jù)數(shù)據(jù)量的不同可能會有天差地別地消。如表級鎖和行級鎖并發(fā)量而言,表級鎖的并發(fā)量只有1畏妖,但是行級鎖則不一樣脉执。行級鎖由你的數(shù)據(jù)庫的連接大小決定,你的服務(wù)端能支撐多少個并發(fā)連接那并發(fā)量就能夠達(dá)到多大(各連接操作不同的行數(shù)據(jù),如果操作相同的行,那并發(fā)量就會相應(yīng)的減小)戒劫。

InnoDB 鎖

使用 MySQL 時半夷,我們講的鎖如果不特殊說明,那么我們講的一定是 InnoDB 引擎的鎖迅细,而這時的鎖我們講的其實是 X 鎖(如果都是 S 鎖的話那還有加鎖的必要嗎)巫橄,那 InnoDBX 鎖到底是如何實現(xiàn)的呢, 它到底鎖住了什么呢?

InnoDB 引擎對不同的查詢采用不同的加鎖方式,如下:

  • 不帶索引條件, 采用表級鎖( select * from table_name )
  • 帶有索引條件, 采用行級鎖( select * from table_name where ...)

表級鎖鎖住了表, 行級鎖鎖住了行嗎? 當(dāng)然不是的茵典,InnoDB 引擎的行級鎖綜合性能和并發(fā)得的考慮湘换,行級鎖鎖住的查詢時候使用的索引列的索引(Index)。當(dāng)為表添加多個索引時统阿,加鎖時就可以通過不同的事務(wù)對不兩只的記錄進(jìn)行加鎖,而使用行鎖或者是表鎖與所建立的類型無關(guān)(pk,unique etc)彩倚。

思考一個問題, 是否可以通過不同的索引列對同一條數(shù)據(jù)同時進(jìn)行加鎖呢 ? ①

行鎖類型

上面提到我們查詢時用得基本都是行鎖,那行鎖對不同類型的查詢又是如何加鎖呢?要回答這個問題得先弄清楚查詢有幾種方式扶平。查詢不外乎精確匹配帆离、范圍匹配兩大類,所以行鎖分為幾類:

  • Record Locks

    記錄鎖,它鎖住查詢時索引列對應(yīng)的索引,如:SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE;

  • Gap Locks

    間隙鎖蜻直,我自己稱其為 區(qū)間鎖, 當(dāng)查詢條件是 大于盯质、小于、BETWEEN..AND 等時就彩此鎖, 該鎖是基于性能和并發(fā)的取舍而設(shè)計概而,且只在一些事務(wù)隔離級別才會使用呼巷。

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

    如上面的查詢語句,它會鎖住 10~20 的整個索引赎瑰,而不管該區(qū)間內(nèi)是否存在如 c1 = 15 等情況王悍,也就是說如果沒有 c1 = 15 的記錄,那么此時其它事務(wù)若想插入 c1 = 15 的記錄也是不允許的餐曼。

  • Next-key

    Next-key 鎖压储,我給不出一個準(zhǔn)確的翻譯名稱鲜漩,它是由 record locksgap locks 聯(lián)合組成的一類鎖,它不僅鎖定匹配的記錄本身集惋,它還鎖定查詢時帶有的一定范圍孕似。

    SELECT c1 FROM t WHERE c1 >= 100 FOR UPDATE;
    

    如該查詢語句,若此時表里面的記錄沒有 c1 的最大值就是 100, 那么該鎖鎖定的范圍是 [100,positive infinity)刮刑。也就是這時如果插入 c1 = 101 是不允許的喉祭,無法加鎖成功。

InnoDB 在事務(wù)隔離級別是 REPEATABLE READ 時雷绢,默認(rèn)就采用該鎖泛烙,從而阻止行的幻讀( Phantom read)。

意向鎖 (Intention Locks)

意向鎖是什么呢? 其實意向鎖就是解決一個問題翘紊,前面我們說行鎖蔽氨、表鎖,但是都是獨立開來講帆疟,意向鎖其實就是行鎖和表鎖共同融合的一類鎖鹉究。意向鎖簡寫 I,同時也分兩類鎖 ISIX, 也就是意向共享鎖和意向看它鎖鸯匹。

  • IS: indicates that a transaction intends to set a shared lock on individual rows in a table.
  • IX: indicates that a transaction intends to set an exclusive lock on individual rows in a table.

意向鎖其實講究的是兼容性坊饶,只要具備兼容性,那么就可以加鎖成功殴蓬。兼容性列表如下:

. X IX S IS
X Conflict Conflict Conflict Conflict
IX Conflict Compatible Conflict Compatible
S Conflict Conflict Compatible Compatible
IS Conflict Compatible Compatible Compatible

只要兼容,或者說只要不產(chǎn)生沖突蟋滴,那么就可以加鎖成功染厅,獲取操作權(quán)限。

AppendX

最后編輯于
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
  • 序言:七十年代末,一起剝皮案震驚了整個濱河市尔苦,隨后出現(xiàn)的幾起案子涩馆,更是在濱河造成了極大的恐慌,老刑警劉巖允坚,帶你破解...
    沈念sama閱讀 211,948評論 6 492
  • 序言:濱河連續(xù)發(fā)生了三起死亡事件魂那,死亡現(xiàn)場離奇詭異,居然都是意外死亡稠项,警方通過查閱死者的電腦和手機涯雅,發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 90,371評論 3 385
  • 文/潘曉璐 我一進(jìn)店門,熙熙樓的掌柜王于貴愁眉苦臉地迎上來展运,“玉大人活逆,你說我怎么就攤上這事精刷。” “怎么了蔗候?”我有些...
    開封第一講書人閱讀 157,490評論 0 348
  • 文/不壞的土叔 我叫張陵怒允,是天一觀的道長。 經(jīng)常有香客問我锈遥,道長误算,這世上最難降的妖魔是什么? 我笑而不...
    開封第一講書人閱讀 56,521評論 1 284
  • 正文 為了忘掉前任迷殿,我火速辦了婚禮儿礼,結(jié)果婚禮上,老公的妹妹穿的比我還像新娘庆寺。我一直安慰自己蚊夫,他們只是感情好,可當(dāng)我...
    茶點故事閱讀 65,627評論 6 386
  • 文/花漫 我一把揭開白布懦尝。 她就那樣靜靜地躺著知纷,像睡著了一般。 火紅的嫁衣襯著肌膚如雪陵霉。 梳的紋絲不亂的頭發(fā)上琅轧,一...
    開封第一講書人閱讀 49,842評論 1 290
  • 那天,我揣著相機與錄音踊挠,去河邊找鬼乍桂。 笑死,一個胖子當(dāng)著我的面吹牛效床,可吹牛的內(nèi)容都是我干的睹酌。 我是一名探鬼主播,決...
    沈念sama閱讀 38,997評論 3 408
  • 文/蒼蘭香墨 我猛地睜開眼剩檀,長吁一口氣:“原來是場噩夢啊……” “哼憋沿!你這毒婦竟也來了?” 一聲冷哼從身側(cè)響起沪猴,我...
    開封第一講書人閱讀 37,741評論 0 268
  • 序言:老撾萬榮一對情侶失蹤辐啄,失蹤者是張志新(化名)和其女友劉穎,沒想到半個月后运嗜,有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體壶辜,經(jīng)...
    沈念sama閱讀 44,203評論 1 303
  • 正文 獨居荒郊野嶺守林人離奇死亡,尸身上長有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點故事閱讀 36,534評論 2 327
  • 正文 我和宋清朗相戀三年洗出,在試婚紗的時候發(fā)現(xiàn)自己被綠了士复。 大學(xué)時的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片。...
    茶點故事閱讀 38,673評論 1 341
  • 序言:一個原本活蹦亂跳的男人離奇死亡,死狀恐怖阱洪,靈堂內(nèi)的尸體忽然破棺而出便贵,到底是詐尸還是另有隱情,我是刑警寧澤冗荸,帶...
    沈念sama閱讀 34,339評論 4 330
  • 正文 年R本政府宣布承璃,位于F島的核電站,受9級特大地震影響蚌本,放射性物質(zhì)發(fā)生泄漏盔粹。R本人自食惡果不足惜,卻給世界環(huán)境...
    茶點故事閱讀 39,955評論 3 313
  • 文/蒙蒙 一程癌、第九天 我趴在偏房一處隱蔽的房頂上張望舷嗡。 院中可真熱鬧,春花似錦嵌莉、人聲如沸进萄。這莊子的主人今日做“春日...
    開封第一講書人閱讀 30,770評論 0 21
  • 文/蒼蘭香墨 我抬頭看了看天上的太陽中鼠。三九已至,卻和暖如春沿癞,著一層夾襖步出監(jiān)牢的瞬間援雇,已是汗流浹背。 一陣腳步聲響...
    開封第一講書人閱讀 32,000評論 1 266
  • 我被黑心中介騙來泰國打工椎扬, 沒想到剛下飛機就差點兒被人妖公主榨干…… 1. 我叫王不留惫搏,地道東北人。 一個月前我還...
    沈念sama閱讀 46,394評論 2 360
  • 正文 我出身青樓盗舰,卻偏偏與公主長得像晶府,于是被迫代替她去往敵國和親。 傳聞我的和親對象是個殘疾皇子钻趋,可洞房花燭夜當(dāng)晚...
    茶點故事閱讀 43,562評論 2 349