小编给大家分享一下MYSQL METADATA LOCK(MDL LOCK)MDL锁问题的示例分析，相信大部分人都还不怎么了解，因此分享这篇文章给大家参考一下，希望大家阅读完这篇文章后大有收获，下面让我们一起去了解一下吧！

一、前言

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

简单的所谓的Waiting for table metadata lock之类的状态，其实MDL LOCK是MYSQL上层一个非常复杂的子系统，有自己的死锁检测机制

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

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

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

----最早获取阶段: THD::enter_stage: 'Opening tables'

调用栈帧

#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

MDL LOCK的死锁抛错和INNODB死锁一模一样不同的只是SHOW ENGINE INNODB 没有死锁信息。

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

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

1、MDL TYPE

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、MDL NAMESPACE

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

下面是NAMESPACE的分类

- GLOBAL is used for the global read lock.

- TABLESPACE is for tablespaces.

- SCHEMA is for schemas (aka databases).

- TABLE is for tables and views.

- FUNCTION is for stored functions.

- PROCEDURE is for stored procedures.

- TRIGGER is for triggers.

- EVENT is for event scheduler events.

- COMMIT is for enabling the global read lock to block commits.

- USER_LEVEL_LOCK is for user-level locks.

- LOCKING_SERVICE is for the name plugin RW-lock service

3、实现分类

scope lock:一般对应全局MDL LOCK 如flush table with read lock 为namespace space:GLOBAL type:S

object lock:如其名字，对象级别的MDL LOCK，比如TABLE

下面是源码中的注释：

/**HelperstructwhichdefineshowdifferenttypesoflocksarehandledforaspecificMDL_lock.Inpracticeweuseonlytwostrategies:"scoped"lockstrategyforlocksinGLOBAL,COMMIT,TABLESPACEandSCHEMAnamespacesand"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、MDL duration及MDL持续到什么时候

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

MDL_STATEMENT:Lockswithstatementdurationareautomaticallyreleasedattheendofstatementortransaction.MDL_TRANSACTION:LockswithtransactiondurationareautomaticallyreleasedattheendoftransactionMDL_EXPLICIT:Lockswithexplicitdurationsurvivetheendofstatementandtransaction.TheyhavetobereleasedexplicitlybycallingMDL_context::release_lock().

6、MDL LOCK FAST PATH（unobtrusive） OR SLOW PATH（obtrusive）

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

A)"unobtrusive"locktypes1)Eachtypefromthissetshouldbecompatiblewithallothertypesfromtheset(includingitself).2)ThesetypesshouldbecommonforDMLoperationsOurgoalistooptimizeacquisitionandreleaseoflocksofthistypebyavoidingcomplexchecksandmanipulationsonm_waiting/m_grantedbitmaps/lists.Wereplacethemwithacheckofandincrement/decrementofintegercounters.Wecallthelattertypeofacquisition/release"fastpath".Useof"fastpath"reducesthesizeofcriticalsectionassociatedwithMDL_lock::m_rwlocklockinthecommoncaseandthusincreasesscalability.Theamountbywhichacquisition/releaseofspecifictype"unobtrusive"lockincreases/decreasespackedcounterinMDL_lock::m_fast_path_stateisreturnedbythisfunction.B)"obtrusive"locktypes1)Grantedorpendinglockofthosetypeisincompatiblewithsomeothertypesoflocksorwithitself.2)NotcommonforDMLoperationsTheselockshavetobealwaysacquiredinvolvingmanipulationsonm_waiting/m_grantedbitmaps/lists,i.e.wehavetouse"slowpath"forthem.Moreoverinthepresenceofactive/pendinglocksfrom"obtrusive"setwehavetoacquireusing"slowpath"evenlocksof"unobtrusive"type.

7、MDL_request类

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

/**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:

uint16 m_length;

uint16 m_db_name_length;

char m_ptr[MAX_MDLKEY_LENGTH];//放到了这里

8、MDL_ticket

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

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

/**Pointersforparticipatinginthelistoflockrequestsforthiscontext.Contextprivate.正如解释这里是context中链表链表的形成，是线程私有的*/MDL_ticket*next_in_context;MDL_ticket**prev_in_context;/**Pointersforparticipatinginthelistofsatisfied/pendingrequestsforthelock.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.ParticularlythismeansthatitwasnotincludedintoMDL_lock::m_grantedbitmap/listandinsteadisaccountedforbyMDL_lock::m_fast_path_locks_granted_counter这里就代表了是否是FASTPATH从注释来看fastpath方式不会在MDLLOCK中占用granted位图和链表取而代之代之的是一个统计器m_fast_path_locks_granted_counter这样一来开销肯定更小*/boolm_is_fast_path;/**Indicatesthatticketcorrespondstolockrequestwhichrequiredstorageenginenotificationduringitsacquisitionandrequiresstorageenginenotificationafteritsrelease.*/boolm_hton_notified;

