MySQL資料庫-鎖詳解

什麼是鎖

現實生活中，什麼是鎖：
置於可啟閉的器物上，以鑰匙或暗碼開啟。
本義：加在門窗、器物等開合處或連線處，必須用鑰匙、密碼、磁卡等才能開啟的金屬裝置。如：門鎖、密碼鎖、鎖鑰。
比如入住酒店的，如果大家隨意進出，就會出現多人搶奪同一個房間的情況，而在房間上裝上鎖，申請到鑰匙的人才可以入住並且將房間鎖起來，其他人只有等他使用完畢才可以再次使用。
而在資料庫管理系統中，鎖是在多使用者併發存取資料庫時，用於防止衝突存取的一種併發控制機制，保證訪問資料庫資源的次序。
他和現實中的鎖的用途本質是一樣的，都是限制或約束某種行為，從而達到保護某個事物的作用。

為什麼需要鎖

鎖的作用是保障在多個事務併發執行，不相互影響，避免髒讀、幻讀、不可重複讀的發生。
保證資料的一致性、保證資料的完整性、保證資料不被破壞。
也就是實現事物ACID特性中的隔離性。
SQL標準定義的四個隔離級別為：
- read uncommited（讀未提交）
- read commited（不可重複讀）
- repeatable read（可重複讀）
- serializable （序列化 ）

不使用鎖會有哪些問題

示例 1

A更新員工工資表，將B員工工資從1000更新到2000，但沒有提交事務。
這時B員工讀取工資表，發現自己的工作從1000上漲到2000了。
然後A員工發現更新錯了，又將沒有提交的資料撤回了，實際上B員工工資還是1000元。這就導致了B讀取到的資料是錯誤的，也就是發生了髒讀。

示例 2

A更新員工工資表，將B員工工資從1000更新到2000，但沒有提交事務。
這時C員工執行刪除工資表資料，刪除工資大於1500的所有資料，由於C員工讀取到B員工的工資2000大於1500，所以把B員工工資資訊也刪除了。
然後A員工發現更新錯了，想將沒有提交的資料撤回了，然後發現資料怎麼沒了。
這就導致了資料被誤刪除，也是髒讀導致的錯誤。

示例 3

A員工在08:00讀取一張大表資料，08:15讀取完成。
B員工在08:05更改這種大表的，08:10分更改完成。
那麼最終A員工讀取到的是哪個是時間點的資料呢？
正常來講，08:00發起的讀取請求，不管讀取時間多長，最終讀取的資料應該全部都是8:00這一時刻的，而不應該是不同時刻的變化資料，在沒有鎖的情況下，最終查詢是不一致的資料。

如何解決這幾個場景的問題

示例 1 ，示例 2 ，示例 3 場景解決方案：

表鎖

A更新員工工資表時，先申請一個表鎖，然後在進行修改，這個鎖會阻塞其他會話對工資表的讀取和修改，其他員工在這段時間申請讀、更新、刪除資料時會卡住，直到A將事務提交或回退，這也就避免了示例1、2、3讀取錯誤資料和誤刪除資料的場景。
所以第一階段，資料庫需要表鎖。

表鎖存在的問題：

但是雖然解決了前面示例的問題，事務只能序列執行，這在高併發下效率會極低，能否即解決前面示例 1,2,3 的問題，又能提高效能。

解決方式： MVCC

在執行資料修改時，還是先加鎖，然後將修改前的舊資料複製出來放在一個地方，在資料提交或回滾前，其他資料讀取資料時，讀取的是之前複製的舊資料。
MVCC(multi-version-concurrent-control)
MVCC即多版本併發控制，MVCC是一種併發控制的方法，一般在資料庫管理系統中，實現對資料庫的併發訪問，在程式語言中實現事務記憶體。
MVCC在MySQL InnoDB中的實現主要是為了提高資料庫的併發效能，用更好的方式去處理讀-寫衝突，做到==即使有讀寫衝突時，也能做到不加鎖，非阻塞併發讀==。

如何降低鎖定範圍？

例如：

T1表有1千萬條資料，A正在更新T1表中的一條資料，更新前新增表鎖，鎖住所有資料，這時其他會話申請更新T1表其他資料，由於存在表鎖，導致更新被阻塞。
如何最佳化示例4場景，這需要降低鎖的範圍。

行鎖

只鎖定指定的行，不阻塞其他行的修改等操作，提高併發效能。

表鎖分類

後設資料鎖 (MDL)

MDL(metadata lock)是表級鎖，是在server層加的，適用於所有儲存引擎。
MDL 是為了保證當使用者對錶執行 CRUD (增、刪、改、查)操作時，防止其他執行緒對這個表結構做了變更。

如果沒有 MDL後設資料鎖：

