鎖的概念

之前我們學(xué)習(xí)過多線程，多線程當中如果想保證數(shù)據(jù)的準確性是如何實現(xiàn)的呢？沒錯，通過同步實現(xiàn)。同步就相當于是加鎖。加了鎖以后有什么好處呢？當一個線程真正在操作數(shù)據(jù)的時候，其他線程只能等待。當一個線程執(zhí)行完畢后，釋放鎖。其他線程才能進行操作！

那么我們的MySQL數(shù)據(jù)庫中的鎖的功能也是類似的。在我們學(xué)習(xí)事務(wù)的時候，講解過事務(wù)的隔離性，可能會出現(xiàn)臟讀、不可重復(fù)讀、幻讀的問題，當時我們的解決方式是通過修改事務(wù)的隔離級別來控制，但是數(shù)據(jù)庫的隔離級別呢我們并不推薦修改。所以，鎖的作用也可以解決掉之前的問題！

鎖機制 : 數(shù)據(jù)庫為了保證數(shù)據(jù)的一致性，而使用各種共享的資源在被并發(fā)訪問時變得有序所設(shè)計的一種規(guī)則。

舉例，在電商網(wǎng)站購買商品時，商品表中只存有1個商品，而此時又有兩個人同時購買，那么誰能買到就是一個關(guān)鍵的問題。

這里會用到事務(wù)進行一系列的操作：

• 先從商品表中取出物品的數(shù)據(jù)
• 然后插入訂單
• 付款后，再插入付款表信息
• 更新商品表中商品的數(shù)量

以上過程中，使用鎖可以對商品數(shù)量數(shù)據(jù)信息進行保護，實現(xiàn)隔離，即只允許第一位用戶完成整套購買流程，而其他用戶只能等待，這樣就解決了并發(fā)中的矛盾問題。

在數(shù)據(jù)庫中，數(shù)據(jù)是一種供許多用戶共享訪問的資源，如何保證數(shù)據(jù)并發(fā)訪問的一致性、有效性，是所有數(shù)據(jù)庫必須解決的一個問題，MySQL由于自身架構(gòu)的特點，在不同的存儲引擎中，都設(shè)計了面對特定場景的鎖定機制，所以引擎的差別，導(dǎo)致鎖機制也是有很大差別的。

表鎖和行鎖

我們首先來了解一下表鎖和行鎖：表鎖是指對一整張表加鎖，一般是 DDL 處理時使用；而行鎖則是鎖定某一行或者某幾行，或者行與行之間的間隙。

表鎖由 MySQL Server 實現(xiàn)，行鎖則是存儲引擎實現(xiàn)，不同的引擎實現(xiàn)的不同。在 MySQL 的常用引擎中 InnoDB 支持行鎖，而 MyISAM 則只能使用 MySQL Server 提供的表鎖。

表鎖

表鎖由 MySQL Server 實現(xiàn)，一般在執(zhí)行 DDL 語句時會對整個表進行加鎖，比如說 ALTER TABLE 等操作。在執(zhí)行 SQL 語句時，也可以明確指定對某個表進行加鎖。

mysql> lock table user read(write); # 分為讀鎖和寫鎖Query OK, 0 rows affected (0.00 sec)mysql> select * from user where id = 100; # 成功mysql> select * from role where id = 100; # 失敗，未提前獲取該 role的讀表鎖mysql> update user  set name = 'Tom' where id = 100; # 失敗，未提前獲得user的寫表鎖mysql> unlock tables; # 顯示釋放表鎖Query OK, 0 rows affected (0.00 sec)

表鎖使用的是一次性鎖技術(shù)，也就是說，在會話開始的地方使用 lock 命令將后續(xù)需要用到的表都加上鎖，在表釋放前，只能訪問這些加鎖的表，不能訪問其他表，直到最后通過 unlock tables 釋放所有表鎖。

除了使用 unlock tables 顯示釋放鎖之外，會話持有其他表鎖時執(zhí)行l(wèi)ock table 語句會釋放會話之前持有的鎖；會話持有其他表鎖時執(zhí)行 start transaction 或者 begin 開啟事務(wù)時，也會釋放之前持有的鎖。

行鎖

不同存儲引擎的行鎖實現(xiàn)不同，后續(xù)沒有特別說明，則行鎖特指 InnoDB 實現(xiàn)的行鎖。

在了解 InnoDB 的加鎖原理前，需要對其存儲結(jié)構(gòu)有一定的了解。InnoDB 是聚簇索引，也就是 B+樹的葉節(jié)點既存儲了主鍵索引也存儲了數(shù)據(jù)行。而 InnoDB 的二級索引的葉節(jié)點存儲的則是主鍵值，所以通過二級索引查詢數(shù)據(jù)時，還需要拿對應(yīng)的主鍵去聚簇索引中再次進行查詢。

下面以兩條 SQL 的執(zhí)行為例，講解一下 InnoDB 對于單行數(shù)據(jù)的加鎖原理。

update user set age = 10 where id = 49;update user set age = 10 where name = 'Tom';

第一條 SQL 使用主鍵索引來查詢，則只需要在 id = 49 這個主鍵索引上加上寫鎖；第二條 SQL 則使用二級索引來查詢，則首先在 name = Tom 這個索引上加寫鎖，然后由于使用 InnoDB 二級索引還需再次根據(jù)主鍵索引查詢，所以還需要在 id = 49 這個主鍵索引上加寫鎖，如上圖所示。

也就是說使用主鍵索引需要加一把鎖，使用二級索引需要在二級索引和主鍵索引上各加一把鎖。

根據(jù)索引對單行數(shù)據(jù)進行更新的加鎖原理了解了，那如果更新操作涉及多個行呢，比如下面 SQL 的執(zhí)行場景。

update user set age = 10 where id > 49;

上述 SQL 的執(zhí)行過程如下圖所示。MySQL Server 會根據(jù) WHERE 條件讀取第一條滿足條件的記錄，然后 InnoDB 引擎會將第一條記錄返回并加鎖，接著 MySQL Server 發(fā)起更新改行記錄的 UPDATE 請求，更新這條記錄。一條記錄操作完成，再讀取下一條記錄，直至沒有匹配的記錄為止。

這種場景下的鎖的釋放較為復(fù)雜，有多種的優(yōu)化方式，我對這塊暫時還沒有了解，還請知道的小伙伴在下方留言解釋。

下面主要依次介紹 InnoDB 中鎖的模式和類型，鎖的類型是指鎖的粒度或者鎖具體加在什么地方；而鎖模式描述的是鎖的兼容性，也就是加的是什么鎖，比如寫鎖或者讀鎖。

內(nèi)容基本來自于 MySQL 的技術(shù)文檔 innodb-lock 一章，感興趣的同學(xué)可以直接去閱讀原文，原文地址為見文章末尾。

行鎖的模式

鎖的模式有：讀意向鎖，寫意向鎖，讀鎖，寫鎖和自增鎖(auto_inc)，下面我們依次來看。

讀寫鎖

讀鎖，又稱共享鎖（Share locks，簡稱 S 鎖），加了讀鎖的記錄，所有的事務(wù)都可以讀取，但是不能修改，并且可同時有多個事務(wù)對記錄加讀鎖。

寫鎖，又稱排他鎖（Exclusive locks，簡稱 X 鎖），或獨占鎖，對記錄加了排他鎖之后，只有擁有該鎖的事務(wù)可以讀取和修改，其他事務(wù)都不可以讀取和修改，并且同一時間只能有一個事務(wù)加寫鎖。

讀寫意向鎖

由于表鎖和行鎖雖然鎖定范圍不同，但是會相互沖突。所以當你要加表鎖時，勢必要先遍歷該表的所有記錄，判斷是否加有排他鎖。這種遍歷檢查的方式顯然是一種低效的方式，MySQL 引入了意向鎖，來檢測表鎖和行鎖的沖突。

意向鎖也是表級鎖，也可分為讀意向鎖（IS 鎖）和寫意向鎖（IX 鎖）。當事務(wù)要在記錄上加上讀鎖或?qū)戞i時，要首先在表上加上意向鎖。這樣判斷表中是否有記錄加鎖就很簡單了，只要看下表上是否有意向鎖就行了。

意向鎖之間是不會產(chǎn)生沖突的，也不和 AUTO_INC 表鎖沖突，它只會阻塞表級讀鎖或表級寫鎖，另外，意向鎖也不會和行鎖沖突，行鎖只會和行鎖沖突。

自增鎖

AUTOINC 鎖又叫自增鎖（一般簡寫成 AI 鎖），是一種表鎖，當表中有自增列（AUTOINCREMENT）時出現(xiàn)。當插入表中有自增列時，數(shù)據(jù)庫需要自動生成自增值，它會先為該表加 AUTOINC 表鎖，阻塞其他事務(wù)的插入操作，這樣保證生成的自增值肯定是唯一的。AUTOINC 鎖具有如下特點：

• AUTO_INC 鎖互不兼容，也就是說同一張表同時只允許有一個自增鎖；
• 自增值一旦分配了就會 +1，如果事務(wù)回滾，自增值也不會減回去，所以自增值可能會出現(xiàn)中斷的情況。

顯然，AUTOINC 表鎖會導(dǎo)致并發(fā)插入的效率降低，為了提高插入的并發(fā)性，MySQL 從 5.1.22 版本開始，引入了一種可選的輕量級鎖（mutex）機制來代替 AUTOINC 鎖，可以通過參數(shù) innodbautoinclockmode 來靈活控制分配自增值時的并發(fā)策略。具體可以參考 MySQL 的 AUTOINCREMENT Handling in InnoDB 一文，鏈接在文末。

不同模式鎖的兼容矩陣

下面是各個表鎖之間的兼容矩陣。

總結(jié)起來有下面幾點：