9、MDL_lock

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

/** The key of the object (data) being protected. */

MDL_key key;

/** List of granted tickets for this lock. */

Ticket_list m_granted;

/** Tickets for contexts waiting to acquire a lock. */

Ticket_list m_waiting;

10、MDL_context

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

/**Ifourrequestforalockisscheduled,orabortedbythedeadlockdetector,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_status m_wait_status;

包含

EMPTY 初始化

GRANTED 获取成功

VICTIM 死锁

TIMEOUT 超时

KILLED KILLED

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}};

三、增加MDL LOCK打印函数

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

UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME ='global_instrumentation';

UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME ='wait/lock/metadata/sql/mdl';

select * from performance_schema.metadata_locks

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

/*p_ticket in parameter*/

int my_print_ticket(const MDL_ticket* p_ticket)

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

friend int my_print_ticket(const MDL_ticket* p_ticket);

主要获取MDL LOCK的如下信息打印到mysql err日志中:

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

mdl lock database name 通过p_ticket->m_lock->key.db_name()获取

mdl lock object name 通过p_ticket->m_lock->key.name()获取

mdl lock namespace 通过p_ticket->m_lock->key.mdl_namespace()获取

mdl lock fast path 通过p_ticket->m_is_fast_path获取判断是则输出否则不输出

mdl lock type 通过p_ticket->m_type获取

mdl lock duration 通过p_ticket->m_duration获取

输出信息如下：

2017-08-03T07:34:21.720583Z 3 [Note] (>MDL PRINT) Thread id is 3:

2017-08-03T07:34:21.720601Z 3 [Note] (->MDL PRINT) DB_name is:test

2017-08-03T07:34:21.720619Z 3 [Note] (-->MDL PRINT) OBJ_name is:test

2017-08-03T07:34:21.720637Z 3 [Note] (--->MDL PRINT) Namespace is:TABLE

2017-08-03T07:34:21.720655Z 3 [Note] (---->MDL PRINT) Fast path is:(Y)

2017-08-03T07:34:21.720673Z 3 [Note] (----->MDL PRINT) Mdl type is:MDL_SHARED_WRITE(SW)

2017-08-03T07:34:21.720692Z 3 [Note] (------>MDL PRINT) Mdl duration is:MDL_TRANSACTION

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

mysql>select*fromperformance_schema.metadata_locks\G***************************1.row***************************OBJECT_TYPE:TABLEOBJECT_SCHEMA:testOBJECT_NAME:testOBJECT_INSTANCE_BEGIN:140734412907760LOCK_TYPE:SHARED_WRITELOCK_DURATION:TRANSACTIONLOCK_STATUS:GRANTEDSOURCE:sql_parse.cc:6314OWNER_THREAD_ID:39OWNER_EVENT_ID:241

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

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

1、加锁：MDL_context::acquire_lock