當一個長查詢SQL正在執行查詢時，已經查詢出部分資料，這時如果另一個會話執行表欄位的刪除操作，如果沒有MDL鎖，是可以刪除掉欄位的，那麼當前正在查詢的SQL語句就會查詢到與實際不符的資料，也就是少了一列的資料，實際上在發起查詢最開始的時候是有這列資料的。
這和前面講到的MVCC不同，MVCC指在執行insert,update,delete操作時，會將舊資料儲存到undo裡，形成多個版本，避免寫阻塞讀。

MDL如何工作：

當有執行緒在執行 select 語句（ 加 MDL 讀鎖）的期間，如果有其他執行緒要更改該表的結構（ 申請 MDL 寫鎖），那麼將會被阻塞，直到執行完 select 語句（ 釋放 MDL 讀鎖）。
反之，當有執行緒對錶結構進行變更（ 加 MDL 寫鎖）的期間，如果有其他執行緒執行了 CRUD 操作（ 申請 MDL 讀鎖），那麼就會被阻塞，直到表結構變更完成（ 釋放 MDL 寫鎖）。

MDL 不需要顯示呼叫，那它是在什麼時候釋放的 ?

MDL 是在事務提交後才會釋放，這意味著事務執行期間，MDL 是一直持有的。
所有的dml操作都會在表上加一個metadata讀鎖；所有的ddl操作都會在表上加一個metadata寫鎖。

讀鎖和寫鎖的阻塞關係如下：

1.讀鎖和寫鎖之間相互阻塞，即同一個表上的dml和ddl之間互相阻塞。
2.寫鎖和寫鎖之間互相阻塞，即兩個session不能對錶同時做表定義變更，需要序列操作。
3.讀鎖和讀鎖之間不會產生阻塞。也就是增刪改查不會因為metadata lock產生阻塞，可以併發執行，日常工作中大家看到的dml之間的鎖等待是innodb行鎖引起的，和metadata lock無關。
不需要顯示的使用 MDL，當對資料庫表進行操作時，會自動給這個表加上 MDL：
1.對一張表進行 CRUD 操作時，加的是 MDL 讀鎖；
2.對一張表做結構變更操作的時候，加的是 MDL 寫鎖；

那如果資料庫有一個長事務（所謂的長事務，就是開啟了事務，但是一直還沒提交），那在對錶結構做變更操作的時候，可能會發生意想不到的事情，比如下面這個順序的場景：

1.首先，執行緒 A 先啟用了事務（但是一直不提交），然後執行一條 select 語句，此時就先對該表加上 MDL 讀鎖；
2.然後，執行緒 B 也執行了同樣的 select 語句，此時並不會阻塞，因為「讀讀」並不衝突；
3.接著，執行緒 C 修改了表欄位，此時由於執行緒 A 的事務並沒有提交，也就是 MDL 讀鎖還在佔用著，這時執行緒 C 就無法申請到 MDL 寫鎖，就會被阻塞，

那麼線上程 C 阻塞後，後續有對該表的 select 語句，就都會被阻塞，如果此時有大量該表的 select 語句的請求到來，就會有大量的執行緒被阻塞住，這時資料庫的執行緒很快就會爆滿了。
為什麼執行緒 C 因為申請不到 MDL 寫鎖，而導致後續的申請讀鎖的查詢操作也會被阻塞？
這是因為申請 MDL 鎖的操作會形成一個佇列，佇列中寫鎖獲取優先順序高於讀鎖，一旦出現 MDL 寫鎖等待，會阻塞後續該表的所有 CRUD 操作。
所以為了能安全的對錶結構進行變更，在對錶結構變更前，先要看看資料庫中的長事務，是否有事務已經對錶加上了 MDL 讀鎖，如果可以考慮 kill 掉這個長事務，然後再做表結構的變更。

意向鎖

當對錶新增表鎖時，如果已經存在 S 共享鎖或 X 排他鎖時，是否可以直接加表鎖呢？

比如：
A會話申請到行鎖，正在對某一行資料進行更新操作，這時另一個會話申請MDL表鎖，申請成功後，執行表結構修改操作，這個操作可能會影響當前正在執行的更新操作，也就是這兩個鎖是互斥的，當存在行鎖時，不能直接新增表鎖。
那麼是否需要在新增表鎖時，逐行檢視哪些行存在行鎖嗎，當表資料量大時，這種操作非常耗時，所以引入了意向鎖：

IS（ Intention Shared Lock），共享意向鎖

IX （ Intention Exclusive Lock ），獨佔意向鎖。