• 意向鎖之間互不沖突；
• S 鎖只和 S/IS 鎖兼容，和其他鎖都沖突；
• X 鎖和其他所有鎖都沖突；
• AI 鎖只和意向鎖兼容；

行鎖的類型

根據(jù)鎖的粒度可以把鎖細分為表鎖和行鎖，行鎖根據(jù)場景的不同又可以進一步細分，依次為 Next-Key Lock，Gap Lock 間隙鎖，Record Lock 記錄鎖和插入意向 GAP 鎖。

不同的鎖鎖定的位置是不同的，比如說記錄鎖只鎖住對應(yīng)的記錄，而間隙鎖鎖住記錄和記錄之間的間隔，Next-Key Lock 則所屬記錄和記錄之前的間隙。不同類型鎖的鎖定范圍大致如下圖所示。

下面我們來依次了解一下不同的類型的鎖。

記錄鎖

記錄鎖是最簡單的行鎖，并沒有什么好說的。上邊描述 InnoDB 加鎖原理中的鎖就是記錄鎖，只鎖住 id = 49 或者 name = 'Tom' 這一條記錄。

當 SQL 語句無法使用索引時，會進行全表掃描，這個時候 MySQL 會給整張表的所有數(shù)據(jù)行加記錄鎖，再由 MySQL Server 層進行過濾。但是，在 MySQL Server 層進行過濾的時候，如果發(fā)現(xiàn)不滿足 WHERE 條件，會釋放對應(yīng)記錄的鎖。這樣做，保證了最后只會持有滿足條件記錄上的鎖，但是每條記錄的加鎖操作還是不能省略的。

所以更新操作必須要根據(jù)索引進行操作，沒有索引時，不僅會消耗大量的鎖資源，增加數(shù)據(jù)庫的開銷，還會極大的降低了數(shù)據(jù)庫的并發(fā)性能。

間隙鎖

還是最開始更新用戶年齡的例子，如果 id = 49 這條記錄不存在，這個 SQL 語句還會加鎖嗎？答案是可能有，這取決于數(shù)據(jù)庫的隔離級別。這種情況下，在 RC 隔離級別不會加任何鎖，在 RR 隔離級別會在 id = 49 前后兩個索引之間加上間隙鎖。

間隙鎖是一種加在兩個索引之間的鎖，或者加在第一個索引之前，或最后一個索引之后的間隙。這個間隙可以跨一個索引記錄，多個索引記錄，甚至是空的。使用間隙鎖可以防止其他事務(wù)在這個范圍內(nèi)插入或修改記錄，保證兩次讀取這個范圍內(nèi)的記錄不會變，從而不會出現(xiàn)幻讀現(xiàn)象。

值得注意的是，間隙鎖和間隙鎖之間是互不沖突的，間隙鎖唯一的作用就是為了防止其他事務(wù)的插入，所以加間隙 S 鎖和加間隙 X 鎖沒有任何區(qū)別。

Next-Key 鎖

Next-key鎖是記錄鎖和間隙鎖的組合，它指的是加在某條記錄以及這條記錄前面間隙上的鎖。假設(shè)一個索引包含 15、18、20 ，30，49，50 這幾個值，可能的 Next-key 鎖如下：

(-∞, 15]，(15, 18]，(18, 20]，(20, 30]，(30, 49]，(49, 50]，(50, +∞)

通常我們都用這種左開右閉區(qū)間來表示 Next-key 鎖，其中，圓括號表示不包含該記錄，方括號表示包含該記錄。前面四個都是 Next-key 鎖，最后一個為間隙鎖。和間隙鎖一樣，在 RC 隔離級別下沒有 Next-key 鎖，只有 RR 隔離級別才有。還是之前的例子，如果 id 不是主鍵，而是二級索引，且不是唯一索引，那么這個 SQL 在 RR 隔離級別下就會加如下的 Next-key 鎖 (30, 49](49, 50)

此時如果插入一條 id = 31 的記錄將會阻塞住。之所以要把 id = 49 前后的間隙都鎖住，仍然是為了解決幻讀問題，因為 id 是非唯一索引，所以 id = 49 可能會有多條記錄，為了防止再插入一條 id = 49 的記錄。

插入意向鎖

插入意向鎖是一種特殊的間隙鎖（簡寫成 II GAP）表示插入的意向，只有在 INSERT 的時候才會有這個鎖。注意，這個鎖雖然也叫意向鎖，但是和上面介紹的表級意向鎖是兩個完全不同的概念，不要搞混了。

插入意向鎖和插入意向鎖之間互不沖突，所以可以在同一個間隙中有多個事務(wù)同時插入不同索引的記錄。譬如在上面的例子中，id = 30 和 id = 49 之間如果有兩個事務(wù)要同時分別插入 id = 32 和 id = 33 是沒問題的，雖然兩個事務(wù)都會在 id = 30 和 id = 50 之間加上插入意向鎖，但是不會沖突。