boolMDL_context::acquire_lock(MDL_request*mdl_request,ulonglock_wait_timeout){if(mdl_request->ticket)//获取成功获得ticket{/*Wehavemanagedtoacquirelockwithoutwaiting.MDL_lock,MDL_contextandMDL_requestwereupdatedaccordingly,sowecansimplyreturnsuccess.*///REQUESET获取TICKET成功此处打印returnFALSE;}/*Ourattempttoacquirelockwithoutwaitinghasfailed.AsaresultofthisattemptwegotMDL_ticketwithm_lockmemberpointingtothecorrespondingMDL_lockobjectwhichhasMDL_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(无向图)//当然到这里也是通过等待后获得成功了状态为GRANTEDDBUG_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获得了TICKETreturnFALSE;}

2、降级：void MDL_ticket::downgrade_lock(enum_mdl_type new_type)

voidMDL_ticket::downgrade_lock(enum_mdl_typenew_type){/*OnlyallowdowngradefromEXCLUSIVEandSHARED_NO_WRITE.*/DBUG_ASSERT(m_type==MDL_EXCLUSIVE||m_type==MDL_SHARED_NO_WRITE);//此处打印出降级前的TICKETif(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_type new_type, ulong lock_wait_timeout)

boolMDL_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);}

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

五、各种MDL LOCK TYPE加锁测试

1、MDL_INTENTION_EXCLUSIVE(IX)

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

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:GLOBAL2017-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_STATEMENT2017-08-03T18:22:38.092551Z3[Note](------->MDLPRINT)Mdlstatusis:EMPTY

下面是 ALETER 语句触发的GLOABL IX MDL LOCK以及SCHEMA级别的MDL LOCK

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:GLOBAL2017-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_STATEMENT2017-08-03T18:46:05.895076Z3[Note](------->MDLPRINT)Mdlstatusis:EMPTY2017-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:test2017-08-03T18:46:05.895243Z3[Note](-->MDLPRINT)OBJ_nameis:2017-08-03T18:46:05.895276Z3[Note](--->MDLPRINT)Namespaceis:SCHEMA2017-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_TRANSACTION2017-08-03T18:46:05.895421Z3[Note](------->MDLPRINT)Mdlstatusis:EMPTY

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

2、MDL_SHARED(S)

这把锁一般用在flush tables with read lock中

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:GLOBAL2017-08-03T18:19:11.603994Z3[Note](----->MDLPRINT)Mdltypeis:MDL_SHARED(S)2017-08-03T18:19:11.604045Z3[Note](------>MDLPRINT)Mdldurationis:MDL_EXPLICIT2017-08-03T18:19:11.604073Z3[Note](------->MDLPRINT)Mdlstatusis:EMPTY2017-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:COMMIT2017-08-03T18:19:11.604217Z3[Note](----->MDLPRINT)Mdltypeis:MDL_SHARED(S)2017-08-03T18:19:11.604240Z3[Note](------>MDLPRINT)Mdldurationis:MDL_EXPLICIT2017-08-03T18:19:11.604310Z3[Note](------->MDLPRINT)Mdlstatusis:EMPTY

我们注意到其namspace为GLOBAL和COMMIT显然他们是SCOPED LOCK，他们的TYPE为S，那么很显然根据兼容性原则
SCOPED 的MDL IX和MDL S 不兼容， flush tables with read lock; 就会堵塞所有DELTE/UPDATE/INSERT/FOR UPDATE
等DML和DDL操作(因为这些操作都需要GLOBAL MDL IX锁)

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:test2017-08-03T19:06:05.843392Z4[Note](-->MDLPRINT)OBJ_nameis:testsort102017-08-03T19:06:05.843425Z4[Note](--->MDLPRINT)Namespaceis:TABLE2017-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_TRANSACTION2017-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:test2017-08-03T19:13:52.338883Z4[Note](-->MDLPRINT)OBJ_nameis:testsort102017-08-03T19:13:52.338917Z4[Note](--->MDLPRINT)Namespaceis:TABLE2017-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_TRANSACTION2017-08-03T19:13:52.339097Z4[Note](------->MDLPRINT)Mdlstatusis:EMPTY

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

