MySQL InnoDB 鎖 學(xué)習(xí)筆記

所在文集：數(shù)據(jù)庫

本文的內(nèi)容參考了：

下面會(huì)涉及到 MySQL 數(shù)據(jù)庫隔離級(jí)別和索引，請(qǐng)先參見：

自增鎖

MySQL InnoDB 默認(rèn)的隔離級(jí)別為 RR，假設(shè)有數(shù)據(jù)表：
t(id AUTO_INCREMENT, name);
數(shù)據(jù)表中有數(shù)據(jù)：

1, shenjian
2, zhangsan
3, lisi

事務(wù) A 先執(zhí)行，還未提交：insert into t(name) values(xxx);
事務(wù) B 后執(zhí)行：insert into t(name) values(ooo);

問：事務(wù)B會(huì)不會(huì)被阻塞？ 答案是會(huì)阻塞，分析如下：

  • 事務(wù) A 先執(zhí)行 insert，會(huì)得到一條 (4, xxx) 的記錄，由于是自增列，InnoDB 會(huì)自動(dòng)增長(zhǎng)，注意此時(shí)事務(wù)并未提交；
  • 事務(wù) B 后執(zhí)行 insert，假設(shè)不會(huì)被阻塞，那會(huì)得到一條 (5, ooo) 的記錄；

此時(shí)，并未有什么不妥，但如果，事務(wù) A 繼續(xù) insert：insert into t(name) values(xxoo);會(huì)得到一條 (6, xxoo) 的記錄。
事務(wù) A 再 select：select * from t where id>3; 得到的結(jié)果是：

4, xxx
6, xxoo

注意：不可能查詢到 (5, ooo) 的記錄，因?yàn)樵?RR 的隔離級(jí)別下，不可能讀取到還未提交事務(wù)生成的數(shù)據(jù)。
這對(duì)于事務(wù) A 來說，就很奇怪了，對(duì)于 AUTO_INCREMENT 的列，連續(xù)插入了兩條記錄，一條是 4，接下來一條變成了 6，就像莫名其妙的幻影。

自增鎖是一種特殊的表級(jí)別鎖（table-level lock），專門針對(duì)事務(wù)插入 AUTO_INCREMENT 類型的列。最簡(jiǎn)單的情況，如果一個(gè)事務(wù)正在往表中插入記錄，所有其他事務(wù)的插入必須等待，以便第一個(gè)事務(wù)插入的行，是連續(xù)的主鍵值。

共享/排他鎖（Shared and Exclusive Locks）

共享鎖（S鎖）和排他鎖（X鎖）是行級(jí)別的鎖（row-level locking）：

  • 事務(wù)拿到某一行記錄的共享S鎖，才可以讀取這一行；
    • 多個(gè)事務(wù)可以拿到一把共享S鎖，讀讀可以并行
  • 事務(wù)拿到某一行記錄的排它X鎖，才可以修改或者刪除這一行；
    • 只有一個(gè)事務(wù)可以拿到排它X鎖，寫寫/讀寫必須互斥；

共享/排它鎖的潛在問題是，不能充分的并行，解決思路是數(shù)據(jù)多版本。參見：MySQL InnoDB 并發(fā)控制，事務(wù)的實(shí)現(xiàn) 學(xué)習(xí)筆記

意向鎖（Intention Locks）

意向鎖是指，未來的某個(gè)時(shí)刻，事務(wù)可能要加共享/排它鎖了，先提前聲明一個(gè)意向。意向鎖有這樣一些特點(diǎn)：

  • 首先，意向鎖，是一個(gè)表級(jí)別的鎖（table-level locking）；
  • 意向鎖分為：
    • 意向共享鎖（intention shared lock, IS）例如：select ... lock in share mode 要設(shè)置意向共享鎖；
    • 意向排它鎖（intention exclusive lock, IX）例如：select ... for update 要設(shè)置意向排它鎖；
  • 意向鎖協(xié)議（intention locking protocol）并不復(fù)雜：
    • 事務(wù)要獲得某些行的 S 鎖，必須先獲得表的 IS 鎖
    • 事務(wù)要獲得某些行的 X 鎖，必須先獲得表的 IX 鎖
  • 由于意向鎖僅僅表明意向，它其實(shí)是比較弱的鎖，意向鎖之間并不相互互斥，而是可以并行

