欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

MySQL 元數(shù)據(jù)鎖及問題排查的解決

 更新時(shí)間:2024年09月03日 10:26:46   作者:V1ncent Chen  
MySQL中的元數(shù)據(jù)鎖主要用于管理并發(fā)操作下的數(shù)據(jù)字典一致性,本文主要介紹了MySQL 元數(shù)據(jù)鎖及問題排查的解決,具有一定的參考價(jià)值,感興趣的可以了解一下

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

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

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

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

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

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

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

在這里插入圖片描述

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

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

alter table t1 add name varchar(16);

在這里插入圖片描述

  • DDL在執(zhí)行前會隱式提交事務(wù)并釋放元數(shù)據(jù)鎖,這就是為什么要另一個(gè)會話發(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ù)鎖阻塞了,導(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í),會從表中刪除。

如果沒有打開元數(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后,另開一個(gè)會話執(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三個(gè)字段,可以判斷會話ID為4107的select語句阻塞了alter table

2.2 常見問題

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

在上面示例的基礎(chǔ)上,再重新開幾個(gè)會話執(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è)務(wù)活躍期間執(zhí)行DDL,特別是有長事務(wù)的時(shí)候
  • 如果已經(jīng)產(chǎn)生了阻塞,立刻取消DDL或?qū)⑵鋾択ill掉,先讓業(yè)務(wù)運(yùn)行下去

注:Online DDL在運(yùn)行過程中也會短暫地獲取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ù)

    本篇文章主要對MySQL的主從復(fù)制、讀寫分離、備份恢復(fù)進(jìn)行詳細(xì)全面的講解,具有很好的參考價(jià)值,需要的朋友一起來看下吧
    2016-12-12
  • 銀河麒麟V10安裝MySQL5.7的詳細(xì)過程

    銀河麒麟V10安裝MySQL5.7的詳細(xì)過程

    這篇文章主要介紹了銀河麒麟V10安裝MySQL5.7,本文給大家介紹的非常詳細(xì),對大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值,需要的朋友可以參考下
    2023-05-05
  • MySQL的常見存儲引擎介紹與參數(shù)設(shè)置調(diào)優(yōu)

    MySQL的常見存儲引擎介紹與參數(shù)設(shè)置調(diào)優(yōu)

    這篇文章主要介紹了MySQL的常見存儲引擎介紹與參數(shù)設(shè)置調(diào)優(yōu),需要的朋友可以參考下
    2018-03-03
  • centos7利用yum安裝mysql 8.0.12

    centos7利用yum安裝mysql 8.0.12

    這篇文章主要為大家詳細(xì)介紹了centos7利用yum安裝mysql 8.0.12,具有一定的參考價(jià)值,感興趣的小伙伴們可以參考一下
    2018-08-08
  • 解決MySQL8.0報(bào)錯(cuò)Client does not support authentication protocol requested by server...問題

    解決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-05
  • MySQLBackup備份數(shù)據(jù)庫的操作過程

    MySQLBackup備份數(shù)據(jù)庫的操作過程

    這篇文章主要介紹了MySQLBackup備份數(shù)據(jù)庫的操作過程,包括安裝部署和配置備份管理員的相關(guān)知識,本文給大家介紹的非常詳細(xì),感興趣的朋友跟隨小編一起看看吧
    2024-03-03
  • Mysql普通索引與唯一索引的選擇詳析

    Mysql普通索引與唯一索引的選擇詳析

    這篇文章主要給大家介紹了關(guān)于Mysql普通索引與唯一索引的選擇的相關(guān)資料,文中通過示例代碼介紹的非常詳細(xì),對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧
    2020-11-11
  • 淺談MySQL聚簇索引

    淺談MySQL聚簇索引

    數(shù)據(jù)庫的索引從不同的角度可以劃分成不同的類型,聚簇索引便是其中一種。聚簇索引并不是一種單獨(dú)的索引類型,而是一種數(shù)據(jù)的存儲方式。本文詳細(xì)介紹了MySQL的聚簇索引,感興趣的同學(xué)可以參考閱讀
    2023-04-04
  • 解析mysql不重復(fù)字段值求和

    解析mysql不重復(fù)字段值求和

    本篇文章是對關(guān)于mysql不重復(fù)字段值求和進(jìn)行了詳細(xì)的分析介紹,需要的朋友參考下
    2013-06-06
  • Mysql數(shù)據(jù)庫性能優(yōu)化一

    Mysql數(shù)據(jù)庫性能優(yōu)化一

    今天,數(shù)據(jù)庫的操作越來越成為整個(gè)應(yīng)用的性能瓶頸了,這點(diǎn)對于Web應(yīng)用尤其明顯。關(guān)于數(shù)據(jù)庫的性能,這并不只是DBA才需要擔(dān)心的事,而這更是我們程序員需要去關(guān)注的事情
    2016-04-04

最新評論