5、MDL_SHARED_WRITE(SW)

这把锁一般用于DELTE/UPDATE/INSERT/FOR UPDATE等操作对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:test2017-08-03T19:25:41.218525Z4[Note](-->MDLPRINT)OBJ_nameis:testsort102017-08-03T19:25:41.218557Z4[Note](--->MDLPRINT)Namespaceis:TABLE2017-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_TRANSACTION2017-08-03T19:25:41.218874Z4[Note](------->MDLPRINT)Mdlstatusis:EMPTY

6、MDL_SHARED_WRITE_LOW_PRIO(SWL)

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

Used by DML statements modifying

tables and using the LOW_PRIORITY clause

会用到

兼容性：

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:test2017-08-03T19:32:47.433547Z4[Note](-->MDLPRINT)OBJ_nameis:testsort102017-08-03T19:32:47.433560Z4[Note](--->MDLPRINT)Namespaceis:TABLE2017-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_TRANSACTION2017-08-03T19:32:47.433620Z4[Note](------->MDLPRINT)Mdlstatusis:EMPTY

7、MDL_SHARED_UPGRADABLE(SU)

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

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

兼容性：

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

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

(For DDL operations with LOCK=DEFAULT, or with the LOCK clause omitted, MySQL uses the lowest level
of locking that is available for that kind of operation, allowing concurrent queries, DML, or both wherever
possible. This is the setting to use when making pre-planned, pre-tested changes that you know will not
cause any availability problems based on the workload for that table
When an operation on the primary key uses ALGORITHM=INPLACE, even though the data is still copied, it
is more efficient than using ALGORITHM=COPY because:
? No undo logging or associated redo logging is required for ALGORITHM=INPLACE. These operations add
overhead to DDL statements that use ALGORITHM=COPY.
? The secondary index entries are pre-sorted, and so can be loaded in order.
? The change buffer is not used, because there are no random-access inserts into the secondary indexes.
)

如下面的语句

