深入理解MDL元數(shù)據(jù)鎖

前言：?

成都創(chuàng)新互聯(lián)公司專注于豐都網(wǎng)站建設服務及定制，我們擁有豐富的企業(yè)做網(wǎng)站經(jīng)驗。熱誠為您提供豐都營銷型網(wǎng)站建設，豐都網(wǎng)站制作、豐都網(wǎng)頁設計、豐都網(wǎng)站官網(wǎng)定制、小程序定制開發(fā)服務，打造豐都網(wǎng)絡公司原創(chuàng)品牌,更為您提供豐都網(wǎng)站排名全網(wǎng)營銷落地服務。

當你在MySQL中執(zhí)行一條SQL時，語句并沒有在你預期的時間內執(zhí)行完成，這時候我們通常會登陸到MySQL數(shù)據(jù)庫上查看是不是出了什么問題，通常會使用的一個命令就是 show processlist，看看有哪些session，這些session在做什么事情。當你看到?waiting for table metadata lock 時，那就是遇到MDL元數(shù)據(jù)鎖了。本篇文章將會介紹MDL鎖的產(chǎn)生與排查過程。

1.什么是MDL鎖

MDL全稱為metadata lock，即元數(shù)據(jù)鎖。MDL鎖主要作用是維護表元數(shù)據(jù)的數(shù)據(jù)一致性，在表上有活動事務（顯式或隱式）的時候，不可以對元數(shù)據(jù)進行寫入操作。因此從MySQL5.5版本開始引入了MDL鎖，來保護表的元數(shù)據(jù)信息，用于解決或者保證DDL操作與DML操作之間的一致性。

對于引入MDL，其主要解決了2個問題，一個是事務隔離問題，比如在可重復隔離級別下，會話A在2次查詢期間，會話B對表結構做了修改，兩次查詢結果就會不一致，無法滿足可重復讀的要求；另外一個是數(shù)據(jù)復制的問題，比如會話A執(zhí)行了多條更新語句期間，另外一個會話B做了表結構變更并且先提交，就會導致slave在重做時，先重做alter，再重做update時就會出現(xiàn)復制錯誤的現(xiàn)象。

元數(shù)據(jù)鎖是server層的鎖，表級鎖，每執(zhí)行一條DML、DDL語句時都會申請MDL鎖，DML操作需要MDL讀鎖，DDL操作需要MDL寫鎖（MDL加鎖過程是系統(tǒng)自動控制，無法直接干預，讀讀共享，讀寫互斥，寫寫互斥），申請MDL鎖的操作會形成一個隊列，隊列中寫鎖獲取優(yōu)先級高于讀鎖。一旦出現(xiàn)寫鎖等待，不但當前操作會被阻塞，同時還會阻塞后續(xù)該表的所有操作。事務一旦申請到MDL鎖后，直到事務執(zhí)行完才會將鎖釋放。（這里有種特殊情況如果事務中包含DDL操作，mysql會在DDL操作語句執(zhí)行前，隱式提交commit，以保證該DDL語句操作作為一個單獨的事務存在，同時也保證元數(shù)據(jù)排他鎖的釋放）。

注：支持事務的InnoDB引擎表和不支持事務的MyISAM引擎表，都會出現(xiàn)Metadata Lock Wait等待現(xiàn)象。一旦出現(xiàn)Metadata Lock Wait等待現(xiàn)象，后續(xù)所有對該表的訪問都會阻塞在該等待上，導致連接堆積，業(yè)務受影響。

2.模擬與查找MDL鎖

MDL鎖通常發(fā)生在DDL操作掛起的時候，原因是有未提交的事務對該表進行DML操作。而MySQL的會話那么多，不知道哪個會話的操作沒有及時提交影響了DDL。通常我們排查這類問題，往往需要從information_schema.innodb_trx表中查詢當前在執(zhí)行的事務，但當SQL已經(jīng)執(zhí)行過了，沒有commit，這個時候這個表中是看不到SQL的。

在MySQL5.7中，performance_schema庫中新增了metadata_locks表，專門記錄MDL的相關信息。首先要開啟MDL鎖記錄，執(zhí)行如下SQL開啟：

UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME = 'wait/lock/metadata/sql/mdl';

下面展示下模擬及查找MDL鎖的過程：

# 會話1 事務中執(zhí)行DML操作
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into student_tb (stu_id,stu_name) values (1009,'xin');
Query OK, 1 row affected (0.00 sec)

