本文導(dǎo)讀

本文將通過(guò)鎖的分類，包括庫(kù)鎖、表鎖、頁(yè)鎖、行鎖等等，詳細(xì)介紹MySQL鎖的使用、以及MySQL的優(yōu)化和MySQL InnoDB加鎖原理。

一、MySQL中三種鎖分類

這里直接給出結(jié)論，MySQL中有三種鎖：頁(yè)級(jí)鎖、表級(jí)鎖和行級(jí)鎖。

表鎖：低開(kāi)銷，快速鎖定；無(wú)死鎖；鎖粒度大，鎖沖突的概率最高，并發(fā)性最低。它出現(xiàn)在MyISAM、Memory、InnoDB、BDB和其他存儲(chǔ)引擎中，基本都支持。

行鎖：高開(kāi)銷，慢鎖定；將出現(xiàn)死鎖；鎖粒度最小，鎖沖突的概率最低，并發(fā)性最高。InnoDB存儲(chǔ)引擎支持。

頁(yè)鎖：成本和鎖定時(shí)間介于表鎖和行鎖之間；有可能出現(xiàn)死鎖；鎖定粒度介于表鎖和行鎖之間，并發(fā)性一般，僅有BDB存儲(chǔ)引擎支持。

行鎖、表鎖、頁(yè)鎖分別對(duì)應(yīng)存儲(chǔ)引擎關(guān)系 ：

二、MySQL中存在的第四種鎖——庫(kù)鎖

1、什么是庫(kù)鎖

庫(kù)鎖是鎖定整個(gè)數(shù)據(jù)庫(kù)實(shí)例。MySQL提供了一種添加全局讀鎖的方法。需要使整個(gè)庫(kù)為只讀時(shí)，可以使用此鎖。

這時(shí)數(shù)據(jù)更新語(yǔ)句（數(shù)據(jù)添加、刪除和修改）、數(shù)據(jù)定義語(yǔ)句（包括表創(chuàng)建、表結(jié)構(gòu)修改等）以及更新類型事務(wù)的提交語(yǔ)句，都會(huì)被阻塞。

FLUSH TABLES WITH READ LOCK -- 啟動(dòng)庫(kù)鎖，這整個(gè)庫(kù)只讀

UNLOCK TABLES  -- 釋放庫(kù)鎖

2、庫(kù)鎖的使用場(chǎng)景

庫(kù)鎖的典型使用場(chǎng)景是對(duì)整個(gè)數(shù)據(jù)庫(kù)進(jìn)行邏輯備份。但是官方的邏輯備份工具mysqldump使用參數(shù) –single transaction 時(shí)，將在導(dǎo)入數(shù)據(jù)之前啟動(dòng)事務(wù)，以確保獲得一致性視圖（MVCC支持）。MVCC在，MySQLMVCC原理中詳解：（鏈接待補(bǔ)充）。

所以庫(kù)鎖一般資料中很少提到，并且工作中也很少使用。

三、MySQL鎖的使用

1、表鎖

表鎖通常處理并發(fā)問(wèn)題。然而，支持行鎖定的引擎InnoDB通常不使用 lock-tables 命令來(lái)控制并發(fā)。

-- MySQL表鎖語(yǔ)法
LOCK TABLES tbl_name [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE}
lock tables fork_business_detail read/write

unlock tables -- 釋放鎖

2、InnoDB 中的鎖

在 MySQL InnoDB 存儲(chǔ)引擎中，鎖分為行鎖和表鎖。

2.1、共享鎖、獨(dú)占鎖

行鎖包括兩種類型：共享鎖、獨(dú)占鎖

共享鎖（S）：可以同時(shí)讀取多個(gè)事務(wù)，不互斥，但是共享鎖會(huì)阻止獨(dú)占鎖；獨(dú)占鎖（X）：允許獲得獨(dú)占鎖的事務(wù)更新數(shù)據(jù)，并防止其他事務(wù)獲得同一數(shù)據(jù)集的共享讀鎖和獨(dú)占寫(xiě)鎖。