mysql> alter table testsort12 add column it int not null;
Query OK, 0 rows affected (6.27 sec)
Records: 0 Duplicates: 0 Warnings: 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:test2017-08-03T19:46:54.781990Z3[Note](-->MDLPRINT)OBJ_nameis:testsort122017-08-03T19:46:54.782026Z3[Note](--->MDLPRINT)Namespaceis:TABLE2017-08-03T19:46:54.782060Z3[Note](----->MDLPRINT)Mdltypeis:MDL_SHARED_UPGRADABLE(SU)2017-08-03T19:46:54.782096Z3[Note](------>MDLPRINT)Mdldurationis:MDL_TRANSACTION2017-08-03T19:46:54.782175Z3[Note](------->MDLPRINT)Mdlstatusis:EMPTY2017-08-03T19:46:54.803898Z3[Note](upgrade_shared_lock)THISMDLLOCKwillupgrade2017-08-03T19:46:54.804201Z3[Note](upgrade_shared_lock)THISMDLLOCKupgradeTO2017-08-03T19:46:54.804240Z3[Note](>MDLPRINT)Threadidis3:2017-08-03T19:46:54.804254Z3[Note](->MDLPRINT)DB_nameis:test2017-08-03T19:46:54.804267Z3[Note](-->MDLPRINT)OBJ_nameis:testsort122017-08-03T19:46:54.804280Z3[Note](--->MDLPRINT)Namespaceis:TABLE2017-08-03T19:46:54.804293Z3[Note](----->MDLPRINT)Mdltype:MDL_EXCLUSIVE(X)2017-08-03T19:46:54.804306Z3[Note](------>MDLPRINT)Mdldurationis:MDL_TRANSACTION2017-08-03T19:46:54.804319Z3[Note](------->MDLPRINT)Mdlstatusis:EMPTY2017-08-03T19:46:54.855563Z3[Note](downgrade_lock)THISMDLLOCKwilldowngrade2017-08-03T19:46:54.855693Z3[Note](downgrade_lock)tothisMDLlock2017-08-03T19:46:54.855706Z3[Note](>MDLPRINT)Threadidis3:2017-08-03T19:46:54.855717Z3[Note](->MDLPRINT)DB_nameis:test2017-08-03T19:46:54.856053Z3[Note](-->MDLPRINT)OBJ_nameis:testsort122017-08-03T19:46:54.856069Z3[Note](--->MDLPRINT)Namespaceis:TABLE2017-08-03T19:46:54.856082Z3[Note](----->MDLPRINT)Mdltypeis:MDL_SHARED_UPGRADABLE(SU)2017-08-03T19:46:54.856094Z3[Note](------>MDLPRINT)Mdldurationis:MDL_TRANSACTION2017-08-03T19:46:54.856214Z3[Note](------->MDLPRINT)Mdlstatusis:EMPTY2017-08-03T19:47:00.260166Z3[Note](upgrade_shared_lock)THISMDLLOCKwillupgrade2017-08-03T19:47:00.304057Z3[Note](upgrade_shared_lock)THISMDLLOCKupgradeTO2017-08-03T19:47:00.304090Z3[Note](>MDLPRINT)Threadidis3:2017-08-03T19:47:00.304105Z3[Note](->MDLPRINT)DB_nameis:test2017-08-03T19:47:00.304119Z3[Note](-->MDLPRINT)OBJ_nameis:testsort122017-08-03T19:47:00.304132Z3[Note](--->MDLPRINT)Namespaceis:TABLE2017-08-03T19:47:00.304181Z3[Note](----->MDLPRINT)Mdltypeis:MDL_EXCLUSIVE(X)2017-08-03T19:47:00.304196Z3[Note](------>MDLPRINT)Mdldurationis:MDL_TRANSACTION2017-08-03T19:47:00.304211Z3[Note](------->MDLPRINT)Mdlstatusis:EMPTY2017-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操作，但是这个过程实际在MDL SU模式下所以不会堵塞DML/SELECT操作。
这里再给大家提个醒所谓的ONLINE DDL只是在COPY阶段不堵塞DML/SELECT操作，还是尽量在数据库压力小的时候，
比如如果有DML没有提交或者SELECT没有做完这个时候SW SR必然堵塞X，而X能够堵塞一切且为高优先级。这样导致
的现象就是由于DML未提交堵塞DDL操作而DDL操作堵塞一切操作，基本对于这个TABLE的表全部堵塞。而对于ALGORITHM=COPY 其他部分差不多，但是在COPY阶段用的是SNW锁，接下来我就先来看看SNW锁

8、MDL_SHARED_NO_WRITE(SNW)

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

Request|Grantedrequestsforlocktype|SSHSRSWSWLPSUSROSNWSNRWX|----------+---------------------------------------------+SNW|+++---+---|

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

