MySQL--MDL,元数据锁的粒度

    MySQLServer层定义了如下的元数据锁的粒度，这些锁较多，超出了读锁和写锁两种锁粒度，这是因为读锁和写锁通常是理论上根据概念探讨的范畴，而在工程实践中会根据实际情况进行细化，以最大限度地提高并发度。

/**

    Type ofmetadata lock request.

    @saComments for MDL_object_lock::can_grant_lock()and MDL_scoped_lock::can_grant_lock()for details.

*/

enum enum_mdl_type{  //元数据锁的粒度定义，但属于MySQL Server层的元数据锁。如下的注释写得很好，仔细阅读理解为好

    /*  An intention exclusive metadata lock. Usedonly for scoped locks. //scoped locks,范围类型的锁，参见“9 相容性”小节

        Ownerof this type of lock can acquire upgradableexclusive locks on individual objects.//可升级的排它锁，从意向升级到非意向的排它

       Compatible with other IX locks, but is incompatible with scoped S and Xlocks. */

    MDL_INTENTION_EXCLUSIVE= 0, //意向排它锁，缩写为IX。与其他的IX是兼容的，但是与范围类型的S和X锁不兼容

                                //此锁用于GLOBAL/COMMIT/TABLESPACE/SCHEMA/EVENT/PROCEDURE/TABLE这些元数据对象上

 

    /*  A shared metadata lock.  //共享锁，元数据共享锁，即表级的共享锁

        To beused in cases when we are interested in objectmetadata only and there is no intention to //读操作“元数据”获取S锁

        accessobject data (e.g. for stored routines or during preparing prepared statements).//例如存储过程等      

        Wealso mis-use this type of lock for open HANDLERs, since lock acquired by thisstatement has to be compatible with

        lockacquired by LOCK TABLES ... WRITEstatement, i.e. //“HANDLER tbl_name OPEN/READ...”操作不能获得S锁

        SNRW (We can't get by acquiring S lock at HANDLER... OPEN time and upgrading it to SR lock for HANDLER ... READ

        as itdoesn't solve problem with need to abort DML statements which wait on tablelevel lock while having

        openHANDLER in the same connection).

        To avoid deadlock which may occur whenSNRW lock is being upgraded to X lock for table //升级到X锁避免死锁

        onwhich there is an active S lock which is owned by thread which waits in itsturn for table-level lock owned by thread

       performing upgrade we have to use thr_abort_locks_for_thread()facility in such situation.       

        Thisproblem does not arise for locks on stored routines as we don't use SNRW locksfor them.

        Italso does not arise when S locks are used during PREPARE calls as table-levellocks are not acquired in this case. */

    MDL_SHARED, //共享锁，缩写为S。需要对元数据进行读操作

 

    /*  A high priority shared metadata lock.    //高优先级的共享锁，高优先级的元数据共享锁，即高优先级的S锁

        Usedfor cases when there is no intention to access object data (i.e. data in thetable). //用于不需要意向锁的情况下

       "High priority"means that, unlike other sharedlocks, it is granted ignoring pending requests for exclusive locks.

       Intended for use in cases when we only need to access metadata and notdata, e.g. when filling an INFORMATION_SCHEMAtable.

        //如上是在说，高优先级是在在表级操作但不会影响数据（如修改列的数据类型则会影响数据，修改表名则不会影响数据）       

        SinceSH lock is compatible with SNRW lock, the connection that holds SH lock lockshould not try to

       acquire any kind of table-level or row-level lock, as this can lead to adeadlock.  //持有SH锁则不应该在请求表级或行级锁

       Moreover, after acquiring SH lock, the connection should not wait forany other resource,

        as itmight cause starvation for X locks and a potential deadlock during upgrade ofSNW or SNRW to X lock

        (e.g.if the upgrading connection holds the resource that is being waited for). */

    MDL_SHARED_HIGH_PRIO, //高优先级的共享锁，缩写为SH。用于操作INFORMATION_SCHEMA中的表，如执行SHOW CREATE TABLE t1或DESC t1等

 

    /*  A shared metadata lock for cases when thereis an intention to read data from table. //存在读数据意向的锁

        Aconnection holding this kind of lock can readtable metadata and read table data   //先在元数据上加此锁

       (after acquiring appropriate table and row-level locks). //之后才会获取表锁和行级锁

        Thismeans that one can only acquire TL_READ, TL_READ_NO_INSERT, and similartable-level locks on table

        ifone holds SR MDL lock on it.

        To beused for tables in SELECTs, subqueries,and LOCK TABLE ... READ statements. */