mysql> select * from student_tb;
+--------------+--------+----------+---------------------+---------------------+
| increment_id | stu_id | stu_name | create_time         | update_time         |
+--------------+--------+----------+---------------------+---------------------+
|            1 |   1001 | from1    | 2019-11-28 16:36:14 | 2019-11-28 16:36:14 |
|            2 |   1002 | dfsfd    | 2019-11-28 16:36:14 | 2019-11-28 16:36:14 |
|            3 |   1003 | fdgfg    | 2019-11-28 16:36:14 | 2019-11-28 16:36:14 |
|            4 |   1004 | sdfsdf   | 2019-11-28 16:36:14 | 2019-11-28 16:36:14 |
|            5 |   1005 | dsfsdg   | 2019-11-28 16:36:14 | 2019-11-28 16:36:14 |
|            6 |   1006 | fgd      | 2019-11-28 16:36:14 | 2019-11-28 16:36:14 |
|            7 |   1007 | fgds     | 2019-11-28 16:36:14 | 2019-11-28 16:36:14 |
|            8 |   1008 | dgfsa    | 2019-11-28 16:36:14 | 2019-11-28 16:36:14 |
|            9 |   1009 | xin      | 2019-11-28 17:05:29 | 2019-11-28 17:05:29 |
+--------------+--------+----------+---------------------+---------------------+

# 會話2 對該表加字段 執(zhí)行DDL操作 發(fā)現(xiàn)DDL掛起
mysql> alter table student_tb add stu_age int after stu_name;

# 會話3 查詢所有會話 發(fā)現(xiàn)發(fā)生MDL鎖
mysql> show processlist;
+----+------+-----------+--------+---------+------+---------------------------------+-------------------------------------------------------+
| Id | User | Host      | db     | Command | Time | State                           | Info                                                  |
+----+------+-----------+--------+---------+------+---------------------------------+-------------------------------------------------------+
| 31 | root | localhost | testdb | Sleep   |  125 |                                 | NULL                                                  |
| 32 | root | localhost | testdb | Query   |    7 | Waiting for table metadata lock | alter table student_tb add stu_age int after stu_name |
| 33 | root | localhost | testdb | Query   |    0 | starting                        | show processlist                                      |
+----+------+-----------+--------+---------+------+---------------------------------+-------------------------------------------------------+

# 會話3 查看metadata_locks表記錄 發(fā)現(xiàn)student_tb表有MDL鎖沖突
mysql> select * from performance_schema.metadata_locks;  
+-------------+--------------------+----------------+-----------------------+---------------------+---------------+-------------+--------+-----------------+----------------+
| OBJECT_TYPE | OBJECT_SCHEMA      | OBJECT_NAME    | OBJECT_INSTANCE_BEGIN | LOCK_TYPE           | LOCK_DURATION | LOCK_STATUS | SOURCE | OWNER_THREAD_ID | OWNER_EVENT_ID |
+-------------+--------------------+----------------+-----------------------+---------------------+---------------+-------------+--------+-----------------+----------------+
| TABLE       | testdb             | student_tb     |        94189250717664 | SHARED_WRITE        | TRANSACTION   | GRANTED     |        |              56 |             34 |
| GLOBAL      | NULL               | NULL           |       139764477045472 | INTENTION_EXCLUSIVE | STATEMENT     | GRANTED     |        |              57 |             18 |
| SCHEMA      | testdb             | NULL           |       139764477697808 | INTENTION_EXCLUSIVE | TRANSACTION   | GRANTED     |        |              57 |             18 |
| TABLE       | testdb             | student_tb     |       139764477697904 | SHARED_UPGRADABLE   | TRANSACTION   | GRANTED     |        |              57 |             18 |
| TABLE       | testdb             | student_tb     |       139764477697696 | EXCLUSIVE           | TRANSACTION   | PENDING     |        |              57 |             18 |
| TABLE       | performance_schema | metadata_locks |       139764544135120 | SHARED_READ         | TRANSACTION   | GRANTED     |        |              58 |             20 |
+-------------+--------------------+----------------+-----------------------+---------------------+---------------+-------------+--------+-----------------+----------------+

