溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
MySQL 5.5.3版本中引入了Metadata lock: DDL語句打破了事務的隔離級別
在5.5.3版本之前,MySQL事務對于表結構元數據(Metadata)的鎖定是語句(statement)粒度的， 即語句執行完成后，不管事務是否可以完成，其表結構就可以被其他會話更新掉！
引入Metadata lock后，表結構元數據(Metadata)的鎖定變成了事務(transaction)粒度的，即只有事務結束時才會釋放Metadata lock。
現象：
顯式開啟事務后start transaction; 該事務內的query語句(包含select)會占用相關表的metadata lock(profile:Opening tables階段)
導致DDL語句被阻塞，因為獲取不到表的metadata lock
MySQL 5.6.6版本后 優化
metadata lock不阻塞DDL語句，但原有session再訪問此表時會返回Error信息“Table definition has changed, please retry transaction”
案列
可以看到，卡住的那一步是在rename result table，MySQL后臺將ALTER保存變成連續操作“創建臨時新表->插入老表的數據->臨時新表取到老表(RENAME)”，即實際的變化老表的操作在RENAME階段才發生。而這一步即是被阻塞的那一步。所以可以確認，Metadata lock影響到的是要真實修改表結構的動作。（回想：和pt-osc那節，一個select未執行完，alter 修改表結構產生metadata lock一個道理）
基于此，我們可以驗證ALTER TABLE,RENAME TABLE,DROP TABLE都會被Metadata lock影響，驗證步驟與上類似，省略。
值得注意的是，創建一個已存在的表也會被Metadata lock影響。如果在一個事務中用到了表A的Metadata lock（如SELECT了該表）且事務未完成，那么創建同名的表也會被卡住。所參考的文章中提及這一點。有興趣可參看：討論頁 ，這里不作討論。
metadata lock的另一個副作用：如上例中，ALTER TABLE應metadata lock被阻塞后，這導致后續其他事務針對該表的SELECT也會被阻塞！即ALTER TABLE的操作會影響到其他SELECT操作。根據上述示例：
Session2 mysql> alter table table1 add column c1 int;
此步一直處于等待狀態時，新會話中查詢table1。
Session3 mysql> select * from test1; #一直等待
Session4 mysql>show full processlist;
mysql> show full processlist;
+----+------+-----------+------+---------+------+---------------------------------+-------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+---------------------------------+-------------------------------------+
| 4 | root | localhost | test | Sleep | 1857 | | NULL |
| 5 | root | localhost | test | Query | 504 | Waiting for table metadata lock | select * from test1 |
| 6 | root | localhost | NULL | Query | 0 | NULL | show full processlist |
| 8 | root | localhost | test | Query | 511 | Waiting for table metadata lock | alter table test1 add column c2 int |
+----+------+-----------+------+---------+------+---------------------------------+-------------------------------------+
Session1 mysql> commit;
第一個會話提交后，Session2、Session3也成功執行。看看Session3的select在哪個步驟需要等待：
Session3 mysql> show profile;
+——————————–+———–+
| Status | Duration |
+——————————–+———–+
| starting | 0.000018 |
| Waiting for query cache lock | 0.000003 |
| checking query cache for query | 0.000037 |
| checking permissions | 0.000007 |
| Opening tables | 22.502591 |
| System lock | 0.000013 |
| Waiting for query cache lock | 0.000024 |
| init | 0.000022 |
| optimizing | 0.000009 |
| statistics | 0.000011 |
| preparing | 0.000015 |
| executing | 0.000003 |
| Sending data | 0.000036 |
| end | 0.000009 |
| query end | 0.000005 |
| closing tables | 0.000006 |
| freeing items | 0.000007 |
| Waiting for query cache lock | 0.000003 |
| freeing items | 0.000015 |
| Waiting for query cache lock | 0.000002 |
| freeing items | 0.000002 |
| storing result in query cache | 0.000004 |
| logging slow query | 0.000002 |
| logging slow query | 0.000028 |
| cleaning up | 0.000003 |
+——————————–+———–+
可以看到SELECT語句首先檢查查詢緩存，沒有命中，然后在Opening tables階段卡住。
這引出了另一個特點，基于上述場景，如果后續的SELECT能夠在查詢緩存中命中，那么不會被ALTER TABLE卡住，因為不需要Opening tables操作。命中緩存SELECT的SHOW PROFILE如下：
mysql> show profile;
+——————————–+———-+
| Status | Duration |
+——————————–+———-+
| starting | 0.000017 |
| Waiting for query cache lock | 0.000004 |
| checking query cache for query | 0.000006 |
| checking privileges on cached | 0.000004 |
| checking permissions | 0.000007 |
| sending cached result to clien | 0.000011 |
| logging slow query | 0.000002 |
| cleaning up | 0.000003 |
+——————————–+———-+
8 rows in set (0.00 sec)
遺留問題1.為什么第二次查詢，記錄沒有發生變化，但結果集返回空？
這個問題在這個頁面進行了討論，確認原因是：
MySQL中ALTER操作實際后臺是“創建新表”->”拷貝老表數據”->”RENAME新表成老表”。InnoDB引擎針對客戶端的REPEATABLE READ隔離級別，采用的是基于多版本(Multi-version)功能的”一致性讀”，即在事務中第一個查詢時，獲取的是當時版本的表的快照，而ALTER操作后實際是創建的新表，這個新表對于之前的快照而言是沒有數據的，所以第二次查詢新表基于快照的數據，返回空。在MySQL 5.6.6版本后，這樣的第二個查詢會返回Error信息“Table definition has changed, please retry transaction”。詳細請看這里。
遺留問題2.為什么沒有鎖等待超時？？
我們知道InnoDB行鎖的等待時間超過innodb_lock_wait_timeout就會報超時錯誤。這里的metadata lock一直等待著，為什么沒有超時呢？其實metadata lock的超時設置是另一個系統變量lock_wait_timeout。
lock_wait_timeout控制著所有涉及到metadata lock的操作，包括DML and DDL，以及在表、視圖、存儲過程、存儲函數上的操作，以及LOCK TABLES、FLUSH TABLES WITH READ LOCK、HANDLER等。
lock_wait_timeout的默認設置時一年，可以動態設置，根據實際場景進行調整。
大量參考：
http://www.mysqlperformanceblog.com/2013/02/01/implications-of-metadata-locking-changes-in-mysql-5-5/
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