    MDL_SHARED_READ, //意向读锁，缩写为SR。也许会读表的元数据（表级元数据锁）和表的数据（行级锁）

        //另外还有通过mysql_admin_table()函数访问表的ANALYZE/CHECK TABLE/OPTIMIZE TABLE/REPAIR TABLE等操作可获取此锁

 

    /*  A shared metadata lock for cases when thereis an intention to modify(and not just read) data in the table.

        Aconnection holding SW lock can read table metadata and modify or read tabledata  //存在修改数据意向的锁

       (after acquiring appropriate table and row-level locks). //之后才会获取表锁和行级锁

        To beused for tables to be modified by INSERT,UPDATE, DELETE statements, but notLOCK TABLE ... WRITE or DDL).

        Alsotaken by SELECT ... FOR UPDATE.  */

    MDL_SHARED_WRITE, //意向写锁，缩写为SW。INSERT, UPDATE, DELETE statements

 

    /*  A version of MDL_SHARED_WRITE lock which haslower priority than MDL_SHARED_READ_ONLY locks.

        Usedby DML statements modifying tables and usingthe LOW_PRIORITY clause. */  //SQL语句中带有“LOW_PRIORITY”子句

    MDL_SHARED_WRITE_LOW_PRIO,  //低优先级的共享写锁，缩写为SWLP。

 

    /*  An upgradable shared metadata lock which allows concurrent updates and reads of tabledata. //允许并发更新和读同一个表

        Aconnection holding this kind of lock can read table metadata and read tabledata.

        Itshould not modify data as this lock is compatible with SRO locks.       

        Canbe upgraded to SNW, SNRW and X locks.

        OnceSU lock is upgraded to X or SNRW lock data modification can happen freely.

        To beused for the first phase of ALTER TABLE.*/   

    MDL_SHARED_UPGRADABLE,  //共享可升级锁，缩写为SU。ALTER TABLE命令早期阶段使用本锁

   

    /*   A shared metadata lock for cases when weneed to read data from table and block allconcurrent modifications to it

        (forboth data and metadata).  Used by LOCK TABLES READ statement. */

    MDL_SHARED_READ_ONLY,  //共享只读锁，缩写为SRO。阻塞所有的并发修改

 

    /*  An upgradable shared metadata lock whichblocks all attempts to update table data, allowing reads. //阻塞锁更新表数据的操作

        Aconnection holding this kind of lock can read table metadata and read tabledata.  //允许读元数据和表数据     

        Canbe upgraded to X metadata lock.

        Note,that since this type of lock is not compatible with SNRW or SW lock types,

       acquiring appropriate engine-level locks for reading(TL_READ* forMyISAM, shared row locks in InnoDB) should be contention-free.

        To beused for the first phase of ALTER TABLE,when copying data between tables,

        to allow concurrent SELECTs from thetable, but not UPDATEs. */   

    MDL_SHARED_NO_WRITE,  //共享非写锁，缩写为SNW。

   

    /*  An upgradable shared metadata lock whichallows other connections to access tablemetadata, but not data.

        It blocks all attempts to read or updatetable data, while allowing INFORMATION_SCHEMA and SHOW queries.

        //允许其他会话访问表的元数据。但不允许访问表的数据      

        Aconnection holding this kind of lock can read table metadata modify and readtable data. //持锁者可读表的元数据和表数据

        Canbe upgraded to X metadata lock.

        To beused for LOCK TABLES WRITE statement.

        Notcompatible with any other lock type except S and SH. */

    MDL_SHARED_NO_READ_WRITE,  //共享非读写锁，缩写为SNRW。用于LOCK TABLES...WRITE

   

    /*  An exclusivemetadata lock.

        Aconnection holding this lock can modifyboth table's metadata and data.

        Noother type of metadata lock can be granted while this lock is held.

        To beused for CREATE/DROP/RENAME TABLEstatements and for execution of certain phases of other DDL statements. */  

    MDL_EXCLUSIVE,  //排它锁，缩写为X。ALTER TABLE等一些操作也要用到此锁，如本地更新时调用mysql_inplace_alter_table()函数

    /* Thisshould be the last !!! */

   MDL_TYPE_END};