MYSQL METADATA LOCK(MDL LOCK)MDL锁问题分析

2023-09-18 21:21:05 60

一、前言

MYSQL中MDL锁一直是一个比较让人比较头疼的问题，我们谈起锁一般更加倾向于INNODB下层的gaplock、nextkeylock、rowlock等，因为它很好理解，也很好观察，而对于MDLLOCK却了解得很少，因为它实在不好观察，只有出现问题查看showprocesslist勉强可以看到

简单的所谓的Waitingfortablemetadatalock之类的状态，其实MDLLOCK是MYSQL上层一个非常复杂的子系统，有自己的死锁检测机制

(无向图?)而大家一般口中的是不是锁表了其实就是指的它，可见的它的关键性和严重性，笔者也是根据自己的需求学习了一些(冰山一角)，而没有能力阅读全部的代码，但是笔者通过增加一个TICKET的打印函数让语句的MDLLOCK加锁流程全部打印出来方便学习研究，下面从一些基础说起然后告诉大家修改了哪些东西，最后对每种MDLTYPE进行测试和分析,如果大家对基本概念和增加打印函数不感兴趣可直接参考第五部分加锁测试,但是如果不了解基础知识可能看起来有点困难。

刚好最近遇到一次MDLLOCK出现死锁的情况会在下篇文章中给出案例，这里只看理论

----处于层次:MYSQLSERVER层次，实际上早在open_table函数中MDLLOCK就开始获取了，可以说他是最早获取的LOCK结构

----最早获取阶段:THD::enter_stage:'Openingtables'

调用栈帧

#0open_table_get_mdl_lock(thd=0x7fffd0000df0,ot_ctx=0x7fffec06fb00,
table_list=0x7fffd00067d8,flags=0,mdl_ticket=0x7fffec06f950)
at/root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_base.cc:2789
#10x0000000001516e17inopen_table(thd=0x7fffd0000df0,
table_list=0x7fffd00067d8,ot_ctx=0x7fffec06fb00)
at/root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_base.cc:3237

----死锁检测出错码:

{"ER_LOCK_DEADLOCK",1213,"Deadlockfoundwhentryingtogetlock;tryrestartingtransaction"},
ERROR1213(40001):Deadlockfoundwhentryingtogetlock;tryrestartingtransaction

MDLLOCK的死锁抛错和INNODB死锁一模一样不同的只是SHOWENGINEINNODB没有死锁信息。

----涉及代码:mdl.hmdl.cc

二、基础重要的数据结构(类)和概念

1、MDLTYPE

MDL_INTENTION_EXCLUSIVE(IX)

MDL_SHARED(S)

MDL_SHARED_HIGH_PRIO(SH)

MDL_SHARED_READ(SR)

MDL_SHARED_WRITE(SW)

MDL_SHARED_WRITE_LOW_PRIO(SWL)

MDL_SHARED_UPGRADABLE(SU)

MDL_SHARED_READ_ONLY(SRO)

MDL_SHARED_NO_WRITE(SNW)

MDL_SHARED_NO_READ_WRITE(SNRW)

MDL_EXCLUSIVE(X)

后面会对每种TYPE进行详细的测试，最后也会给出源码中解释

2、MDLNAMESPACE

在MDL中MDL_KEY按照NAMESPACE+DB+OBJECT_NAME的方式进行表示，所谓的namespace也不叫重要

下面是NAMESPACE的分类

-GLOBALisusedfortheglobalreadlock.

-TABLESPACEisfortablespaces.

-SCHEMAisforschemas(akadatabases).

-TABLEisfortablesandviews.

-FUNCTIONisforstoredfunctions.

-PROCEDUREisforstoredprocedures.

-TRIGGERisfortriggers.

-EVENTisforeventschedulerevents.

-COMMITisforenablingtheglobalreadlocktoblockcommits.

-USER_LEVEL_LOCKisforuser-levellocks.

-LOCKING_SERVICEisforthenamepluginRW-lockservice

3、实现分类

scopelock:一般对应全局MDLLOCK如flushtablewithreadlock为namespacespace:GLOBALtype:S

objectlock:如其名字，对象级别的MDLLOCK，比如TABLE

下面是源码中的注释：

/**
Helperstructwhichdefineshowdifferenttypesoflocksarehandled
foraspecificMDL_lock.Inpracticeweuseonlytwostrategies:"scoped"
lockstrategyforlocksinGLOBAL,COMMIT,TABLESPACEandSCHEMAnamespaces
and"object"lockstrategyforallothernamespaces.
*/

4、MDL兼容矩阵

scopelock:
|Typeofactive|
Request|scopedlock|
type|IS(*)IXSX|
---------+------------------+
IS|++++|
IX|++--|
S|+-+-|
X|+---|

objectlock:
Request|Grantedrequestsforlock|
type|SSHSRSWSWLPSUSROSNWSNRWX|
----------+---------------------------------------------+
S|+++++++++-|
SH|+++++++++-|
SR|++++++++--|
SW|++++++----|
SWLP|++++++----|
SU|+++++-+---|
SRO|+++--+++--|
SNW|+++---+---|
SNRW|++--------|
X|----------|

5、MDLduration及MDL持续到什么时候

这个也不多用过多解释，看源码注释即可

MDL_STATEMENT:Lockswithstatementdurationareautomaticallyreleasedattheend
ofstatementortransaction.
MDL_TRANSACTION:Lockswithtransactiondurationareautomaticallyreleasedattheend
oftransaction
MDL_EXPLICIT:Lockswithexplicitdurationsurvivetheendofstatementandtransaction.
TheyhavetobereleasedexplicitlybycallingMDL_context::release_lock().

6、MDLLOCKFASTPATH（unobtrusive）ORSLOWPATH（obtrusive）

使用两种不同的方式目的在于优化MDLlock的实现，下面是源码的注释

A)"unobtrusive"locktypes
1)Eachtypefromthissetshouldbecompatiblewithallother
typesfromtheset(includingitself).
2)ThesetypesshouldbecommonforDMLoperations
Ourgoalistooptimizeacquisitionandreleaseoflocksofthis
typebyavoidingcomplexchecksandmanipulationsonm_waiting/
m_grantedbitmaps/lists.Wereplacethemwithacheckofand
increment/decrementofintegercounters.
Wecallthelattertypeofacquisition/release"fastpath".
Useof"fastpath"reducesthesizeofcriticalsectionassociated
withMDL_lock::m_rwlocklockinthecommoncaseandthusincreases
scalability.
Theamountbywhichacquisition/releaseofspecifictype
"unobtrusive"lockincreases/decreasespackedcounterin
MDL_lock::m_fast_path_stateisreturnedbythisfunction.
B)"obtrusive"locktypes
1)Grantedorpendinglockofthosetypeisincompatiblewith
someothertypesoflocksorwithitself.
2)NotcommonforDMLoperations
Theselockshavetobealwaysacquiredinvolvingmanipulationson
m_waiting/m_grantedbitmaps/lists,i.e.wehavetouse"slowpath"
forthem.Moreoverinthepresenceofactive/pendinglocksfrom
"obtrusive"setwehavetoacquireusing"slowpath"evenlocksof
"unobtrusive"type.

7、MDL_request类