此外InnoDB還具有兩種類型的內(nèi)部意圖鎖，這兩種類型都是表鎖。

2.2、意向共享鎖、意向獨(dú)占鎖、自增鎖

表鎖有三種類型：意向共享鎖、意向獨(dú)占鎖（排他鎖）、自增鎖（自增計(jì)數(shù)器）

意向共享鎖（IS）：事務(wù)計(jì)劃將行共享鎖添加到數(shù)據(jù)行。在向數(shù)據(jù)行添加共享鎖之前，事務(wù)必須首先獲得表的IS鎖。

有意獨(dú)占鎖（IX）：事務(wù)打算向數(shù)據(jù)行添加獨(dú)占鎖。在向數(shù)據(jù)行添加獨(dú)占鎖之前，事務(wù)必須首先獲得表的IX鎖。

自增鎖（AUTO-INC Locks）：表鎖的一種。自增長(zhǎng)計(jì)數(shù)器通過(guò)這個(gè)“鎖”獲得子增長(zhǎng)計(jì)數(shù)器的最大計(jì)數(shù)值。

在添加行鎖之前，您必須首先獲得一個(gè)表級(jí)意圖鎖，或者等待innodb_lock_wait_timeout，根據(jù)innodb_ rollback_on_timeout確定是否回滾事務(wù)。

3、Innodb行鎖

3.1、InnoDB行鎖的三種類型

InnoDB行鎖定是通過(guò)鎖定索引數(shù)據(jù)頁(yè)上的記錄來(lái)實(shí)現(xiàn)的。有三種主要算法：Record Lock、Gap Lock 和 Next-key Lock。

行鎖（Record Lock）鎖：?jiǎn)涡杏涗浀逆i定（鎖定數(shù)據(jù)，而不是間隙）。鎖被直接添加到索引記錄而不是行數(shù)據(jù)，鍵被鎖定。

間隙鎖（Gap Lock）鎖：間隙鎖，鎖定一個(gè)范圍，不包括記錄本身（不鎖定數(shù)據(jù)，只鎖定數(shù)據(jù)前面的間隙），鎖定索引記錄的間隙，并確保索引記錄的間距保持不變。

間隙鎖用于隔離處于或高于可重復(fù)讀取級(jí)別的事務(wù)。

Next-key Lock 鎖：同時(shí)鎖定數(shù)據(jù)，并鎖定數(shù)據(jù)前面的間隙。行鎖和間隙鎖的組合稱為下一個(gè)鍵鎖。

3.2、Innodb默認(rèn)使用 Next-Key Lock

默認(rèn)情況下，Innodb 工作在可重復(fù)讀取隔離級(jí)別，并以 Next-Key Lock 的方式鎖定數(shù)據(jù)，這可以有效地防止幻讀。

Next Key Lock 是行鎖和間隙鎖的組合。

當(dāng)InnoDB掃描索引記錄時(shí)，它首先對(duì)索引記錄應(yīng)用行鎖（Record Lock），然后對(duì)索引記錄兩側(cè)的間隙應(yīng)用間隙鎖（Gap Lock），添加間隙鎖定后，其他事務(wù)無(wú)法在此間隙中修改或插入記錄。

-- 注：普通查詢是快照讀，不需要加鎖
-- for update 僅適用于InnoDB，并且必須開(kāi)啟事務(wù)，在begin與commit之間才生效。
begin;
select * from user_info where name = 'xiaoming' for update;
commit;

3.3、Innodb對(duì)Next-Key Lock的優(yōu)化

優(yōu)化1：對(duì)于索引的等效查詢，當(dāng)唯一索引被鎖定時(shí)，下一個(gè)鍵鎖退化為行鎖。

