653
[MySQL 5.7 metadata lock] 測試
介紹：
Metadata：事物內的表級鎖
5.5開始引入進來，5.6.6前，事物開啟後，會鎖定表的meta data lock，其他會話對表有DDL操作時，均需要等待DML釋放後方可繼續、5.6.6後，不再阻塞其他會話執行的DDL，但原來的會話再次訪問數據表時，會有error提示：table definition has changed ， please retry transaction
是想要對一個表執行DDL時，會查看表有沒有DML如果有則等待修改數據結構之前如何產生MDL鎖，都會產生MDL 鎖，meta data lock 鎖，執行過程中避免修改，使用lock_wait_time 超時時間為1年，需要進行調小
mysql@3306>[rds_test]>show variables like "%lock_wait_timeout%";
+--------------------------+----------+
| Variable_name | Value |
+--------------------------+----------+
| innodb_lock_wait_timeout | 50 |
| lock_wait_timeout | 31536000 |
+--------------------------+----------+
2 rows in set (0.03 sec)
案例：
DDL操作被大查詢block(阻塞)
當應用上線進入維護階段，則開始會有較多的數據庫變更操作，比如：添加字段，添加索引等操作，這一類操作導致鎖故障也非常頻繁，下麵將會介紹一則案例，一個DDL操作被查詢block，導致數據庫連接堆積。
mysql@3306>[rds_test]>show processlist;
+-----------+--------------+---------------------+----------+---------+------+---------------------------------+----------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----------+--------------+---------------------+----------+---------+------+---------------------------------+----------------------------------------+
| 218380240 | dba_test | 10.173.185.41:60754 | test_sql | Query | 12 | Waiting for table metadata lock | update a set id=8888888 where id=1 |
| 219013568 | dba_test | 10.173.185.41:60746 | test_sql | Query | 746 | User sleep | select id , sleep(200) from a |
| 268877248 | dba_test | 10.173.185.41:60752 | test_sql | Query | 688 | Waiting for table dba_test | select * from a limit 1 |
| 269152246 |dba_test | 10.173.185.41:60748 | test_sql | Query | 742 | Waiting for table metadata lock | alter table a add column gmt_c1 date |
| 269224487 | dba_test | 10.173.185.41:60750 | rds_test | Query | 0 | init | show processlist |
+-----------+--------------+---------------------+----------+---------+------+---------------------------------+----------------------------------------+
5 rows in set (0.02 sec)
metadata lock wait含義：為了在並發環境下維護表元數據的數據一致性，在表上有活動事物（顯示或者隱式）的時候，不可以對元數據進行寫入操作，因此MySQL引入了metadata lock，來飽和表的元數據信息。因此在對表進行上述操作時，如果表上有活動事物（未提交或回滾），請求寫入的會話，會等待在metadata lock wait。
導致 Metadata lock wait 等待的常見因素包括：活動事務，當前有對表的長時間查詢，顯示或者隱式開啟事務後未提交或回滾，比如查詢完成後未提交或者回滾，表上有失敗的查詢事務等。
查詢，更新和DDL操作的線程狀態都為Waiting for table metadata lock，對表t的操作全部被阻塞，前端源源不斷的請求到達數據庫，這個時候數據庫的連接很容易被打滿，那我們來分析一下為什麼有這這些鎖等待：
1.alter 操作的鎖等待：由於在表t上做了一個添加字段的操作，該操作會在結束前對表獲取一個metadata lock，但是該表上已經有一個查詢一直未結束，導致metadata lock一直獲取不到，所以添加字段操作隻能等待查詢結束，這就解釋了alter操作為什麼狀態為Waiting for table metadata lock。
2.查詢和更新的鎖等待：由於前麵進行的alter操作已經在a表上試圖獲取metadata lock，所以後續對表a的的查詢和更新操作在獲取metadata lock的時候會被alter操作所阻塞，進而導致這些線程狀態為Waiting for table metadata lock。
最後更新：2017-08-20 18:08:46