也就是通过语句解析后需要获得的MDLLOCK的需求，然后通过这个类对象在MDL子系统
中进行MDLLOCK申请，大概包含如下一些属性

/**Typeofmetadatalock.*/
enumenum_mdl_typetype;//需求的类型
/**Durationforrequestedlock.*/
enumenum_mdl_durationduration;//持续时间
/**
Pointersforparticipatinginthelistoflockrequestsforthiscontext.
*/
MDL_request*next_in_list;//双向链表实现
MDL_request**prev_in_list;
/**
Pointertothelockticketobjectforthislockrequest.
Validonlyifthislockrequestissatisfied.
*/
MDL_ticket*ticket;//注意这里如果申请成功(没有等待)，会指向一个实际的TICKET，否则为NULL
/**Alockisrequestedbasedonafullyqualifiednameandtype.*/
MDL_keykey;//注意这里是一个MDL_KEY类型，主要的就是前面说的NAMESPACE+DB+OBJECT_NAME

MDL_key类，就是实际的NAMESPACE+DB+OBJECT_NAME，整个放到一个char数组里面，他会是MDL_LOCK和MDL_REQUEST中出现

private:

uint16m_length;

uint16m_db_name_length;

charm_ptr[MAX_MDLKEY_LENGTH];//放到了这里

8、MDL_ticket

如同门票一样，如果获取了MDLLOCK必然给MDL_request返回一张门票，如果等待则不会源码MDL_context::acquire_lock

可以观察到。当然这也是我主要观察的一个类

/**
Pointersforparticipatinginthelistoflockrequestsforthiscontext.
Contextprivate.正如解释这里是context中链表链表的形成，是线程私有的
*/
MDL_ticket*next_in_context;
MDL_ticket**prev_in_context;
/**
Pointersforparticipatinginthelistofsatisfied/pendingrequests
forthelock.Externallyaccessible.正如解释这里是MDL_LOCK中链表链表的形成，是全局的
*/
MDL_ticket*next_in_lock;
MDL_ticket**prev_in_lock;
/**
Contextoftheownerofthemetadatalockticket.Externallyaccessible.
很明显这里指向了这个ticket的拥有者也就是MDL_context，它是线程的属性
*/
MDL_context*m_ctx;
/**
Pointertothelockobjectforthislockticket.Externallyaccessible.
很明显这里是一个指向MDL_LOCK的一个指针
*/
MDL_lock*m_lock;
/**
Indicatesthatticketcorrespondstolockacquiredusing"fastpath"
algorithm.Particularlythismeansthatitwasnotincludedinto
MDL_lock::m_grantedbitmap/listandinsteadisaccountedforby
MDL_lock::m_fast_path_locks_granted_counter
这里就代表了是否是FASTPATH从注释来看fastpath方式不会在MDLLOCK中
占用granted位图和链表取而代之代之的是一个统计器m_fast_path_locks_granted_counter
这样一来开销肯定更小
*/
boolm_is_fast_path;
/**
Indicatesthatticketcorrespondstolockrequestwhichrequired
storageenginenotificationduringitsacquisitionandrequires
storageenginenotificationafteritsrelease.
*/
boolm_hton_notified;

9、MDL_lock

每一个MDL_key都会对应一个MDL_lock，其中包含了所谓的GRANTED链表和WAIT链表，考虑它的复杂性，可以直接参考源码注释也非常详细，这里给出我所描述的几个属性。

/**Thekeyoftheobject(data)beingprotected.*/

MDL_keykey;

/**Listofgrantedticketsforthislock.*/

Ticket_listm_granted;

/**Ticketsforcontextswaitingtoacquirealock.*/

Ticket_listm_waiting;

10、MDL_context

这是整个MYSQL线程和MDLlock子系统进行交互的一个所谓的上下文结构其中包含了很多方法和属性，我比较关注的属性如下：

/**
Ifourrequestforalockisscheduled,orabortedbythedeadlock
detector,theresultisrecordedinthisclass.
*/
MDL_waitm_wait;
/**
ListsofallMDLticketsacquiredbythisconnection.
这是一个不同MDLlock持续时间的一个链表数组。实际就是
MDL_STATEMENT一个链表
MDL_TRANSACTION一个链表
MDL_EXPLICIT一个链表
*/
Ticket_listm_tickets[MDL_DURATION_END];
//这是一个父类指针指向子类对象，虚函数重写的典型,实际他就指向了一个线程
/*
classTHD:publicMDL_context_owner,
publicQuery_arena,
publicOpen_tables_state
*/
MDL_context_owner*m_owner;

11、MDL_wait

这个类主要是当前ticket获取状态

enum_wait_statusm_wait_status;

包含

EMPTY初始化

GRANTED获取成功

VICTIM死锁

TIMEOUT超时

KILLEDKILLED

12、等待标记

PSI_stage_infoMDL_key::m_namespace_to_wait_state_name[NAMESPACE_END]=
{
{0,"Waitingforglobalreadlock",0},
{0,"Waitingfortablespacemetadatalock",0},
{0,"Waitingforschemametadatalock",0},
{0,"Waitingfortablemetadatalock",0},
{0,"Waitingforstoredfunctionmetadatalock",0},
{0,"Waitingforstoredproceduremetadatalock",0},
{0,"Waitingfortriggermetadatalock",0},
{0,"Waitingforeventmetadatalock",0},
{0,"Waitingforcommitlock",0},
{0,"Userlock",0},/*Becompatiblewitholdstatus.*/
{0,"Waitingforlockingservicelock",0},
{0,"Waitingforbackuplock",0},
{0,"Waitingforbinloglock",0}
};

三、增加MDLLOCK打印函数

研究MDLLOCK锁最好的方式当然是能够获取MDL加锁、升级、降级的流程，因为源码太庞大了，不可能面面俱到
虽然5.7加入了

UPDATEperformance_schema.setup_consumersSETENABLED='YES'WHERENAME='global_instrumentation';

UPDATEperformance_schema.setup_instrumentsSETENABLED='YES'WHERENAME='wait/lock/metadata/sql/mdl';

select*fromperformance_schema.metadata_locks

的方式进行MDLLOCK的查看，但是如果要观察一个语句到底获取了哪些MDLLOCK还是显得无力所以笔者在mdl.cc中加入了一个函数原型如下

/*p_ticketinparameter*/

intmy_print_ticket(constMDL_ticket*p_ticket)

并且在mdl_ticket类中增加了这个函数原型为友元函数，否则私有成员获取不到，而给出的公有方法比较繁杂

friendintmy_print_ticket(constMDL_ticket*p_ticket);

主要获取MDLLOCK的如下信息打印到mysqlerr日志中:

线程id通过p_ticket->m_ctx->get_thd();获取

mdllockdatabasename通过p_ticket->m_lock->key.db_name()获取

mdllockobjectname通过p_ticket->m_lock->key.name()获取

mdllocknamespace通过p_ticket->m_lock->key.mdl_namespace()获取

mdllockfastpath通过p_ticket->m_is_fast_path获取判断是则输出否则不输出

mdllocktype通过p_ticket->m_type获取

mdllockduration通过p_ticket->m_duration获取

输出信息如下：

2017-08-03T07:34:21.720583Z3[Note](>MDLPRINT)Threadidis3:

2017-08-03T07:34:21.720601Z3[Note](->MDLPRINT)DB_nameis:test

2017-08-03T07:34:21.720619Z3[Note](-->MDLPRINT)OBJ_nameis:test

2017-08-03T07:34:21.720637Z3[Note](--->MDLPRINT)Namespaceis:TABLE

2017-08-03T07:34:21.720655Z3[Note](---->MDLPRINT)Fastpathis:(Y)

2017-08-03T07:34:21.720673Z3[Note](----->MDLPRINT)Mdltypeis:MDL_SHARED_WRITE(SW)

2017-08-03T07:34:21.720692Z3[Note](------>MDLPRINT)Mdldurationis:MDL_TRANSACTION

实际上和metadata_locks中的信息差不多，这是我这里的Threadid是showprocesslist出来的id，但是我可以获得锁获取的历史信息,我这里同时没有LOCK_STATUS:GRANTED，但是可以在MDL_context::acquire_lock逻辑上可以判断出来

mysql>select*fromperformance_schema.metadata_locks\G
***************************1.row***************************
OBJECT_TYPE:TABLE
OBJECT_SCHEMA:test
OBJECT_NAME:test
OBJECT_INSTANCE_BEGIN:140734412907760
LOCK_TYPE:SHARED_WRITE
LOCK_DURATION:TRANSACTION
LOCK_STATUS:GRANTED
SOURCE:sql_parse.cc:6314
OWNER_THREAD_ID:39
OWNER_EVENT_ID:241

四、在合适的位置增加打印函数进行观察

既然我们要研究MDLLOCK的加锁\升级\降级、那么我们就必要找到他们的函数入口，然后在合适的位置增加打印函数进行观察，下面标示出打印位置，删除了大部分的源代码，需要参考请自行查看源码

1、加锁：MDL_context::acquire_lock

bool
MDL_context::acquire_lock(MDL_request*mdl_request,ulonglock_wait_timeout)
{
if(mdl_request->ticket)//获取成功获得ticket
{
/*
Wehavemanagedtoacquirelockwithoutwaiting.
MDL_lock,MDL_contextandMDL_requestwereupdated
accordingly,sowecansimplyreturnsuccess.
*/
//REQUESET获取TICKET成功此处打印
returnFALSE;
}
/*
Ourattempttoacquirelockwithoutwaitinghasfailed.
AsaresultofthisattemptwegotMDL_ticketwithm_lock
memberpointingtothecorrespondingMDL_lockobjectwhich
hasMDL_lock::m_rwlockwrite-locked.
*/
//获取不成功加入MDL_lock等待队列
lock=ticket->m_lock;
lock->m_waiting.add_ticket(ticket);
will_wait_for(ticket);//死锁检测
/*Thereisasharedorexclusivelockontheobject.*/
DEBUG_SYNC(get_thd(),"mdl_acquire_lock_wait");
find_deadlock();
//此处打印TICKET进入了等待流程
if(lock->needs_notification(ticket)||lock->needs_connection_check())
{
}
done_waiting_for();//等待完成对死锁检测等待图进行调整去掉本等待边edge(无向图)
//当然到这里也是通过等待后获得成功了状态为GRANTED
DBUG_ASSERT(wait_status==MDL_wait::GRANTED);
m_tickets[mdl_request->duration].push_front(ticket);
mdl_request->ticket=ticket;
mysql_mdl_set_status(ticket->m_psi,MDL_ticket::GRANTED);
//此处打印通过等待REQUEST获得了TICKET
returnFALSE;
}

2、降级：voidMDL_ticket::downgrade_lock(enum_mdl_typenew_type)

voidMDL_ticket::downgrade_lock(enum_mdl_typenew_type)
{
/*OnlyallowdowngradefromEXCLUSIVEandSHARED_NO_WRITE.*/
DBUG_ASSERT(m_type==MDL_EXCLUSIVE||
m_type==MDL_SHARED_NO_WRITE);
//此处打印出降级前的TICKET
if(m_hton_notified)
{
mysql_mdl_set_status(m_psi,MDL_ticket::POST_RELEASE_NOTIFY);
m_ctx->get_owner()->notify_hton_post_release_exclusive(&m_lock->key);
m_hton_notified=false;
mysql_mdl_set_status(m_psi,MDL_ticket::GRANTED);
}
//函数结尾答应出降级后的TICKET
}

3、升级：MDL_context::upgrade_shared_lock(MDL_ticket*mdl_ticket,enum_mdl_typenew_type,ulonglock_wait_timeout)

bool
MDL_context::upgrade_shared_lock(MDL_ticket*mdl_ticket,
enum_mdl_typenew_type,
ulonglock_wait_timeout)
{
MDL_REQUEST_INIT_BY_KEY(&mdl_new_lock_request,
&mdl_ticket->m_lock->key,new_type,
MDL_TRANSACTION);//构造一个request
//此处打印出来的TICKET类型
if(acquire_lock(&mdl_new_lock_request,lock_wait_timeout))//尝试使用新的LOCK_TYPE进行加锁
DBUG_RETURN(TRUE);
is_new_ticket=!has_lock(mdl_svp,mdl_new_lock_request.ticket);
lock=mdl_ticket->m_lock;
//下面进行一系列对MDL_LOCK的维护并且对所谓的合并操作
/*Codebelowassumesthatwewereupgradingto"obtrusive"typeoflock.*/
DBUG_ASSERT(lock->is_obtrusive_lock(new_type));
/*Mergetheacquiredandtheoriginallock.@todo:movetoamethod.*/
mysql_prlock_wrlock(&lock->m_rwlock);
if(is_new_ticket)
{
m_tickets[MDL_TRANSACTION].remove(mdl_new_lock_request.ticket);
MDL_ticket::destroy(mdl_new_lock_request.ticket);
}
//此处打印出来的升级后TICKET类型
DBUG_RETURN(FALSE);
}

当然我现在只是在这些地方进行了打印，以后如果需要在其他地方答应加上函数就可以了。

五、各种MDLLOCKTYPE加锁测试

1、MDL_INTENTION_EXCLUSIVE(IX)

这个锁会在很多操作的时候都会出现比如做任何一个DML/DDL操作都会触发，实际上DELTE/UPDATE/INSERT/FORUPDATE等DML操作会在GLOBAL上加IX锁然后才会在本对象上加锁而DDL语句至少会在GLOBAL上加IX锁，对象所属SCHEMA上加IX锁，本对象加锁下面是DELETE触发的GLOABLIXMDLLOCK

2017-08-03T18:22:38.092100Z3[Note]Test2:open_tables_for_query()
2017-08-03T18:22:38.092205Z3[Note](acquire_lock)THISMDLLOCKacquireok!
2017-08-03T18:22:38.092242Z3[Note](>MDLPRINT)Threadidis3:
2017-08-03T18:22:38.092276Z3[Note](--->MDLPRINT)Namespaceis:GLOBAL
2017-08-03T18:22:38.092310Z3[Note](---->MDLPRINT)Fastpathis:(Y)
2017-08-03T18:22:38.092344Z3[Note](----->MDLPRINT)Mdltypeis:MDL_INTENTION_EXCLUSIVE(IX)
2017-08-03T18:22:38.092380Z3[Note](------>MDLPRINT)Mdldurationis:MDL_STATEMENT
2017-08-03T18:22:38.092551Z3[Note](------->MDLPRINT)Mdlstatusis:EMPTY