插入意向鎖（Insert Intention Locks）

對(duì)已有數(shù)據(jù)行的修改 update 與刪除 delete，必須加排他鎖（X鎖），那對(duì)于數(shù)據(jù)的插入 insert，是否還需要加這么強(qiáng)的鎖，來實(shí)施互斥呢？

插入意向鎖，是間隙鎖（Gap Locks）的一種（所以，也是實(shí)施在索引上的），它是專門針對(duì) insert 操作的：
多個(gè)事務(wù)，在同一個(gè)索引，同一個(gè)范圍區(qū)間插入記錄時(shí)，如果插入的位置不沖突，不會(huì)阻塞彼此。

回到上面自增鎖時(shí)所用的插入的例子。如果主鍵不是自增的 t(id unique PK, name); 數(shù)據(jù)表中有數(shù)據(jù)：

10, shenjian
20, zhangsan
30, lisi

事務(wù) A 先執(zhí)行，在 10 與 20 兩條記錄中插入了一行，還未提交：insert into t values(11, xxx);
事務(wù) B 后執(zhí)行，也在 10 與 20 兩條記錄中插入了一行：insert into t values(12, ooo);

問：會(huì)使用什么鎖？使用的是插入意向鎖。

問：事務(wù)B會(huì)不會(huì)被阻塞呢？雖然事務(wù)隔離級(jí)別是 RR，雖然是同一個(gè)索引，雖然是同一個(gè)區(qū)間，但插入的記錄并不沖突，故這里并不會(huì)阻塞事務(wù) B。

思路總結(jié)：

  • InnoDB 使用共享鎖，可以提高讀讀并發(fā)；
  • 為了保證數(shù)據(jù)強(qiáng)一致，InnoDB 使用強(qiáng)互斥鎖，保證同一行記錄修改與刪除的串行性；
  • InnoDB 使用插入意向鎖，可以提高插入并發(fā)；

記錄鎖（Record Locks）

記錄鎖封鎖索引記錄，例如：
select * from t where id=1 for update;
它會(huì)在 id=1 的索引記錄上加鎖，以阻止其他事務(wù)插入，更新，刪除 id=1 的這一行。

需要說明的是：
select * from t where id=1;
則是快照讀(SnapShot Read)，它并不加鎖。

間隙鎖（Gap Locks）

間隙鎖封鎖索引記錄中的間隔，或者第一條索引記錄之前的范圍，又或者最后一條索引記錄之后的范圍。
t(id PK, name KEY); 表中有四條記錄：

1, shenjian
3, zhangsan
5, lisi
9, wangwu

SQL 語句：select * from t where id between 8 and 15 for update;會(huì)封鎖區(qū)間，以阻止其他事務(wù) id=10 的記錄插入。

為什么要阻止 id=10 的記錄插入？如果能夠插入成功，頭一個(gè)事務(wù)執(zhí)行相同的 SQL 語句，會(huì)發(fā)現(xiàn)結(jié)果集多出了一條記錄，即幻影數(shù)據(jù)。（即我們采用 MySQL InnoDB 的 可重復(fù)讀 RR 隔離級(jí)別）

如果把事務(wù)的隔離級(jí)別降級(jí)為讀提交（Read Committed, RC），間隙鎖則會(huì)自動(dòng)失效。

臨鍵鎖（Next-Key Locks）

臨鍵鎖，是記錄鎖與間隙鎖的組合，它的封鎖范圍，既包含索引記錄，又包含索引區(qū)間。
更具體的，臨鍵鎖會(huì)封鎖索引記錄本身，以及索引記錄之前的區(qū)間。
t(id PK, name KEY); 表中有四條記錄：