這兩個鎖是表級別的鎖，當需要對錶中的某條記錄上 S 鎖的時候，先在表上加個 IS 鎖，表明此時表內有 S 鎖。當需要對錶中的某條記錄上 X 鎖的時候，先在表上加個 IX 鎖，表明此時表內有 X 鎖。
這樣操作之後，如果要加表鎖，就不需要遍歷所有記錄去找了，直接看看錶上面有沒有 IS 和 IX 鎖。
比如，此時要上表級別的 S 鎖，如果表上沒有 IX ，說明表中沒有記錄有獨佔鎖，其實就可以直接上表級 S 鎖。
如果此時要上表級別的 X 鎖，如果表上沒有 IX 和 IS ，說明表中的所有記錄都沒加鎖，其實就可以直接上表級 X 鎖。
因此 IS 和 IX 的作用就是在上表級鎖的時候，可以快速判斷是否可以上鎖，而不需要遍歷表中的所有記錄。
所以，意向鎖的目的是為了快速判斷表裡是否有記錄被加鎖。

自增( AUTO-INC )  鎖

在為某個欄位宣告 AUTO_INCREMENT 屬性時，之後可以在插入資料時，可以不指定該欄位的值，資料庫會自動給該欄位賦值遞增的值，這主要是透過 AUTO-INC 鎖實現的。
AUTO-INC 鎖是特殊的表鎖機制，鎖不是在一個事務提交後才釋放，而是再執行完插入語句後就會立即釋放。
在插入資料時，會加一個表級別的 AUTO-INC 鎖，然後為被 AUTO_INCREMENT 修飾的欄位賦值遞增的值，等插入語句執行完成後，才會把 AUTO-INC 鎖釋放掉。
那麼，一個事務在持有 AUTO-INC 鎖的過程中，其他事務的如果要向該表插入語句都會被阻塞，從而保證插入資料時，被 AUTO_INCREMENT 修飾的欄位的值是連續遞增的。
但是， AUTO-INC 鎖在對大量資料進行插入的時候，會影響插入效能，因為另一個事務中的插入會被阻塞。
因此， 在 MySQL 5.1.22 版本開始，InnoDB 儲存引擎提供了一種輕量級的鎖來實現自增。
一樣也是在插入資料的時候，會為被 AUTO_INCREMENT 修飾的欄位加上輕量級鎖，然後給該欄位賦值一個自增的值，就把這個輕量級鎖釋放了，而不需要等待整個插入語句執行完後才釋放鎖。
InnoDB 儲存引擎提供了個 innodb_autoinc_lock_mode 的系統變數，是用來控制選擇用 AUTO-INC 鎖，還是輕量級的鎖。
1.當 innodb_autoinc_lock_mode = 0，就採用 AUTO-INC 鎖；
2.當 innodb_autoinc_lock_mode = 2，就採用輕量級鎖；
3.當 innodb_autoinc_lock_mode = 1，這個是預設值，兩種鎖混著用，如果能夠確定插入記錄的數量就採用輕量級鎖，不確定時就採用 AUTO-INC 鎖。
不過，當 innodb_autoinc_lock_mode = 2 是效能最高的方式，但是會帶來一定的問題。因為併發插入的存在，在每次插入時，自增長的值可能不是連續的，這在有主從複製的場景中是不安全的。

行鎖分類

MySQL 的行鎖是在引擎層由各個引擎自己實現的， 且鎖定顆粒度在 MySQL 中是最小的，比如 MyISAM 引擎就不支援行鎖，行級鎖主要應用於 InnoDB 儲存引擎。
不支援行鎖意味著併發控制只能使用表鎖，對於這種引擎的表，同一張表上任何時刻只能有一個更新在執行，這就會影響到併發度，只針對操作的當前行進行加鎖，所以行級鎖發生鎖定資源爭用的機率也最小。
比如事務 A 更新了一行，而這時候事務 B 也要更新同一行，則必須等事務 A 的操作完成後才能進行更新。

行級鎖的型別主要有三類：

1.Record Lock，記錄鎖，也就是僅僅把一條記錄鎖上；
2.Gap Lock，間隙鎖，鎖定一個範圍，但是不包含記錄本身；
3.Next-Key Lock：Record Lock + Gap Lock 的組合，鎖定一個範圍，並且鎖定記錄本身。

記錄鎖(Record Locks)

屬於為行鎖，表示對某一行記錄加鎖。

例如：

id 列為主鍵列或唯一索引列
SELECT * FROM test WHERE id = 10 FOR UPDATE;
記錄鎖總是鎖定索引記錄（SELECT和UPDATE都會加鎖），即使表沒有定義索引。對於這種情況， InnoDB建立一個隱藏的聚集索引並使用該索引進行記錄鎖定。但因為可能會掃描全表，那麼該鎖也就會退化為表鎖。
注意：
1.id列必須為唯一索引或主鍵列，否則上述語句加的鎖會變成臨鍵鎖。
2.查詢語句必須為精準匹配=，不能>、<、like等，否則也會退化成臨鍵鎖。

間隙鎖 (Gap Locks)

如何解決幻讀問題？

