"元數(shù)據(jù)"是用來描述數(shù)據(jù)對(duì)象定義的，而元數(shù)據(jù)鎖（Metadata Lock MDL）即是加在這些定義上。通常我們認(rèn)為非鎖定一致性讀（簡(jiǎn)單select）是不加鎖的，這個(gè)是基于表內(nèi)數(shù)據(jù)層面，其依然會(huì)對(duì)表的元數(shù)據(jù)加鎖，保證讀取數(shù)據(jù)期間表結(jié)構(gòu)不會(huì)變更。

一、元數(shù)據(jù)鎖簡(jiǎn)介

在事務(wù)執(zhí)行過程中，MySQL會(huì)對(duì)所有涉及對(duì)象的定義加上元數(shù)據(jù)鎖（語句執(zhí)行的時(shí)候加鎖），目的是保證事務(wù)執(zhí)行過程中對(duì)象定義不被修改（你不能在別人查詢的時(shí)候修改表結(jié)構(gòu)或者把表刪了）。

對(duì)表進(jìn)行DML操作時(shí)（select, update等），MySQL會(huì)對(duì)表的定義施加一個(gè)共享元數(shù)據(jù)鎖（S MDL），而進(jìn)行DDL操作時(shí)，會(huì)施加排他元數(shù)據(jù)鎖（X MDL）。DML之間的元數(shù)據(jù)鎖時(shí)不會(huì)互相阻塞的，而普通用戶通常只會(huì)執(zhí)行DML，他們是感知不到元數(shù)據(jù)鎖的。

如果DBA在業(yè)務(wù)運(yùn)行期間執(zhí)行了DDL，那么DDL也會(huì)嘗試獲取元數(shù)據(jù)鎖，在事務(wù)都很短小的時(shí)候，可能很快就獲取到了。但如果有長(zhǎng)事務(wù)阻塞了DDL，那么就有可能導(dǎo)致嚴(yán)重的問題。

示例：在會(huì)話1中執(zhí)行下面SQL:

create table t1 (id int primary key auto_increment);
begin;
select * from t1;

• MySQL對(duì)DML默認(rèn)是自動(dòng)提交的，因此每條DML語句都是獨(dú)立事務(wù)，當(dāng)語句執(zhí)行完，元數(shù)據(jù)鎖就釋放了，這里通過begin顯式開啟事務(wù)，讓select語句執(zhí)行完后，事務(wù)依然存在。

另啟動(dòng)一個(gè)會(huì)話2，執(zhí)行下面DDL語句，可以發(fā)現(xiàn)其被阻塞（會(huì)話遲遲不返回）：

alter table t1 add name varchar(16);

• DDL在執(zhí)行前會(huì)隱式提交事務(wù)并釋放元數(shù)據(jù)鎖，這就是為什么要另一個(gè)會(huì)話發(fā)起DDL。

啟動(dòng)會(huì)話3，執(zhí)行show processlist;命令，即可看到會(huì)話2在等待元數(shù)據(jù)鎖（Waiting for table metadata lock）：

show processlist;

二、查看元數(shù)據(jù)鎖

除了表，元數(shù)據(jù)鎖也會(huì)加在表空間，存儲(chǔ)過程，函數(shù)，觸發(fā)器等對(duì)象上。但最常遇到的問題是我們想修改表結(jié)構(gòu)，但是卻被元數(shù)據(jù)鎖阻塞了，導(dǎo)致DDL無法執(zhí)行，進(jìn)一步導(dǎo)致后續(xù)DML無法執(zhí)行（業(yè)務(wù)停滯），此時(shí)需要進(jìn)行人工干預(yù)。

2.1 查詢?cè)獢?shù)據(jù)鎖

MySQL提供了performance_schema.metadata_locks用來查詢具體元數(shù)據(jù)鎖信息，且默認(rèn)就打開了元數(shù)據(jù)鎖的信息收集，直接查詢即可。表中包含了持有，等待及其他中間狀態(tài)的MDL數(shù)據(jù)，當(dāng)鎖釋放時(shí)，會(huì)從表中刪除。

如果沒有打開元數(shù)據(jù)鎖信息收集，可以執(zhí)行下面的SQL：

update performance_schema.setup_instruments
set enabled = 'YES', timed = 'YES'
where name = 'wait/lock/metadata/sql/mdl';

也可以持久化寫入配置文件（需要重啟）：

[mysqld]
performance-schema-instrument='wait/lock/metadata/sql/mdl=ON'

我們依然用第1章的示例，在執(zhí)行完會(huì)話1的SQL后，另開一個(gè)會(huì)話執(zhí)行下面SQL：

select 
l.object_schema 數(shù)據(jù)庫(kù)名,
l.object_type 對(duì)象類型, 
l.object_name 對(duì)象名稱,
l.lock_type 鎖類型,
l.lock_duration 持續(xù)類型,
l.lock_status 鎖狀態(tài),
l.owner_thread_id 線程ID,
t.processlist_id 會(huì)話ID,
s.sql_text
from performance_schema.metadata_locks l
join performance_schema.threads t on t.thread_id=l.owner_thread_id
join performance_schema.events_statements_current s on s.thread_id=l.owner_thread_id
where l.object_schema='test'and l.object_name='t1';

• 鎖狀態(tài)為granted，代表成功獲取了元數(shù)據(jù)鎖

隨后執(zhí)行會(huì)話2的DDL，再次執(zhí)行查詢SQL，可以看到鎖狀態(tài)pending（等待中）：

• 通過會(huì)話ID，鎖狀態(tài)和SQL_Text三個(gè)字段，可以判斷會(huì)話ID為4107的select語句阻塞了alter table

2.2 常見問題

元數(shù)據(jù)鎖的獲取是有優(yōu)先級(jí)的，X鎖的優(yōu)先級(jí)要高于S鎖。在實(shí)際生產(chǎn)環(huán)境中，如果一個(gè)長(zhǎng)事務(wù)阻塞了DDL，由于其嘗試獲取的是X鎖（優(yōu)先級(jí)高），那么它還會(huì)阻止后續(xù)DML獲取S鎖。即：DML => DDL阻塞 =>DML阻塞，從現(xiàn)象上看就是表無法執(zhí)行任何操作。

在上面示例的基礎(chǔ)上，再重新開幾個(gè)會(huì)話執(zhí)行下面的SQL，你會(huì)發(fā)現(xiàn)所有類型DML都無法返回（甚至無法讀）：

insert into t1 values(1,'Vincent');
update t1 set name='Victor' where id=1;
delete from t1 where id=1;
select * from t1;

如果生產(chǎn)環(huán)境出現(xiàn)了DDL阻塞，你的processlist可能就是下面的樣子，堆積的DML會(huì)越來越多，最后擠爆線程：

show procelist;

解決方案：

• 盡量避免在業(yè)務(wù)活躍期間執(zhí)行DDL，特別是有長(zhǎng)事務(wù)的時(shí)候
• 如果已經(jīng)產(chǎn)生了阻塞，立刻取消DDL或?qū)⑵鋾?huì)話kill掉，先讓業(yè)務(wù)運(yùn)行下去

注：Online DDL在運(yùn)行過程中也會(huì)短暫地獲取X鎖，所以并不能解決DDL阻塞問題。

到此這篇關(guān)于MySQL 元數(shù)據(jù)鎖及問題排查的解決的文章就介紹到這了,更多相關(guān)MySQL 元數(shù)據(jù)鎖內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論