插入意向鎖只會和間隙鎖或 Next-key 鎖沖突，正如上面所說，間隙鎖唯一的作用就是防止其他事務(wù)插入記錄造成幻讀，正是由于在執(zhí)行 INSERT 語句時需要加插入意向鎖，而插入意向鎖和間隙鎖沖突，從而阻止了插入操作的執(zhí)行。

不同類型鎖的兼容矩陣

不同類型鎖的兼容下如下圖所示。

其中，第一行表示已有的鎖，第一列表示要加的鎖。插入意向鎖較為特殊，所以我們先對插入意向鎖做個總結(jié)，如下：

• 插入意向鎖不影響其他事務(wù)加其他任何鎖。也就是說，一個事務(wù)已經(jīng)獲取了插入意向鎖，對其他事務(wù)是沒有任何影響的；
• 插入意向鎖與間隙鎖和 Next-key 鎖沖突。也就是說，一個事務(wù)想要獲取插入意向鎖，如果有其他事務(wù)已經(jīng)加了間隙鎖或 Next-key 鎖，則會阻塞。

其他類型的鎖的規(guī)則較為簡單：

• 間隙鎖不和其他鎖（不包括插入意向鎖）沖突；
• 記錄鎖和記錄鎖沖突，Next-key 鎖和 Next-key 鎖沖突，記錄鎖和 Next-key 鎖沖突；

常見加鎖場景分析

今天我們就從原理走向?qū)崙?zhàn)，分析常見 SQL 語句的加鎖場景。了解了這幾種場景，相信小伙伴們也能舉一反三，靈活地分析真實開發(fā)過程中遇到的加鎖問題。

如下圖所示，數(shù)據(jù)庫的隔離等級，SQL 語句和當前數(shù)據(jù)庫數(shù)據(jù)會共同影響該條 SQL 執(zhí)行時數(shù)據(jù)庫生成的鎖模式，鎖類型和鎖數(shù)量。

下面，我們會首先講解一下隔離等級、不同 SQL 語句 和 當前數(shù)據(jù)庫數(shù)據(jù)對生成鎖影響的基本規(guī)則，然后再依次具體 SQL 的加鎖場景。

隔離等級對加鎖的影響

MySQL 的隔離等級對加鎖有影響，所以在分析具體加鎖場景時，首先要確定當前的隔離等級。

• 讀未提交（Read Uncommitted 后續(xù)簡稱 RU）：可以讀到未提交的讀，基本上不會使用該隔離等級，所以暫時忽略。
• 讀已提交（Read Committed 后續(xù)簡稱 RC）：存在幻讀問題，對當前讀獲取的數(shù)據(jù)加記錄鎖。
• 可重復(fù)讀（Repeatable Read 后續(xù)簡稱 RR）：不存在幻讀問題，對當前讀獲取的數(shù)據(jù)加記錄鎖，同時對涉及的范圍加間隙鎖，防止新的數(shù)據(jù)插入，導(dǎo)致幻讀。
• 序列化（Serializable）：從 MVCC 并發(fā)控制退化到基于鎖的并發(fā)控制，不存在快照讀，都是當前讀，并發(fā)效率急劇下降，不建議使用。

這里說明一下，RC 總是讀取記錄的最新版本，而 RR 是讀取該記錄事務(wù)開始時的那個版本，雖然這兩種讀取的版本不同，但是都是快照數(shù)據(jù)，并不會被寫操作阻塞，所以這種讀操作稱為 快照讀（Snapshot Read）

MySQL 還提供了另一種讀取方式叫當前讀（Current Read），它讀的不再是數(shù)據(jù)的快照版本，而是數(shù)據(jù)的最新版本，并會對數(shù)據(jù)加鎖，根據(jù)語句和加鎖的不同，又分成三種情況：

• SELECT ... LOCK IN SHARE MODE：加共享(S)鎖
• SELECT ... FOR UPDATE：加排他(X)鎖
• INSERT / UPDATE / DELETE：加排他(X)鎖

當前讀在 RR 和 RC 兩種隔離級別下的實現(xiàn)也是不一樣的：RC 只加記錄鎖，RR 除了加記錄鎖，還會加間隙鎖，用于解決幻讀問題。

不同 SQL 語句對加鎖的影響

不同的 SQL 語句當然會加不同的鎖，總結(jié)起來主要分為五種情況：

• SELECT ... 語句正常情況下為快照讀，不加鎖；
• SELECT ... LOCK IN SHARE MODE 語句為當前讀，加 S 鎖；
• SELECT ... FOR UPDATE 語句為當前讀，加 X 鎖；
• 常見的 DML 語句（如 INSERT、DELETE、UPDATE）為當前讀，加 X 鎖；
• 常見的 DDL 語句（如 ALTER、CREATE 等）加表級鎖，且這些語句為隱式提交，不能回滾。

其中，當前讀的 SQL 語句的 where 從句的不同也會影響加鎖，包括是否使用索引，索引是否是唯一索引等等。

當前數(shù)據(jù)對加鎖的影響