前面講的記錄鎖是加到已經存在的記錄上，但是如果要給此時還未存在的記錄加鎖怎麼辦？也就是要預防幻讀的出現，這需要使用間隙鎖。
比如此時有 1、3、5、10 這四條記錄，資料頁中還有兩條虛擬的記錄，分別是 Infimum 和 Supremum。

可以看到，記錄之前都有間隙，間隙鎖鎖的就是這個間隙。
比如把3和5之間的間隙鎖了，此時要插入 id = 4 的記錄，就會被這個間隙鎖給阻塞了，這樣就避免了幻讀的產生！也就實現了鎖定未插入的記錄的需求！
        間隙鎖(Gap Locks)是對索引（非唯一索引）記錄之間的間隙，鎖定一個區間：加在兩個索引之間，或者加在第一個索引之前，或者加在最後一個索引之後的間隙。
   這裡還值得注意的是，間隙鎖只阻止其他事務插入到間隙中，並不阻止其他事務在同一個間隙上獲得間隙鎖，所以 gap x lock 和 gap s lock 有相同的作用，即不同的事務可以在間隙上持有衝突的鎖。 例如，事務 A 可以在間隙上持有共享間隙鎖（間隙 S 鎖），而事務 B 在同一間隙上持有排他間隙鎖（間隙 X 鎖）。允許衝突間隙鎖的原因是，如果從索引中清除記錄，則必須合併不同事務在記錄上持有的間隙鎖。

如何顯示禁用 間隙鎖？

間隙鎖是在事務隔離級別為可重複讀的時候生效的，如果將事務隔離級別更改為
 
READ COMMITTED
，就會禁用了，此時，間隙鎖對於搜尋和索引掃描是禁用的，僅用於外來鍵約束檢查和重複鍵檢查。

總結：

1.間隙鎖鎖區間的索引，使用唯一索引搜尋唯一行不需要間隙鎖定。
2.在READ COMMITTED（RC）隔離級別下，不會使用gap lock，在RR級別及以上（Serializable）才會使用它。
3.間隙鎖可以共存。一個事務採用的間隙鎖不會阻止另一個事務在同一間隙上採用間隙鎖。共享和排他間隙鎖之間沒有區別。它們彼此不衝突，並且執行相同的功能。

臨界鎖 (Next-Key)  

臨鍵鎖(Next-Key)簡單理解是 “記錄鎖+間隙鎖” 的組合，但Next-Key lock與record lock加鎖的粒度一樣，都是加在一條索引記錄上的。一個next-key lock=對應的索引記錄的record lock+該索引前面的間隙的gap lock，透過臨鍵鎖可以解決幻讀的問題。
        預設情況下，InnoDB在 REPEATABLE READ事務隔離級別執行，在這種情況下，InnoDB使用臨鍵鎖進行搜尋和索引掃描，以防止幻像行，比如select ... in share mode或者select ... for update語句。但即使你的隔離級別是RR，如果你這是使用普通的select語句，那麼InnoDB將是快照讀，不會使用任何鎖，因而還是無法防止幻讀。
總結：InnoDB在RR事務隔離級別下，在根據非唯一索引對記錄行進行UPDATE \ FOR UPDATE \ LOCK IN SHARE MODE 操作時，InnoDB 會獲取該記錄行的臨鍵鎖 ，並同時獲取該記錄行下一個區間間隙鎖。

全域性讀鎖(FTWRL)

對整個資料庫例項加鎖。
 這個命令可以使整個庫處於只讀狀態。
使用該命令之後，資料更新語句、資料定義語句和更新類事務的提交語句等操作都會被阻塞。使用場景：全庫邏輯備份。
Flush tables with read lock
如果要釋放全域性鎖，則要執行這條命令：
unlock tables
當然，當會話斷開了，全域性鎖會被自動釋放。

風險：

1.如果在主庫備份，在備份期間不能更新，業務停擺
2.如果在從庫備份，備份期間不能執行主庫同步的binlog，導致主從延遲
官方自帶的邏輯備份工具mysqldump，當mysqldump使用引數–single-transaction的時候，會啟動一個事務，確保拿到一致性檢視。而由於MVCC的支援，這個過程中資料是可以正常更新的。
-single-transaction 命令加鎖
官方自帶的邏輯備份工具是 mysqldump。當 mysqldump 使用引數 –single-transaction 的時候，導資料之前就會啟動一個事務，來確保拿到一致性檢視。而由於 MVCC 的支援，這個過程中資料是可以正常更新的。

頁鎖

頁級鎖是
MySQL
中比較獨特的一種鎖定級別。頁級鎖定的特點是鎖定顆粒度介於行級鎖定與表級鎖之間，所以獲取鎖定所需要的資源開銷，以及所能提供的併發處理能力也介於表級鎖和行級鎖中間。 使用頁級鎖定的主要是 
BerkeleyDB 
儲存引擎。

鎖分類總結

按照鎖定範圍 (粒度)

全域性鎖。

表鎖：後設資料鎖 (MDL) 、意向鎖、 AUTO-INC 鎖。