1, shenjian
3, zhangsan
5, lisi
9, wangwu

主鍵上潛在的臨鍵鎖為：

(-infinity, 1]
(1, 3]
(3, 5]
(5, 9]
(9, +infinity]

臨鍵鎖的主要目的，也是為了避免幻讀（Phantom Read）。如果把事務(wù)的隔離級(jí)別降級(jí)為RC，臨鍵鎖則也會(huì)失效。

各種SQL到底加了什么鎖

普通 select

  • 在讀未提交（Read Uncommitted），讀提交（Read Committed, RC），可重復(fù)讀（Repeated Read, RR）這三種事務(wù)隔離級(jí)別下，普通 selec 使用快照讀（snpashot read），不加鎖，并發(fā)非常高；
  • 在串行化（Serializable）這種事務(wù)的隔離級(jí)別下，普通 select 會(huì)升級(jí)為 select ... in share mode;

加鎖 select

  • select ... for update
  • select ... in share mode

如果，在唯一索引（unique index）上使用唯一的查詢條件（unique search condition），會(huì)使用記錄鎖（record lock），而不會(huì)封鎖記錄之間的間隔，即不會(huì)使用間隙鎖（gap lock）與臨鍵鎖（next-key lock）；

假設(shè)有 InnoDB 表：t(id PK, name);

1, shenjian
2, zhangsan
3, lisi

SQL 語句：select * from t where id=1 for update; 只會(huì)封鎖記錄，而不會(huì)封鎖區(qū)間。
其他的查詢條件和索引條件，InnoDB 會(huì)封鎖被掃描的索引范圍，并使用間隙鎖與臨鍵鎖，避免索引范圍區(qū)間插入記錄。

update 與 delete

和加鎖 select 類似，如果在唯一索引上使用唯一的查詢條件來 update/delete，例如：
update t set name=xxx where id=1; 也只加記錄鎖；

否則，符合查詢條件的索引記錄之前，都會(huì)加排他臨鍵鎖（exclusive next-key lock），來封鎖索引記錄與之前的區(qū)間；

尤其需要特殊說明的是，如果 update 的是聚集索引（clustered index）記錄，則對(duì)應(yīng)的普通索引（secondary index）記錄也會(huì)被隱式加鎖，這是由 InnoDB 索引的實(shí)現(xiàn)機(jī)制決定的：普通索引存儲(chǔ) PK 的值，檢索普通索引本質(zhì)上要二次掃描聚集索引。

insert

同樣是寫操作，insert 和 update 與 delete 不同，它會(huì)用排它鎖封鎖被插入的索引記錄，而不會(huì)封鎖記錄之前的范圍。

同時(shí)，會(huì)在插入?yún)^(qū)間加插入意向鎖（insert intention lock），但這個(gè)并不會(huì)真正封鎖區(qū)間，也不會(huì)阻止相同區(qū)間的不同 KEY 插入。

InnoDB 調(diào)試死鎖

InnoDB 的行鎖都是實(shí)現(xiàn)在索引上的，實(shí)驗(yàn)可以使用主鍵，建表時(shí)設(shè)定為 InnoDB 引擎：

create table t (
id int(10) primary key
)engine=innodb;

插入一些實(shí)驗(yàn)數(shù)據(jù)：

start transaction;
insert into t values(1);
insert into t values(3);
insert into t values(10);
commit;

實(shí)驗(yàn)一，間隙鎖互斥

開啟區(qū)間鎖，RR 的隔離級(jí)別下，上例會(huì)有四個(gè)區(qū)間：

(-infinity, 1)
(1, 3)
(3, 10)
(10, infinity)

事務(wù) A 刪除某個(gè)區(qū)間內(nèi)的一條不存在記錄，獲取到共享間隙鎖，會(huì)阻止其他事務(wù) B 在相應(yīng)的區(qū)間插入數(shù)據(jù)，因?yàn)椴迦胄枰@取排他間隙鎖。

