Q: MySQL 加鎖時鎖的是索引還是數(shù)據(jù) ?
如果別人問你這個問題的話,你是成竹在胸還是不知所措? 當(dāng)然蹦狂,這都不是重點击蹲,重點是之后你只有一個答案那就是胸有成竹询刹。
鎖類型
鎖的名稱真的有很多種莽红,但從資源的使用來說就是兩種竭业,共享鎖(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)行操作。為 T1 加 S 鎖凌唬,然后再 T2 加 X 鎖并齐,顯示如下的信息。
...省略...
------------
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ù)頁
表疙描、行诚隙、頁面鎖也分 X 與 S 鎖,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 鎖的話那還有加鎖的必要嗎)巫橄,那 InnoDB
的 X 鎖到底是如何實現(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 locks 和 gap 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,同時也分兩類鎖 IS 和 IX, 也就是意向共享鎖和意向看它鎖鸯匹。
- 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
- ① 該問題與索引的實現(xiàn)有關(guān)津函,閱讀 MySQL 之 InnoDB Index 淺談 肖粮。