MySQL優化篇系列文章（二）——MyISAM表鎖與InnoDB鎖問題

龍騰萬里sky發表於2022-04-02
MySql優化

我可以和麵試官多聊幾句嗎？只是想...
MySQL優化篇系列文章（基於MySQL8.0測試驗證），上部分：優化SQL語句、資料庫物件，MyISAM表鎖和InnoDB鎖問題。

面試官：咦，小夥子，又來啦。

我：面試官，您好。一面確實收穫不少，二面想獲取更多的經驗。

面試官：不錯，不錯，不錯，年紀輕輕，有我當年一半的風範，挺有覺悟。接著聊MySQL鎖問題。

我：好呀，這次我準備了MyISAM和InnoDB鎖一些總結，希望您多多指教。

面試官：那，讓我們進入今天的話題，一起討論MySQL鎖問題。

我：好的，請接著往下看。

接著上一篇MySQL資料庫SQL優化流程。在對MySQL進行舉例並使用到示例資料庫：大多數情況使用MySQL官方提供的sakila（模擬電影出租資訊管理系統）和world資料庫，類似於Oracle的scott使用者。

第一篇MySQL優化篇SQL優化流程已經發布，原本想合一起發，但示例程式碼太多。導致篇幅很長，所以分篇發出來。（單篇太長，確實影響閱讀體驗，後續整理後會分篇發。）

分篇傳送第二篇MySQL優化篇，MyISAM表鎖和InnoDB鎖問題。

你可以將這篇博文，當成過度到MySQL8.0的參考資料。友情提示：經驗是用來參考，不是拿來即用。如果你能看到並分享這篇文章，我很榮幸。如果有誤導你的地方，我表示抱歉。

如果沒有進行特別說明，一般是基於MySQL8.0.28進行測試驗證。官方文件非常具有參考意義。目前市面上針對MySQL8.0書籍還比較少，部分停留在5.6.x和5.7.x版本，但仍然具有借鑑意義。

文中會給出官方文件可以找到的參考內容，基本上在小標題末尾有提及並說明。輔助你快速定位出處，心裡更有底氣。如果想應對MySQL面試，我想這篇總結還是有一定的參考意義。需要有耐心看完，個人總結時參考書籍和MySQL8.0官方文件也很乏味，純英文文件更令人頭大。不懂的地方可以使用有道，結合實際測試進行理解。

個人理解有限，難免出現錯誤偏差。所有測試，僅供參考。

如果感覺對你起到作用，有參考意義，想獲取原markdown檔案。

可以訪問我的個人github倉庫，定期上傳md檔案，空餘時間會製作目錄連結：

https://github.com/cnwangk/SQL-study/tree/master/md/SQL/MySQL

目錄

MySQL優化篇（二）

給出sakila-db資料庫包含三個檔案，便於大家獲取與使用：

  1. sakila-schema.sql：資料庫表結構；
  2. sakila-data.sql：資料庫示例模擬資料；
  3. sakila.mwb：資料庫物理模型，在MySQL workbench中可以開啟檢視。

https://downloads.mysql.com/docs/sakila-db.zip

world-db資料庫，包含三張表：city、country、countrylanguage。

只是用於用於簡單測試學習，建議使用world-db：

https://downloads.mysql.com/docs/world-db.zip

正文

友情提示：在某些情況，你自己測試的結果可能與我演示有所不同，我省略了查詢結果的部分引數。

本文側重點在SQL優化流程以及MySQL鎖問題（MyISAM和InnoDB儲存引擎）。圖片可能會掛，演示時儘量使用SQL查詢語句返回結果進行示例。篇幅很長，因此使用markdown語法加了目錄。

起初，也只是想看MySQL8.0.28有哪些變化，後面索性結合書籍和官方文件總結了一篇。花了將近兩週，基本是每天完善一點，因為個人只有晚上和週末有時間總結並測試驗證。或許後面會拆開發，如果拆開發會編寫再詳細一點。如果有錯別字，也請多多擔待。如果你能看到並分享這篇文章，我很榮幸。如果有誤導你的地方，我表示抱歉。

如果你是從MySQL5.6或者5.7版本過渡到MySQL8.0。學習之前，建議線看官方文件這一章節：1.3 What Is New MySQL8.0 。在做對比的時候，文件中帶有Note標識是你應該注意的地方。比如下面這張截圖：

在這裡插入圖片描述

參考文件：refman-8.0-en.pdf

參考書籍：

  • 《深入淺出MySQL 第2版 資料庫開發、優化與管理維護》，個人參考優化篇部分。
  • 《MySQL技術內幕InnoDB儲存引擎 第2版》，個人參考索引與鎖章節描述。

一、鎖問題

簡單概括鎖：鎖是計算機協調多個程式或執行緒併發訪問某一資源的機制。

MySQL中的鎖看上去用法和表面實現（對比其它DBMS），貌似很簡單，但真正深入理解其實也不是那麼容易。

01 MySQL鎖介紹

1.1 什麼是鎖

為何要使用鎖？開發多使用者、資料庫驅動的應用時，難點（痛點）：一方面要最大程度地利用資料庫的併發訪問，另一方面還要確保每個使用者能以一致的方式讀取和修改資料。因此有了鎖（locking）的機制，同時也是資料庫系統區別於檔案系統的一個關鍵特性。

在資料庫中，除傳統的計算資源（如CPU、RAM、I/O等）的消耗外，資料也是一種供許多使用者共享的資源。

如何保證資料併發訪問的一致性、有效性是所有資料庫必須解決的一個問題，鎖衝突也是影響資料庫併發訪問效能的重要因素。從描述來看，鎖對資料庫顯得尤為重要，也更加複雜。接下來，會對鎖機制特點進行介紹、常見的鎖問題，以及解決MySQL鎖問題的方法。

1.2 MySQL鎖

相比其它資料庫來說，MySQL的鎖機制相對好理解一點，其最顯著的特點是不同的儲存引擎支援不同鎖機制。比如MyISAM和MEMORY儲存引擎採用表級鎖（table-level locking）；BDB儲存引擎（MySQL8.0文件沒看到介紹）採用頁面鎖（page-level locking），但也支援表級鎖（table-level locking）；InnoDB儲存引擎既支援行級鎖（row-level locking），也支援表級鎖，預設採用行級鎖。

MySQL中3種鎖特性：

  • 表級鎖：開銷小，加鎖塊。不會出現死鎖，鎖粒度大，發生鎖衝突概率最高，併發度最低。
  • 行級鎖：開銷大，加鎖慢。會出現死鎖，鎖粒度最小，發生鎖衝突概率最低，併發度最高。
  • 頁面鎖：開銷和加鎖時間介於表鎖與行鎖之間。會出現死鎖，鎖粒度介於表鎖與行鎖之間，併發度一般。

從上述各種鎖特點來看，不能一概而論哪種鎖更好，但可以從具體應用特點來判斷哪種鎖更合適。

單從鎖角度出發：表鎖較為適合以查詢為主，少量按索引條件更新資料的應用。行級鎖更適合有大量按索引條件、併發更新少量不同資料，同時有併發查詢的應用。

02 MyISAM 表鎖

MyISAM（儲存引擎限制256TB）儲存引擎只支援表鎖，是MySQL開始幾個版本中唯一支援的鎖型別。

隨著應用對事務完整性和併發性要求的不斷提高，MySQL開發了基於事務儲存引擎，後來出現了支援頁面鎖的BDB（逐漸被InnoDB替代，MySQL8.0文件已看不到介紹）儲存引擎和支援行鎖的InnoDB儲存引擎。MyISAM表鎖依舊是使用比較廣泛的鎖型別。

2.1 查詢表鎖互掐

通過檢查Table_locks_waited和Table_locks_immediate狀態變數來分析系統上表鎖爭搶：

mysql> show status like 'table%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Table_locks_immediate      | 164   |
| Table_locks_waited         | 0     |
+----------------------------+-------+
...
5 rows in set (0.00 sec)

如果Table_locks_waited值比較大，則說明存在嚴重的表鎖爭搶情況。

2.2 MySQL表鎖模式

MySQL表鎖有兩種模式：

  1. Table Read Lock：表共享讀鎖；
  2. Table Write Lock：表獨佔寫鎖。

MyISAM表讀操作，不會阻塞其他使用者對同一表的讀請求，但會阻塞對同一表的寫請求；對MyISAM表的寫操作，則會阻塞其他使用者對同一表的讀和寫操作。MyISAM表的讀操作和寫操作之間，以及與寫操作之間是序列的。