SQL 語句執(zhí)行時數(shù)據(jù)庫中的數(shù)據(jù)也會對加鎖產(chǎn)生影響。

比如一條最簡單的根據(jù)主鍵進行更新的 SQL 語句，如果主鍵存在，則只需要對其加記錄鎖，如果不存在，則需要在加間隙鎖。

至于其他非唯一性索引更新或者插入時的加鎖也都不同程度的受到現(xiàn)存數(shù)據(jù)的影響，后續(xù)我們會一一說明。

具體場景分析

具體 SQL 場景分析主要借鑒何登成前輩的《MySQL 加鎖處理分析》文章和 aneasystone 的系列文章，在他們的基礎(chǔ)上進行了總結(jié)和整理。

我們使用下面這張 book 表作為實例，其中 id 為主鍵，ISBN（書號）為二級唯一索引，Author（作者）為二級非唯一索引，score（評分）無索引。

UPDATE 語句加鎖分析

下面，我們先來分析 UPDATE 相關(guān) SQL 在使用較為簡單 where 從句情況下加鎖情況。其中的分析原則也適用于 UPDATE,DELETE 和 SELECT ... FOR UPDATE等當前讀的語句。

聚簇索引，查詢命中

聚簇索引就是 InnoDB 存儲引擎下的主鍵索引，具體可參考《MySQL索引》。

下圖展示了使用 UPDATE book SET score = 9.2 WHERE ID = 10 語句命中的情況下在 RC 和 RR 隔離等級下的加鎖，兩種隔離等級下沒有任何區(qū)別，都是對 ID = 10 這個索引加排他記錄鎖。

聚簇索引，查詢未命中

下圖展示了 UPDATE book SET score = 9.2 WHERE ID = 16 語句未命中時 RR 隔離級別下的加鎖情況。

在 RC 隔離等級下，不需要加鎖；而在 RR 隔離級別會在 ID = 16 前后兩個索引之間加上間隙鎖。

值得注意的是，間隙鎖和間隙鎖之間是互不沖突的，間隙鎖唯一的作用就是為了防止其他事務(wù)的插入新行，導(dǎo)致幻讀，所以加間隙 S 鎖和加間隙 X 鎖沒有任何區(qū)別。

二級唯一索引，查詢命中

下圖展示了 UPDATE book SET score = 9.2 WHERE ISBN = 'N0003' 在 RC 和 RR 隔離等級下命中時的加鎖情況。

在 InnoDB 存儲引擎中，二級索引的葉子節(jié)點保存著主鍵索引的值，然后再拿主鍵索引去獲取真正的數(shù)據(jù)行，所以在這種情況下，二級索引和主鍵索引都會加排他記錄鎖。

二級唯一索引，查詢未命中

下圖展示了 UPDATE book SET score = 9.2 WHERE ISBN = 'N0008' 語句在 RR 隔離等級下未命中時的加鎖情況，RC 隔離等級下該語句未命中不會加鎖。

因為 N0008 大于 N0007，所以要鎖住 (N0007,正無窮)這段區(qū)間，而 InnoDB 的索引一般都使用 Suprenum Record 和 Infimum Record 來分別表示記錄的上下邊界。Infimum 是比該頁中任何記錄都要小的值，而 Supremum 比該頁中最大的記錄值還要大，這兩條記錄在創(chuàng)建頁的時候就有了，并且不會刪除。

所以，在 N0007 和 Suprenum Record 之間加了間隙鎖。

為什么不在主鍵上也加 GAP 鎖呢？歡迎留言說出你的想法。

二級非唯一索引，查詢命中

下圖展示了 UPDATE book SET score = 9.2 WHERE Author = 'Tom' 語句在 RC 隔離等級下命中時的加鎖情況。

我們可以看到，在 RC 等級下，二級唯一索引和二級非唯一索引的加鎖情況是一致的，都是在涉及的二級索引和對應(yīng)的主鍵索引上加上排他記錄鎖。

但是在 RR 隔離等級下，加鎖的情況產(chǎn)生了變化，它不僅對涉及的二級索引和主鍵索引加了排他記錄鎖，還在非唯一二級索引上加了三個間隙鎖，鎖住了兩個 Tom 索引值相關(guān)的三個范圍。

那為什么唯一索引不需要加間隙鎖呢？間隙鎖的作用是為了解決幻讀，防止其他事務(wù)插入相同索引值的記錄，而唯一索引和主鍵約束都已經(jīng)保證了該索引值肯定只有一條記錄，所以無需加間隙鎖。

需要注意的是，上圖雖然畫著 4 個記錄鎖，三個間隙鎖，但是實際上間隙鎖和它右側(cè)的記錄鎖會合并成 Next-Key 鎖。

所以實際情況有兩個 Next-Key 鎖，一個間隙鎖(Tom60,正無窮)和兩個記錄鎖。

二級非唯一索引，查詢未命中

