共計 4978 個字符,預計需要花費 13 分鐘才能閱讀完成。
行業資訊
數據庫
MySQL 數據庫
MySQL 中查詢事物與 DDL 引發 Waiting for table metadata lock 的兩個階段是什么
這篇文章將為大家詳細講解有關 MySQL 中查詢事物與 DDL 引發 Waiting for table metadata lock 的兩個階段是什么,丸趣 TV 小編覺得挺實用的,因此分享給大家做個參考,希望大家閱讀完這篇文章后可以有所收獲。
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 的兩個階段是什么”這篇文章就分享到這里了,希望以上內容可以對大家有一定的幫助,使各位可以學到更多知識,如果覺得文章不錯,請把它分享出去讓更多的人看到。