當一個執行緒獲得對一個表的寫鎖後，只有持有鎖的執行緒可以對錶進行更新操作。其它執行緒的讀、寫操作都會等待，直到被釋放。

2.3 如何加表鎖

MyISAM在執行查詢（select）前，會自動給涉及的所有表加讀鎖；在執行更新（update、insert、delete等）操作前，會自動給涉及的表加寫鎖，這一過程並不需要使用者干預。所以，一般情況不需要使用者執行lock table命令給MyISAM顯式加鎖。

下面只是演示一下加鎖和解鎖，阻塞示例。如果想測試，可以在創表時指定儲存引擎為MyISAM，或者使用alter table命令修改儲存引擎。例如修改city表儲存引擎為MyISAM，使用show create table命令檢視：

mysql> alter table world.city engine=MyISAM;
mysql> show create table city;

city表測試新增寫鎖（lock table write）

mysql> lock table city write;
Query OK, 0 rows affected (0.00 sec)

當前session視窗，測試查詢（select）、插入（insert）、修改（update）、刪除（delete）均不受影響。

開啟其它session視窗，測試增刪改查均在等待中。分別給出查詢上鎖與解鎖示例。

上了寫鎖（當前session），其它視窗查詢顯示等待中...

解鎖（當前session），其它session視窗查詢正常並顯示查詢等待時間：

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from city limit 0,1;
1 row in set (1 min 33.43 sec)

city表測試新增讀鎖（lock table read）

mysql> lock table city read;
Query OK, 0 rows affected (0.00 sec)

演示更新操作插入（insert）、修改（update）、刪除（delete）：均提示有讀鎖（當前session）

mysql> insert into city values(9527,'ts','ts','ts',9527000);
ERROR 1099 (HY000): Table 'city' was locked with a READ lock and can't be updated

mysql> update city set name='Kabuls' where id=1;
ERROR 1099 (HY000): Table 'city' was locked with a READ lock and can't be updated

mysql> delete from city where id=1;
ERROR 1099 (HY000): Table 'city' was locked with a READ lock and can't be updated

解鎖（unlock）：

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

tips：新增讀鎖，當前session的新增、修改刪除均會提示已經上了鎖，查詢其它未上鎖表也會提示報錯。

其它session視窗依舊可以查詢、更新未上鎖的表。鎖住表不會提示，但是會在等待中。

使用lock table時，可能需要一次性鎖定用到的所有表，同一個表出現多次，需要對別名鎖定。

2.4 併發插入

整體上看，MyISAM表讀和寫是序列。在一定條件下，MyISAM表也支援查詢和插入操作併發進行。

MyISAM儲存引擎有一個系統變數concurrent_insert，用於控制其併發插入行為，值可以為0、1或2，預設為AUTO。

如下所示，使用select @@引數形式查詢系統變數值：

mysql> select @@concurrent_insert;
+---------------------+
| @@concurrent_insert |
+---------------------+
| AUTO                |
+---------------------+
  1. 當concurrent_insert設定為0：不允許併發插入。
  2. 當concurrent_insert設定為AUTO（or 1）：如果MyISAM表中間沒有被刪除的行，允許在一個程式讀表的同時，另一個程式從表尾插入記錄。也是MySQL預設設定AUTO（or 1）。
  3. 當concurrent_insert設定為2：無論MyISAM有無空洞（表中間沒有被刪除的行），都允許在表尾併發插入記錄。

示例：其中一個session獲得一張表read local鎖，該執行緒可以對當前表進行查詢，但不能進行更新操作。其它執行緒（session），雖然不能進行刪除和更新操作，但可以進行插入（insert）操作。假定條件：表中間沒有空洞。

其中一個執行緒（session1）獲取鎖：查詢不受影響，不能做更新操作

mysql> lock table xxls read local;						-- session1獲取read local鎖
Query OK, 0 rows affected (0.00 sec)
mysql> select * from xxls limit 0,1;					 -- 查詢一條資料（可行）
mysql> insert into xxls values(1015,'xxls','女','B');	-- 演示更新操作是拒絕的
ERROR 1099 (HY000): Table 'xxls' was locked with a READ lock and can't be updated

另外一個執行緒（session2）演示：插入資料完成，不受影響

mysql> insert into xxls values(1015,'xxls','女','B');
Query OK, 1 row affected (0.01 sec)

總結：可以利用MyISAM儲存引擎併發插入特性解決應用中對同一表查詢和插入鎖爭用。設定concurrent_insert值為2，總是允許併發插入。與此同時，通過定期在系統空閒時段（不活躍時段）執行optimize table整理空間碎片，回收刪除記錄產生的空洞。

optimize用法如下：注意如果有讀鎖情況下，是不能進行操作的。

mysql> optimize table tolove;	-- 優化tolove表
+-------------+----------+----------+----------+
| Table       | Op       | Msg_type | Msg_text |
+-------------+----------+----------+----------+
| test.tolove | optimize | status   | OK       |
+-------------+----------+----------+----------+
1 row in set (1.24 sec)

更多詳細描述（MySQL8.0）可以參考：

5.1.8 Server System Variables（服務系統變數）

2.5 MyISAM鎖排程

MyISAM儲存引擎讀鎖與寫鎖互斥，讀寫操作序列。

一個程式請求某MyISAM表讀鎖，另一個程式請求同一表寫鎖，MySQL能友好的處理麼？

答案是寫程式先獲得鎖。即使讀請求先等等待（排隊時，讀在前），寫在後等的不耐煩了。讀你給我挪挪位，寫優先插隊進入佇列中。個人認為寫鎖優先是合理的，畢竟寫（更新操作：insert、update、delete）比較重要，如何在最大限度保證資料完整性、一致性。

寫鎖優先，MySQL認為寫請求一般比讀請求重要。這也是為什麼MyISAM表不適合同時有大量更新操作和查詢操作的原因。大量更新操作會造成查詢操作很難獲得讀鎖，導致永遠阻塞。好在可以通過一些系統引數來調節MyISAM排程行為。通過指定引數low_priority_updates，讓MyISAM儲存引擎預設給予讀請求優先權利。

給出官方文件設定示範：

mysql> select @@low_priority_updates;	-- 查詢出預設值是0

--low-priority-updates[={OFF|ON}] 		-- 命令列格式，也可以在配置檔案中進行設定

set low_priority_updates=1				-- 在字元介面臨時設定值，設定為1，降低連線發出更新請求

另外MySQL提供了折中方法調節鎖衝突，給系統引數max_write_lock_count設定合適值。當一張表讀鎖達到這個值後，MySQL暫時將寫請求優先順序降低，給讀程式（session）獲得鎖的機會。

也不能太依賴一條SQL查詢語句解決問題，適當進行拆分成中間表進行合理控制查詢時間。有些複雜查詢（統計）操作是無法避免的，但可以人為定時操作，在夜深人靜之時（凌晨），悄無聲息執行。如果你是一名Java開發人員，或許在配置檔案（xml、yml）中應該做過這種操作。

03 InnoDB 鎖問題

ACID：在瞭解InnoDB鎖問題之前，可以先看一下InnoDB儲存引擎一些特性：簡稱ACID。

  1. 原子性A（atomicity）：事務是一個原子操作單元，對資料的修改要麼全執行，要麼全不執行。舉個例子：（銀行存錢，典型事務），正常情況：小芳去銀行存錢，銀行要麼將錢存到系統並顯示正常增長後的餘額，要沒全部回退出來。不正常情況：小芳存了一百大洋，銀行將錢吞了，賬戶餘額沒變；或者小芳賬戶餘額增加了，錢退回來了。
  2. 一致性C（consistency）：在事務開始和完成時，資料必須保持一致狀態。
  3. 隔離性I（isolation）：資料庫系統提供一定的隔離機制，保證事務在不受外部併發操作影響獨立環境執行。
  4. 永續性D（durability）：事務完成之後，它對資料的修改是永久性的，即使出現系統故障也能保持。