下圖展示了 UPDATE book SET score = 9.2 WHERE Author = 'Sarah' 在 RR 隔離等級下未命中的加鎖情況，它會在二級索引 Rose 和 Tom 之間加間隙鎖。而 RC 隔離等級下不需要加鎖。

無索引

當 Where 從句的條件并不使用索引時，則會對全表進行掃描，在 RC 隔離等級下對所有的數(shù)據(jù)加排他記錄鎖。在RR 隔離等級下，除了給記錄加鎖，還會對記錄和記錄之間加間隙鎖。和上邊一樣，間隙鎖會和左側(cè)的記錄鎖合并成 Next-Key 鎖。

下圖就是 UPDATE book SET score = 9.2 WHERE score = 22 語句在兩種隔離等級下的加鎖情況。

聚簇索引，范圍查詢

上面介紹的場景都是 where 從句的等值查詢，而范圍查詢的加鎖又是怎么樣的呢？我們慢慢來看。

下圖是 UPDATE book SET score = 9.2 WHERE ID <= 25 在 RC 和 RR 隔離等級下的加鎖情況。

RC 場景下與等值查詢類似，只會在涉及的 ID = 10，ID = 18 和 ID = 25 索引上加排他記錄鎖。

而在 RR 隔離等級下則有所不同，它會加上間隙鎖，和對應(yīng)的記錄鎖合并稱為 Next-Key 鎖。除此之外，它還會在(25, 30] 上分別加 Next-Key 鎖。這一點是十分特殊的，具體原因還需要再探究。

二級索引，范圍查詢

下圖展示了 UPDATE book SET ISBN = N0001 WHERE score <= 7.9 在 RR 級別下的加鎖情況。

修改索引值

UPDATE 語句修改索引值的情況可以分開分析，首先 Where 從句的加鎖分析如上文所述，多了一步 Set 部分的加鎖。

下圖展示了 UPDATE book SET Author = 'John' WHERE ID = 10 在 RC 和 RR 隔離等級下的加鎖情況。除了在主鍵 ID 上進行加鎖，還會對二級索引上的 Bob(就值) 和 John(新值) 上進行加鎖。

DELETE 語句加鎖分析

一般來說，DELETE 的加鎖和 SELECT FOR UPDATE 或 UPDATE 并沒有太大的差異。

因為，在 MySQL 數(shù)據(jù)庫中，執(zhí)行 DELETE 語句其實并沒有直接刪除記錄，而是在記錄上打上一個刪除標記，然后通過后臺的一個叫做 purge 的線程來清理。從這一點來看，DELETE 和 UPDATE 確實是非常相像。事實上，DELETE 和 UPDATE 的加鎖也幾乎是一樣的。

INSERT 語句加鎖分析

接下來，我們來看一下 Insert 語句的加鎖情況。

Insert 語句在兩種情況下會加鎖：

• 為了防止幻讀，如果記錄之間加有間隙鎖，此時不能 Insert；
• 如果 Insert 的記錄和已有記錄造成唯一鍵沖突，此時不能 Insert；

除了上述情況，Insert 語句的鎖都是隱式鎖。隱式鎖是 InnoDB 實現(xiàn)的一種延遲加鎖的機制來減少加鎖的數(shù)量。

隱式鎖的特點是只有在可能發(fā)生沖突時才加鎖，減少了鎖的數(shù)量。另外，隱式鎖是針對被修改的 B+Tree 記錄，因此都是記錄類型的鎖，不可能是間隙鎖或 Next-Key 類型。

具體 Insert 語句的加鎖流程如下：

• 首先對插入的間隙加插入意向鎖（Insert Intension Locks）如果該間隙已被加上了間隙鎖或 Next-Key 鎖，則加鎖失敗進入等待；如果沒有，則加鎖成功，表示可以插入；
• 然后判斷插入記錄是否有唯一鍵，如果有，則進行唯一性約束檢查如果不存在相同鍵值，則完成插入如果存在相同鍵值，則判斷該鍵值是否加鎖如果沒有鎖， 判斷該記錄是否被標記為刪除如果標記為刪除，說明事務(wù)已經(jīng)提交，還沒來得及 purge，這時加 S 鎖等待；如果沒有標記刪除，則報 duplicate key 錯誤；如果有鎖，說明該記錄正在處理（新增、刪除或更新），且事務(wù)還未提交，加 S 鎖等待；
• 插入記錄并對記錄加 X 記錄鎖；

演示InnoDB鎖

• 數(shù)據(jù)準備

-- 創(chuàng)建db13數(shù)據(jù)庫
CREATE DATABASE db13;

-- 使用db13數(shù)據(jù)庫
USE db13;

-- 創(chuàng)建student表
CREATE TABLE student(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(10),
    age INT,
    score INT
);
-- 添加數(shù)據(jù)
INSERT INTO student VALUES (NULL,'張三',23,99),(NULL,'李四',24,95),
(NULL,'王五',25,98),(NULL,'趙六',26,97);

• 共享鎖

-- 標準語法
SELECT語句 LOCK IN SHARE MODE;