頁鎖。

行鎖 : 記錄鎖 (Record Locks) 、間隙鎖 (Gap Locks) 、臨界鎖 (Next Key Locks) 。

按照鎖的屬性

共享鎖 ( 讀鎖 ) 。

排他鎖 ( 寫鎖 ) 。

按照鎖的意願

1. 悲觀鎖

當要對資料庫中的一條資料進行修改的時候，為了避免同時被其他人修改，最好的辦法就是直接對該資料進行加鎖以防止併發。這種藉助資料庫鎖機制，在修改資料之前先鎖定，再修改的方式被稱之為悲觀併發控制【 Pessimistic Concurrency Control ，縮寫“ PCC ”，又名“悲觀鎖”】。

2. 樂觀鎖

樂觀鎖是相對悲觀鎖而言的，樂觀鎖假設資料一般情況不會造成衝突，所以在資料進行提交更新的時候，才會正式對資料的衝突與否進行檢測，如果衝突，則返回給使用者異常資訊，讓使用者決定如何去做。樂觀鎖適用於讀多寫少的場景，這樣可以提高程式的吞吐量。

鎖測試

MDL後設資料鎖問題分析

---會話4
mysql> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
|               4 |
+-----------------+
1 row in set (0.00 sec)
---執行慢查詢SQL，會加後設資料讀鎖MDL。
mysql> select sleep(100) from t1;
mysql> select * from performance_schema.metadata_locks;
+-------------+--------------------+----------------+-----------------------+-------------+---------------+-------------+-------------------+-----------------+----------------+
| OBJECT_TYPE | OBJECT_SCHEMA      | OBJECT_NAME    | OBJECT_INSTANCE_BEGIN | LOCK_TYPE   | LOCK_DURATION | LOCK_STATUS | SOURCE            | OWNER_THREAD_ID | OWNER_EVENT_ID |
+-------------+--------------------+----------------+-----------------------+-------------+---------------+-------------+-------------------+-----------------+----------------+
| TABLE       | cjc                | t1             |       140735273864736 | SHARED_READ | TRANSACTION   | GRANTED     | sql_parse.cc:6020 |              34 |             19 |
| TABLE       | performance_schema | metadata_locks |       140734804093088 | SHARED_READ | TRANSACTION   | GRANTED     | sql_parse.cc:6020 |              37 |             16 |
+-------------+--------------------+----------------+-----------------------+-------------+---------------+-------------+-------------------+-----------------+----------------+
2 rows in set (0.00 sec)
查詢thread_id和processlist_id對應關係
mysql> select name,thread_id,processlist_id ,thread_os_id from performance_schema.threads where thread_id in (34);
+---------------------------+-----------+----------------+--------------+
| name                      | thread_id | processlist_id | thread_os_id |
+---------------------------+-----------+----------------+--------------+
| thread/sql/one_connection |        34 |              4 |         4179 |
+---------------------------+-----------+----------------+--------------+
1 row in set (0.00 sec)
---會話5
mysql> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
|               5 |
+-----------------+
1 row in set (0.00 sec)
---可以正常查詢資料
mysql> select * from t1;
+----+------+
| id | name |
+----+------+
|  1 | cjc  |
+----+------+
1 row in set (0.00 sec)
---會話6
mysql> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
|               6 |
+-----------------+
1 row in set (0.00 sec)
---新增欄位，卡住
mysql> alter table t1 add col1 int;
---會話5
mysql> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
|               5 |
+-----------------+
1 row in set (0.00 sec)
---此時會話5，再次執行查詢會卡住，被阻塞
這是因為申請 MDL 鎖的操作會形成一個佇列，佇列中寫鎖獲取優先順序高於讀鎖，一旦出現 MDL 寫鎖等待，會阻塞後續該表的所有CRUD操作。
mysql> select * from t1;
---會話7
mysql> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
|               7 |
+-----------------+
1 row in set (0.00 sec)
---processlist檢視資訊
mysql> show processlist;
+----+-----------------+-----------+--------------------+---------+------+---------------------------------+-----------------------------+
| Id | User            | Host      | db                 | Command | Time | State                           | Info                        |
+----+-----------------+-----------+--------------------+---------+------+---------------------------------+-----------------------------+
|  1 | event_scheduler | localhost | NULL               | Daemon  |  431 | Waiting on empty queue          | NULL                        |
|  4 | root            | localhost | cjc                | Query   |   87 | User sleep                      | select sleep(100) from t1   |
|  5 | root            | localhost | cjc                | Query   |   52 | Waiting for table metadata lock | select * from t1            |
|  6 | root            | localhost | cjc                | Query   |   69 | Waiting for table metadata lock | alter table t1 add col1 int |
|  7 | root            | localhost | performance_schema | Query   |    0 | starting                        | show processlist            |
+----+-----------------+-----------+--------------------+---------+------+---------------------------------+-----------------------------+
5 rows in set (0.00 sec)
查詢MDL資訊
mysql> select * from performance_schema.metadata_locks;
+-------------+--------------------+----------------+-----------------------+---------------------+---------------+-------------+-------------------+-----------------+----------------+
| OBJECT_TYPE | OBJECT_SCHEMA      | OBJECT_NAME    | OBJECT_INSTANCE_BEGIN | LOCK_TYPE           | LOCK_DURATION | LOCK_STATUS | SOURCE            | OWNER_THREAD_ID | OWNER_EVENT_ID |
+-------------+--------------------+----------------+-----------------------+---------------------+---------------+-------------+-------------------+-----------------+----------------+
| TABLE       | cjc                | t1             |       140735273864736 | SHARED_READ         | TRANSACTION   | GRANTED     | sql_parse.cc:6020 |              34 |             18 |
| GLOBAL      | NULL               | NULL           |       140735005409760 | INTENTION_EXCLUSIVE | STATEMENT     | GRANTED     | sql_base.cc:5533  |              36 |             23 |
| SCHEMA      | cjc                | NULL           |       140735005409504 | INTENTION_EXCLUSIVE | TRANSACTION   | GRANTED     | sql_base.cc:5518  |              36 |             23 |
| TABLE       | cjc                | t1             |       140735005408624 | SHARED_UPGRADABLE   | TRANSACTION   | GRANTED     | sql_parse.cc:6020 |              36 |             23 |
| TABLE       | cjc                | t1             |       140735005431216 | EXCLUSIVE           | TRANSACTION   | PENDING     | mdl.cc:3919       |              36 |             23 |
| TABLE       | cjc                | t1             |       140734938293072 | SHARED_READ         | TRANSACTION   | PENDING     | sql_parse.cc:6020 |              35 |             12 |
| TABLE       | performance_schema | metadata_locks |       140734804093088 | SHARED_READ         | TRANSACTION   | GRANTED     | sql_parse.cc:6020 |              37 |             13 |
+-------------+--------------------+----------------+-----------------------+---------------------+---------------+-------------+-------------------+-----------------+----------------+
7 rows in set (0.00 sec)
查詢thread_id和processlist_id對應關係
mysql> select name,thread_id,processlist_id ,thread_os_id from performance_schema.threads where thread_id in (34,35,36,37);
+---------------------------+-----------+----------------+--------------+
| name                      | thread_id | processlist_id | thread_os_id |
+---------------------------+-----------+----------------+--------------+
| thread/sql/one_connection |        34 |              4 |         4179 |
| thread/sql/one_connection |        35 |              5 |         4289 |
| thread/sql/one_connection |        36 |              6 |         4355 |
| thread/sql/one_connection |        37 |              7 |         4464 |
+---------------------------+-----------+----------------+--------------+
4 rows in set (0.00 sec)
可以看到thread_id=36，processlist_id=6和thread_id=35，processlist_id=5的LOCK_STATUS為PENDING，被阻塞。
如果查詢performance_schema.metadata_locks沒有資料，需要開啟鎖監控。
---metadata_locks沒有MDL後設資料鎖資訊
mysql> select * from performance_schema.metadata_locks;
Empty set (0.00 sec)
---檢查鎖監控沒有開啟
mysql> select * from performance_schema.setup_instruments where name like '%lock%' limit 20;
metadata_locks是5.7中被引入，記錄了metadata lock的相關資訊，包括持有物件、型別、狀態等資訊。
但5.7預設設定是關閉的（8.0預設開啟），需要透過下面命令開啟設定：
---開啟鎖監控
mysql> update performance_schema.setup_instruments set enabled = 'YES' where  name like '%lock%';
Query OK, 175 rows affected (0.00 sec)
Rows matched: 182  Changed: 175  Warnings: 0
如果要永久生效，需要在配置檔案中加入如下內容：
[mysqld]
performance-schema-instrument='wait/lock/metadata/sql/mdl=ON'
後設資料鎖MDL等待超時時間，預設1年。
mysql> show variables like 'lock_wait_timeout';
+-------------------+----------+
| Variable_name     | Value    |
+-------------------+----------+
| lock_wait_timeout | 31536000 |
+-------------------+----------+
1 row in set (0.00 sec)