下面是ALETER语句触发的GLOABLIXMDLLOCK以及SCHEMA级别的MDLLOCK

2017-08-03T18:46:05.894871Z3[Note](acquire_lock)THISMDLLOCKacquireok!
2017-08-03T18:46:05.894915Z3[Note](>MDLPRINT)Threadidis3:
2017-08-03T18:46:05.894948Z3[Note](--->MDLPRINT)Namespaceis:GLOBAL
2017-08-03T18:46:05.894980Z3[Note](---->MDLPRINT)Fastpathis:(Y)
2017-08-03T18:46:05.895012Z3[Note](----->MDLPRINT)Mdltypeis:MDL_INTENTION_EXCLUSIVE(IX)
2017-08-03T18:46:05.895044Z3[Note](------>MDLPRINT)Mdldurationis:MDL_STATEMENT
2017-08-03T18:46:05.895076Z3[Note](------->MDLPRINT)Mdlstatusis:EMPTY
2017-08-03T18:46:05.895116Z3[Note](acquire_lock)THISMDLLOCKacquireok!
2017-08-03T18:46:05.895147Z3[Note](>MDLPRINT)Threadidis3:
2017-08-03T18:46:05.895206Z3[Note](->MDLPRINT)DB_nameis:test
2017-08-03T18:46:05.895243Z3[Note](-->MDLPRINT)OBJ_nameis:
2017-08-03T18:46:05.895276Z3[Note](--->MDLPRINT)Namespaceis:SCHEMA
2017-08-03T18:46:05.895325Z3[Note](---->MDLPRINT)Fastpathis:(Y)
2017-08-03T18:46:05.895357Z3[Note](----->MDLPRINT)Mdltypeis:MDL_INTENTION_EXCLUSIVE(IX)
2017-08-03T18:46:05.895390Z3[Note](------>MDLPRINT)Mdldurationis:MDL_TRANSACTION
2017-08-03T18:46:05.895421Z3[Note](------->MDLPRINT)Mdlstatusis:EMPTY

所以这个MDLLOCK无所不在，而只有是否兼容问题，如果不兼容则堵塞。SCOPED的IX类型一般都是兼容的除非遇到
S类型

2、MDL_SHARED(S)

这把锁一般用在flushtableswithreadlock中

mysql>flushtableswithreadlock;
QueryOK,0rowsaffected(0.01sec)
2017-08-03T18:19:11.603911Z3[Note](acquire_lock)THISMDLLOCKacquireok!
2017-08-03T18:19:11.603947Z3[Note](>MDLPRINT)Threadidis3:
2017-08-03T18:19:11.603971Z3[Note](--->MDLPRINT)Namespaceis:GLOBAL
2017-08-03T18:19:11.603994Z3[Note](----->MDLPRINT)Mdltypeis:MDL_SHARED(S)
2017-08-03T18:19:11.604045Z3[Note](------>MDLPRINT)Mdldurationis:MDL_EXPLICIT
2017-08-03T18:19:11.604073Z3[Note](------->MDLPRINT)Mdlstatusis:EMPTY
2017-08-03T18:19:11.604133Z3[Note](acquire_lock)THISMDLLOCKacquireok!
2017-08-03T18:19:11.604156Z3[Note](>MDLPRINT)Threadidis3:
2017-08-03T18:19:11.604194Z3[Note](--->MDLPRINT)Namespaceis:COMMIT
2017-08-03T18:19:11.604217Z3[Note](----->MDLPRINT)Mdltypeis:MDL_SHARED(S)
2017-08-03T18:19:11.604240Z3[Note](------>MDLPRINT)Mdldurationis:MDL_EXPLICIT
2017-08-03T18:19:11.604310Z3[Note](------->MDLPRINT)Mdlstatusis:EMPTY

我们注意到其namspace为GLOBAL和COMMIT显然他们是SCOPEDLOCK，他们的TYPE为S，那么很显然根据兼容性原则
SCOPED的MDLIX和MDLS不兼容，flushtableswithreadlock;就会堵塞所有DELTE/UPDATE/INSERT/FORUPDATE
等DML和DDL操作(因为这些操作都需要GLOBALMDLIX锁)

3、MDL_SHARED_HIGH_PRIO(SH)

这个锁基本上大家也是经常用到只是没感觉到而已，比如我们一般desc操作

兼容性：

Request|Grantedrequestsforlock|
type|SSHSRSWSWLPSUSROSNWSNRWX|
----------+---------------------------------------------+
SH|+++++++++-|
mysql>desctest.testsort10;

2017-08-03T19:06:05.843277Z4[Note](acquire_lock)THISMDLLOCKacquireok!
2017-08-03T19:06:05.843324Z4[Note](>MDLPRINT)Threadidis4:
2017-08-03T19:06:05.843359Z4[Note](->MDLPRINT)DB_nameis:test
2017-08-03T19:06:05.843392Z4[Note](-->MDLPRINT)OBJ_nameis:testsort10
2017-08-03T19:06:05.843425Z4[Note](--->MDLPRINT)Namespaceis:TABLE
2017-08-03T19:06:05.843456Z4[Note](---->MDLPRINT)Fastpathis:(Y)
2017-08-03T19:06:05.843506Z4[Note](----->MDLPRINT)Mdltypeis:MDL_SHARED_HIGH_PRIO(SH)
2017-08-03T19:06:05.843538Z4[Note](------>MDLPRINT)Mdldurationis:MDL_TRANSACTION
2017-08-03T19:06:05.843570Z4[Note](------->MDLPRINT)Mdlstatusis:EMPTY

这中类型的优先级比较高，但是其和X不兼容。也很好理解比如在rename阶段肯定不能进行desc操作。

4、MDL_SHARED_READ(SR)

这把锁一般用在非当前读取的select中兼容性：

Request|Grantedrequestsforlock|
type|SSHSRSWSWLPSUSROSNWSNRWX|
----------+---------------------------------------------+
SR|++++++++--|
mysql>select*fromtest.testsort10limit1;

2017-08-03T19:13:52.338764Z4[Note](acquire_lock)THISMDLLOCKacquireok!
2017-08-03T19:13:52.338813Z4[Note](>MDLPRINT)Threadidis4:
2017-08-03T19:13:52.338847Z4[Note](->MDLPRINT)DB_nameis:test
2017-08-03T19:13:52.338883Z4[Note](-->MDLPRINT)OBJ_nameis:testsort10
2017-08-03T19:13:52.338917Z4[Note](--->MDLPRINT)Namespaceis:TABLE
2017-08-03T19:13:52.338950Z4[Note](---->MDLPRINT)Fastpathis:(Y)
2017-08-03T19:13:52.339025Z4[Note](----->MDLPRINT)Mdltypeis:MDL_SHARED_READ(SR)
2017-08-03T19:13:52.339062Z4[Note](------>MDLPRINT)Mdldurationis:MDL_TRANSACTION
2017-08-03T19:13:52.339097Z4[Note](------->MDLPRINT)Mdlstatusis:EMPTY