併發事務處理帶來的問題：

  1. 丟失更新（lost update）：當兩個或多個事務選擇同一行，然後基於最初選定的值更新該行時，由於每個事務都不知道其它事務的存在，就會發生丟失更新問題，最後的更新覆蓋了由其它事務所做的更新。（可以想象多人線上編輯同一份文件，有多個版本控制，最後還原到鎖問題上）
  2. 髒讀（dirty read）：一個事務正在對一條記錄做修改，在這個事務完成並提交前，這條記錄的資料就處於不一致狀態；這時，另一個事務也來讀取同一條記錄，如果不加控制，第二個事務讀取了這些“髒”資料，並作進一步處理，會產生未提交的資料依賴關係。這種現象被稱為髒讀。
  3. 不可重複度（non-repeatable read）：一個事務在讀取某些資料後的某個時間再次讀取以前讀過的資料，卻發現其讀過的資料已經發生了改變或某些記錄已被刪除。這種現象被稱為不可重複讀。
  4. 幻讀（phantom read）：一個事務按相同的查詢條件重新讀取以前檢索過的資料，卻發現其它事務插入了滿足其查詢條件的新資料，這種現象稱為幻讀。

髒讀與不可重複讀區別：髒讀是讀到未提交的資料，而不可重複度讀到的是已經提交的資料。

更多MySQL8.0資料庫的ACID模型介紹可以參考：

15.2 InnoDB and the ACID Model（InnoDB和ACID模型）

3.1 行級鎖的神話

InnoDB儲存引擎較MySQL資料庫其它儲存引擎在鎖這一方面技高一籌，實現方式類似於Oracle資料庫，提供一致性的非鎖定讀、行級鎖支援。行鎖沒有相關額外開銷，並可以同時得到併發性和一致性。

行級鎖的一個神話，鎖總會增加開銷。其實是這樣的，當實現本身會增加開銷時，行級鎖才會增加開銷。InnoDB不需要鎖升級，因為一個鎖和多個鎖的開銷是想同的。

對於MyISAM儲存引擎，其鎖是表鎖設計。併發情況讀沒有問題，但是併發插入效能略微差了一些。如果插入在底部，MyISAM儲存引擎還是有一定的併發寫入操作的。這裡重複介紹了，在介紹MyISAM表鎖時也有提到過。

3.2 lock與latch

MySQL資料庫區分鎖過程中，有一個容易令人混淆的概念lock與latch。在資料庫中，lock與latch都被稱為鎖，但二者有截然不同的含義。

  1. latch：一般稱為閂（shuan）鎖（輕量級的鎖），因為其要求鎖定的時間必須非常短。若持續時間長，則應用的效能會非常差。在InnoDB儲存引擎中，latch又可以分為mutex（互斥量）和rwlock（讀寫鎖）。其目的是用來保證併發執行緒操作臨界資源的正確性，並且通常沒有死鎖檢測的機制。
  2. lock：lock的物件是事務，用來鎖定的是資料庫中的物件，比如表、頁、行。一般lock的物件僅在事務commit或rollback後進行釋放，不同事務隔離級別釋放的時間可能不同。此外，lock正如在大多數資料庫中一樣，是有死鎖機制的。

對於InnoDB儲存引擎中的latch，可以通過命令檢視：

語法：SHOW ENGINE engine_name {STATUS | MUTEX}

mysql> show engine innodb mutex;
+--------+----------------------------+-------------+
| Type   | Name                       | Status      |
+--------+----------------------------+-------------+
| InnoDB | rwlock: fil0fil.cc:3360    | waits=6     |
| InnoDB | rwlock: dict0dict.cc:2508  | waits=4     |
| InnoDB | sum rwlock: buf0buf.cc:787 | waits=40351 |
+--------+----------------------------+-------------+
3 rows in set (0.00 sec)

參考MySQL8.0文件可以看到mutex更多介紹：13.7.7.15 SHOW ENGINE Statement

tips：在debug版本中，可以檢視到status引數的更多資訊。

3.3 鎖型別

鎖型別列表（InnoDB Locking）：

序號 InnoDB Locking
1 標準行級鎖共享鎖和排它鎖（Shared and Exclusive Locks）
2 記錄鎖（Record Locks）
3 間隙鎖（Gap Locks）
4 Next-Key Locks
5 插入意圖鎖（Insert Intention Locks）
6 AUTO-INC Locks
7 空間索引謂詞鎖（Predicate Locks for Spatial Indexes）

雖然上面列出了7種鎖，但下面只介紹標準行級鎖和意向鎖，其它鎖型別介紹可以參考MySQL8.0官方文件。

InnoDB儲存引擎實現了以下兩種型別標準行級鎖：

  1. 共享鎖（S Lock）：允許事務讀一行資料。
  2. 排它鎖（X Lock）：允許事務刪除或更新一行資料。

如果一個事務T1持有行r上的一個共享(S)鎖，那麼來自不同事務T2的請求對行r上的一個鎖處理如下:

  1. T2對共享鎖（S）的請求可以立即被授予（獲得行r共享鎖）。因此，T1和T2都對行r保持S鎖定。
  2. T2對排它鎖（X）鎖請求不能立即授予。

其它事務想獲得行r共享鎖，其它請求等待T1、T2釋放共享鎖。

如果事務T1持有行r上的排它(X)鎖，那麼來自不同事務T2對行r上任何一種型別的鎖請求都不能立即被授予。相反，事務T2必須等待事務T1釋放其對行r的鎖。

兩種標準行級鎖相容性如下表格所示：

X（排它鎖） S（共享鎖）
X（排它鎖） Conflict（不相容） Conflict（不相容）
S（共享鎖） Conflict（不相容） Compatible（相容）

從上面表格中可以發現X鎖與任何鎖都不相容，而S鎖僅與S鎖相容。

友情提示：S和X鎖是行鎖，相容指對同一行記錄（row）鎖相容性情況。

除此之外，InnoDB儲存引擎支援多粒度（granularity）鎖定，這種鎖定允許事務在行級上的鎖和表級上的鎖同時存在。為了支援在不同粒度上進行加鎖操作，InnoDB儲存引擎支援一種額外的鎖方式，稱為意向鎖（Intention Locks）。意向鎖將鎖定的物件分為多個層次，意味著事務希望在更加細粒度（fine granularity）上加行鎖。如下圖3-3所示：

如果將上鎖的物件看成一棵樹，那麼對最下層的物件上鎖，也就是對最細粒度物件進行上鎖，首先需要對粗粒度物件上鎖。

InnoDB儲存引擎支援意向鎖設計比較簡練，其意向鎖為表級別鎖。設計目的是為了在一個事務中揭示下一行將被請求的鎖型別，支援如下兩種意向鎖。

意向鎖（Intention Locks）：

  1. 意向共享鎖（IS）：事務想要獲得一張表中某幾行的共享鎖。
  2. 意向排它鎖（IX）：事務想要獲得一張表中某幾行的排它鎖。

由於InnoDB儲存引擎支援的是行級別鎖，因此意向鎖不會阻塞除全表掃描以外的任何請求。

表級鎖與行級鎖型別相容性彙總在如下面表格所示，並使用中文進行標註：

X（排它鎖） IX（意向排它鎖） S（共享鎖） IS（意向共享鎖）
X（排它鎖） Conflict（不相容） Conflict（不相容） Conflict（不相容） Conflict（不相容）
IX（意向排它鎖） Conflict（不相容） Compatible（相容） Conflict（不相容） Compatible（相容）
S（共享鎖） Conflict（不相容） Conflict（不相容） Compatible（相容） Compatible（相容）
IS（意向共享鎖） Conflict（不相容） Compatible（相容） Compatible（相容） Compatible（相容）

使用者可以通過命令show engine innodb status檢視當前鎖請求資訊：

show engine innodb status\G
*************************** 1. row ***************************
  Type: InnoDB
  Name:
Status:
=====================================
2022-03-23 22:16:07 0x32d0 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 15 seconds
...

------------
TRANSACTIONS
------------
Trx id counter 16145
Purge done for trx's n:o < 16144 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 283762070116728, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 283762070115952, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 16144, ACTIVE 237 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1128, 2 row lock(s)
MySQL thread id 1249, OS thread handle 12132, query id 15048 localhost ::1 root statistics
select * from world.city where id=1 for update
------- TRX HAS BEEN WAITING 3 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 28 page no 6 n bits 248 index PRIMARY of table `world`.`city` trx id 16144 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
 0: len 4; hex 80000001; asc ;;
 1: len 6; hex 0000000016c9; asc ;;
 2: len 7; hex 81000001410110; asc     A  ;;
 3: len 30; hex 4b6162756c20202020202020202020202020202020202020202020202020; asc Kabul                         ; (total 35 bytes);
 4: len 3; hex 414647; asc AFG;;
 5: len 20; hex 4b61626f6c202020202020202020202020202020; asc Kabol ;;
 6: len 4; hex 801b2920; asc   ) ;;
