MySQL 元數(shù)據(jù)鎖及問題排查的解決
"元數(shù)據(jù)"是用來描述數(shù)據(jù)對象定義的,而元數(shù)據(jù)鎖(Metadata Lock MDL)即是加在這些定義上。通常我們認(rèn)為非鎖定一致性讀(簡單select)是不加鎖的,這個(gè)是基于表內(nèi)數(shù)據(jù)層面,其依然會(huì)對表的元數(shù)據(jù)加鎖,保證讀取數(shù)據(jù)期間表結(jié)構(gòu)不會(huì)變更。
一、元數(shù)據(jù)鎖簡介
在事務(wù)執(zhí)行過程中,MySQL會(huì)對所有涉及對象的定義加上元數(shù)據(jù)鎖(語句執(zhí)行的時(shí)候加鎖),目的是保證事務(wù)執(zhí)行過程中對象定義不被修改(你不能在別人查詢的時(shí)候修改表結(jié)構(gòu)或者把表刪了)。
對表進(jìn)行DML操作時(shí)(select, update等),MySQL會(huì)對表的定義施加一個(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í)候,可能很快就獲取到了。但如果有長事務(wù)阻塞了DDL,那么就有可能導(dǎo)致嚴(yán)重的問題。
示例:在會(huì)話1中執(zhí)行下面SQL:
create table t1 (id int primary key auto_increment); begin; select * from t1;
- MySQL對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ā)器等對象上。但最常遇到的問題是我們想修改表結(jié)構(gòu),但是卻被元數(shù)據(jù)鎖阻塞了,導(dǎo)致DDL無法執(zhí)行,進(jìn)一步導(dǎo)致后續(xù)DML無法執(zhí)行(業(yè)務(wù)停滯),此時(shí)需要進(jìn)行人工干預(yù)。
2.1 查詢元數(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ù)庫名, 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 會(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è)長事務(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,特別是有長事務(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)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
詳解MySQL的主從復(fù)制、讀寫分離、備份恢復(fù)
本篇文章主要對MySQL的主從復(fù)制、讀寫分離、備份恢復(fù)進(jìn)行詳細(xì)全面的講解,具有很好的參考價(jià)值,需要的朋友一起來看下吧2016-12-12MySQL的常見存儲(chǔ)引擎介紹與參數(shù)設(shè)置調(diào)優(yōu)
這篇文章主要介紹了MySQL的常見存儲(chǔ)引擎介紹與參數(shù)設(shè)置調(diào)優(yōu),需要的朋友可以參考下2018-03-03解決MySQL8.0報(bào)錯(cuò)Client does not support auth
本文主要介紹了解決MySQL8.0報(bào)錯(cuò)Client does not support authentication protocol requested by server...問題,文中通過代碼介紹的非常詳細(xì),對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2024-05-05MySQLBackup備份數(shù)據(jù)庫的操作過程
這篇文章主要介紹了MySQLBackup備份數(shù)據(jù)庫的操作過程,包括安裝部署和配置備份管理員的相關(guān)知識(shí),本文給大家介紹的非常詳細(xì),感興趣的朋友跟隨小編一起看看吧2024-03-03