这里还是要提及一下平时我们偶尔会出现select也堵住的情况(比如DDL的某个阶段需要对象MDLX锁)。我们不得不抱怨
MYSQL居然会堵塞select其实这里也就是objectmdllockX和SR不兼容的问题(参考前面的兼容矩阵)。

5、MDL_SHARED_WRITE(SW)

这把锁一般用于DELTE/UPDATE/INSERT/FORUPDATE等操作对table的加锁(当前读)，不包含DDL操作
但是要注意DML操作实际上会有一个GLOBAL的IX的锁，前面已经提及过了，这把锁只是对象上的

兼容性：

Request|Grantedrequestsforlock|
type|SSHSRSWSWLPSUSROSNWSNRWX|
----------+---------------------------------------------+
SW|++++++----|
mysql>select*fromtest.testsort10limit1forupdate;

2017-08-03T19:25:41.218428Z4[Note](acquire_lock)THISMDLLOCKacquireok!
2017-08-03T19:25:41.218461Z4[Note](>MDLPRINT)Threadidis4:
2017-08-03T19:25:41.218493Z4[Note](->MDLPRINT)DB_nameis:test
2017-08-03T19:25:41.218525Z4[Note](-->MDLPRINT)OBJ_nameis:testsort10
2017-08-03T19:25:41.218557Z4[Note](--->MDLPRINT)Namespaceis:TABLE
2017-08-03T19:25:41.218588Z4[Note](---->MDLPRINT)Fastpathis:(Y)
2017-08-03T19:25:41.218620Z4[Note](----->MDLPRINT)Mdltypeis:MDL_SHARED_WRITE(SW)
2017-08-03T19:25:41.218677Z4[Note](------>MDLPRINT)Mdldurationis:MDL_TRANSACTION
2017-08-03T19:25:41.218874Z4[Note](------->MDLPRINT)Mdlstatusis:EMPTY

6、MDL_SHARED_WRITE_LOW_PRIO(SWL)

这把锁很少用到源码注释只有

UsedbyDMLstatementsmodifying

tablesandusingtheLOW_PRIORITYclause

会用到

兼容性：

Request|Grantedrequestsforlock|
type|SSHSRSWSWLPSUSROSNWSNRWX|
----------+---------------------------------------------+
SWLP|++++++----|
mysql>updateLOW_PRIORITYtest.testsort10setid1=1000whereid1=96282;
2017-08-03T19:32:47.433507Z4[Note](acquire_lock)THISMDLLOCKacquireok!
2017-08-03T19:32:47.433521Z4[Note](>MDLPRINT)Threadidis4:
2017-08-03T19:32:47.433533Z4[Note](->MDLPRINT)DB_nameis:test
2017-08-03T19:32:47.433547Z4[Note](-->MDLPRINT)OBJ_nameis:testsort10
2017-08-03T19:32:47.433560Z4[Note](--->MDLPRINT)Namespaceis:TABLE
2017-08-03T19:32:47.433572Z4[Note](---->MDLPRINT)Fastpathis:(Y)
2017-08-03T19:32:47.433594Z4[Note](----->MDLPRINT)Mdltypeis:MDL_SHARED_WRITE_LOW_PRIO(SWL)
2017-08-03T19:32:47.433607Z4[Note](------>MDLPRINT)Mdldurationis:MDL_TRANSACTION
2017-08-03T19:32:47.433620Z4[Note](------->MDLPRINT)Mdlstatusis:EMPTY

7、MDL_SHARED_UPGRADABLE(SU)

这把锁一般在ALTERTABLE语句中用到，他可以升级为SNW,SNRW，X，同时至少X锁也可以降级为SU

实际上在INNODBONLINEDDL中非常依赖于他,DML(SW)和SELECT(SR)都不会堵塞

兼容性：

Request|Grantedrequestsforlock|
type|SSHSRSWSWLPSUSROSNWSNRWX|
----------+---------------------------------------------+
SU|+++++-+---|

我们有必要研究一下他的兼容性，可以看到OBJECTLOCK中(SELECT)SR(DML)SW都是允许的，而在SCOPEDLOCK中
虽然DMLDDL都会在GLOBAL上锁但是其类型都是IX所以这个SU锁不堵塞DML/SELECT读写操作进入INNODB引擎层，它是ONLINEDDL的根基，如果不兼容你都进入不了INNODB引擎层，更谈不上什么ONLINEDDL，注意我这里说的ALGORITHM=INPLACE并且不设置LOCK

(ForDDLoperationswithLOCK=DEFAULT,orwiththeLOCKclauseomitted,MySQLusesthelowestlevel
oflockingthatisavailableforthatkindofoperation,allowingconcurrentqueries,DML,orbothwherever
possible.Thisisthesettingtousewhenmakingpre-planned,pre-testedchangesthatyouknowwillnot
causeanyavailabilityproblemsbasedontheworkloadforthattable
WhenanoperationontheprimarykeyusesALGORITHM=INPLACE,eventhoughthedataisstillcopied,it
ismoreefficientthanusingALGORITHM=COPYbecause:
?NoundologgingorassociatedredologgingisrequiredforALGORITHM=INPLACE.Theseoperationsadd
overheadtoDDLstatementsthatuseALGORITHM=COPY.
?Thesecondaryindexentriesarepre-sorted,andsocanbeloadedinorder.
?Thechangebufferisnotused,becausetherearenorandom-accessinsertsintothesecondaryindexes.
)

如下面的语句

mysql>altertabletestsort12addcolumnitintnotnull;
QueryOK,0rowsaffected(6.27sec)
Records:0Duplicates:0Warnings:0

我简单的分析一下：