------------------
...
============================
END OF INNODB MONITOR OUTPUT
============================
... 
1 row in set (0.00 sec)

此處，主要擷取了事務相關引數，其它引數省略掉了。如何看到事務鎖具體資訊，可以手動去加鎖測試，製造一個場景。

示例用法：

前提使用時InnoDB型別表做測試，可以使用show create table table_name查詢當前表儲存引擎。

mysql> show create table world.city\G
*************************** 1. row ***************************
       Table: city
Create Table: CREATE TABLE `city` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `Name` char(35) NOT NULL DEFAULT '',
  `CountryCode` char(3) NOT NULL DEFAULT '',
  `District` char(20) NOT NULL DEFAULT '',
  `Population` int NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`),
  KEY `CountryCode` (`CountryCode`),
  CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`)
) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

MySQL8.0中使用select @@autocommit檢視到預設值是1，代表開啟了自動提交。測試使用時建議通過 set autocommit=0 命令先關閉自動提交，或者手動控制事務（begin、start transaction）。詳細示例不列舉了，可以參考前面SQL優化步驟進行測試。

mysql> begin
Query OK, 0 rows affected (0.00 sec)

SELECT ... LOCK IN SHARE MODE	-- 給語句加上共享鎖
mysql> select * from world.city where id=1 lock in share mode; -- 示例給city表加上共享鎖

SELECT ... FOR UPDATE			-- 給語句加上排它鎖
mysql> select * from world.city where id=1 for update;	-- 示例獲取排它鎖，超時會拋異常
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

上面列印出來引數有很多，執行緒（BACKGROUND THREAD）、訊號量（SEMAPHORES）、事務（TRANSACTIONS）、檔案I/O（FILE I/O）、插入快取和適配HASH索引（INSERT BUFFER AND ADAPTIVE HASH INDEX）、緩衝日誌檢查點（LOG）、緩衝池和記憶體（BUFFER POOL AND MEMORY）以及行操作（ROW OPERATIONS）。

個人感覺有必要說明一下快取（cache）與緩衝（buffer）區別：

  • 緩衝（buffer）：加速資料寫入硬碟；
  • 快取（cache）：加速資料從硬碟讀取。

在MySQL中information_schema架構下可以通過三張表：INNODB_TRX、INNODB_LOCKS、INNODB_LOCK_WAITS監控當前事務並分析可能存在的鎖問題。

友情提示：在5.6.x和5.7.x和MariaDB 10.5.6還能看到INNODB_LOCKS、INNODB_LOCK_WAITS；在MySQL8.0中已經移除，可以說換成另一種形式呈現：在performance_schema架構下有data_lock_waits、data_locks可以查詢參考。

INNODB_LOCKS與data_locks引數變化：有變化的引數加粗顯示

INNODB_LOCKS Column（引數） data_locks Column（引數）
LOCK_ID ENGINE_LOCK_ID：鎖的ID
LOCK_TRX_ID ENGINE_TRANSACTION_ID：儲存引擎事務ID
LOCK_MODE LOCK_MODE：鎖模式
LOCK_TYPE LOCK_TYPE：鎖型別
LOCK_TABLE (combined schema/table names) OBJECT_SCHEMA (schema name), OBJECT_NAME
(table name)：要加鎖的表
LOCK_INDEX LOCK_INDEX：鎖住的索引
LOCK_SPACE：鎖物件space id None
LOCK_PAGE：事務鎖定頁數量 None
LOCK_REC：事務鎖定行數量 None
LOCK_DATA LOCK_DATA：事務鎖定記錄主鍵值

INNODB_LOCK_WAITS與data_lock_waits引數變化：有變化的引數加粗顯示

INNODB_LOCK_WAITS Column（引數） data_lock_waits Column（引數）
REQUESTING_TRX_ID：申請鎖事務ID REQUESTING_ENGINE_TRANSACTION_ID
REQUESTED_LOCK_ID：申請鎖ID REQUESTING_ENGINE_LOCK_ID
BLOCKING_TRX_ID：阻塞事務ID BLOCKING_ENGINE_TRANSACTION_ID
BLOCKING_LOCK_ID：阻塞鎖ID BLOCKING_ENGINE_LOCK_ID

如果命令字元介面檢視不方便，可以藉助客戶端工具MySQL workbench或者SQLyog等等進行檢視。

更多引數詳細介紹，可以參考MySQL8.0官方文件進行檢視測試。

同樣可以使用其它命令檢視InnoDB儲存引擎資訊：

SHOW ENGINE INNODB MUTEX\G
SHOW ENGINE PERFORMANCE_SCHEMA STATUS\G 	-- 列印所有PERFORMANCE_SCHEMA狀態資訊
*************************** 1. row ***************************
  Type: performance_schema
  Name: events_waits_current.size
Status: 168
*************************** 2. row ***************************
  Type: performance_schema
  Name: events_waits_current.count
Status: 1536
...
248 rows in set (0.00 sec)

以上是對鎖型別進行簡單介紹，理論知識偏多，基本結合MySQL8.0進行說明。

與我之前一篇《MySQL8.0.28安裝教程全程參考官方文件》是一樣的用意，希望體會自學的好處，以及閱讀官方文件自我成長。英語差不是藉口，有道谷歌翻譯也能很好輔助你學習。

3.4 一致性非鎖（鎖）定讀

3.4.1 一致性非鎖定讀

查詢預設事務隔離級別 tx_isolation和tx_read_only系統引數已經在MySQL8.0.3中移除掉了，MySQL5.x和MariaDB10.5.6版本還可以繼續使用tx_isolation這個系統引數。

友情提示：新版MySQL8.0.3之後使用transaction_isolation和transaction_read_only替代。

select @@tx_isolation;	-- MySQL5.x版本可以繼續用

mysql> select @@transaction_isolation;	-- MySQL8.0.3版本開始使用新的系統引數
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
1 row in set (0.00 sec)

你可以參考文件：15.7.2.3 Consistent Nonlocking Reads

一致性非鎖定讀（Consistent Nonlocking Reads）是指InnoDB儲存引擎通過多版本控制（multi-versioning ）的方式來讀取當前執行時間資料庫中行的資料。如果讀取的行正在執行 DELETE 或 UPDATE操作，這時讀取操作不會因此去等待行上的鎖釋放。相反，InnoDB儲存引擎會讀取一個快照資料。如圖：3-4所示

圖3-4直觀地展現了InnoDB儲存引擎非鎖定一致性讀。之所以稱其為非鎖定讀：因為不需要等待訪問行上X鎖的釋放。快照資料是指該行之前版本的資料，該實現是通過undo段來完成。而undo用來在事務中回滾資料，因此快照資料本身是沒有開銷的。此外，讀取快照資料是不需要上鎖的，因為沒有事務需要對歷史資料進行修改操作。

可以看出，鎖定讀機制極大地提高了資料庫的併發性。在InnoDB儲存引擎預設設定下，這也是預設讀取方式，即讀取不會佔用和等待表上的鎖。但在不同事務隔離級別下，讀取方式不同，並不是在每個事務隔離級別下都採用非鎖定一致性讀。即使是使用非鎖定一致性讀，對於快照資料定義也各不相同。

通過圖3-4可以知道，快照資料其實是當前行資料之前的歷史版本，每行記錄可能有多個版本。如圖3-4所示，一個行記錄可能不止一個快照資料，一般稱這種技術為行多版本技術。由此帶來的併發控制，稱之為多版本併發控制（multi-version concurrency control (MVCC)）。

在事務隔離級別 READ-COMMITTED和REPEATABLE-READ（InnoDB儲存引擎預設事務隔離級別）下，InnoDB儲存引擎使用非鎖定一致性讀。然而，對於快照資料定義卻不相同。在READ-COMMITTED事務隔離級別下，對於快照資料，非一致性讀總是讀取事務開始時的行資料版本。

如下示例，在兩session A和session B會話中進行對比。在模擬併發過程中，希望帶著思考去測試，不然會暈乎乎的

前提設定事務非自動提交，或者手動開啟事務，前面演示也多次提到過。關鍵字大小寫不影響使用，個人使用統一規則就好。

修改事務隔離級（當前會話生效），便於測試：

mysql> set transaction_isolation='READ-COMMITTED';
Query OK, 0 rows affected (0.00 sec)

mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| READ-COMMITTED          |
+-------------------------+
1 row in set (0.00 sec)

session A：