優(yōu)化2：對(duì)于索引上的等價(jià)查詢，當(dāng)向右遍歷且最后一個(gè)值不滿足等價(jià)條件時(shí)，鎖退化為間隙鎖。當(dāng)“唯一索引”用于“搜索唯一行”語(yǔ)句時(shí)，不需要間隙鎖。

begin;
select name from user_info where name = 'xiaoming' for update;
commit;

例如，如果name是唯一索引，并且只搜索 name，那么只有此行將與記錄鎖一起使用。
如果名稱列沒(méi)有索引或是非唯一索引，則語(yǔ)句將生成間隙鎖。如果搜索條件中有多個(gè)查詢條件（即使每列都有一個(gè)唯一的索引），也會(huì)有間隙鎖。

3.4、排查 InnoDB 鎖問(wèn)題

通常有兩種方法來(lái)解決InnoDB鎖問(wèn)題。

1、打開(kāi) innodb_lock_monitor 表記住在使用后關(guān)閉監(jiān)視器表，否則會(huì)影響性能。

2、在information_schema 庫(kù)下面的 innodb_locks、innodb_lock_waits、innodb_trx排查

3、間隙鎖不是互斥的。兩個(gè)事務(wù)加上間隙鎖不是互斥的。事務(wù)A可以鎖定相同的數(shù)據(jù)以阻止操作，而事務(wù)B可以鎖定相同數(shù)據(jù)以防止操作。這可能導(dǎo)致死鎖問(wèn)題。

4、可以禁用間隙鎖的兩種方法，一是把隔離級(jí)別降為讀已提交（read committed），二開(kāi)啟參數(shù)innodb_locks_unsafe_for_binlog。

可以通過(guò) show variables like 'innodb_locks_unsafe_for_binlog'; （默認(rèn)不開(kāi)啟，如果發(fā)現(xiàn)有l(wèi)ong 事務(wù)可以排查下間隙鎖）命令查看該庫(kù)是否開(kāi)啟間隙鎖。

四、Innodb的 Next-lock 加鎖工作原理

分析鎖時(shí)需要跟隔離級(jí)別聯(lián)系起來(lái)，我們以可重復(fù)讀 RR（REPEATABLE-READ） 為例，首先開(kāi)啟兩個(gè)事務(wù)

左邊執(zhí)行 select * from fork_business_detail where sub_odr_id='xiaoming' ，會(huì)加 next-key lock。

右邊執(zhí)行insert語(yǔ)句就會(huì)阻塞。

加鎖是要基于索引的。

1、主鍵，加鎖行為僅在 主鍵索引記錄上加排他（X）鎖。

2、唯一索引，先在唯一索引 id 上加排他（X）鎖，再在的主鍵索引記錄上加排他（X）鎖。若記錄不存在，那么加間隙鎖。

3、普通索引，先通過(guò)索引上定位到第一個(gè)滿足的記錄，對(duì)該記錄加 X 鎖，而且要在主鍵上面，之間加上 Gap lock，為了防止幻讀，然后在主鍵索引 name 上加對(duì)應(yīng)記錄的X 鎖；再通過(guò)該索引上定位，有沒(méi)有其他滿足的記錄，同上。最后直到發(fā)現(xiàn)沒(méi)有滿足的記錄了，此時(shí)不需要加 X 鎖，但要再加一個(gè) Gap lock（間隙鎖），這個(gè)鎖擴(kuò)到該數(shù)據(jù)的下一位。

也就是說(shuō)滿足條件的數(shù)據(jù)之間上下一位都會(huì)別鎖住。

4、無(wú)索引，表里所有行和間隙均排他（X）鎖，直接鎖表了，所以在使用的時(shí)候一定要走索引。

總結(jié)

本文將通過(guò)鎖的分類，包括庫(kù)鎖、表鎖、頁(yè)鎖、行鎖等等，詳細(xì)介紹MySQL鎖的使用、以及MySQL的優(yōu)化和MySQL InnoDB加鎖原理。