分析鎖源頭

select
    locked_schema,
    locked_table,
    locked_type,
    waiting_processlist_id,
    waiting_age,
    waiting_query,
    waiting_state,
    blocking_processlist_id,
    blocking_age,
    substring_index(sql_text,"transaction_begin;" ,-1) as blocking_query,
    sql_kill_blocking_connection
from
    (
        select
            b.owner_thread_id as granted_thread_id,
            a.object_schema as locked_schema,
            a.object_name as locked_table,
            "metadata lock" as locked_type,
            c.processlist_id as waiting_processlist_id,
            c.processlist_time as waiting_age,
            c.processlist_info as waiting_query,
            c.processlist_state as waiting_state,
            d.processlist_id as blocking_processlist_id,
            d.processlist_time as blocking_age,
            d.processlist_info as blocking_query,
            concat('kill ', d.processlist_id) as sql_kill_blocking_connection
        from
            performance_schema.metadata_locks a
        join performance_schema.metadata_locks b on a.object_schema = b.object_schema
        and a.object_name = b.object_name
        and a.lock_status = 'pending'
        and b.lock_status = 'granted'
        and a.owner_thread_id <> b.owner_thread_id
        and a.lock_type = 'exclusive'
        join performance_schema.threads c on a.owner_thread_id = c.thread_id
        join performance_schema.threads d on b.owner_thread_id = d.thread_id
    ) t1,
    (
        select
            thread_id,
            group_concat(   case when event_name = 'statement/sql/begin' then "transaction_begin" else sql_text end order by event_id separator ";" ) as sql_text
        from
           performance_schema.events_statements_history
        group by thread_id
    ) t2