-- session A
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from parent where id=1;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

會話A中已通過顯示地執行命令BEGIN開啟了一個事務，並讀取parent表中id=1的這條資料，但事務並沒有結束。與此同時使用者再開啟另一個會話B，可以模擬出併發場景，然後對session B做如下操作。

session B：

-- session B
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update parent set id=7 where id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

在會話B中將parent表id為1欄位值記錄修改為id=7，但事務同樣未提交，此時id=1的行加了一個X鎖。如果在會話A中再次讀取id=1的記錄，根據InnoDB儲存引擎特性，即在READ-COMMITTED和REPEATABLE-READ事務隔離級別下會使用非鎖定一致性讀。此時，再回到會話A中，繼續未提交的事務，執行SQL語句：select * from parent where id=1;操作，不管使用READ-COMMITTED還是REPEATABLE-READ事務隔離級別，顯示資料應該是：

mysql> select * from parent where id=1;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

由於當前id=1的資料被修改了1次，因此只有一個行版本記錄。接著會話2未提交的事務，提交事務：

-- session B
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

會話B提交事務後，會話1再次執行select * from parent where id=1;SQL語句，在READ-COMMITTED和REPEATABLE-READ事務隔離級別下得到結果就不一樣了。對於READ-COMMITTED事務隔離級別，它總是讀取該行版本最新一個快照（fresh snapshot）。在上述示例中，因為會話B已經提交事務，所以READ-COMMITTED事務隔離級別下會得到如下結果：

mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| READ-COMMITTED          |
+-------------------------+
1 row in set (0.00 sec)

mysql> select * from parent where id=1;
Empty set (0.00 sec)

對於REPEATABLE-READ（預設事務隔離級別），總是讀取事務開始時的行資料。此時將session A和session B步驟對調來操作。起初我看文件時，也誤解了，多研讀幾次才明白。得到示例結果如下：

mysql> select @@transaction_isolation;	
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
1 row in set (0.00 sec)

mysql> select * from parent where id=1;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

梳理一下session A和session B執行步驟，從時間角度演示：

時間（time） session A session B
1 BEGIN;
2 SELECT * FROM parent WHERE id=1;
3 BEGIN;
4 UPDATE parent SET id=7 WHERE id=1;
5 SELECT * FROM parent WHERE id=1;
6 COMMIT;
7 SELECT * FROM parent WHERE id=1;
8 COMMIT;

tips：測試時使用BEGIN顯示開啟也行，使用SET AUTOCOMMIT=0同樣也行。因為AUTOCOMMIT預設是1，所以手動禁止自動提交。

3.4.2 一致性鎖定讀

可以找到文件：15.7.2.4 Locking Reads

預設配置下，事務隔離級別為REPEATABLE-READ模式下，InnoDB儲存引擎的select操作使用一致性非鎖定讀。但在某種場景下，使用者需要顯示地對資料庫讀取操作進行加鎖以保證資料邏輯一致性。需要資料庫支援加鎖語句，即使是對select的只讀操作。InnoDB儲存引擎對select語句支援兩種一致性鎖定讀（locking reads ）

  • SELECT ... FOR UPDATE
  • SELECT ... LOCK IN SHARE MODE

友情提示：在MySQL8.0.22可以使用SELECT ... FOR SHARE替代SELECT ... LOCK IN SHARE MODE，但是SELECT ... LOCK IN SHARE MODE是向後相容，這兩個描述是相同的。然而，使用FOR SHARE支援table_name, NOWAIT（不等待），和越過LOCKED選項。

SELECT ... FOR UPDATE對讀取的行記錄加一個X鎖，其它事務不能對已鎖定的行加任何鎖。SELECT ... LOCK IN SHARE MODE對讀取的行記錄加上一個S鎖，其它事務可以向被鎖定的行加S鎖，但如果是X鎖，則會被阻塞。

對於一致性非鎖定讀，即使讀取的行已被執行SELECT ... FOR UPDATE，也是可以進行讀取的。此外，SELECT ... FOR UPDATE和SELECT ... LOCK IN SHARE MODE必須在一個事務中，當事務提交了，鎖也就釋放了。因此在使用上述兩句select鎖定語句時，務必加上begin，使用start transaction要設定set autocommit=0。前面也提到過autocommit值為0代表禁用自動提交。

3.5 自增長與鎖

自增長在資料庫中是非常常見的一種屬性，也是很多DBA或開發人員首選的主鍵方式。在InnoDB儲存引擎記憶體結構中，對每個含有自增長值的表都有一個自增長計數器（auto-increment counter）。對含有自增長的極計數器的表進行插入操作時，這個計數器會被初始化，執行如下語句得到計數器的值：

select MAX(auto_inc_col) from tbl_name for update;

插入操作會依據這個自增長的計數器值加1賦予自增長列。這種實現方式稱作AUTO-INC Locks。這種鎖其實是一個特殊的表鎖機制，為了提高插入效能，鎖不是在一個事務完成後才釋放，而是在完成對自增長值插入的SQL語句後立即釋放。

雖然AUTO-INC Locks從一定程度上提高了併發插入的效率，但還是存在一些效能上的問題。對於有自增長值的列併發插入效能較差，事務必須等待前一個插入的完成（不用等待事務的完成）。此外，對於insert ... select 的大資料量的插入會影響插入效能，因為另一個事務中的插入會被阻塞。

從MySQL5.1.22版本開始，InnoDB儲存引擎提供了一種輕量級互斥量的自增長實現機制，這種機制大大提高了自增長值插入的效能。並且該版本開始，InnoDB儲存引擎提供了一個引數innodb_autoinc_lock_mode來 控制自增長模式，該引數預設值為2（MySQL8.0）。一共有三個引數值可以設定，分別為（0、1、2），MySQL5.7預設值為1，MariaDB10.5.6版本預設也是1。

MySQL8.0查詢innodb_autoinc_lock_mode預設值：

mysql> select @@innodb_autoinc_lock_mode;
+----------------------------+
| @@innodb_autoinc_lock_mode |
+----------------------------+
|                          2 |
+----------------------------+
1 row in set (0.00 sec)

自增長型別：

  1. INSERT-like：有INSERT, INSERT ... SELECT，REPLACE， REPLACE ... SELECT，LOAD DATA等。包含 simple-inserts，bulk-inserts以及mixed-mode inserts
  2. Simple inserts：有 INSERT 和 REPLACE，不包含INSERT ... ON DUPLICATE KEY UPDATE。
  3. Bulk inserts：有INSERT ... SELECT，REPLACE ...SELECT，and LOAD DATA 。
  4. Mixed-mode inserts：出入中有一部分是自增長的，有一部分是確定的。比如：INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d')；也可以是 INSERT ... ON DUPLICATE KEY UPDATE。

自增長分類，一共有三個引數值可以設定，分別為（0、1、2）：

  1. innodb_autoinc_lock_mode=2：預設值為2。對於所有INSERT-like自增長值的產生都是通過互斥量，而不是AUTO-INC Locks方式。使用row-base replication，保證最大併發效能以及資料一致性，MySQL8.0推薦設定。包含 simple-inserts，bulk-inserts以及
    mixed-mode inserts。
  2. innodb_autoinc_lock_mode=1：預設值為1。對於simple-inserts，該值會用互斥量去對記憶體的計數器進行累加操作。對於bulk-inserts，是使用傳統表鎖的AUTO-INC Locks方式。
  3. innodb_autoinc_lock_mode=0（traditional lock mode）：老版資料庫傳統鎖模式。

友情提示：InnoDB儲存引擎中自增長實現與MyISAM儲存引擎不同，MyISAM是表鎖設計，自增長不用考慮插入問題。在某種場景下，主節點（master）使用InnoDB儲存引擎，在子節點（slave）使用MyISAM儲存引擎的replication架構，使用者需要考慮這種情況。

此外，在InnoDB儲存引擎中，自增長值的列必須是索引，同時必須是索引的第一個列。如果不是第一個列，MySQL資料庫則會拋異常，而MyISAM儲存引擎沒有這個問題。

進行示例演示：出現1075異常，正常情況是c1在前，c2在後即可執行成功。

CREATE TABLE t1 (
c1 INT(11) NOT NULL AUTO_INCREMENT,
c2 VARCHAR(10) DEFAULT NULL,
KEY (c2,c1)
) ENGINE=InnoDB;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

