MySQL中查詢事物與DDL引發Waiting for table metadata lock的兩個階段是什么

這篇文章將為大家詳細講解有關MySQL中查詢事物與DDL引發Waiting for table metadata lock的兩個階段是什么，小編覺得挺實用的，因此分享給大家做個參考，希望大家閱讀完這篇文章后可以有所收獲。

1.現象描述：
SESSION1：

SESSION2：


SESSION3：


備注：（這里SESSION1，SESSION2，SESSION3按先后順序執行）
當SESSION1未提交時，SESSION2阻塞，SESSION3阻塞
當SESSION1提交時，SESSION2仍然阻塞，SESSION3執行成功（這里限于篇幅，讀者可以自行實驗）


2.現象質疑：
當session1未提交時，我們看看metadata_locks,由下圖我們可以分析得出，是session1的SHARE_READ阻塞了EXCLUSIVE，同時SESSION3的SHARE_READ被EXCLUSIVE給阻塞了


當SESSION1提交后，我們再來看metadata_locks（如下圖）：我們發現SESSION2被SESSION3阻塞了，且還是SESSION3的EXCLUSIVE被SESSION2的SHARE_READ阻塞了，這里我們不經疑惑，難道是SESSION3的SHARD_READ的優先級要高些？（但是本人查看MDL_SHARE_READ的源碼注釋，沒有發現MDL_SHARE_READ的優先級要高于MDL_EXCLUSIVE）


3.現象分析：
帶著上一步中標紅部分的這個疑問，我們來查看下sql執行耗時的各個階段，具體情況如下：

mysql> show profile;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| Waiting for table metadata loc | 1.001239 |
| After create                   | 0.000047 |
| Waiting for table metadata loc | 1.002126 |
| After create                   | 0.000047 |
| Waiting for table metadata loc | 1.000864 |
| After create                   | 0.000047 |
| Waiting for table metadata loc | 1.001443 |
| After create                   | 0.000047 |
| Waiting for table metadata loc | 1.001984 |
| After create                   | 0.000046 |
| Waiting for table metadata loc | 1.003780 |
| After create                   | 0.000049 |
| Waiting for table metadata loc | 1.003622 |
| After create                   | 0.000049 |
| Waiting for table metadata loc | 1.000299 |
| After create                   | 0.000051 |
| Waiting for table metadata loc | 1.001613 |
| After create                   | 0.000048 |
| Waiting for table metadata loc | 1.000226 |
| After create                   | 0.000077 |
| Waiting for table metadata loc | 1.000196 |
| After create                   | 0.000048 |
| Waiting for table metadata loc | 1.000574 |
| After create                   | 0.000049 |
| Waiting for table metadata loc | 1.001014 |
| After create                   | 0.000046 |
| Waiting for table metadata loc | 1.000834 |
| After create                   | 0.000047 |
| Waiting for table metadata loc | 1.001708 |
| After create                   | 0.000047 |
| Waiting for table metadata loc | 0.492941 |
| After create                   | 0.000130 |
| System lock                    | 0.000028 |
| preparing for alter table      | 0.000184 |
| altering table                 | 0.000037 |
| Waiting for table metadata loc | 1.000922 |
| altering table                 | 0.000057 |
| Waiting for table metadata loc | 1.000320 |
| altering table                 | 0.000082 |
| Waiting for table metadata loc | 1.001329 |
| altering table                 | 0.000055 |
| Waiting for table metadata loc | 1.002728 |
| altering table                 | 0.000054 |
| Waiting for table metadata loc | 1.000887 |
| altering table                 | 0.000055 |
| Waiting for table metadata loc | 1.002754 |
| altering table                 | 0.000055 |
| Waiting for table metadata loc | 1.001484 |
| altering table                 | 0.000055 |
| Waiting for table metadata loc | 1.001034 |
| altering table                 | 0.000059 |
| Waiting for table metadata loc | 1.000547 |
| altering table                 | 0.000057 |
| Waiting for table metadata loc | 1.003391 |
| altering table                 | 0.000058 |
| Waiting for table metadata loc | 1.002230 |
| altering table                 | 0.000059 |
| Waiting for table metadata loc | 1.002789 |
| altering table                 | 0.000058 |
| Waiting for table metadata loc | 1.002071 |
| altering table                 | 0.000059 |
| Waiting for table metadata loc | 1.003891 |
| altering table                 | 0.000057 |
| Waiting for table metadata loc | 1.003908 |
| altering table                 | 0.000057 |
| Waiting for table metadata loc | 1.000404 |
| altering table                 | 0.000055 |
| Waiting for table metadata loc | 1.003572 |
| altering table                 | 0.000056 |
| Waiting for table metadata loc | 1.000270 |
| altering table                 | 0.000056 |
| Waiting for table metadata loc | 1.003832 |
| altering table                 | 0.000148 |
| Waiting for table metadata loc | 1.000791 |
| altering table                 | 0.000054 |
| Waiting for table metadata loc | 1.004019 |
| altering table                 | 0.000059 |
| Waiting for table metadata loc | 1.000523 |
| altering table                 | 0.000056 |
| Waiting for table metadata loc | 1.004071 |
| altering table                 | 0.000058 |
| Waiting for table metadata loc | 1.000656 |
| altering table                 | 0.000055 |
| Waiting for table metadata loc | 1.001957 |
| altering table                 | 0.000058 |
| Waiting for table metadata loc | 1.000260 |
| altering table                 | 0.000056 |
| Waiting for table metadata loc | 1.000440 |
| altering table                 | 0.000057 |
| Waiting for table metadata loc | 1.002061 |
| altering table                 | 0.000055 |
| Waiting for table metadata loc | 0.878074 |
| altering table                 | 0.000127 |
| committing alter table to stor | 0.031622 |
| end                            | 0.000078 |
| query end                      | 0.002045 |
| closing tables                 | 0.000041 |
| freeing items                  | 0.000143 |
| logging slow query             | 0.000116 |
| cleaning up                    | 0.000043 |
+--------------------------------+----------+
100 rows in set, 1 warning (0.00 sec)