where
    t1.granted_thread_id = t2.thread_id \G;

結果如下：

*************************** 1. row ***************************
               locked_schema: cjc
                locked_table: t1
                 locked_type: metadata lock
      waiting_processlist_id: 6
                 waiting_age: 6
               waiting_query: alter table t1 add col1 int
               waiting_state: Waiting for table metadata lock
     blocking_processlist_id: 4
                blocking_age: 12
              blocking_query: alter table t1 drop col1;alter table t1 drop col2;alter table t1 drop col5;select * from t1;select connection_id();select sleep(100) from t1;select sleep(100) from t1;select sleep(100) from t1;select sleep(100) from t1;select sleep(100) from t1
sql_kill_blocking_connection: kill 4
1 row in set (0.00 sec)

可以看到

processlist_id=4 是阻塞的源頭，阻塞了processlist_id=6的alter table t1 add col1 int操作。

如果是dml操作，例如delete是阻塞源頭，查詢資訊類似如下：

*************************** 1. row ***************************
               locked_schema: cjc
                locked_table: t1
                 locked_type: metadata lock
      waiting_processlist_id: 6
                 waiting_age: 74
               waiting_query: alter table t1 add col2 int
               waiting_state: Waiting for table metadata lock
     blocking_processlist_id: 4
                blocking_age: 92
              blocking_query: delete from t1
sql_kill_blocking_connection: kill 4
1 row in set (0.00 sec)

MDL阻塞場景：

場景一：

在執行下面增、刪、改、查時，會加MDL讀鎖，如果這些執行較慢或沒有及時提交，
select id from t1 where ...;
delete from t1 where ...;
update t1 set id=xx where xxx;
insert into t1 values(...);
會阻塞其他會話的DDL操作，
例如新增列、刪除列、修改列、建立索引、清空表等操作。
alter table t1 add col1 int;
alter table t1 drop col1;
alter table t1 modify name char(20);
create index i_t1_id on t1(id);
truncate table t1;
總結：
增、刪、改、查時會新增後設資料MDL讀鎖，這個鎖不會阻塞其他會話的查詢，也不會阻塞其他會話的其他行的增、刪、改操作。
如果其他會話開始執行DDL，由於已經有後設資料MDL讀鎖，索引無法申請到後設資料MDL寫鎖，所以會阻塞DDL操作，
並且由於在MDL申請佇列裡，MDL寫鎖優先順序別較高，所以正在等待的DDL操作又會阻塞其他會話對錶的增、刪、改、查等操作。

場景二：

