閱讀653 返回首頁    go 阿裏雲 go 技術社區[雲棲]


[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

  上一篇:go  keepalived是如何實現MySQL高可用的?
  下一篇:go  銀行存取款模型的線程同步問題