mysql>altertabletestsort12addcolumnikintnotnull,ALGORITHM=COPY;2017-08-03T20:07:58.413215Z3[Note](upgrade_shared_lock)THISMDLLOCKupgradeTO2017-08-03T20:07:58.413241Z3[Note](>MDLPRINT)Threadidis3:2017-08-03T20:07:58.413257Z3[Note](->MDLPRINT)DB_nameis:test2017-08-03T20:07:58.413273Z3[Note](-->MDLPRINT)OBJ_nameis:testsort122017-08-03T20:07:58.413292Z3[Note](--->MDLPRINT)Namespaceis:TABLE2017-08-03T20:07:58.413308Z3[Note](----->MDLPRINT)Mdltypeis:MDL_SHARED_NO_WRITE(SNW)2017-08-03T20:07:58.413325Z3[Note](------>MDLPRINT)Mdldurationis:MDL_TRANSACTION2017-08-03T20:07:58.413341Z3[Note](------->MDLPRINT)Mdlstatusis:EMPTY2017-08-03T20:08:25.392006Z3[Note](upgrade_shared_lock)THISMDLLOCKupgradeTO2017-08-03T20:08:25.392024Z3[Note](>MDLPRINT)Threadidis3:2017-08-03T20:08:25.392086Z3[Note](->MDLPRINT)DB_nameis:test2017-08-03T20:08:25.392159Z3[Note](-->MDLPRINT)OBJ_nameis:testsort122017-08-03T20:08:25.392199Z3[Note](--->MDLPRINT)Namespaceis:TABLE2017-08-03T20:08:25.392214Z3[Note](----->MDLPRINT)Mdltypeis:MDL_EXCLUSIVE(X)2017-08-03T20:08:25.392228Z3[Note](------>MDLPRINT)Mdldurationis:MDL_TRANSACTION2017-08-03T20:08:25.392242Z3[Note](------->MDLPRINT)Mdlstatusis:EMPTY2017-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)

用于LOCK TABLES READ 语句

兼容性如下

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:test2017-08-03T21:08:27.268040Z3[Note](-->MDLPRINT)OBJ_nameis:testsort122017-08-03T21:08:27.268070Z3[Note](--->MDLPRINT)Namespaceis:TABLE2017-08-03T21:08:27.268113Z3[Note](----->MDLPRINT)Mdltypeis:MDL_SHARED_READ_ONLY(SRO)2017-08-03T21:08:27.268145Z3[Note](------>MDLPRINT)Mdldurationis:MDL_TRANSACTION2017-08-03T21:08:27.268175Z3[Note](------->MDLPRINT)Mdlstatusis:EMPTY

10、MDL_SHARED_NO_READ_WRITE(SNRW)

用于LOCK TABLES WRITE语句

兼容性：

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:GLOBAL2017-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_STATEMENT2017-08-03T21:13:07.113604Z3[Note](------->MDLPRINT)Mdlstatusis:EMPTY2017-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:test2017-08-03T21:13:07.113703Z3[Note](-->MDLPRINT)OBJ_nameis:2017-08-03T21:13:07.113725Z3[Note](--->MDLPRINT)Namespaceis:SCHEMA2017-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_TRANSACTION2017-08-03T21:13:07.113813Z3[Note](------->MDLPRINT)Mdlstatusis:EMPTY2017-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:test2017-08-03T21:13:07.113922Z3[Note](-->MDLPRINT)OBJ_nameis:testsort122017-08-03T21:13:07.113945Z3[Note](--->MDLPRINT)Namespaceis:TABLE2017-08-03T21:13:07.113975Z3[Note](----->MDLPRINT)Mdltypeis:MDL_SHARED_NO_READ_WRITE(SNRW)2017-08-03T21:13:07.113998Z3[Note](------>MDLPRINT)Mdldurationis:MDL_TRANSACTION2017-08-03T21:13:07.114021Z3[Note](------->MDLPRINT)Mdlstatusis:EMPTY

除此之外lock table 还需要GLOBAL和SCHEMA上的IX锁，换句话说flush tables with read lock; 会堵塞
lock table testsort12 write;但是lock table testsort12 read 却不会堵塞。

11、MDL_EXCLUSIVE(X)

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

兼容性：

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

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

比如刚才的add column操作

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

六、源码注释部分