建立測試資料
mysql> insert into t1 select * from t1;
......
mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
| 16777216 |
+----------+
1 row in set (42.41 sec)
新增欄位，耗時長
mysql> alter table t1 add col1 char(255) default 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx';
檢視對應MDL資訊
mysql> select * from performance_schema.metadata_locks;
+-------------+--------------------+----------------+-----------------------+---------------------+---------------+-------------+-------------------+-----------------+----------------+
| OBJECT_TYPE | OBJECT_SCHEMA      | OBJECT_NAME    | OBJECT_INSTANCE_BEGIN | LOCK_TYPE           | LOCK_DURATION | LOCK_STATUS | SOURCE            | OWNER_THREAD_ID | OWNER_EVENT_ID |
+-------------+--------------------+----------------+-----------------------+---------------------+---------------+-------------+-------------------+-----------------+----------------+
| GLOBAL      | NULL               | NULL           |       140735273869632 | INTENTION_EXCLUSIVE | STATEMENT     | GRANTED     | sql_base.cc:5533  |              34 |             81 |
| SCHEMA      | cjc                | NULL           |       140735273864736 | INTENTION_EXCLUSIVE | TRANSACTION   | GRANTED     | sql_base.cc:5518  |              34 |             81 |
| TABLE       | cjc                | t1             |       140735283470544 | SHARED_UPGRADABLE   | TRANSACTION   | GRANTED     | sql_parse.cc:6020 |              34 |             81 |
| TABLE       | performance_schema | metadata_locks |       140734804148624 | SHARED_READ         | TRANSACTION   | GRANTED     | sql_parse.cc:6020 |              37 |             41 |
+-------------+--------------------+----------------+-----------------------+---------------------+---------------+-------------+-------------------+-----------------+----------------+
4 rows in set (0.00 sec)
不會阻塞增、刪、改、查操作
mysql> select * from t1 where id=1000;
+------+------+
| id   | name |
+------+------+
| 1000 | cjc  |
+------+------+
1 row in set (0.00 sec)
mysql> insert into t1 values(3,'aaa');
Query OK, 1 row affected (0.81 sec)
mysql> update t1 set id=100 where id=0;
Query OK, 0 rows affected (0.52 sec)
Rows matched: 0  Changed: 0  Warnings: 0
mysql> delete from t1 where id=0;
Query OK, 0 rows affected (0.03 sec)
會阻塞DDL操作
mysql> alter table t1 add col2 int;
卡住
mysql> select * from performance_schema.metadata_locks;
+-------------+--------------------+----------------+-----------------------+---------------------+---------------+-------------+-------------------+-----------------+----------------+
| OBJECT_TYPE | OBJECT_SCHEMA      | OBJECT_NAME    | OBJECT_INSTANCE_BEGIN | LOCK_TYPE           | LOCK_DURATION | LOCK_STATUS | SOURCE            | OWNER_THREAD_ID | OWNER_EVENT_ID |
+-------------+--------------------+----------------+-----------------------+---------------------+---------------+-------------+-------------------+-----------------+----------------+
| GLOBAL      | NULL               | NULL           |       140735273869632 | INTENTION_EXCLUSIVE | STATEMENT     | GRANTED     | sql_base.cc:5533  |              34 |             81 |
| SCHEMA      | cjc                | NULL           |       140735273864736 | INTENTION_EXCLUSIVE | TRANSACTION   | GRANTED     | sql_base.cc:5518  |              34 |             81 |
| TABLE       | cjc                | t1             |       140735283470544 | SHARED_UPGRADABLE   | TRANSACTION   | GRANTED     | sql_parse.cc:6020 |              34 |             81 |
| GLOBAL      | NULL               | NULL           |       140735005409760 | INTENTION_EXCLUSIVE | STATEMENT     | GRANTED     | sql_base.cc:5533  |              36 |             44 |
| SCHEMA      | cjc                | NULL           |       140735005429680 | INTENTION_EXCLUSIVE | TRANSACTION   | GRANTED     | sql_base.cc:5518  |              36 |             44 |
| TABLE       | cjc                | t1             |       140735005431216 | SHARED_UPGRADABLE   | TRANSACTION   | PENDING     | sql_parse.cc:6020 |              36 |             44 |
| TABLE       | performance_schema | metadata_locks |       140734804148624 | SHARED_READ         | TRANSACTION   | GRANTED     | sql_parse.cc:6020 |              37 |             42 |
+-------------+--------------------+----------------+-----------------------+---------------------+---------------+-------------+-------------------+-----------------+----------------+
7 rows in set (0.35 sec)
mysql> show processlist;
+----+-----------------+-----------+--------------------+---------+------+---------------------------------+----------------------------------------------------------------------------------------------+
| Id | User            | Host      | db                 | Command | Time | State                           | Info                                                                                         |
+----+-----------------+-----------+--------------------+---------+------+---------------------------------+----------------------------------------------------------------------------------------------+
|  1 | event_scheduler | localhost | NULL               | Daemon  | 8634 | Waiting on empty queue          | NULL                                                                                         |
|  4 | root            | localhost | cjc                | Query   |  227 | altering table                  | alter table t1 add col1 char(255) default 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx' |
|  5 | root            | localhost | cjc                | Sleep   |  187 |                                 | NULL                                                                                         |
|  6 | root            | localhost | cjc                | Query   |   91 | Waiting for table metadata lock | alter table t1 add col2 int                                                                  |
|  7 | root            | localhost | performance_schema | Sleep   |   88 |                                 | NULL                                                                                         |
| 12 | root            | localhost | NULL               | Query   |    0 | starting                        | show processlist                                                                             |
+----+-----------------+-----------+--------------------+---------+------+---------------------------------+----------------------------------------------------------------------------------------------+
6 rows in set (0.50 sec)

總結：

新增欄位、刪除欄位等DDL操作，不會阻塞其他會話的增、刪、改、查操作，會阻塞其他會話的DDL操作。

參考：

https://blog.csdn.net/songguojiebd/article/details/127930451

https://www.cnblogs.com/ilovebath/p/15893673.html