-- 窗口1
/*
    共享鎖：數(shù)據(jù)可以被多個事務(wù)查詢，但是不能修改
*/
-- 開啟事務(wù)
START TRANSACTION;

-- 查詢id為1的數(shù)據(jù)記錄。加入共享鎖
SELECT * FROM student WHERE id=1 LOCK IN SHARE MODE;

-- 查詢分數(shù)為99分的數(shù)據(jù)記錄。加入共享鎖
SELECT * FROM student WHERE score=99 LOCK IN SHARE MODE;

-- 提交事務(wù)
COMMIT;

-- 窗口2
-- 開啟事務(wù)
START TRANSACTION;

-- 查詢id為1的數(shù)據(jù)記錄(普通查詢，可以查詢)
SELECT * FROM student WHERE id=1;

-- 查詢id為1的數(shù)據(jù)記錄，并加入共享鎖(可以查詢。共享鎖和共享鎖兼容)
SELECT * FROM student WHERE id=1 LOCK IN SHARE MODE;

-- 修改id為1的姓名為張三三(不能修改，會出現(xiàn)鎖的情況。只有窗口1提交事務(wù)后，才能修改成功)
UPDATE student SET NAME='張三三' WHERE id = 1;

-- 修改id為2的姓名為李四四(修改成功，InnoDB引擎默認是行鎖)
UPDATE student SET NAME='李四四' WHERE id = 2;

-- 修改id為3的姓名為王五五(注意：InnoDB引擎如果不采用帶索引的列。則會提升為表鎖)
UPDATE student SET NAME='王五五' WHERE id = 3;

-- 提交事務(wù)
COMMIT;

• 排他鎖

-- 標準語法
SELECT語句 FOR UPDATE;

-- 窗口1
/*
    排他鎖：加鎖的數(shù)據(jù)，不能被其他事務(wù)加鎖查詢或修改
*/
-- 開啟事務(wù)
START TRANSACTION;

-- 查詢id為1的數(shù)據(jù)記錄，并加入排他鎖
SELECT * FROM student WHERE id=1 FOR UPDATE;

-- 提交事務(wù)
COMMIT;

-- 窗口2
-- 開啟事務(wù)
START TRANSACTION;

-- 查詢id為1的數(shù)據(jù)記錄(普通查詢沒問題)
SELECT * FROM student WHERE id=1;

-- 查詢id為1的數(shù)據(jù)記錄，并加入共享鎖(不能查詢。因為排他鎖不能和其他鎖共存)
SELECT * FROM student WHERE id=1 LOCK IN SHARE MODE;

-- 查詢id為1的數(shù)據(jù)記錄，并加入排他鎖(不能查詢。因為排他鎖不能和其他鎖共存)
SELECT * FROM student WHERE id=1 FOR UPDATE;

-- 修改id為1的姓名為張三(不能修改，會出現(xiàn)鎖的情況。只有窗口1提交事務(wù)后，才能修改成功)
UPDATE student SET NAME='張三' WHERE id=1;

-- 提交事務(wù)
COMMIT;

注意：鎖的兼容性

• 共享鎖和共享鎖 兼容
• 共享鎖和排他鎖 沖突
• 排他鎖和排他鎖 沖突
• 排他鎖和共享鎖 沖突

演示MyISAM鎖

• 數(shù)據(jù)準備

-- 創(chuàng)建product表
CREATE TABLE product(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(20),
    price INT
)ENGINE = MYISAM;  -- 指定存儲引擎為MyISAM

-- 添加數(shù)據(jù)
INSERT INTO product VALUES (NULL,'華為手機',4999),(NULL,'小米手機',2999),
(NULL,'蘋果',8999),(NULL,'中興',1999);

• 讀鎖

-- 標準語法
-- 加鎖
LOCK TABLE 表名 READ;

-- 解鎖(將當前會話所有的表進行解鎖)
UNLOCK TABLES;

-- 窗口1
/*
    讀鎖：所有連接只能讀取數(shù)據(jù)，不能修改
*/
-- 為product表加入讀鎖
LOCK TABLE product READ;

-- 查詢product表(查詢成功)
SELECT * FROM product;

-- 修改華為手機的價格為5999(修改失敗)
UPDATE product SET price=5999 WHERE id=1;

-- 解鎖
UNLOCK TABLES;

-- 窗口2
-- 查詢product表(查詢成功)
SELECT * FROM product;

-- 修改華為手機的價格為5999(不能修改，窗口1解鎖后才能修改成功)
UPDATE product SET price=5999 WHERE id=1;

• 寫鎖

-- 標準語法
-- 加鎖
LOCK TABLE 表名 WRITE;

-- 解鎖(將當前會話所有的表進行解鎖)
UNLOCK TABLES;

-- 窗口1
/*
    寫鎖：其他連接不能查詢和修改數(shù)據(jù)
*/
-- 為product表添加寫鎖
LOCK TABLE product WRITE;

-- 查詢product表(查詢成功)
SELECT * FROM product;