從這里我們可以看出，After create和altering table 這兩個階段最耗時，同時這里兩個階段也出現了Waiting for table metadata lock的字眼，說明alter table add/drop index是阻塞在這兩階段（從時間上可以看出是一秒掃描一次是否能上鎖）。
altering table這個階段我們知道修改完數據之后會上MDL_EXCLUSIVE,這樣就會與MDL_SHARE_READ阻塞，那么After create又是什么階段呢？
在oracle官方bug帖上看到oracle人員的一個回復：
At certain point ALTER TABLE needs to acquire exclusive lock on table to install a new version of .FRM and to get rid of outdated  TABLE/TABLE_SHARE/handler instances in Table and Table Definition caches. At this point it will wait for existing SELECTs to stop and will block any new SELECTs.
意思是在某個點上(從源代碼可以追蹤到這是After create階段)，alter table add/drop index需要獲得排他鎖（MDL_EXCLUSIVE），目的是新建.FRM并清除舊的TABLE,TABLE_SHARE,handler的實例，這些實例在表以及表定義緩存中；這樣，這個階段需要的排它鎖（MDL_EXCLUSIZE）也會跟MDL_SHARE_READ互斥。


4.總結：
當線上alter table add /drop index，可能阻塞在兩個階段，一個是After create，另外一個是altering table;
通常情況下：大查詢在alter table語句執行之前（大查詢沒執行完而alter table 開始），alter table語句會阻塞在After create階段，大查詢在alter table語句執行之后（alter table事物沒完成，大查詢開始），alter table會阻塞在altering table階段.

關于“MySQL中查詢事物與DDL引發Waiting for table metadata lock的兩個階段是什么”這篇文章就分享到這里了，希望以上內容可以對大家有一定的幫助，使各位可以學到更多知識，如果覺得文章不錯，請把它分享出去讓更多的人看到。