MySQL中查詢事物與DDL引發(fā)Waiting for table metadata lock的兩個(gè)階段是什么

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

1.現(xiàn)象描述：
SESSION1：

SESSION2：


SESSION3：


備注：（這里SESSION1，SESSION2，SESSION3按先后順序執(zhí)行）
當(dāng)SESSION1未提交時(shí)，SESSION2阻塞，SESSION3阻塞
當(dāng)SESSION1提交時(shí)，SESSION2仍然阻塞，SESSION3執(zhí)行成功（這里限于篇幅，讀者可以自行實(shí)驗(yàn)）


2.現(xiàn)象質(zhì)疑：
當(dāng)session1未提交時(shí)，我們看看metadata_locks,由下圖我們可以分析得出，是session1的SHARE_READ阻塞了EXCLUSIVE，同時(shí)SESSION3的SHARE_READ被EXCLUSIVE給阻塞了


當(dāng)SESSION1提交后，我們?cè)賮砜磎etadata_locks（如下圖）：我們發(fā)現(xiàn)SESSION2被SESSION3阻塞了，且還是SESSION3的EXCLUSIVE被SESSION2的SHARE_READ阻塞了，這里我們不經(jīng)疑惑，難道是SESSION3的SHARD_READ的優(yōu)先級(jí)要高些？（但是本人查看MDL_SHARE_READ的源碼注釋，沒有發(fā)現(xiàn)MDL_SHARE_READ的優(yōu)先級(jí)要高于MDL_EXCLUSIVE）


3.現(xiàn)象分析：
帶著上一步中標(biāo)紅部分的這個(gè)疑問，我們來查看下sql執(zhí)行耗時(shí)的各個(gè)階段，具體情況如下：

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 這兩個(gè)階段最耗時(shí)，同時(shí)這里兩個(gè)階段也出現(xiàn)了Waiting for table metadata lock的字眼，說明alter table add/drop index是阻塞在這兩階段（從時(shí)間上可以看出是一秒掃描一次是否能上鎖）。
altering table這個(gè)階段我們知道修改完數(shù)據(jù)之后會(huì)上MDL_EXCLUSIVE,這樣就會(huì)與MDL_SHARE_READ阻塞，那么After create又是什么階段呢？
在oracle官方bug帖上看到oracle人員的一個(gè)回復(fù)：
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.
意思是在某個(gè)點(diǎn)上(從源代碼可以追蹤到這是After create階段)，alter table add/drop index需要獲得排他鎖（MDL_EXCLUSIVE），目的是新建.FRM并清除舊的TABLE,TABLE_SHARE,handler的實(shí)例，這些實(shí)例在表以及表定義緩存中；這樣，這個(gè)階段需要的排它鎖（MDL_EXCLUSIZE）也會(huì)跟MDL_SHARE_READ互斥。


4.總結(jié)：
當(dāng)線上alter table add /drop index，可能阻塞在兩個(gè)階段，一個(gè)是After create，另外一個(gè)是altering table;
通常情況下：大查詢?cè)赼lter table語句執(zhí)行之前（大查詢沒執(zhí)行完而alter table 開始），alter table語句會(huì)阻塞在After create階段，大查詢?cè)赼lter table語句執(zhí)行之后（alter table事物沒完成，大查詢開始），alter table會(huì)阻塞在altering table階段.

關(guān)于“MySQL中查詢事物與DDL引發(fā)Waiting for table metadata lock的兩個(gè)階段是什么”這篇文章就分享到這里了，希望以上內(nèi)容可以對(duì)大家有一定的幫助，使各位可以學(xué)到更多知識(shí)，如果覺得文章不錯(cuò)，請(qǐng)把它分享出去讓更多的人看到。