session A：

set session autocommit=0;
start transaction;
delete from t where id=5;

session B：

set session autocommit=0;
start transaction;
insert into t values(0);
insert into t values(2);
insert into t values(12);
insert into t values(7);

事務(wù) B 插入的值：0, 2, 12 都不在 (3, 10) 區(qū)間內(nèi)，能夠成功插入，而 7 在 (3, 10) 這個(gè)區(qū)間內(nèi)，會(huì)阻塞。

可以使用：show engine innodb status; 來查看鎖的情況。

如果事務(wù) A 提交或者回滾，事務(wù) B 就能夠獲得相應(yīng)的鎖，以繼續(xù)執(zhí)行。
如果事務(wù) A 一直不提交，事務(wù) B 會(huì)一直等待，直到超時(shí)。

實(shí)驗(yàn)二，共享排他鎖死鎖

事務(wù) A 先執(zhí)行：

set session autocommit=0;
start transaction;
insert into t values(7);

事務(wù) B 后執(zhí)行：

set session autocommit=0;
start transaction;
insert into t values(7);

事務(wù) C 最后執(zhí)行：

set session autocommit=0;
start transaction;
insert into t values(7);

三個(gè)事務(wù)都試圖往表中插入一條為 7 的記錄：

  • A 先執(zhí)行，插入成功，并獲取 id=7 的排他鎖；
  • B 后執(zhí)行，需要進(jìn)行PK校驗(yàn)，故需要先獲取 id=7 的共享鎖，阻塞；
  • C 后執(zhí)行，也需要進(jìn)行PK校驗(yàn)，也要先獲取 id=7 的共享鎖，也阻塞；

如果此時(shí)，事務(wù) A 執(zhí)行：rollback; 釋放 id=7排他鎖。
則 B，C 會(huì)繼續(xù)進(jìn)行主鍵校驗(yàn)：

  • B 會(huì)獲取到 id=7共享鎖，主鍵未互斥；
  • C 也會(huì)獲取到 id=7 共享鎖，主鍵未互斥；

B 和 C 要想插入成功，必須獲得 id=7 的排他鎖，但由于雙方都已經(jīng)獲取到 id=7 的共享鎖，它們都無法獲取到彼此的排他鎖，死鎖就出現(xiàn)了。

當(dāng)然，InnoDB有死鎖檢測(cè)機(jī)制，B 和 C 中的一個(gè)事務(wù)會(huì)插入成功，另一個(gè)事務(wù)會(huì)自動(dòng)放棄。

共享排他鎖，在并發(fā)量插入相同記錄的情況下會(huì)出現(xiàn)，相應(yīng)的案例比較容易分析。

實(shí)驗(yàn)三，并發(fā)間隙鎖的死鎖

SQL 執(zhí)行序列如下：

A：set session autocommit=0;
A：start transaction;
A：delete from t where id=6;
         B：set session autocommit=0;
         B：start transaction;
         B：delete from t where id=7;
A：insert into t values(5);
         B：insert into t values(8);
  • A 執(zhí)行 delete 后，會(huì)獲得 (3, 10) 的共享間隙鎖。
  • B 執(zhí)行 delete 后，也會(huì)獲得 (3, 10) 的共享間隙鎖。
  • A 執(zhí)行 insert 后，希望獲得 (3, 10) 的排他間隙鎖，于是會(huì)阻塞。
  • B 執(zhí)行 insert 后，也希望獲得 (3, 10) 的排他間隙鎖，于是死鎖出現(xiàn)。

檢測(cè)到死鎖后，事務(wù) B 自動(dòng)回滾了，事務(wù) A 將會(huì)執(zhí)行成功。

最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請(qǐng)結(jié)合常識(shí)與多方信息審慎甄別。
平臺(tái)聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡(jiǎn)書系信息發(fā)布平臺(tái)，僅提供信息存儲(chǔ)服務(wù)。

友情鏈接更多精彩內(nèi)容