# 會話3 聯(lián)合其他系統(tǒng)表 查找出會話ID
mysql> select m.*,t.PROCESSLIST_ID from performance_schema.metadata_locks m left join performance_schema.threads t on m.owner_thread_id=t.thread_id;
+-------------+--------------------+----------------+-----------------------+---------------------+---------------+-------------+--------+-----------------+----------------+----------------+
| OBJECT_TYPE | OBJECT_SCHEMA      | OBJECT_NAME    | OBJECT_INSTANCE_BEGIN | LOCK_TYPE           | LOCK_DURATION | LOCK_STATUS | SOURCE | OWNER_THREAD_ID | OWNER_EVENT_ID | PROCESSLIST_ID |
+-------------+--------------------+----------------+-----------------------+---------------------+---------------+-------------+--------+-----------------+----------------+----------------+
| TABLE       | testdb             | student_tb     |        94189250717664 | SHARED_WRITE        | TRANSACTION   | GRANTED     |        |              56 |             34 |             31 |
| GLOBAL      | NULL               | NULL           |       139764477045472 | INTENTION_EXCLUSIVE | STATEMENT     | GRANTED     |        |              57 |             18 |             32 |
| SCHEMA      | testdb             | NULL           |       139764477697808 | INTENTION_EXCLUSIVE | TRANSACTION   | GRANTED     |        |              57 |             18 |             32 |
| TABLE       | testdb             | student_tb     |       139764477697904 | SHARED_UPGRADABLE   | TRANSACTION   | GRANTED     |        |              57 |             18 |             32 |
| TABLE       | testdb             | student_tb     |       139764477697696 | EXCLUSIVE           | TRANSACTION   | PENDING     |        |              57 |             18 |             32 |
| TABLE       | performance_schema | metadata_locks |       139764544135120 | SHARED_READ         | TRANSACTION   | GRANTED     |        |              58 |             22 |             33 |
| TABLE       | performance_schema | threads        |       139764549217280 | SHARED_READ         | TRANSACTION   | GRANTED     |        |              58 |             22 |             33 |
+-------------+--------------------+----------------+-----------------------+---------------------+---------------+-------------+--------+-----------------+----------------+----------------+

# 結果解讀：從上面結果明顯可以看出會話31持有student_tb表的SHARED_WRITE鎖，
# 需要等待其提交后或手動殺掉該會話方可解除MDL鎖。

3.如何優(yōu)化與避免MDL鎖

MDL鎖一旦發(fā)生會對業(yè)務造成極大影響，因為后續(xù)所有對該表的訪問都會被阻塞，造成連接積壓。我們日常要盡量避免MDL鎖的發(fā)生，下面給出幾點優(yōu)化建議可供參考：

開啟metadata_locks表記錄MDL鎖。
設置參數(shù)lock_wait_timeout為較小值，使被阻塞端主動停止。
規(guī)范使用事務，及時提交事務，避免使用大事務。
增強監(jiān)控告警，及時發(fā)現(xiàn)MDL鎖。
DDL操作及備份操作放在業(yè)務低峰期執(zhí)行。
少用工具開啟事務進行查詢，圖形化工具要及時關閉。

總結：?

本篇文章主要分三方面來詳解MDL鎖，首先介紹了MDL鎖產(chǎn)生的原因及作用，然后我們模擬出MDL鎖，并給出查找及解決方法，最后給出幾點避免MDL鎖的建議。其實，MDL鎖在DB運維過程中經(jīng)常遇到，它不是洪水猛獸，只是為了保護數(shù)據(jù)庫對象，保證數(shù)據(jù)一致性。希望大家看完這篇文章后能對MDL鎖有更清晰的認識。

參考：

https://blog.csdn.net/wanbf123/article/details/80697787
https://blog.csdn.net/finalkof1983/article/details/88063328
https://www.jianshu.com/p/c2efdde99e2f

本文名稱：深入理解MDL元數(shù)據(jù)鎖
分享URL：http://aaarwkj.com/article34/jpogpe.html

成都網(wǎng)站建設公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站設計公司、動態(tài)網(wǎng)站、定制網(wǎng)站、網(wǎng)站制作、網(wǎng)站策劃、網(wǎng)站建設

聲明：本網(wǎng)站發(fā)布的內容（圖片、視頻和文字）以用戶投稿、用戶轉載內容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內容未經(jīng)允許不得轉載，或轉載時需注明來源：創(chuàng)新互聯(lián)

猜你還喜歡下面的內容

欧美一级特黄大片做受成人-亚洲成人一区二区电影-激情熟女一区二区三区-日韩专区欧美专区国产专区

深入理解MDL元數(shù)據(jù)鎖

1.什么是MDL鎖

2.模擬與查找MDL鎖

3.如何優(yōu)化與避免MDL鎖