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

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

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

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

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

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

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

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

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

alter table t1 add name varchar(16);

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

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

show processlist;

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

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

2.1 查詢元數(shù)據(jù)鎖

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

如果沒有打開元數(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í)行完會話1的SQL后，另開一個會話執(zhí)行下面SQL：

select 
l.object_schema 數(shù)據(jù)庫名,
l.object_type 對象類型, 
l.object_name 對象名稱,
l.lock_type 鎖類型,
l.lock_duration 持續(xù)類型,
l.lock_status 鎖狀態(tài),
l.owner_thread_id 線程ID,
t.processlist_id 會話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í)行會話2的DDL，再次執(zhí)行查詢SQL，可以看到鎖狀態(tài)pending（等待中）：

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

2.2 常見問題

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

在上面示例的基礎上，再重新開幾個會話執(zhí)行下面的SQL，你會發(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會越來越多，最后擠爆線程：

show procelist;

解決方案：

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

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

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

最新評論