enumenum_mdl_type{/*Anintentionexclusivemetadatalock.Usedonlyforscopedlocks.Ownerofthistypeoflockcanacquireupgradableexclusivelocksonindividualobjects.CompatiblewithotherIXlocks,butisincompatiblewithscopedSandXlocks.*/MDL_INTENTION_EXCLUSIVE=0,/*Asharedmetadatalock.Tobeusedincaseswhenweareinterestedinobjectmetadataonlyandthereisnointentiontoaccessobjectdata(e.g.forstoredroutinesorduringpreparingpreparedstatements).Wealsomis-usethistypeoflockforopenHANDLERs,sincelockacquiredbythisstatementhastobecompatiblewithlockacquiredbyLOCKTABLES...WRITEstatement,i.e.SNRW(Wecan'tgetbybyacquiringSlockatHANDLER...OPENtimeandupgradingittoSRlockforHANDLER...READasitdoesn'tsolveproblemwithneedtoabortDMLstatementswhichwaitontablelevellockwhilehavingopenHANDLERinthesameconnection).ToavoiddeadlockwhichmayoccurwhenSNRWlockisbeingupgradedtoXlockfortableonwhichthereisanactiveSlockwhichisownedbythreadwhichwaitsinitsturnfortable-levellockownedbythreadperformingupgradewehavetousethr_abort_locks_for_thread()facilityinsuchsituation.Thisproblemdoesnotariseforlocksonstoredroutinesaswedon'tuseSNRWlocksforthem.ItalsodoesnotarisewhenSlocksareusedduringPREPAREcallsastable-levellocksarenotacquiredinthiscase.*/MDL_SHARED,/*Ahighprioritysharedmetadatalock.Usedforcaseswhenthereisnointentiontoaccessobjectdata(i.e.datainthetable)."Highpriority"meansthat,unlikeothersharedlocks,itisgrantedignoringpendingrequestsforexclusivelocks.Intendedforuseincaseswhenweonlyneedtoaccessmetadataandnotdata,e.g.whenfillinganINFORMATION_SCHEMAtable.SinceSHlockiscompatiblewithSNRWlock,theconnectionthatholdsSHlocklockshouldnottrytoacquireanykindoftable-levelorrow-levellock,asthiscanleadtoadeadlock.Moreover,afteracquiringSHlock,theconnectionshouldnotwaitforanyotherresource,asitmightcausestarvationforXlocksandapotentialdeadlockduringupgradeofSNWorSNRWtoXlock(e.g.iftheupgradingconnectionholdstheresourcethatisbeingwaitedfor).*/MDL_SHARED_HIGH_PRIO,/*Asharedmetadatalockforcaseswhenthereisanintentiontoreaddatafromtable.Aconnectionholdingthiskindoflockcanreadtablemetadataandreadtabledata(afteracquiringappropriatetableandrow-levellocks).ThismeansthatonecanonlyacquireTL_READ,TL_READ_NO_INSERT,andsimilartable-levellocksontableifoneholdsSRMDLlockonit.TobeusedfortablesinSELECTs,subqueries,andLOCKTABLE...READstatements.*/MDL_SHARED_READ,/*Asharedmetadatalockforcaseswhenthereisanintentiontomodify(andnotjustread)datainthetable.AconnectionholdingSWlockcanreadtablemetadataandmodifyorreadtabledata(afteracquiringappropriatetableandrow-levellocks).TobeusedfortablestobemodifiedbyINSERT,UPDATE,DELETEstatements,butnotLOCKTABLE...WRITEorDDL).AlsotakenbySELECT...FORUPDATE.*/MDL_SHARED_WRITE,/*AversionofMDL_SHARED_WRITElockwhichhaslowerprioritythanMDL_SHARED_READ_ONLYlocks.UsedbyDMLstatementsmodifyingtablesandusingtheLOW_PRIORITYclause.*/MDL_SHARED_WRITE_LOW_PRIO,/*Anupgradablesharedmetadatalockwhichallowsconcurrentupdatesandreadsoftabledata.Aconnectionholdingthiskindoflockcanreadtablemetadataandreadtabledata.ItshouldnotmodifydataasthislockiscompatiblewithSROlocks.CanbeupgradedtoSNW,SNRWandXlocks.OnceSUlockisupgradedtoXorSNRWlockdatamodificationcanhappenfreely.TobeusedforthefirstphaseofALTERTABLE.*/MDL_SHARED_UPGRADABLE,/*Asharedmetadatalockforcaseswhenweneedtoreaddatafromtableandblockallconcurrentmodificationstoit(forbothdataandmetadata).UsedbyLOCKTABLESREADstatement.*/MDL_SHARED_READ_ONLY,/*Anupgradablesharedmetadatalockwhichblocksallattemptstoupdatetabledata,allowingreads.Aconnectionholdingthiskindoflockcanreadtablemetadataandreadtabledata.CanbeupgradedtoXmetadatalock.Note,thatsincethistypeoflockisnotcompatiblewithSNRWorSWlocktypes,acquiringappropriateengine-levellocksforreading(TL_READ*forMyISAM,sharedrowlocksinInnoDB)shouldbecontention-free.TobeusedforthefirstphaseofALTERTABLE,whencopyingdatabetweentables,toallowconcurrentSELECTsfromthetable,butnotUPDATEs.*/MDL_SHARED_NO_WRITE,/*Anupgradablesharedmetadatalockwhichallowsotherconnectionstoaccesstablemetadata,butnotdata.Itblocksallattemptstoreadorupdatetabledata,whileallowingINFORMATION_SCHEMAandSHOWqueries.Aconnectionholdingthiskindoflockcanreadtablemetadatamodifyandreadtabledata.CanbeupgradedtoXmetadatalock.TobeusedforLOCKTABLESWRITEstatement.NotcompatiblewithanyotherlocktypeexceptSandSH.*/MDL_SHARED_NO_READ_WRITE,/*Anexclusivemetadatalock.Aconnectionholdingthislockcanmodifybothtable'smetadataanddata.Noothertypeofmetadatalockcanbegrantedwhilethislockisheld.TobeusedforCREATE/DROP/RENAMETABLEstatementsandforexecutionofcertainphasesofotherDDLstatements.*/MDL_EXCLUSIVE,/*Thisshouldbethelast!!!*/MDL_TYPE_END};/**Durationofmetadatalock.*/enumenum_mdl_duration{/**Lockswithstatementdurationareautomaticallyreleasedattheendofstatementortransaction.*/MDL_STATEMENT=0,/**Lockswithtransactiondurationareautomaticallyreleasedattheendoftransaction.*/MDL_TRANSACTION,/**Lockswithexplicitdurationsurvivetheendofstatementandtransaction.TheyhavetobereleasedexplicitlybycallingMDL_context::release_lock().*/MDL_EXPLICIT,/*Thisshouldbethelast!*/MDL_DURATION_END};/**Objectnamespaces.Sic:whenaddinganewmembertothisenummakesuretoupdatem_namespace_to_wait_state_namearrayinmdl.Differenttypesofobjectsexistindifferentnamespaces-GLOBALisusedfortheglobalreadlock.-TABLESPACEisfortablespaces.-SCHEMAisforschemas(akadatabases).-TABLEisfortablesandviews.-FUNCTIONisforstoredfunctions.-PROCEDUREisforstoredprocedures.-TRIGGERisfortriggers.-EVENTisforeventschedulerevents.-COMMITisforenablingtheglobalreadlocktoblockcommits.-USER_LEVEL_LOCKisforuser-levellocks.-LOCKING_SERVICEisforthenamepluginRW-lockserviceNotethatalthoughthereisn'tmetadatalockingontriggers,it'snecessarytohaveaseparatenamespaceforthemsinceMDL_keyisalsousedoutsideoftheMDLsubsystem.Alsonotethatrequestswaitingforuser-levellocksgetspecialtreatment-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锁问题的示例分析”这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注亿速云行业资讯频道！