CREATE TABLE t1 (
c1 INT(11) NOT NULL AUTO_INCREMENT,
c2 VARCHAR(10) DEFAULT NULL,
KEY (c1,c2)
) ENGINE=InnoDB;
Query OK, 0 rows affected, 1 warning (0.02 sec)

你可以找到參考文件：

  1. 7.1 InnoDB Locking

  2. 6.1.6 InnoDB AUTO_INCREMENT Counter Initialization

3.6 外來鍵與鎖

3.6.1 外來鍵用法

tips：目前MySQL支援外來鍵的儲存引擎有InnoDB和NDB。

外來鍵的作用：用來保證參照完整性。比如有兩張表主表parent table和子表child table，在子表中擁有主表外來鍵約束；你想同時幹掉兩張表；MySQL告訴你，沒門，不給刪；需要先刪除約束，才能徹底刪除，使用第三方工具刪除表時深有體會。MySQL資料庫InnoDB儲存引擎完整支援外來鍵。

外來鍵語法定義：

[CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (col_name, ...)
REFERENCES tbl_name (col_name,...)
[ON DELETE reference_option]
[ON UPDATE reference_option]
reference_option:
RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT

13.1.20.5 FOREIGN KEY Constraints

示例建立一張父表（parent）和一張子表（child）：

CREATE TABLE parent (
id INT NOT NULL,
PRIMARY KEY (id)
) ENGINE=INNODB;

CREATE TABLE child (
id INT,
parent_id INT,
INDEX par_ind (parent_id),	-- 給parent_id新增索引
FOREIGN KEY (parent_id)		-- parent_id設定為外來鍵引用主表主鍵id
REFERENCES parent(id)		-- 引用主表（parent）主鍵id
) ENGINE=INNODB;

演示插入資料外來鍵衝突：主表插入1條資料，在子表插入一條資料，違反外來鍵約束，主表沒有id=2的行。此時無法級聯更新。

mysql> INSERT INTO parent (id) VALUES (1); -- 主表插入1條資料
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO child (id,parent_id) VALUES(2,2); -- 在子表插入一條資料，違反外來鍵約束，主表沒有id=2的行
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`))

演示刪除資料外來鍵衝突：有外來鍵約束和索引，此時無法級聯刪除。

mysql> DELETE FROM parent WHERE id=1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`))

如果想級聯更新和刪除，在建立子表（child）時加入CASCADE關鍵字。同樣Oracle中也支援CASCADE，在Oracle中建立外來鍵時注意給這個列加上索引，具體用法可能略有差異。刪除原表，重新建立子表child，並加入給update與delete條件加入CASCADE屬性。

DROP TABLE child;-- 刪除原有建立子表child

-- 重新建立子表child，並加入給update與delete條件加入CASCADE
CREATE TABLE child (
id INT,
parent_id INT,
INDEX par_ind (parent_id),
FOREIGN KEY (parent_id)
REFERENCES parent(id)
ON UPDATE CASCADE
ON DELETE CASCADE
) ENGINE=INNODB;

子表（child）插入測試資料：

mysql> INSERT INTO child (id,parent_id) VALUES(1,1),(2,1),(3,1);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

更新主表（parent）id值為2：

mysql> UPDATE parent SET id = 2 WHERE id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

查詢驗證主表（parent）：

mysql> select * from parent;
+----+
| id |
+----+
|  2 |
+----+
1 row in set (0.00 sec)

查詢驗證子表（child）的parent_id值：此時已經全部更新（update）成 2

mysql> SELECT * FROM child;
+------+-----------+
| id   | parent_id |
+------+-----------+
|    1 |         2 |
|    2 |         2 |
|    3 |         2 |
+------+-----------+
3 rows in set (0.00 sec)

演示級聯刪除效果：此時可以刪除資料內容

mysql> delete from parent where id=2;
Query OK, 1 row affected (0.01 sec)

再次檢視子表（child）：此時子表中的資料內容一併刪除掉

mysql> SELECT * FROM child;
Empty set (0.00 sec)

補充一點，如果想檢視錶約束，可以通過命令去驗證show create table table_name：

查到子表（child）已經自動在外來鍵列加入了索引。

mysql> show create table child\G
*************************** 1. row ***************************
       Table: child