2017-08-03T19:46:54.781453Z3[Note](acquire_lock)THISMDLLOCKacquireok!
2017-08-03T19:46:54.781487Z3[Note](>MDLPRINT)Threadidis3:
2017-08-03T19:46:54.781948Z3[Note](->MDLPRINT)DB_nameis:test
2017-08-03T19:46:54.781990Z3[Note](-->MDLPRINT)OBJ_nameis:testsort12
2017-08-03T19:46:54.782026Z3[Note](--->MDLPRINT)Namespaceis:TABLE
2017-08-03T19:46:54.782060Z3[Note](----->MDLPRINT)Mdltypeis:MDL_SHARED_UPGRADABLE(SU)
2017-08-03T19:46:54.782096Z3[Note](------>MDLPRINT)Mdldurationis:MDL_TRANSACTION
2017-08-03T19:46:54.782175Z3[Note](------->MDLPRINT)Mdlstatusis:EMPTY
2017-08-03T19:46:54.803898Z3[Note](upgrade_shared_lock)THISMDLLOCKwillupgrade
2017-08-03T19:46:54.804201Z3[Note](upgrade_shared_lock)THISMDLLOCKupgradeTO
2017-08-03T19:46:54.804240Z3[Note](>MDLPRINT)Threadidis3:
2017-08-03T19:46:54.804254Z3[Note](->MDLPRINT)DB_nameis:test
2017-08-03T19:46:54.804267Z3[Note](-->MDLPRINT)OBJ_nameis:testsort12
2017-08-03T19:46:54.804280Z3[Note](--->MDLPRINT)Namespaceis:TABLE
2017-08-03T19:46:54.804293Z3[Note](----->MDLPRINT)Mdltype:MDL_EXCLUSIVE(X)
2017-08-03T19:46:54.804306Z3[Note](------>MDLPRINT)Mdldurationis:MDL_TRANSACTION
2017-08-03T19:46:54.804319Z3[Note](------->MDLPRINT)Mdlstatusis:EMPTY
2017-08-03T19:46:54.855563Z3[Note](downgrade_lock)THISMDLLOCKwilldowngrade
2017-08-03T19:46:54.855693Z3[Note](downgrade_lock)tothisMDLlock
2017-08-03T19:46:54.855706Z3[Note](>MDLPRINT)Threadidis3:
2017-08-03T19:46:54.855717Z3[Note](->MDLPRINT)DB_nameis:test
2017-08-03T19:46:54.856053Z3[Note](-->MDLPRINT)OBJ_nameis:testsort12
2017-08-03T19:46:54.856069Z3[Note](--->MDLPRINT)Namespaceis:TABLE
2017-08-03T19:46:54.856082Z3[Note](----->MDLPRINT)Mdltypeis:MDL_SHARED_UPGRADABLE(SU)
2017-08-03T19:46:54.856094Z3[Note](------>MDLPRINT)Mdldurationis:MDL_TRANSACTION
2017-08-03T19:46:54.856214Z3[Note](------->MDLPRINT)Mdlstatusis:EMPTY
2017-08-03T19:47:00.260166Z3[Note](upgrade_shared_lock)THISMDLLOCKwillupgrade
2017-08-03T19:47:00.304057Z3[Note](upgrade_shared_lock)THISMDLLOCKupgradeTO
2017-08-03T19:47:00.304090Z3[Note](>MDLPRINT)Threadidis3:
2017-08-03T19:47:00.304105Z3[Note](->MDLPRINT)DB_nameis:test
2017-08-03T19:47:00.304119Z3[Note](-->MDLPRINT)OBJ_nameis:testsort12
2017-08-03T19:47:00.304132Z3[Note](--->MDLPRINT)Namespaceis:TABLE
2017-08-03T19:47:00.304181Z3[Note](----->MDLPRINT)Mdltypeis:MDL_EXCLUSIVE(X)
2017-08-03T19:47:00.304196Z3[Note](------>MDLPRINT)Mdldurationis:MDL_TRANSACTION
2017-08-03T19:47:00.304211Z3[Note](------->MDLPRINT)Mdlstatusis:EMPTY
2017-08-03T19:47:01.032329Z3[Note](acquire_lock)THISMDLLOCKacquireok!

首先获得testsort12表上的

2017-08-03T19:46:54.781487获得MDL_SHARED_UPGRADABLE(SU)
2017-08-03T19:46:54.804293升级MDL_EXCLUSIVE(X)
2017-08-03T19:46:54.855563降级MDL_SHARED_UPGRADABLE(SU)
2017-08-03T19:47:00.304057升级MDL_EXCLUSIVE(X)

因为不管如何这个alter操作还是比较费时的，从时间我们看到2017-08-03T19:46:54降级完成到2017-08-03T19:47:00这段时间
实际上是最耗时的实际上这里就是实际的COPY操作，但是这个过程实际在MDLSU模式下所以不会堵塞DML/SELECT操作。
这里再给大家提个醒所谓的ONLINEDDL只是在COPY阶段不堵塞DML/SELECT操作，还是尽量在数据库压力小的时候，
比如如果有DML没有提交或者SELECT没有做完这个时候SWSR必然堵塞X，而X能够堵塞一切且为高优先级。这样导致
的现象就是由于DML未提交堵塞DDL操作而DDL操作堵塞一切操作，基本对于这个TABLE的表全部堵塞。而对于ALGORITHM=COPY其他部分差不多，但是在COPY阶段用的是SNW锁，接下来我就先来看看SNW锁

8、MDL_SHARED_NO_WRITE(SNW)

SU可以升级为SNW而SNW可以升级为X，如前面所提及用于ALGORITHM=COPY中，保护数据的一致性。
先看看它的兼容性

Request|Grantedrequestsforlock
type|SSHSRSWSWLPSUSROSNWSNRWX|
----------+---------------------------------------------+
SNW|+++---+---|

可以看到SR可以但是SW不行，当然也就堵塞了DML(SW)而SELECT(SR)不会堵塞，下面我只是给出了关键部分

mysql>altertabletestsort12addcolumnikintnotnull,ALGORITHM=COPY;
2017-08-03T20:07:58.413215Z3[Note](upgrade_shared_lock)THISMDLLOCKupgradeTO
2017-08-03T20:07:58.413241Z3[Note](>MDLPRINT)Threadidis3:
2017-08-03T20:07:58.413257Z3[Note](->MDLPRINT)DB_nameis:test
2017-08-03T20:07:58.413273Z3[Note](-->MDLPRINT)OBJ_nameis:testsort12
2017-08-03T20:07:58.413292Z3[Note](--->MDLPRINT)Namespaceis:TABLE
2017-08-03T20:07:58.413308Z3[Note](----->MDLPRINT)Mdltypeis:MDL_SHARED_NO_WRITE(SNW)
2017-08-03T20:07:58.413325Z3[Note](------>MDLPRINT)Mdldurationis:MDL_TRANSACTION
2017-08-03T20:07:58.413341Z3[Note](------->MDLPRINT)Mdlstatusis:EMPTY
2017-08-03T20:08:25.392006Z3[Note](upgrade_shared_lock)THISMDLLOCKupgradeTO
2017-08-03T20:08:25.392024Z3[Note](>MDLPRINT)Threadidis3:
2017-08-03T20:08:25.392086Z3[Note](->MDLPRINT)DB_nameis:test
2017-08-03T20:08:25.392159Z3[Note](-->MDLPRINT)OBJ_nameis:testsort12
2017-08-03T20:08:25.392199Z3[Note](--->MDLPRINT)Namespaceis:TABLE
2017-08-03T20:08:25.392214Z3[Note](----->MDLPRINT)Mdltypeis:MDL_EXCLUSIVE(X)
2017-08-03T20:08:25.392228Z3[Note](------>MDLPRINT)Mdldurationis:MDL_TRANSACTION
2017-08-03T20:08:25.392242Z3[Note](------->MDLPRINT)Mdlstatusis:EMPTY
2017-08-03T20:07:58.413308获得了MDL_SHARED_NO_WRITE(SNW)
2017-08-03T20:08:25.392006升级为MDL_EXCLUSIVE(X)

这2017-08-03T20:07:58.413308到2017-08-03T20:08:25.392006就是实际COPY的时间，可见整个COPY期间只能DML
而不能SELECT，也是ALGORITHM=COPY和ALGORITHM=INPLACE一个关键区别。

9、MDL_SHARED_READ_ONLY(SRO)

用于LOCKTABLESREAD语句

兼容性如下