-- 修改小米手機的金額為3999(修改成功)
UPDATE product SET price=3999 WHERE id=2;

-- 解鎖
UNLOCK TABLES;

-- 窗口2
-- 查詢product表(不能查詢。只有窗口1解鎖后才能查詢成功)
SELECT * FROM product;

-- 修改小米手機的金額為2999(不能修改。只有窗口1解鎖后才能修改成功)
UPDATE product SET price=2999 WHERE id=2;

演示悲觀鎖和樂觀鎖

悲觀鎖的概念

• 就是很悲觀，它對于數(shù)據(jù)被外界修改的操作持保守態(tài)度，認為數(shù)據(jù)隨時會修改。
• 整個數(shù)據(jù)處理中需要將數(shù)據(jù)加鎖。悲觀鎖一般都是依靠關(guān)系型數(shù)據(jù)庫提供的鎖機制。
• 我們之前所學(xué)的行鎖，表鎖不論是讀寫鎖都是悲觀鎖。

樂觀鎖的概念

• 就是很樂觀，每次自己操作數(shù)據(jù)的時候認為沒有人會來修改它，所以不去加鎖。
• 但是在更新的時候會去判斷在此期間數(shù)據(jù)有沒有被修改。
• 需要用戶自己去實現(xiàn)，不會發(fā)生并發(fā)搶占資源，只有在提交操作的時候檢查是否違反數(shù)據(jù)完整性。

悲觀鎖和樂觀鎖使用前提

• 對于讀的操作遠多于寫的操作的時候，這時候一個更新操作加鎖會阻塞所有的讀取操作，降低了吞吐量。最后還要釋放鎖，鎖是需要一些開銷的，這時候可以選擇樂觀鎖。
• 如果是讀寫比例差距不是非常大或者系統(tǒng)沒有響應(yīng)不及時，吞吐量瓶頸的問題，那就不要去使用樂觀鎖，它增加了復(fù)雜度，也帶來了業(yè)務(wù)額外的風(fēng)險。這時候可以選擇悲觀鎖。

樂觀鎖的實現(xiàn)方式

版本號

• 給數(shù)據(jù)表中添加一個version列，每次更新后都將這個列的值加1。
• 讀取數(shù)據(jù)時，將版本號讀取出來，在執(zhí)行更新的時候，比較版本號。
• 如果相同則執(zhí)行更新，如果不相同，說明此條數(shù)據(jù)已經(jīng)發(fā)生了變化。
• 用戶自行根據(jù)這個通知來決定怎么處理，比如重新開始一遍，或者放棄本次更新。

-- 創(chuàng)建city表
CREATE TABLE city(
    id INT PRIMARY KEY AUTO_INCREMENT,  -- 城市id
    NAME VARCHAR(20),                   -- 城市名稱
    VERSION INT                         -- 版本號
);

-- 添加數(shù)據(jù)
INSERT INTO city VALUES (NULL,'北京',1),(NULL,'上海',1),(NULL,'廣州',1),(NULL,'深圳',1);

-- 修改北京為北京市
-- 1.查詢北京的version
SELECT VERSION FROM city WHERE NAME='北京';
-- 2.修改北京為北京市，版本號+1。并對比版本號
UPDATE city SET NAME='北京市',VERSION=VERSION+1 WHERE NAME='北京' AND VERSION=1;

時間戳

• 和版本號方式基本一樣，給數(shù)據(jù)表中添加一個列，名稱無所謂，數(shù)據(jù)類型需要是timestamp
• 每次更新后都將最新時間插入到此列。
• 讀取數(shù)據(jù)時，將時間讀取出來，在執(zhí)行更新的時候，比較時間。
• 如果相同則執(zhí)行更新，如果不相同，說明此條數(shù)據(jù)已經(jīng)發(fā)生了變化。

鎖的總結(jié)

表鎖和行鎖

• 行鎖：鎖的粒度更細，加行鎖的性能損耗較大。并發(fā)處理能力較高。InnoDB引擎默認支持！
• 表鎖：鎖的粒度較粗，加表鎖的性能損耗較小。并發(fā)處理能力較低。InnoDB、MyISAM引擎支持！

InnoDB鎖優(yōu)化建議

• 盡量通過帶索引的列來完成數(shù)據(jù)查詢，從而避免InnoDB無法加行鎖而升級為表鎖。
• 合理設(shè)計索引，索引要盡可能準確，盡可能的縮小鎖定范圍，避免造成不必要的鎖定。
• 盡可能減少基于范圍的數(shù)據(jù)檢索過濾條件。
• 盡量控制事務(wù)的大小，減少鎖定的資源量和鎖定時間長度。
• 在同一個事務(wù)中，盡可能做到一次鎖定所需要的所有資源，減少死鎖產(chǎn)生概率。
• 對于非常容易產(chǎn)生死鎖的業(yè)務(wù)部分，可以嘗試使用升級鎖定顆粒度，通過表級鎖定來減少死鎖的產(chǎn)生。