Create Table: CREATE TABLE `child` (
  `id` int DEFAULT NULL,
  `parent_id` int DEFAULT NULL,
  KEY `par_ind` (`parent_id`),
  CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

友情提示：MySQL資料庫外來鍵是即時檢查的，對每一行都會執行外來鍵檢查。匯入資料，在檢查外來鍵約束上往往消耗大量時間。有時候，可以靈活處理，在匯入過程中忽略外來鍵檢查：set foreign_key_checks=0，預設值是1，開啟了外來鍵約束檢查。

前面列舉示例進行外來鍵功能說明，接下來配合鎖進行描述。

3.6.2 外來鍵與鎖

在InnoDB儲存引擎中，對於一個外來鍵列，如果沒有顯示地（人為手動新增）對這個列新增索引，在InnoDB儲存引擎會自動對其加一個索引，因此可以避免表鎖。這一點比Oracle資料庫做得更好，Oracle資料庫使用外來鍵時，需要人為手動給該列新增索引。

對於外來鍵值插入和更新，首先需要查詢父表（parent）中的記錄，即select父表。但對於父表進行select操作，不是使用一致性非鎖定讀方式，這樣會發生資料不一致問題。因此這時使用的是select ... lock in share mode方式（共享鎖），主動給父表加一個S鎖。如果父表已經加了X鎖，子表操作會被阻塞。（可以在兩個會話視窗進行測試）

示例阻塞：

分別在session1會話和session2會話視窗執行事務。session1會話進行刪除父表（parent）id為1的內容，session2會話執行插入內容到子表（child），發現session2此時發生阻塞，阻塞等待超時發出警告（預設50秒）。

-- session1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from parent where id=1;
Query OK, 1 row affected (0.01 sec)

-- session2，阻塞等待超時發出警告（預設50秒）
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into child select 4,1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

在這裡插入圖片描述

此時子表（child）處於鎖定等待中，在MySQL8.0中可以使用data_locks引數進行分析：

mysql> select * from performance_schema.data_locks order by event_id desc limit 0,1\G
*************************** 1. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 2079935859840:93:5:1:2079912818080
ENGINE_TRANSACTION_ID: 16653
            THREAD_ID: 48
             EVENT_ID: 12
        OBJECT_SCHEMA: test
          OBJECT_NAME: child
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: par_ind
OBJECT_INSTANCE_BEGIN: 2079912818080
            LOCK_TYPE: RECORD
            LOCK_MODE: S
          LOCK_STATUS: GRANTED
            LOCK_DATA: supremum pseudo-record
1 row in set (0.00 sec)

鎖等待，在MySQL8.0中可以使用data_lock_waits引數進行分析：

mysql> select * from performance_schema.data_lock_waits  limit 0,1\G
*************************** 1. row ***************************
                          ENGINE: INNODB
       REQUESTING_ENGINE_LOCK_ID: 2079935860616:91:4:2:2079912823744
REQUESTING_ENGINE_TRANSACTION_ID: 16658
            REQUESTING_THREAD_ID: 49
             REQUESTING_EVENT_ID: 10
REQUESTING_OBJECT_INSTANCE_BEGIN: 2079912823744
         BLOCKING_ENGINE_LOCK_ID: 2079935859840:91:4:2:2079912817048
  BLOCKING_ENGINE_TRANSACTION_ID: 16653
              BLOCKING_THREAD_ID: 48
               BLOCKING_EVENT_ID: 12
  BLOCKING_OBJECT_INSTANCE_BEGIN: 2079912817048
1 row in set (0.00 sec)

前面介紹鎖型別也提到過data_locks和data_lock_waits這兩個引數，MySQL8.0之前在information_schema架構下有INNODB_LOCKS、INNODB_LOCK_WAITS兩個系統引數可以進行參考。此處進行示例，也算補足在鎖型別章節沒有進行示例演示。

下面是官方對外來鍵鎖定介紹：MySQL在必要時擴充套件後設資料鎖，通過外來鍵約束關聯表。擴充套件後設資料鎖可以防止DML和DDL操作在相關表上併發執行引起的衝突。該特性還支援在父表被修改時，更新外來鍵後設資料。MySQL早期版本中，外來鍵後設資料(由子表擁有)不能安全更新。如果一個表被LOCK TABLES顯式鎖定，那麼任何與外來鍵約束相關的表都會被隱式開啟和鎖定。對於外來鍵檢查，在相關表上獲取一個共享只讀鎖(LOCK TABLES READ)。對於級聯更新，在操作涉及的相關表上獲取一個無共享的寫鎖(LOCK TABLES WRITE)。

外來鍵定義和後設資料（Foreign Key Definitions and Metadata）。檢視外來鍵定義，可以使用SHOW CREATE TABLE child\G，之前也提到過，這裡不再贅述。

如下是檢視到資料庫中哪些表使用到的外來鍵資訊，顯示資料庫名（TABLE_SCHEMA）、表名（TABLE_NAME）、欄位列名（COLUMN_NAME）以及外來鍵約束名（CONSTRAINT_NAME）。

mysql> SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME
    -> FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
    -> WHERE REFERENCED_TABLE_SCHEMA IS NOT NULL;
+--------------+-----------------+----------------------+---------------------------+
| TABLE_SCHEMA | TABLE_NAME      | COLUMN_NAME          | CONSTRAINT_NAME           |
+--------------+-----------------+----------------------+---------------------------+
| world        | city            | CountryCode          | city_ibfk_1               |
| world        | countrylanguage | CountryCode          | countryLanguage_ibfk_1    |
| test         | child           | parent_id            | child_ibfk_1              |
...
+--------------+-----------------+----------------------+---------------------------+
25 rows in set (0.02 sec)

查詢INFORMATION_SCHEMA架構下的INNODB_FOREIGN，使用limit查詢2條記錄進行演示。world資料庫與sakila資料庫均為MySQL官方示例，前面有官方連結，可自行獲取。

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN limit 0,2 \G
*************************** 1. row ***************************
      ID: world/city_ibfk_1
FOR_NAME: world/city
REF_NAME: world/country
  N_COLS: 1
    TYPE: 48
*************************** 2. row ***************************
      ID: sakila/fk_address_city
FOR_NAME: sakila/address
REF_NAME: sakila/city
  N_COLS: 1
    TYPE: 4
2 rows in set (0.00 sec)

3.7 鎖的演算法

在描述鎖型別，我已經將InnoDB儲存引擎中鎖型別全部列舉出來了。

15.7.1 InnoDB Locking。

3.7.1 行鎖的3種演算法

InnoDB儲存引擎有3種行鎖演算法：

  1. Record Locks：單個行記錄上的鎖；
  2. Gap Locks：間隙鎖，鎖定一個範圍，不包含記錄本身；
  3. Next-Key Locks：Record Locks和Gap Locks，鎖定一個範圍，並且鎖定記錄本身。

Record Locks記錄鎖總是鎖定索引記錄。即使表沒有定義索引，對於這種情況InnoDB會建立一個隱藏的聚集索引，並使用這個索引進行記錄鎖定。

Next-Key Locks是結合了Record Locks和Gap Locks的一種鎖定演算法，在Next-Key Locks演算法下，InnoDB對於行的查詢都是採用這種鎖定演算法。

InnoDB執行行級鎖的方式是這樣的：當它搜尋或掃描一個表索引時，它會在遇到的索引記錄上設定共享或排它鎖。因此，行級鎖實際上是索引記錄鎖。索引記錄上的next-key鎖也會影響該索引記錄之前的間隙。也就是說，next-key鎖是索引記錄鎖加上索引記錄之前的間隙鎖。如果一個會話對索引中的記錄R有一個共享或排它鎖，那麼另一個會話不能在緊挨著索引順序的R之前的間隙插入一個新索引記錄。

假設一個索引包含值10、11、13和20。此索引可能的next-key鎖覆蓋以下區間，其中圓括號表示排除區間端點，方括號表示包含端點：

(negative infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity)

如果事務T1已經通過Next-Key Locks鎖定如下範圍：

(10, 11]、(11, 13]

當插入新記錄12時，鎖定範圍會變成：

(10, 11]、(11,12]、(12, 13]

當查詢的索引包含唯一屬性時，InnoDB儲存引擎會對Next-Key Locks進行優化，將其降級為Record Locks，僅鎖住索引本身，而不是範圍。在InnoDB儲存引擎中，對於insert操作，會檢查插入記錄的一條記錄是否被鎖定，如果已經被鎖定，則不允許查詢。

3.7.2 解決Phantom Problem

什麼是Phantom Problem？：指在同一事務中，連續執行兩次同樣的SQL語句可能導致不同的結果，第二次執行的SQL語句可能返回之前不存在的行。

目的：解決資料一致性。你可以聯想到幻讀、髒讀、更新丟失，其實也是為了解決資料一致性問題。

當同一查詢在不同時間產生不同的行集時，就會在事務中出現所謂的幻影問題。例如，如果一個SELECT被執行了兩次，但是第二次返回了第一次沒有返回的一行，那麼該行就是一個幻像行。

假設子表的id列上有一個索引，您希望讀取和鎖定表中識別符號值大於100的所有行，以便稍後更新選中行的某些列：

SELECT * FROM child WHERE id > 100 FOR UPDATE;

查詢從 id 大於 100 的第一條記錄開始掃描索引。讓表包含 id 值為 90 和 102 的行。如果在掃描的索引記錄上設定的鎖範圍不鎖定間隙鎖記錄（在這種情況下，90 和 102 之間的間隙記錄），另一個 session 可以在表中插入id 為101的新行。如果在同一個事務中，要執行相同的 SELECT，此時查詢返回，會在結果集中看到一個 id 為 101 的新行（幻像） 。如果將一組行視為一個資料項，則新的幻像將違反 一個事務執行的事務隔離原則，以便它擁有的資料 （read 操作） 在事務期間不會改變。

InnoDB儲存引擎提供了SQL92標準所描述的四種事務隔離級別：

  • READ UNCOMMITTED：未提交讀
  • READ COMMITTED：已提交讀
  • REPEATABLE READ ：可重複讀
  • SERIALIZABLE：可序列化（序列化）

而InnoDB預設事務隔離級別是REPEATABLE READ，通過如下命令可以查詢到。transaction_isolation系統引數是動態的，可以在資料庫執行過程中進行調整測試，你也可以在不同會話中測試不同事務隔離級別。

當然，你還可以在my.ini或者my.cnf配置檔案中設定測試：transaction-isolation=name，name為上面介紹的事務隔離級別

mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
1 row in set (0.00 sec)

為了解決phantoms problem，InnoDB使用了一種稱為next-key locking鎖的演算法，它結合了索引行鎖（index-row
locking ）和間隙鎖（Gap Locks）。InnoDB執行行級鎖的方式是這樣的：當它搜尋或掃描一個表索引時，它會在遇到的索引記錄上設定共享或排它鎖。因此，行級鎖實際上是索引記錄鎖。此外，索引記錄上的next-key鎖也會影響索引記錄之前的間隙。也就是說，next-key鎖是索引記錄鎖加上索引記錄之前的間隙鎖。

當InnoDB掃描一個索引時，它也可以鎖定索引中最後一條記錄之後的間隙。就像上面的例子中所發生的那樣：為了防止表中插入任何id大於100的行，InnoDB設定的鎖包含了id值102後面的一個鎖。

你可以在應用程式中使用next-key locking來實現唯一性檢查：如果閱讀了共享模式下的資料，並且看不到要插入行的重複項（看不到幻象），那麼可以安全地插入行，並知道讀取期間在行的後續設定的next-key locking鎖，防止任何人同時在你所使用的行插入重複項。因此，next-key鎖定能夠鎖定表中不存在的內容。

可以禁用間隙鎖定，這可能會導致幻象問題，因為當間隙鎖定被禁用時，其它會話可能會將新行插入到間隙中。

個人理解難免有些不到位，如果給你帶來誤解，我表示抱歉。你可以找到參考文件：

15.7.4 Phantom Rows

同時你還可以參考這本書籍《MySQL技術內幕InnoDB儲存引擎 第2版》，如果作者能針對MySQL8.0進行更新就好了。雖然過去快10年了，依然是一本經典書籍，頗有參考意義，便於理解InnoDB。

3.8 阻塞、死鎖、鎖升級

3.8.1 阻塞

如何理解阻塞，想象一下有東西被堵住了，如何處理。

資料庫中阻塞：因為不同鎖之間的相容性關係，在某些時刻一個事務中的鎖需要等待另一事務中的鎖釋放它所佔用的資源，這就是阻塞。阻塞並不是一件壞事，為了保證事務併發並且正常執行。

在InnoDB儲存引擎中，控制阻塞等待時間引數innodb_lock_wait_timeout，預設值為50秒。

查詢示例：說明一下，在文中多次用到select @@系統引數查詢。當然，在官方文件中也有引數說明。

mysql> select @@innodb_lock_wait_timeout;
+----------------------------+
| @@innodb_lock_wait_timeout |
+----------------------------+
|                         50 |
+----------------------------+
1 row in set (0.00 sec)

臨時設定生效，如下：

檢視文件，引數時是動態的，在資料庫執行時是可以修改的。

mysql> set @@innodb_lock_wait_timeout=60;	-- set和@符號之間可以不加空格
Query OK, 0 rows affected (0.00 sec)

如果想永久生效，可以在my.ini或者my.cnf中加入引數innodb-lock-wait-timeout=#（例如設定60），重啟服務生效。

此外，還有一個引數innodb_rollback_on_timeout用於設定是否在等待超時時對進行中的事務進行回滾操作。預設值是OFF，查詢出來值是0，代表不回滾。查詢示例如下：

mysql> select @@innodb_rollback_on_timeout;
+------------------------------+
| @@innodb_rollback_on_timeout |
+------------------------------+
|                            0 |
+------------------------------+
1 row in set (0.00 sec)

檢視文件，由於非動態是非動態，在資料庫執行時，不允許被更改。一旦更改，會提示引數只讀。

關於引數是不是動態，看文件引數說明Dynamic值（YES代表動態，NO為非動態），預設值引數說明為Default Value。

mysql> set @@innodb_rollback_on_timeout=1;
ERROR 1238 (HY000): Variable 'innodb_rollback_on_timeout' is a read only variable

當發生超時，MySQL資料庫會丟擲異常ERROR 1205：

mysql> begin
Query OK, 0 rows affected (0.00 sec)

mysql> select * from world.city where id=1 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

友情提示：在預設情況下InnoDB儲存引擎不會回滾超時引發的錯誤異常。InnoDB儲存引擎在絕大多數情況下，都不會對異常進行回滾。

15.14 InnoDB Startup Options and System Variables

3.8.2 死鎖

死鎖概念：死鎖是指兩個或兩個以上的事務在執行過程中，因爭奪鎖資源而造成的一種互相等待現象。如果沒有外力作用，事務將無法推進。解決死鎖問題最直接方式是不等待，將任何等待轉換為回滾，並且事務重新開始。這種做法確實可以避免死鎖產生，但線上上環境中，這可能導致併發效能下降，甚至任何一個事務都不能進行。帶來的問題，比死鎖更嚴重，很難發現問題並浪費資源。

解決死鎖問題最簡單一種方法是超時，當兩個事務互相等待時，等待時間超過系統引數設定閾值時，其中一個事務進行回滾，另一個等待的事務繼續進行。在InnoDB儲存引擎中，引數innodb_rollback_on_timeout用來設定超時時間，前面講解阻塞提到過。

超時機制是一種簡單解決方法，僅通過超時後對事務進行回滾處理，或者是根據First In，First Out（FIFO），一進一出順序選擇回滾物件。如果超時事務所佔權重比較大，事務操作更新很多行，佔用較多undo log，這時採用FIFO方式並不那麼合適。回滾事務時間相對一個事務所佔用時間會更多。

除了超時機制，可尋求其它解決方案。當前資料庫普遍採用wait-for graph（等待圖）方式，主動檢測死鎖，判斷是否存在迴路。要求資料庫儲存以下兩種資訊：

  • 鎖資訊連結串列；
  • 事務等待連結串列。

等圖方式是之前版本中的一種，當然也還有新的處理方式。

CATS演算法通過分配一個排程權重對等待的事務進行優先順序排序，該權重是根據一個事務塊的事務數量計算出來的。例如，如果兩個事務正在等待同一個物件上的一個鎖，那麼阻塞最多事務的事務將被分配更大的排程權重。如果權值相等，則優先順序為等待時間最長的事務。

在MySQL 8.0.20之前，InnoDB也使用先進先出(FIFO)演算法來排程事務，CATS演算法只在重鎖爭用的情況下使用。MySQL 8.0.20中的CATS演算法增強使FIFO演算法冗餘，允許刪除它。之前由FIFO演算法執行的事務排程是由MySQL 8.0.20的CATS演算法執行的。在某種情況下，此更改可能會影響授予事務鎖的順序。

友情提示：MySQL8.0.20後，新版InnoDB使用爭用感知事務排程(CATS)演算法對等待鎖的事務進行優先順序排序。當多個事務在同一個物件上等待一個鎖時，CATS演算法確定哪個事務首先接收這個鎖。

15.7.6 Transaction Scheduling

死鎖概率：

一般而言，死鎖概率應該發生非常少，如果經常發生，系統是不可用的。

死鎖次數，應該少於等待，至少需要兩次等待才會產生一次死鎖。

  1. 一定環境下，系統事務數量越多，發生死鎖概率越大；
  2. 每個事務運算元量越多，發生死鎖概率越大；
  3. 運算元據集合越小，發生死鎖概率越大。

死鎖示例：

如果程式是序列的，那麼不可能發生死鎖，比如MyISAM儲存引擎不會出現死鎖，要麼全部獲取，要麼全不獲取。死鎖只存在於併發情況下，資料庫本身是一個併發執行程式，可能會發生死鎖。

具體SQL語句就不貼出來，可以參考上面使用進行模擬場景。在兩個會話視窗session1和session2中進行執行獲取排它鎖，注意執行之前使用begin開始事務。

死鎖原因：兩個會話資源互相等待。大多數死鎖InnoDB儲存引擎可以偵測到，無需人為進行干預。發現死鎖，InnoDB儲存引擎會立刻回滾一個事務。

在Oracle資料庫中產生死鎖常見原因是沒有對外來鍵新增索引，而MySQL資料庫InnoDB儲存引擎會自動為外來鍵上索引，避免這種情況發生。人為刪除外來鍵索引，MySQL會丟擲一個異常。

3.8.3 鎖升級

鎖升級（lock escalation）是指將當前鎖粒度降低。

打個比方，資料庫可以將1000個行鎖升級為一個頁鎖，或者將頁鎖升級為表鎖。如果資料庫設計人為鎖是一種稀有資源，想避免鎖開銷，資料庫中會頻繁出現鎖升級。

友情提示：MySQL中InnoDB事務模型目標是將多版本資料庫（MVCC）最佳特性與傳統兩階段鎖結合起來。InnoDB在行級執行鎖定，預設情況下以非鎖定的一致讀取方式執行查詢，這是Oracle的風格。InnoDB中的鎖資訊被有效地儲存在空間中，因此不需要鎖升級。通常，允許多個使用者鎖定InnoDB表中的每一行，或者任意隨機的行子集，而不會導致InnoDB記憶體耗盡。

MySQL8.0中InnoDB鎖和事務模型可以參考refman-8.0文件：15.7 InnoDB Locking and Transaction Model

參考資料&鳴謝：

《深入淺出MySQL 第2版 資料庫開發、優化與管理維護》，個人參考優化篇部分。

《MySQL技術內幕InnoDB儲存引擎 第2版》，個人參考索引與鎖章節描述。

MySQL8.0官網文件：refman-8.0-en.pdf，要學習新版本，官方文件是非常不錯的選擇。

雖然書籍年份比較久遠（停留在MySQL5.6.x版本），但仍然具有借鑑意義。

最後，對以上書籍和官方文件所有作者表示衷心感謝。讓我充分體會到：前人栽樹，後人乘涼。

莫問收穫，但問耕耘

只停留在看上面，提升效果甚微。應該帶著思考去測試佐證，或者使用（同類書籍）新版本進行對比，這樣帶來的效果更好。最重要的一環，養成閱讀官方文件，是一個良好的習慣。能編寫官方文件，至少證明他們在這個領域是有很高的造詣，對用法足夠熟練。

能看到這裡的，都是帥哥靚妹。以上是本次MySQL優化篇（上部分）全部內容，希望能對你的工作與學習有所幫助。感覺寫的好，就拿出你的一鍵三連。如果感覺總結的不到位，也希望能留下您寶貴的意見，我會在文章中定期進行調整優化。好記性不如爛筆頭，多實踐多積累。你會發現，自己的知識寶庫越來越豐富。原創不易，轉載也請標明出處和作者，尊重原創。

一般情況下，會優先在公眾號釋出：龍騰萬里sky。

不定期上傳到github倉庫：

https://github.com/cnwangk/SQL-study

相關文章