Request|Grantedrequestsforlock|
type|SSHSRSWSWLPSUSROSNWSNRWX|
----------+---------------------------------------------+
SRO|+++--+++--|
堵塞DML(SW)但是SELECT(SR)还是可以的。
mysql>locktabletestsort12read;
QueryOK,0rowsaffected(0.01sec)
2017-08-03T21:08:27.267947Z3[Note](acquire_lock)THISMDLLOCKacquireok!
2017-08-03T21:08:27.267979Z3[Note](>MDLPRINT)Threadidis3:
2017-08-03T21:08:27.268009Z3[Note](->MDLPRINT)DB_nameis:test
2017-08-03T21:08:27.268040Z3[Note](-->MDLPRINT)OBJ_nameis:testsort12
2017-08-03T21:08:27.268070Z3[Note](--->MDLPRINT)Namespaceis:TABLE
2017-08-03T21:08:27.268113Z3[Note](----->MDLPRINT)Mdltypeis:MDL_SHARED_READ_ONLY(SRO)
2017-08-03T21:08:27.268145Z3[Note](------>MDLPRINT)Mdldurationis:MDL_TRANSACTION
2017-08-03T21:08:27.268175Z3[Note](------->MDLPRINT)Mdlstatusis:EMPTY

10、MDL_SHARED_NO_READ_WRITE(SNRW)

用于LOCKTABLESWRITE语句

兼容性：

Request|Grantedrequestsforlock|
type|SSHSRSWSWLPSUSROSNWSNRWX|
----------+---------------------------------------------+
SNRW|++--------|

可以看到DML(SW)和SELECT(SR)都被堵塞只有SH还可以，还可以DESC(SH)。

mysql>locktabletestsort12write;
QueryOK,0rowsaffected(0.00sec)
2017-08-03T21:13:07.113347Z3[Note](acquire_lock)THISMDLLOCKacquireok!
2017-08-03T21:13:07.113407Z3[Note](>MDLPRINT)Threadidis3:
2017-08-03T21:13:07.113435Z3[Note](--->MDLPRINT)Namespaceis:GLOBAL
2017-08-03T21:13:07.113458Z3[Note](---->MDLPRINT)Fastpathis:(Y)
2017-08-03T21:13:07.113482Z3[Note](----->MDLPRINT)Mdltypeis:MDL_INTENTION_EXCLUSIVE(IX)
2017-08-03T21:13:07.113505Z3[Note](------>MDLPRINT)Mdldurationis:MDL_STATEMENT
2017-08-03T21:13:07.113604Z3[Note](------->MDLPRINT)Mdlstatusis:EMPTY
2017-08-03T21:13:07.113637Z3[Note](acquire_lock)THISMDLLOCKacquireok!
2017-08-03T21:13:07.113660Z3[Note](>MDLPRINT)Threadidis3:
2017-08-03T21:13:07.113681Z3[Note](->MDLPRINT)DB_nameis:test
2017-08-03T21:13:07.113703Z3[Note](-->MDLPRINT)OBJ_nameis:
2017-08-03T21:13:07.113725Z3[Note](--->MDLPRINT)Namespaceis:SCHEMA
2017-08-03T21:13:07.113746Z3[Note](---->MDLPRINT)Fastpathis:(Y)
2017-08-03T21:13:07.113768Z3[Note](----->MDLPRINT)Mdltypeis:MDL_INTENTION_EXCLUSIVE(IX)
2017-08-03T21:13:07.113791Z3[Note](------>MDLPRINT)Mdldurationis:MDL_TRANSACTION
2017-08-03T21:13:07.113813Z3[Note](------->MDLPRINT)Mdlstatusis:EMPTY
2017-08-03T21:13:07.113842Z3[Note](acquire_lock)THISMDLLOCKacquireok!
2017-08-03T21:13:07.113865Z3[Note](>MDLPRINT)Threadidis3:
2017-08-03T21:13:07.113887Z3[Note](->MDLPRINT)DB_nameis:test
2017-08-03T21:13:07.113922Z3[Note](-->MDLPRINT)OBJ_nameis:testsort12
2017-08-03T21:13:07.113945Z3[Note](--->MDLPRINT)Namespaceis:TABLE
2017-08-03T21:13:07.113975Z3[Note](----->MDLPRINT)Mdltypeis:MDL_SHARED_NO_READ_WRITE(SNRW)
2017-08-03T21:13:07.113998Z3[Note](------>MDLPRINT)Mdldurationis:MDL_TRANSACTION
2017-08-03T21:13:07.114021Z3[Note](------->MDLPRINT)Mdlstatusis:EMPTY

除此之外locktable还需要GLOBAL和SCHEMA上的IX锁，换句话说flushtableswithreadlock;会堵塞
locktabletestsort12write;但是locktabletestsort12read却不会堵塞。

11、MDL_EXCLUSIVE(X)

用于各种DDL操作，注释为CREATE/DROP/RENAMETABLE操作，实际上基本全部的DDL都会涉及到这个锁，如上面分析的
addcolumn操作，但是持续时间一般比较短暂。

兼容性：

Request|Grantedrequestsforlock|
type|SSHSRSWSWLPSUSROSNWSNRWX|
----------+---------------------------------------------+
X|----------|

没有上面意外堵塞一切，也被一切所堵塞

比如刚才的addcolumn操作

2017-08-03T19:46:54.804240Z3[Note](>MDLPRINT)Threadidis3:
2017-08-03T19:46:54.804254Z3[Note](->MDLPRINT)DB_nameis:test
2017-08-03T19:46:54.804267Z3[Note](-->MDLPRINT)OBJ_nameis:testsort12
2017-08-03T19:46:54.804280Z3[Note](--->MDLPRINT)Namespaceis:TABLE
2017-08-03T19:46:54.804293Z3[Note](----->MDLPRINT)Mdltypeis:MDL_EXCLUSIVE(X)
2017-08-03T19:46:54.804306Z3[Note](------>MDLPRINT)Mdldurationis:MDL_TRANSACTION
2017-08-03T19:46:54.804319Z3[Note](------->MDLPRINT)Mdlstatusis:EMPTY

六、源码注释部分

enumenum_mdl_type{
/*
Anintentionexclusivemetadatalock.Usedonlyforscopedlocks.
Ownerofthistypeoflockcanacquireupgradableexclusivelockson
individualobjects.
CompatiblewithotherIXlocks,butisincompatiblewithscopedSand
Xlocks.
*/
MDL_INTENTION_EXCLUSIVE=0,
/*
Asharedmetadatalock.
Tobeusedincaseswhenweareinterestedinobjectmetadataonly
andthereisnointentiontoaccessobjectdata(e.g.forstored
routinesorduringpreparingpreparedstatements).
Wealsomis-usethistypeoflockforopenHANDLERs,sincelock
acquiredbythisstatementhastobecompatiblewithlockacquired
byLOCKTABLES...WRITEstatement,i.e.SNRW(Wecan'tgetbyby
acquiringSlockatHANDLER...OPENtimeandupgradingittoSR
lockforHANDLER...READasitdoesn'tsolveproblemwithneed
toabortDMLstatementswhichwaitontablelevellockwhilehaving
openHANDLERinthesameconnection).
ToavoiddeadlockwhichmayoccurwhenSNRWlockisbeingupgradedto
XlockfortableonwhichthereisanactiveSlockwhichisownedby
threadwhichwaitsinitsturnfortable-levellockownedbythread
performingupgradewehavetousethr_abort_locks_for_thread()
facilityinsuchsituation.
Thisproblemdoesnotariseforlocksonstoredroutinesaswedon't
useSNRWlocksforthem.ItalsodoesnotarisewhenSlocksareused
duringPREPAREcallsastable-levellocksarenotacquiredinthis
case.
*/
MDL_SHARED,
/*
Ahighprioritysharedmetadatalock.
Usedforcaseswhenthereisnointentiontoaccessobjectdata(i.e.
datainthetable).
"Highpriority"meansthat,unlikeothersharedlocks,itisgranted
ignoringpendingrequestsforexclusivelocks.Intendedforusein
caseswhenweonlyneedtoaccessmetadataandnotdata,e.g.when
fillinganINFORMATION_SCHEMAtable.
SinceSHlockiscompatiblewithSNRWlock,theconnectionthat
holdsSHlocklockshouldnottrytoacquireanykindoftable-level
orrow-levellock,asthiscanleadtoadeadlock.Moreover,after
acquiringSHlock,theconnectionshouldnotwaitforanyother
resource,asitmightcausestarvationforXlocksandapotential
deadlockduringupgradeofSNWorSNRWtoXlock(e.g.ifthe
upgradingconnectionholdstheresourcethatisbeingwaitedfor).
*/
MDL_SHARED_HIGH_PRIO,
/*
Asharedmetadatalockforcaseswhenthereisanintentiontoreaddata
fromtable.
Aconnectionholdingthiskindoflockcanreadtablemetadataandread
tabledata(afteracquiringappropriatetableandrow-levellocks).
ThismeansthatonecanonlyacquireTL_READ,TL_READ_NO_INSERT,and
similartable-levellocksontableifoneholdsSRMDLlockonit.
TobeusedfortablesinSELECTs,subqueries,andLOCKTABLE...READ
statements.
*/
MDL_SHARED_READ,
/*
Asharedmetadatalockforcaseswhenthereisanintentiontomodify
(andnotjustread)datainthetable.
AconnectionholdingSWlockcanreadtablemetadataandmodifyorread
tabledata(afteracquiringappropriatetableandrow-levellocks).
TobeusedfortablestobemodifiedbyINSERT,UPDATE,DELETE
statements,butnotLOCKTABLE...WRITEorDDL).Alsotakenby
SELECT...FORUPDATE.
*/
MDL_SHARED_WRITE,
/*
AversionofMDL_SHARED_WRITElockwhichhaslowerprioritythan
MDL_SHARED_READ_ONLYlocks.UsedbyDMLstatementsmodifying
tablesandusingtheLOW_PRIORITYclause.
*/
MDL_SHARED_WRITE_LOW_PRIO,
/*
Anupgradablesharedmetadatalockwhichallowsconcurrentupdatesand
readsoftabledata.
Aconnectionholdingthiskindoflockcanreadtablemetadataandread
tabledata.Itshouldnotmodifydataasthislockiscompatiblewith
SROlocks.
CanbeupgradedtoSNW,SNRWandXlocks.OnceSUlockisupgradedtoX
orSNRWlockdatamodificationcanhappenfreely.
TobeusedforthefirstphaseofALTERTABLE.
*/
MDL_SHARED_UPGRADABLE,
/*
Asharedmetadatalockforcaseswhenweneedtoreaddatafromtable
andblockallconcurrentmodificationstoit(forbothdataandmetadata).
UsedbyLOCKTABLESREADstatement.
*/
MDL_SHARED_READ_ONLY,
/*
Anupgradablesharedmetadatalockwhichblocksallattemptstoupdate
tabledata,allowingreads.
Aconnectionholdingthiskindoflockcanreadtablemetadataandread
tabledata.
CanbeupgradedtoXmetadatalock.
Note,thatsincethistypeoflockisnotcompatiblewithSNRWorSW
locktypes,acquiringappropriateengine-levellocksforreading
(TL_READ*forMyISAM,sharedrowlocksinInnoDB)shouldbe
contention-free.
TobeusedforthefirstphaseofALTERTABLE,whencopyingdatabetween
tables,toallowconcurrentSELECTsfromthetable,butnotUPDATEs.
*/
MDL_SHARED_NO_WRITE,
/*
Anupgradablesharedmetadatalockwhichallowsotherconnections
toaccesstablemetadata,butnotdata.
Itblocksallattemptstoreadorupdatetabledata,whileallowing
INFORMATION_SCHEMAandSHOWqueries.
Aconnectionholdingthiskindoflockcanreadtablemetadatamodifyand
readtabledata.
CanbeupgradedtoXmetadatalock.
TobeusedforLOCKTABLESWRITEstatement.
NotcompatiblewithanyotherlocktypeexceptSandSH.
*/
MDL_SHARED_NO_READ_WRITE,
/*
Anexclusivemetadatalock.
Aconnectionholdingthislockcanmodifybothtable'smetadataanddata.
Noothertypeofmetadatalockcanbegrantedwhilethislockisheld.
TobeusedforCREATE/DROP/RENAMETABLEstatementsandforexecutionof
certainphasesofotherDDLstatements.
*/
MDL_EXCLUSIVE,
/*Thisshouldbethelast!!!*/
MDL_TYPE_END};
/**Durationofmetadatalock.*/
enumenum_mdl_duration{
/**
Lockswithstatementdurationareautomaticallyreleasedattheend
ofstatementortransaction.
*/
MDL_STATEMENT=0,
/**
Lockswithtransactiondurationareautomaticallyreleasedattheend
oftransaction.
*/
MDL_TRANSACTION,
/**
Lockswithexplicitdurationsurvivetheendofstatementandtransaction.
TheyhavetobereleasedexplicitlybycallingMDL_context::release_lock().
*/
MDL_EXPLICIT,
/*Thisshouldbethelast!*/
MDL_DURATION_END};
/**
Objectnamespaces.
Sic:whenaddinganewmembertothisenummakesureto
updatem_namespace_to_wait_state_namearrayinmdl.
Differenttypesofobjectsexistindifferentnamespaces
-GLOBALisusedfortheglobalreadlock.
-TABLESPACEisfortablespaces.
-SCHEMAisforschemas(akadatabases).
-TABLEisfortablesandviews.
-FUNCTIONisforstoredfunctions.
-PROCEDUREisforstoredprocedures.
-TRIGGERisfortriggers.
-EVENTisforeventschedulerevents.
-COMMITisforenablingtheglobalreadlocktoblockcommits.
-USER_LEVEL_LOCKisforuser-levellocks.
-LOCKING_SERVICEisforthenamepluginRW-lockservice
Notethatalthoughthereisn'tmetadatalockingontriggers,
it'snecessarytohaveaseparatenamespaceforthemsince
MDL_keyisalsousedoutsideoftheMDLsubsystem.
Alsonotethatrequestswaitingforuser-levellocksgetspecial
treatment-waitingisabortedifconnectiontoclientislost.
*/
enumenum_mdl_namespace{GLOBAL=0,
TABLESPACE,
SCHEMA,
TABLE,
FUNCTION,
PROCEDURE,
TRIGGER,
EVENT,
COMMIT,
USER_LEVEL_LOCK,
LOCKING_SERVICE,
BACKUP,
BINLOG,
/*Thisshouldbethelast!*/
NAMESPACE_END};

MYSQL METADATA LOCK(MDL LOCK)MDL锁问题分析

热门推荐

随机推荐