快捷導(dǎo)航

詳細(xì)分析mysql MDL元數(shù)據(jù)鎖

更新時(shí)間：2020年08月06日 15:17:16 作者：MySQL技術(shù)

這篇文章主要介紹了mysql MDL元數(shù)據(jù)鎖的相關(guān)資料，文中講解非常細(xì)致，代碼幫助大家更好的理解和學(xué)習(xí)，感興趣的朋友可以了解下

前言：

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

1.什么是MDL鎖

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

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

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

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

2.模擬與查找MDL鎖

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

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

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

下面展示下模擬及查找MDL鎖的過(guò)程：

# 會(huì)話1 事務(wù)中執(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 |
+--------------+--------+----------+---------------------+---------------------+

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

# 會(huì)話3 查詢所有會(huì)話 發(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          |
+----+------+-----------+--------+---------+------+---------------------------------+-------------------------------------------------------+

# 會(huì)話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 |
+-------------+--------------------+----------------+-----------------------+---------------------+---------------+-------------+--------+-----------------+----------------+

# 會(huì)話3 聯(lián)合其他系統(tǒng)表 查找出會(huì)話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 |
+-------------+--------------------+----------------+-----------------------+---------------------+---------------+-------------+--------+-----------------+----------------+----------------+

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

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

MDL鎖一旦發(fā)生會(huì)對(duì)業(yè)務(wù)造成極大影響，因?yàn)楹罄m(xù)所有對(duì)該表的訪問(wèn)都會(huì)被阻塞，造成連接積壓。我們?nèi)粘ＲM量避免MDL鎖的發(fā)生，下面給出幾點(diǎn)優(yōu)化建議可供參考：

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

總結(jié)：

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

以上就是詳細(xì)分析mysql MDL元數(shù)據(jù)鎖的詳細(xì)內(nèi)容，更多關(guān)于mysql MDL元數(shù)據(jù)鎖的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！

您可能感興趣的文章: