备案控制台
探索云世界
开发者社区 数据库 文章 正文

MYSQL METADATA LOCK(MDL LOCK)学习(1) 理论知识和加锁类型测试

2017-08-04 1327
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云数据库 RDS MySQL Serverless，0.5-2RCU 50GB
推荐场景：
学生管理系统数据库设计 搭建个人博客
简介： 原创，水平有限如有错误请指出共同探讨本文中某些结论性的东西我用黄色标记出来请大家多多留意一下另外我家二娃刚刚出生，大家祝福我一下吧。谢谢！^_^ 本文网址 http://blog.itpub.net/7728585/viewspace-2143093/源码版本:5.7.14注意MDL和DML术语的不同。
原创，水平有限如有错误请指出共同探讨
本文中某些结论性的东西我用黄色标记出来请大家多多留意一下
另外我家二娃刚刚出生，大家祝福我一下吧。谢谢！^_^

本文网址
http://blog.itpub.net/7728585/viewspace-2143093/

源码版本:5.7.14
注意MDL和DML术语的不同。

一、前言
   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'
调用栈帧

点击(此处)折叠或打开

  1. #0 open_table_get_mdl_lock (thd=0x7fffd0000df0, ot_ctx=0x7fffec06fb00,
  2.     table_list=0x7fffd00067d8, flags=0, mdl_ticket=0x7fffec06f950)
  3.     at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_base.cc:2789
  4. #1 0x0000000001516e17 in open_table (thd=0x7fffd0000df0,
  5.     table_list=0x7fffd00067d8, ot_ctx=0x7fffec06fb00)
  6.     at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_base.cc:3237
----死锁检测出错码:
{ "ER_LOCK_DEADLOCK", 1213, "Deadlock found when trying to get lock; try restarting transaction" },
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
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
下面是源码中的注释：
  /**
    Helper struct which defines how different types of locks are handled
    for a specific MDL_lock. In practice we use only two strategies: "scoped"
    lock strategy for locks in GLOBAL, COMMIT, TABLESPACE and SCHEMA namespaces
    and "object" lock strategy for all other namespaces.
  */
4、MDL兼容矩阵
scope lock:
          | Type of active   |
  Request |   scoped lock    |
   type   | IS(*)  IX   S  X |
 ---------+------------------+
 IS       |  +      +   +  + |
 IX       |  +      +   -  - |
 S        |  +      -   +  - |
 X        |  +      -   -  - |

object lock:
       Request  |  Granted requests for lock            |
        type    | S  SH  SR  SW  SWLP  SU  SRO  SNW  SNRW  X  |
      ----------+---------------------------------------------+
      S         | +   +   +   +    +    +   +    +    +    -  |
      SH        | +   +   +   +    +    +   +    +    +    -  |
      SR        | +   +   +   +    +    +   +    +    -    -  |
      SW        | +   +   +   +    +    +   -    -    -    -  |
      SWLP      | +   +   +   +    +    +   -    -    -    -  |
      SU        | +   +   +   +    +    -   +    -    -    -  |
      SRO       | +   +   +   -    -    +   +    +    -    -  |
      SNW       | +   +   +   -    -    -   +    -    -    -  |
      SNRW      | +   +   -   -    -    -   -    -    -    -  |
      X         | -   -   -   -    -    -   -    -    -    -  |

5、MDL duration及MDL持续到什么时候
这个也不多用过多解释，看源码注释即可
MDL_STATEMENT:Locks with statement duration are automatically released at the end
              of statement or transaction.
MDL_TRANSACTION: Locks with transaction duration are automatically released at the end
                of transaction
MDL_EXPLICIT:Locks with explicit duration survive the end of statement and transaction.
             They have to be released explicitly by calling MDL_context::release_lock().
6、MDL LOCK FAST PATH（unobtrusive） OR SLOW PATH（obtrusive）
使用两种不同的方式目的在于优化MDL lock的实现，下面是源码的注释
A) "unobtrusive" lock types
            1) Each type from this set should be compatible with all other
               types from the set (including itself).
            2) These types should be common for DML operations
          Our goal is to optimize acquisition and release of locks of this
          type by avoiding complex checks and manipulations on m_waiting/
          m_granted bitmaps/lists. We replace them with a check of and
          increment/decrement of integer counters.
          We call the latter type of acquisition/release "fast path".
          Use of "fast path" reduces the size of critical section associated
          with MDL_lock::m_rwlock lock in the common case and thus increases
          scalability.
          The amount by which acquisition/release of specific type
          "unobtrusive" lock increases/decreases packed counter in
          MDL_lock::m_fast_path_state is returned by this function.
B) "obtrusive" lock types
            1) Granted or pending lock of those type is incompatible with
               some other types of locks or with itself.
            2) Not common for DML operations
          These locks have to be always acquired involving manipulations on
          m_waiting/m_granted bitmaps/lists, i.e. we have to use "slow path"
          for them. Moreover in the presence of active/pending locks from
          "obtrusive" set we have to acquire using "slow path" even locks of
          "unobtrusive" type.
7、MDL_request类，也就是通过语句解析后需要获得的MDL LOCK的需求，然后通过这个类对象在MDL子系统
                  中进行MDL LOCK申请，大概包含如下一些属性

点击(此处)折叠或打开

  1. /** Type of metadata lock. */
  2.   enum enum_mdl_type type; //需求的类型
  3.   /** Duration for requested lock. */
  4.   enum enum_mdl_duration duration; //持续时间
  5.   /**
  6.     Pointers for participating in the list of lock requests for this context.
  7.   */
  8.   MDL_request *next_in_list; //双向链表实现
  9.   MDL_request **prev_in_list;
  10.   /**
  11.     Pointer to the lock ticket object for this lock request.
  12.     Valid only if this lock request is satisfied.
  13.   */
  14.   MDL_ticket *ticket; //注意这里如果申请成功(没有等待)，会指向一个实际的TICKET，否则为NULL
  15.   /** A lock is requested based on a fully qualified name and type. */
  16.   MDL_key key;//注意这里是一个MDL_KEY类型，主要的就是前面说的NAMESPACE+DB+OBJECT_NAME
7、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
   可以观察到。当然这也是我主要观察的一个类

点击(此处)折叠或打开

  1. /**
  2.     Pointers for participating in the list of lock requests for this context.
  3.     Context private.正如解释这里是context中链表链表的形成，是线程私有的
  4.   */
  5.   MDL_ticket *next_in_context;
  6.   MDL_ticket **prev_in_context;
  7.   /**
  8.     Pointers for participating in the list of satisfied/pending requests
  9.     for the lock. Externally accessible.正如解释这里是MDL_LOCK中链表链表的形成，是全局的
  10.   */
  11.   MDL_ticket *next_in_lock;
  12.   MDL_ticket **prev_in_lock;
  13. /**
  14.     Context of the owner of the metadata lock ticket. Externally accessible.
  15.     很明显这里指向了这个ticket的拥有者也就是MDL_context，它是线程的属性
  16.   */
  17.   MDL_context *m_ctx;


  20.   /**
  21.     Pointer to the lock object for this lock ticket. Externally accessible.
  22.     很明显这里是一个指向MDL_LOCK的一个指针
  23.   */
  24.   MDL_lock *m_lock;


  27.   /**
  28.     Indicates that ticket corresponds to lock acquired using "fast path"
  29.     algorithm. Particularly this means that it was not included into
  30.     MDL_lock::m_granted bitmap/list and instead is accounted for by
  31.     MDL_lock::m_fast_path_locks_granted_counter
  32.     这里就代表了是否是FAST PATH从注释来看fast path方式不会在MDL LOCK中
  33.     占用granted位图和链表取而代之代之的是一个统计器m_fast_path_locks_granted_counter
  34.     这样一来开销肯定更小
  35.   */
  36.   bool m_is_fast_path;


  39.   /**
  40.     Indicates that ticket corresponds to lock request which required
  41.     storage engine notification during its acquisition and requires
  42.     storage engine notification after its release.
  43.   */
  44.   bool m_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子系统进行交互的一个所谓的上下文结构
其中包含了很多方法和属性，我比较关注的属性如下：

点击(此处)折叠或打开

  1. /**
  2.     If our request for a lock is scheduled, or aborted by the deadlock
  3.     detector, the result is recorded in this class.
  4.   */
  5.   MDL_wait m_wait;
  6. /**
  7.     Lists of all MDL tickets acquired by this connection.
  8.     这是一个不同MDL lock持续时间的一个链表数组。实际就是
  9.     MDL_STATEMENT一个链表
  10.     MDL_TRANSACTION一个链表
  11.     MDL_EXPLICIT一个链表
  12.   */
  13. Ticket_list m_tickets[MDL_DURATION_END];
  14. //这是一个父类指针指向子类对象，虚函数重写的典型,实际他就指向了一个线程
  15. /*
  16. class THD :public MDL_context_owner,
  17.            public Query_arena,
  18.            public Open_tables_state
  19. */
  20. MDL_context_owner *m_owner;
11、MDL_wait 这个类主要是当前ticket获取状态
    enum_wait_status m_wait_status;
   包含
   EMPTY   初始化
   GRANTED 获取成功
   VICTIM  死锁
   TIMEOUT 超时
   KILLED  KILLED
12、等待标记

点击(此处)折叠或打开

  1. PSI_stage_info MDL_key::m_namespace_to_wait_state_name[NAMESPACE_END]=
  2. {
  3.   {0, "Waiting for global read lock", 0},
  4.   {0, "Waiting for tablespace metadata lock", 0},
  5.   {0, "Waiting for schema metadata lock", 0},
  6.   {0, "Waiting for table metadata lock", 0},
  7.   {0, "Waiting for stored function metadata lock", 0},
  8.   {0, "Waiting for stored procedure metadata lock", 0},
  9.   {0, "Waiting for trigger metadata lock", 0},
  10.   {0, "Waiting for event metadata lock", 0},
  11.   {0, "Waiting for commit lock", 0},
  12.   {0, "User lock", 0}, /* Be compatible with old status. */
  13.   {0, "Waiting for locking service lock", 0},
  14.   {0, "Waiting for backup lock", 0},
  15.   {0, "Waiting for binlog lock", 0}
  16. };
三、增加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 * from performance_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
四、在合适的位置增加打印函数进行观察
既然我们要研究MDL LOCK的加锁\升级\降级、那么我们就必要找到他们的函数入口，然后在合适
的位置增加打印函数进行观察，下面标示出打印位置，删除了大部分的源代码，需要参考请自行
查看源码

1、加锁：MDL_context::acquire_lock

点击(此处)折叠或打开

  1. bool
  2. MDL_context::acquire_lock(MDL_request *mdl_request, ulong lock_wait_timeout)
  3. {
  4.   if (mdl_request->ticket) //获取成功获得ticket
  5.   {
  6.     /*
  7.       We have managed to acquire lock without waiting.
  8.       MDL_lock, MDL_context and MDL_request were updated
  9.       accordingly, so we can simply return success.
  10.     */
  11.     //REQUESET获取TICKET成功 此处打印
  12.     return FALSE;
  13.   }
  14.   /*
  15.     Our attempt to acquire lock without waiting has failed.
  16.     As a result of this attempt we got MDL_ticket with m_lock
  17.     member pointing to the corresponding MDL_lock object which
  18.     has MDL_lock::m_rwlock write-locked.
  19.   */
  20.   //获取不成功加入MDL_lock 等待队列
  21.   lock= ticket->m_lock;

  23.   lock->m_waiting.add_ticket(ticket);

  25.   will_wait_for(ticket); //死锁检测

  27.   /* There is a shared or exclusive lock on the object. */
  28.   DEBUG_SYNC(get_thd(), "mdl_acquire_lock_wait");

  30.   find_deadlock();
  31.   
  32.   //此处打印TICKET进入了等待流程
  33.  
  34.   if (lock->needs_notification(ticket) || lock->needs_connection_check())
  35.   {
  36.    }
  37.   done_waiting_for();//等待完成对死锁检测等待图进行调整去掉本等待边edge(无向图)

  39.   //当然到这里也是通过等待后获得成功了状态为GRANTED
  40.   DBUG_ASSERT(wait_status == MDL_wait::GRANTED);

  42.   m_tickets[mdl_request->duration].push_front(ticket);

  44.   mdl_request->ticket= ticket;

  46.   mysql_mdl_set_status(ticket->m_psi, MDL_ticket::GRANTED);
  47.   //此处打印通过等待REQUEST获得了TICKET
  48.   return FALSE;
  49. }
2、降级：void MDL_ticket::downgrade_lock(enum_mdl_type new_type)

点击(此处)折叠或打开

  1. void MDL_ticket::downgrade_lock(enum_mdl_type new_type)
  2. {
  3.   
  4.   /* Only allow downgrade from EXCLUSIVE and SHARED_NO_WRITE. */
  5.   DBUG_ASSERT(m_type == MDL_EXCLUSIVE ||
  6.               m_type == MDL_SHARED_NO_WRITE);

  8. //此处打印出降级前的TICKET

  10.   if (m_hton_notified)
  11.   {
  12.     mysql_mdl_set_status(m_psi, MDL_ticket::POST_RELEASE_NOTIFY);
  13.     m_ctx->get_owner()->notify_hton_post_release_exclusive(&m_lock->key);
  14.     m_hton_notified= false;
  15.     mysql_mdl_set_status(m_psi, MDL_ticket::GRANTED);
  16.   }
  17. //函数结尾答应出降级后的TICKET
  18. }
3、升级：MDL_context::upgrade_shared_lock(MDL_ticket *mdl_ticket,enum_mdl_type new_type, ulong lock_wait_timeout)

点击(此处)折叠或打开

  1. bool
  2. MDL_context::upgrade_shared_lock(MDL_ticket *mdl_ticket,
  3.                                  enum_mdl_type new_type,
  4.                                  ulong lock_wait_timeout)
  5. {
  6.   MDL_REQUEST_INIT_BY_KEY(&mdl_new_lock_request,
  7.                           &mdl_ticket->m_lock->key, new_type,
  8.                           MDL_TRANSACTION);//构造一个request

  10.  //此处打印出来的TICKET类型
  11.   
  12.   if (acquire_lock(&mdl_new_lock_request, lock_wait_timeout)) //尝试使用新的LOCK_TYPE进行加锁
  13.     DBUG_RETURN(TRUE);
  14.   
  15.   is_new_ticket= ! has_lock(mdl_svp, mdl_new_lock_request.ticket);

  17.   lock= mdl_ticket->m_lock;

  19.   //下面进行一系列对MDL_LOCK的维护并且对所谓的合并操作
  20.   /* Code below assumes that we were upgrading to "obtrusive" type of lock. */
  21.   DBUG_ASSERT(lock->is_obtrusive_lock(new_type));

  23.   /* Merge the acquired and the original lock. @todo: move to a method. */
  24.   mysql_prlock_wrlock(&lock->m_rwlock);

  26.   if (is_new_ticket)
  27.   {
  28.     m_tickets[MDL_TRANSACTION].remove(mdl_new_lock_request.ticket);
  29.     MDL_ticket::destroy(mdl_new_lock_request.ticket);
  30.   }
  31.  //此处打印出来的升级后TICKET类型
  32.   DBUG_RETURN(FALSE);
  33. }
当然我现在只是在这些地方进行了打印，以后如果需要在其他地方答应加上函数就可以了。
五、各种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.092100Z 3 [Note] Test2:open_tables_for_query()
2017-08-03T18:22:38.092205Z 3 [Note] (acquire_lock)THIS MDL LOCK acquire ok!
2017-08-03T18:22:38.092242Z 3 [Note] (>MDL PRINT) Thread id is 3: 
2017-08-03T18:22:38.092276Z 3 [Note] (--->MDL PRINT) Namespace is:GLOBAL 
2017-08-03T18:22:38.092310Z 3 [Note] (---->MDL PRINT) Fast path is:(Y)
2017-08-03T18:22:38.092344Z 3 [Note] (----->MDL PRINT) Mdl type is:MDL_INTENTION_EXCLUSIVE(IX) 
2017-08-03T18:22:38.092380Z 3 [Note] (------>MDL PRINT) Mdl  duration is:MDL_STATEMENT 
2017-08-03T18:22:38.092551Z 3 [Note] (------->MDL PRINT) Mdl  status is:EMPTY 

下面是 ALETER 语句触发的 GLOABL   IX MDL LOCK以及SCHEMA级别的MDL LOCK
2017-08-03T18:46:05.894871Z 3 [Note] (acquire_lock)THIS MDL LOCK acquire ok!
2017-08-03T18:46:05.894915Z 3 [Note] (>MDL PRINT) Thread id is 3: 
2017-08-03T18:46:05.894948Z 3 [Note] (--->MDL PRINT) Namespace is:GLOBAL 
2017-08-03T18:46:05.894980Z 3 [Note] (---->MDL PRINT) Fast path is:(Y)
2017-08-03T18:46:05.895012Z 3 [Note] (----->MDL PRINT) Mdl type is:MDL_INTENTION_EXCLUSIVE(IX) 
2017-08-03T18:46:05.895044Z 3 [Note] (------>MDL PRINT) Mdl  duration is:MDL_STATEMENT 
2017-08-03T18:46:05.895076Z 3 [Note] (------->MDL PRINT) Mdl  status is:EMPTY 

2017-08-03T18:46:05.895116Z 3 [Note] (acquire_lock)THIS MDL LOCK acquire ok!
2017-08-03T18:46:05.895147Z 3 [Note] (>MDL PRINT) Thread id is 3: 
2017-08-03T18:46:05.895206Z 3 [Note] (->MDL PRINT) DB_name is:test 
2017-08-03T18:46:05.895243Z 3 [Note] (-->MDL PRINT) OBJ_name is: 
2017-08-03T18:46:05.895276Z 3 [Note] (--->MDL PRINT) Namespace is:SCHEMA 
2017-08-03T18:46:05.895325Z 3 [Note] (---->MDL PRINT) Fast path is:(Y)
2017-08-03T18:46:05.895357Z 3 [Note] (----->MDL PRINT) Mdl type is:MDL_INTENTION_EXCLUSIVE(IX) 
2017-08-03T18:46:05.895390Z 3 [Note] (------>MDL PRINT) Mdl  duration is:MDL_TRANSACTION 
2017-08-03T18:46:05.895421Z 3 [Note] (------->MDL PRINT) Mdl  status is:EMPTY 

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

2、MDL_SHARED(S)
这把锁一般用在flush tables with read lock中
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.01 sec)

2017-08-03T18:19:11.603911Z 3 [Note] (acquire_lock)THIS MDL LOCK acquire ok!
2017-08-03T18:19:11.603947Z 3 [Note] (>MDL PRINT) Thread id is 3: 
2017-08-03T18:19:11.603971Z 3 [Note] (--->MDL PRINT) Namespace is:GLOBAL 
2017-08-03T18:19:11.603994Z 3 [Note] (----->MDL PRINT) Mdl type is:MDL_SHARED(S) 
2017-08-03T18:19:11.604045Z 3 [Note] (------>MDL PRINT) Mdl  duration is:MDL_EXPLICIT 
2017-08-03T18:19:11.604073Z 3 [Note] (------->MDL PRINT) Mdl  status is:EMPTY 
2017-08-03T18:19:11.604133Z 3 [Note] (acquire_lock)THIS MDL LOCK acquire ok!
2017-08-03T18:19:11.604156Z 3 [Note] (>MDL PRINT) Thread id is 3: 
2017-08-03T18:19:11.604194Z 3 [Note] (--->MDL PRINT) Namespace is:COMMIT 
2017-08-03T18:19:11.604217Z 3 [Note] (----->MDL PRINT) Mdl type is:MDL_SHARED(S) 
2017-08-03T18:19:11.604240Z 3 [Note] (------>MDL PRINT) Mdl  duration is:MDL_EXPLICIT 
2017-08-03T18:19:11.604310Z 3 [Note] (------->MDL PRINT) Mdl  status is: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  |  Granted requests for lock                  |
        type    | S  SH  SR  SW  SWLP  SU  SRO  SNW  SNRW  X  |
      ----------+---------------------------------------------+
      SH        | +   +   +   +    +    +   +    +    +    -  |
mysql> desc test.testsort10;

2017-08-03T19:06:05.843277Z 4 [Note] (acquire_lock)THIS MDL LOCK acquire ok!
2017-08-03T19:06:05.843324Z 4 [Note] (>MDL PRINT) Thread id is 4: 
2017-08-03T19:06:05.843359Z 4 [Note] (->MDL PRINT) DB_name is:test 
2017-08-03T19:06:05.843392Z 4 [Note] (-->MDL PRINT) OBJ_name is:testsort10 
2017-08-03T19:06:05.843425Z 4 [Note] (--->MDL PRINT) Namespace is:TABLE 
2017-08-03T19:06:05.843456Z 4 [Note] (---->MDL PRINT) Fast path is:(Y)
2017-08-03T19:06:05.843506Z 4 [Note] (----->MDL PRINT) Mdl type is:MDL_SHARED_HIGH_PRIO(SH) 
2017-08-03T19:06:05.843538Z 4 [Note] (------>MDL PRINT) Mdl  duration is:MDL_TRANSACTION 
2017-08-03T19:06:05.843570Z 4 [Note] (------->MDL PRINT) Mdl  status is:EMPTY 
这中类型的优先级比较高，但是其和X不兼容。也很好理解比如在rename 阶段肯定不能进行desc操作。

4、MDL_SHARED_READ(SR)
这把锁一般用在非当前读取的select中
兼容性：
       Request  |  Granted requests for lock                  |
        type    | S  SH  SR  SW  SWLP  SU  SRO  SNW  SNRW  X  |
      ----------+---------------------------------------------+
      SR        | +   +   +   +    +          +      +        +    -    -  |
mysql> select * from test.testsort10 limit 1;

2017-08-03T19:13:52.338764Z 4 [Note] (acquire_lock)THIS MDL LOCK acquire ok!
2017-08-03T19:13:52.338813Z 4 [Note] (>MDL PRINT) Thread id is 4: 
2017-08-03T19:13:52.338847Z 4 [Note] (->MDL PRINT) DB_name is:test 
2017-08-03T19:13:52.338883Z 4 [Note] (-->MDL PRINT) OBJ_name is:testsort10 
2017-08-03T19:13:52.338917Z 4 [Note] (--->MDL PRINT) Namespace is:TABLE 
2017-08-03T19:13:52.338950Z 4 [Note] (---->MDL PRINT) Fast path is:(Y)
2017-08-03T19:13:52.339025Z 4 [Note] (----->MDL PRINT) Mdl type is:MDL_SHARED_READ(SR) 
2017-08-03T19:13:52.339062Z 4 [Note] (------>MDL PRINT) Mdl  duration is:MDL_TRANSACTION 
2017-08-03T19:13:52.339097Z 4 [Note] (------->MDL PRINT) Mdl  status is: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  |  Granted requests for lock                  |
        type    | S  SH  SR  SW  SWLP  SU  SRO  SNW  SNRW  X  |
      ----------+---------------------------------------------+
      SW        | +   +   +   +    +    +   -    -    -    -  |
mysql> select * from test.testsort10 limit 1 for update;

2017-08-03T19:25:41.218428Z 4 [Note] (acquire_lock)THIS MDL LOCK acquire ok!
2017-08-03T19:25:41.218461Z 4 [Note] (>MDL PRINT) Thread id is 4: 
2017-08-03T19:25:41.218493Z 4 [Note] (->MDL PRINT) DB_name is:test 
2017-08-03T19:25:41.218525Z 4 [Note] (-->MDL PRINT) OBJ_name is:testsort10 
2017-08-03T19:25:41.218557Z 4 [Note] (--->MDL PRINT) Namespace is:TABLE 
2017-08-03T19:25:41.218588Z 4 [Note] (---->MDL PRINT) Fast path is:(Y)
2017-08-03T19:25:41.218620Z 4 [Note] (----->MDL PRINT) Mdl type is:MDL_SHARED_WRITE(SW) 
2017-08-03T19:25:41.218677Z 4 [Note] (------>MDL PRINT) Mdl  duration is:MDL_TRANSACTION 
2017-08-03T19:25:41.218874Z 4 [Note] (------->MDL PRINT) Mdl  status is:EMPTY 
6、MDL_SHARED_WRITE_LOW_PRIO(SWL)
这把锁很少用到源码注释只有
Used by DML statements modifying
    tables and using the LOW_PRIORITY clause
会用到
兼容性：

       Request  |  Granted requests for lock                  |
        type    | S  SH  SR  SW  SWLP  SU  SRO  SNW  SNRW  X  |
      ----------+---------------------------------------------+
      SWLP      | +   +   +   +    +    +   -    -    -    -  |
mysql> update  LOW_PRIORITY  test.testsort10 set id1=1000 where id1= 96282;
2017-08-03T19:32:47.433507Z 4 [Note] (acquire_lock)THIS MDL LOCK acquire ok!
2017-08-03T19:32:47.433521Z 4 [Note] (>MDL PRINT) Thread id is 4: 
2017-08-03T19:32:47.433533Z 4 [Note] (->MDL PRINT) DB_name is:test 
2017-08-03T19:32:47.433547Z 4 [Note] (-->MDL PRINT) OBJ_name is:testsort10 
2017-08-03T19:32:47.433560Z 4 [Note] (--->MDL PRINT) Namespace is:TABLE 
2017-08-03T19:32:47.433572Z 4 [Note] (---->MDL PRINT) Fast path is:(Y)
2017-08-03T19:32:47.433594Z 4 [Note] (----->MDL PRINT) Mdl type is:MDL_SHARED_WRITE_LOW_PRIO(SWL) 
2017-08-03T19:32:47.433607Z 4 [Note] (------>MDL PRINT) Mdl  duration is:MDL_TRANSACTION 
2017-08-03T19:32:47.433620Z 4 [Note] (------->MDL PRINT) Mdl  status is:EMPTY 
7、MDL_SHARED_UPGRADABLE(SU)

这把锁一般在ALTER TABLE语句中用到，他可以升级为SNW, SNRW，X，同时至少X锁也可以降级为SU
实际上在INNODB ONLINE DDL中非常依赖于他,DML(SW)和SELECT(SR)都不会堵塞
兼容性：
       Request  |  Granted requests for lock                  |
        type    | S  SH  SR  SW  SWLP  SU  SRO  SNW  SNRW  X  |
      ----------+---------------------------------------------+
      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.781453Z 3 [Note] (acquire_lock)THIS MDL LOCK acquire ok!
2017-08-03T19:46:54.781487Z 3 [Note] (>MDL PRINT) Thread id is 3: 
2017-08-03T19:46:54.781948Z 3 [Note] (->MDL PRINT) DB_name is:test 
2017-08-03T19:46:54.781990Z 3 [Note] (-->MDL PRINT) OBJ_name is:testsort12 
2017-08-03T19:46:54.782026Z 3 [Note] (--->MDL PRINT) Namespace is:TABLE 
2017-08-03T19:46:54.782060Z 3 [Note] (----->MDL PRINT) Mdl type is:MDL_SHARED_UPGRADABLE(SU) 
2017-08-03T19:46:54.782096Z 3 [Note] (------>MDL PRINT) Mdl  duration is:MDL_TRANSACTION 
2017-08-03T19:46:54.782175Z 3 [Note] (------->MDL PRINT) Mdl  status is:EMPTY 
2017-08-03T19:46:54.803898Z 3 [Note] (upgrade_shared_lock)THIS MDL LOCK will upgrade
2017-08-03T19:46:54.804201Z 3 [Note] (upgrade_shared_lock)THIS MDL LOCK  upgrade TO
2017-08-03T19:46:54.804240Z 3 [Note] (>MDL PRINT) Thread id is 3: 
2017-08-03T19:46:54.804254Z 3 [Note] (->MDL PRINT) DB_name is:test 
2017-08-03T19:46:54.804267Z 3 [Note] (-->MDL PRINT) OBJ_name is:testsort12 
2017-08-03T19:46:54.804280Z 3 [Note] (--->MDL PRINT) Namespace is:TABLE 
2017-08-03T19:46:54.804293Z 3 [Note] (----->MDL PRINT) Mdl type : MDL_EXCLUSIVE(X) 
2017-08-03T19:46:54.804306Z 3 [Note] (------>MDL PRINT) Mdl  duration is:MDL_TRANSACTION 
2017-08-03T19:46:54.804319Z 3 [Note] (------->MDL PRINT) Mdl  status is:EMPTY 
2017-08-03T19:46:54.855563Z 3 [Note] (downgrade_lock)THIS MDL LOCK will downgrade
2017-08-03T19:46:54.855693Z 3 [Note] (downgrade_lock) to this MDL lock
2017-08-03T19:46:54.855706Z 3 [Note] (>MDL PRINT) Thread id is 3: 
2017-08-03T19:46:54.855717Z 3 [Note] (->MDL PRINT) DB_name is:test 
2017-08-03T19:46:54.856053Z 3 [Note] (-->MDL PRINT) OBJ_name is:testsort12 
2017-08-03T19:46:54.856069Z 3 [Note] (--->MDL PRINT) Namespace is:TABLE 
2017-08-03T19:46:54.856082Z 3 [Note] (----->MDL PRINT) Mdl type is:MDL_SHARED_UPGRADABLE(SU) 
2017-08-03T19:46:54.856094Z 3 [Note] (------>MDL PRINT) Mdl  duration is:MDL_TRANSACTION 
2017-08-03T19:46:54.856214Z 3 [Note] (------->MDL PRINT) Mdl  status is:EMPTY 
2017-08-03T19:47:00.260166Z 3 [Note] (upgrade_shared_lock)THIS MDL LOCK will upgrade
2017-08-03T19:47:00.304057Z 3 [Note] (upgrade_shared_lock)THIS MDL LOCK  upgrade TO
2017-08-03T19:47:00.304090Z 3 [Note] (>MDL PRINT) Thread id is 3: 
2017-08-03T19:47:00.304105Z 3 [Note] (->MDL PRINT) DB_name is:test 
2017-08-03T19:47:00.304119Z 3 [Note] (-->MDL PRINT) OBJ_name is:testsort12 
2017-08-03T19:47:00.304132Z 3 [Note] (--->MDL PRINT) Namespace is:TABLE 
2017-08-03T19:47:00.304181Z 3 [Note] (----->MDL PRINT) Mdl type is: MDL_EXCLUSIVE(X) 
2017-08-03T19:47:00.304196Z 3 [Note] (------>MDL PRINT) Mdl  duration is:MDL_TRANSACTION 
2017-08-03T19:47:00.304211Z 3 [Note] (------->MDL PRINT) Mdl  status is:EMPTY 
2017-08-03T19:47:01.032329Z 3 [Note] (acquire_lock)THIS MDL LOCK acquire ok!

首先获得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  |  Granted requests for lock                   
        type    | S  SH  SR  SW  SWLP  SU  SRO  SNW  SNRW  X  |
      ----------+---------------------------------------------+
      SNW       | +   +   +   -    -    -   +    -    -    -  |
可以看到SR可以但是SW不行，当然也就堵塞了DML(SW)而SELECT(SR)不会堵塞，下面我只是给出了关键部分
mysql>  alter table testsort12 add column ik int not null, ALGORITHM=COPY  ;

2017-08-03T20:07:58.413215Z 3 [Note] (upgrade_shared_lock)THIS MDL LOCK  upgrade TO
2017-08-03T20:07:58.413241Z 3 [Note] (>MDL PRINT) Thread id is 3: 
2017-08-03T20:07:58.413257Z 3 [Note] (->MDL PRINT) DB_name is:test 
2017-08-03T20:07:58.413273Z 3 [Note] (-->MDL PRINT) OBJ_name is:testsort12 
2017-08-03T20:07:58.413292Z 3 [Note] (--->MDL PRINT) Namespace is:TABLE 
2017-08-03T20:07:58.413308Z 3 [Note] (----->MDL PRINT) Mdl type is:MDL_SHARED_NO_WRITE(SNW) 
2017-08-03T20:07:58.413325Z 3 [Note] (------>MDL PRINT) Mdl  duration is:MDL_TRANSACTION 
2017-08-03T20:07:58.413341Z 3 [Note] (------->MDL PRINT) Mdl  status is:EMPTY 
2017-08-03T20:08:25.392006Z 3 [Note] (upgrade_shared_lock)THIS MDL LOCK  upgrade TO
2017-08-03T20:08:25.392024Z 3 [Note] (>MDL PRINT) Thread id is 3: 
2017-08-03T20:08:25.392086Z 3 [Note] (->MDL PRINT) DB_name is:test 
2017-08-03T20:08:25.392159Z 3 [Note] (-->MDL PRINT) OBJ_name is:testsort12 
2017-08-03T20:08:25.392199Z 3 [Note] (--->MDL PRINT) Namespace is:TABLE 
2017-08-03T20:08:25.392214Z 3 [Note] (----->MDL PRINT) Mdl type is: MDL_EXCLUSIVE(X) 
2017-08-03T20:08:25.392228Z 3 [Note] (------>MDL PRINT) Mdl  duration is:MDL_TRANSACTION 
2017-08-03T20:08:25.392242Z 3 [Note] (------->MDL PRINT) Mdl  status is: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)
用于LOCK TABLES READ 语句
兼容性如下
       Request  |  Granted requests for lock                  |
        type    | S  SH  SR  SW  SWLP  SU  SRO  SNW  SNRW  X  |
      ----------+---------------------------------------------+
      SRO       | +   +   +   -    -    +   +    +    -    -  |
堵塞DML(SW)但是SELECT(SR)还是可以的。
mysql> lock table testsort12 read;
Query OK, 0 rows affected (0.01 sec)
2017-08-03T21:08:27.267947Z 3 [Note] (acquire_lock)THIS MDL LOCK acquire ok!
2017-08-03T21:08:27.267979Z 3 [Note] (>MDL PRINT) Thread id is 3: 
2017-08-03T21:08:27.268009Z 3 [Note] (->MDL PRINT) DB_name is:test 
2017-08-03T21:08:27.268040Z 3 [Note] (-->MDL PRINT) OBJ_name is:testsort12 
2017-08-03T21:08:27.268070Z 3 [Note] (--->MDL PRINT) Namespace is:TABLE 
2017-08-03T21:08:27.268113Z 3 [Note] (----->MDL PRINT) Mdl type is:MDL_SHARED_READ_ONLY(SRO) 
2017-08-03T21:08:27.268145Z 3 [Note] (------>MDL PRINT) Mdl  duration is:MDL_TRANSACTION 
2017-08-03T21:08:27.268175Z 3 [Note] (------->MDL PRINT) Mdl  status is:EMPTY 
 
10、MDL_SHARED_NO_READ_WRITE(SNRW)

用于LOCK TABLES WRITE语句
兼容性：
       Request  |  Granted requests for lock                  |
        type    | S  SH  SR  SW  SWLP  SU  SRO  SNW  SNRW  X  |
      ----------+---------------------------------------------+
      SNRW      | +   +   -   -    -    -   -    -    -    -  |
可以看到DML(SW)和SELECT(SR)都被堵塞只有SH还可以，还可以DESC(SH) 。


mysql> lock table testsort12 write;
Query OK, 0 rows affected (0.00 sec)
2017-08-03T21:13:07.113347Z 3 [Note] (acquire_lock)THIS MDL LOCK acquire ok!
2017-08-03T21:13:07.113407Z 3 [Note] (>MDL PRINT) Thread id is 3: 
2017-08-03T21:13:07.113435Z 3 [Note] (--->MDL PRINT) Namespace is:GLOBAL 
2017-08-03T21:13:07.113458Z 3 [Note] (---->MDL PRINT) Fast path is:(Y)
2017-08-03T21:13:07.113482Z 3 [Note] (----->MDL PRINT) Mdl type is:MDL_INTENTION_EXCLUSIVE(IX) 
2017-08-03T21:13:07.113505Z 3 [Note] (------>MDL PRINT) Mdl  duration is:MDL_STATEMENT 
2017-08-03T21:13:07.113604Z 3 [Note] (------->MDL PRINT) Mdl  status is:EMPTY 
2017-08-03T21:13:07.113637Z 3 [Note] (acquire_lock)THIS MDL LOCK acquire ok!
2017-08-03T21:13:07.113660Z 3 [Note] (>MDL PRINT) Thread id is 3: 
2017-08-03T21:13:07.113681Z 3 [Note] (->MDL PRINT) DB_name is:test 
2017-08-03T21:13:07.113703Z 3 [Note] (-->MDL PRINT) OBJ_name is: 
2017-08-03T21:13:07.113725Z 3 [Note] (--->MDL PRINT) Namespace is:SCHEMA 
2017-08-03T21:13:07.113746Z 3 [Note] (---->MDL PRINT) Fast path is:(Y)
2017-08-03T21:13:07.113768Z 3 [Note] (----->MDL PRINT) Mdl type is:MDL_INTENTION_EXCLUSIVE(IX) 
2017-08-03T21:13:07.113791Z 3 [Note] (------>MDL PRINT) Mdl  duration is:MDL_TRANSACTION 
2017-08-03T21:13:07.113813Z 3 [Note] (------->MDL PRINT) Mdl  status is:EMPTY 
2017-08-03T21:13:07.113842Z 3 [Note] (acquire_lock)THIS MDL LOCK acquire ok!
2017-08-03T21:13:07.113865Z 3 [Note] (>MDL PRINT) Thread id is 3: 
2017-08-03T21:13:07.113887Z 3 [Note] (->MDL PRINT) DB_name is:test 
2017-08-03T21:13:07.113922Z 3 [Note] (-->MDL PRINT) OBJ_name is:testsort12 
2017-08-03T21:13:07.113945Z 3 [Note] (--->MDL PRINT) Namespace is:TABLE 
2017-08-03T21:13:07.113975Z 3 [Note] (----->MDL PRINT) Mdl type is:MDL_SHARED_NO_READ_WRITE(SNRW) 
2017-08-03T21:13:07.113998Z 3 [Note] (------>MDL PRINT) Mdl  duration is:MDL_TRANSACTION 
2017-08-03T21:13:07.114021Z 3 [Note] (------->MDL PRINT) Mdl  status is: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  |  Granted requests for lock                  |
        type    | S  SH  SR  SW  SWLP  SU  SRO  SNW  SNRW  X  |
      ----------+---------------------------------------------+
      X         | -   -   -   -    -    -   -    -    -    -  |
没有上面意外堵塞一切，也被一切所堵塞
比如刚才的add column操作

2017-08-03T19:46:54.804240Z 3 [Note] (>MDL PRINT) Thread id is 3: 
2017-08-03T19:46:54.804254Z 3 [Note] (->MDL PRINT) DB_name is:test 
2017-08-03T19:46:54.804267Z 3 [Note] (-->MDL PRINT) OBJ_name is:testsort12 
2017-08-03T19:46:54.804280Z 3 [Note] (--->MDL PRINT) Namespace is:TABLE 
2017-08-03T19:46:54.804293Z 3 [Note] (----->MDL PRINT) Mdl type is:MDL_ EXCLUSIVE(X) 
2017-08-03T19:46:54.804306Z 3 [Note] (------>MDL PRINT) Mdl  duration is:MDL_TRANSACTION 
2017-08-03T19:46:54.804319Z 3 [Note] (------->MDL PRINT) Mdl  status is:EMPTY 

六、源码注释部分

点击(此处)折叠或打开

  1. enum enum_mdl_type {
  2.   /*
  3.     An intention exclusive metadata lock. Used only for scoped locks.
  4.     Owner of this type of lock can acquire upgradable exclusive locks on
  5.     individual objects.
  6.     Compatible with other IX locks, but is incompatible with scoped S and
  7.     X locks.
  8.   */
  9.   MDL_INTENTION_EXCLUSIVE= 0,
  10.   /*
  11.     A shared metadata lock.
  12.     To be used in cases when we are interested in object metadata only
  13.     and there is no intention to access object data (e.g. for stored
  14.     routines or during preparing prepared statements).
  15.     We also mis-use this type of lock for open HANDLERs, since lock
  16.     acquired by this statement has to be compatible with lock acquired
  17.     by LOCK TABLES ... WRITE statement, i.e. SNRW (We can't get by by
  18.     acquiring S lock at HANDLER ... OPEN time and upgrading it to SR
  19.     lock for HANDLER ... READ as it doesn't solve problem with need
  20.     to abort DML statements which wait on table level lock while having
  21.     open HANDLER in the same connection).
  22.     To avoid deadlock which may occur when SNRW lock is being upgraded to
  23.     X lock for table on which there is an active S lock which is owned by
  24.     thread which waits in its turn for table-level lock owned by thread
  25.     performing upgrade we have to use thr_abort_locks_for_thread()
  26.     facility in such situation.
  27.     This problem does not arise for locks on stored routines as we don't
  28.     use SNRW locks for them. It also does not arise when S locks are used
  29.     during PREPARE calls as table-level locks are not acquired in this
  30.     case.
  31.   */
  32.   MDL_SHARED,
  33.   /*
  34.     A high priority shared metadata lock.
  35.     Used for cases when there is no intention to access object data (i.e.
  36.     data in the table).
  37.     "High priority" means that, unlike other shared locks, it is granted
  38.     ignoring pending requests for exclusive locks. Intended for use in
  39.     cases when we only need to access metadata and not data, e.g. when
  40.     filling an INFORMATION_SCHEMA table.
  41.     Since SH lock is compatible with SNRW lock, the connection that
  42.     holds SH lock lock should not try to acquire any kind of table-level
  43.     or row-level lock, as this can lead to a deadlock. Moreover, after
  44.     acquiring SH lock, the connection should not wait for any other
  45.     resource, as it might cause starvation for X locks and a potential
  46.     deadlock during upgrade of SNW or SNRW to X lock (e.g. if the
  47.     upgrading connection holds the resource that is being waited for).
  48.   */
  49.   MDL_SHARED_HIGH_PRIO,
  50.   /*
  51.     A shared metadata lock for cases when there is an intention to read data
  52.     from table.
  53.     A connection holding this kind of lock can read table metadata and read
  54.     table data (after acquiring appropriate table and row-level locks).
  55.     This means that one can only acquire TL_READ, TL_READ_NO_INSERT, and
  56.     similar table-level locks on table if one holds SR MDL lock on it.
  57.     To be used for tables in SELECTs, subqueries, and LOCK TABLE ... READ
  58.     statements.
  59.   */
  60.   MDL_SHARED_READ,
  61.   /*
  62.     A shared metadata lock for cases when there is an intention to modify
  63.     (and not just read) data in the table.
  64.     A connection holding SW lock can read table metadata and modify or read
  65.     table data (after acquiring appropriate table and row-level locks).
  66.     To be used for tables to be modified by INSERT, UPDATE, DELETE
  67.     statements, but not LOCK TABLE ... WRITE or DDL). Also taken by
  68.     SELECT ... FOR UPDATE.
  69.   */
  70.   MDL_SHARED_WRITE,
  71.   /*
  72.     A version of MDL_SHARED_WRITE lock which has lower priority than
  73.     MDL_SHARED_READ_ONLY locks. Used by DML statements modifying
  74.     tables and using the LOW_PRIORITY clause.
  75.   */
  76.   MDL_SHARED_WRITE_LOW_PRIO,
  77.   /*
  78.     An upgradable shared metadata lock which allows concurrent updates and
  79.     reads of table data.
  80.     A connection holding this kind of lock can read table metadata and read
  81.     table data. It should not modify data as this lock is compatible with
  82.     SRO locks.
  83.     Can be upgraded to SNW, SNRW and X locks. Once SU lock is upgraded to X
  84.     or SNRW lock data modification can happen freely.
  85.     To be used for the first phase of ALTER TABLE.
  86.   */
  87.   MDL_SHARED_UPGRADABLE,
  88.   /*
  89.     A shared metadata lock for cases when we need to read data from table
  90.     and block all concurrent modifications to it (for both data and metadata).
  91.     Used by LOCK TABLES READ statement.
  92.   */
  93.   MDL_SHARED_READ_ONLY,
  94.   /*
  95.     An upgradable shared metadata lock which blocks all attempts to update
  96.     table data, allowing reads.
  97.     A connection holding this kind of lock can read table metadata and read
  98.     table data.
  99.     Can be upgraded to X metadata lock.
  100.     Note, that since this type of lock is not compatible with SNRW or SW
  101.     lock types, acquiring appropriate engine-level locks for reading
  102.     (TL_READ* for MyISAM, shared row locks in InnoDB) should be
  103.     contention-free.
  104.     To be used for the first phase of ALTER TABLE, when copying data between
  105.     tables, to allow concurrent SELECTs from the table, but not UPDATEs.
  106.   */
  107.   MDL_SHARED_NO_WRITE,
  108.   /*
  109.     An upgradable shared metadata lock which allows other connections
  110.     to access table metadata, but not data.
  111.     It blocks all attempts to read or update table data, while allowing
  112.     INFORMATION_SCHEMA and SHOW queries.
  113.     A connection holding this kind of lock can read table metadata modify and
  114.     read table data.
  115.     Can be upgraded to X metadata lock.
  116.     To be used for LOCK TABLES WRITE statement.
  117.     Not compatible with any other lock type except S and SH.
  118.   */
  119.   MDL_SHARED_NO_READ_WRITE,
  120.   /*
  121.     An exclusive metadata lock.
  122.     A connection holding this lock can modify both table's metadata and data.
  123.     No other type of metadata lock can be granted while this lock is held.
  124.     To be used for CREATE/DROP/RENAME TABLE statements and for execution of
  125.     certain phases of other DDL statements.
  126.   */
  127.   MDL_EXCLUSIVE,
  128.   /* This should be the last !!! */
  129.   MDL_TYPE_END};


  132. /** Duration of metadata lock. */

  134. enum enum_mdl_duration {
  135.   /**
  136.     Locks with statement duration are automatically released at the end
  137.     of statement or transaction.
  138.   */
  139.   MDL_STATEMENT= 0,
  140.   /**
  141.     Locks with transaction duration are automatically released at the end
  142.     of transaction.
  143.   */
  144.   MDL_TRANSACTION,
  145.   /**
  146.     Locks with explicit duration survive the end of statement and transaction.
  147.     They have to be released explicitly by calling MDL_context::release_lock().
  148.   */
  149.   MDL_EXPLICIT,
  150.   /* This should be the last ! */
  151.   MDL_DURATION_END };

  153. /**
  154.     Object namespaces.
  155.     Sic: when adding a new member to this enum make sure to
  156.     update m_namespace_to_wait_state_name array in mdl.

  158.     Different types of objects exist in different namespaces
  159.      - GLOBAL is used for the global read lock.
  160.      - TABLESPACE is for tablespaces.
  161.      - SCHEMA is for schemas (aka databases).
  162.      - TABLE is for tables and views.
  163.      - FUNCTION is for stored functions.
  164.      - PROCEDURE is for stored procedures.
  165.      - TRIGGER is for triggers.
  166.      - EVENT is for event scheduler events.
  167.      - COMMIT is for enabling the global read lock to block commits.
  168.      - USER_LEVEL_LOCK is for user-level locks.
  169.      - LOCKING_SERVICE is for the name plugin RW-lock service
  170.     Note that although there isn't metadata locking on triggers,
  171.     it's necessary to have a separate namespace for them since
  172.     MDL_key is also used outside of the MDL subsystem.
  173.     Also note that requests waiting for user-level locks get special
  174.     treatment - waiting is aborted if connection to client is lost.
  175.   */
  176.   enum enum_mdl_namespace { GLOBAL=0,
  177.                             TABLESPACE,
  178.                             SCHEMA,
  179.                             TABLE,
  180.                             FUNCTION,
  181.                             PROCEDURE,
  182.                             TRIGGER,
  183.                             EVENT,
  184.                             COMMIT,
  185.                             USER_LEVEL_LOCK,
  186.                             LOCKING_SERVICE,
  187.                             BACKUP,
  188.                             BINLOG,
  189.                             /* This should be the last ! */
  190.                             NAMESPACE_END };
作者微信:

img_4166a896a28155d27307bf8bdad181d5.jpg
文章标签：
云数据库 RDS MySQL 版
SQL
关系型数据库
Shell
测试技术
MySQL
关键词：
云数据库 RDS MySQL 版锁
云数据库 RDS MySQL 版学习
云数据库 RDS MySQL 版类型
测试mysql
测试学习
相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境，其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心，利用清洁计算为万物互联的新世界提供源源不断的能源动力，目前开服的区域包括中国（华北、华东、华南、香港）、新加坡、美国（美东、美西）、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程，来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品：云服务器ECS 云服务器 ECS（Elastic Compute Service）是一种弹性可伸缩的计算服务，助您降低 IT 成本，提升运维效率，使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
重庆八怪
目录
相关文章
Cool架构
|
存储 关系型数据库 MySQL
MySQL学习（五）
MySQL学习
Cool架构
90 0
穆雄雄.
|
29天前
Mybatis+mysql动态分页查询数据案例——测试类HouseDaoMybatisImplTest)
Mybatis+mysql动态分页查询数据案例——测试类HouseDaoMybatisImplTest)
穆雄雄.
20 1
穆雄雄.
|
29天前
|
Java 关系型数据库 数据库连接
Mybatis+MySQL动态分页查询数据经典案例（含代码以及测试）
Mybatis+MySQL动态分页查询数据经典案例（含代码以及测试）
穆雄雄.
24 1
2019ab
|
1月前
|
关系型数据库 MySQL
Mysql基础第十九天,使用不同类型的链接
Mysql基础第十九天,使用不同类型的链接
2019ab
25 0
Mysql基础第十九天,使用不同类型的链接
Cool架构
|
存储 关系型数据库 MySQL
MySQL学习（七）
MySQL学习
Cool架构
81 0
1024小神
|
1月前
|
存储 关系型数据库 MySQL
最全MySQL面试60题（含答案）：存储引擎+数据库锁+索引+SQL优化等
最全MySQL面试60题（含答案）：存储引擎+数据库锁+索引+SQL优化等
1024小神
169 0
追逐时光者
|
15天前
|
SQL 存储 关系型数据库
6本值得推荐的MySQL学习书籍
本文是关于MySQL学习书籍的推荐，作者在DotNetGuide技术社区和微信公众号收到读者请求后，精选了6本值得阅读的MySQL书籍，包括《SQL学习指南（第3版）》、《MySQL是怎样使用的：快速入门MySQL》、《MySQL是怎样运行的：从根儿上理解MySQL》、《深入浅出MySQL：数据库开发、优化与管理维护（第3版）》以及《高性能MySQL（第4版）》和《MySQL技术内幕InnoDB存储引擎(第2版)》。此外，还有12本免费书籍的赠送活动，涵盖《SQL学习指南》、《MySQL是怎样使用的》等，赠书活动有效期至2024年4月9日。
追逐时光者
30 0
技术蜜糖罐
|
20天前
|
SQL 关系型数据库 MySQL
轻松入门MySQL：深入学习数据库表管理，创建、修改、约束、建议与性能优化（3）
轻松入门MySQL：深入学习数据库表管理，创建、修改、约束、建议与性能优化（3）
技术蜜糖罐
36 0
xiaohua616
|
4天前
|
关系型数据库 MySQL 索引
MySQL 锁机制
MySQL 锁机制
xiaohua616
7 0
一只牛博
|
12天前
|
关系型数据库 MySQL 数据库
MySQL锁三部曲：临键、间隙与记录的奇妙旅程
MySQL锁三部曲：临键、间隙与记录的奇妙旅程
一只牛博
18 0

热门文章

最新文章

  • 1
    深入理解自动化测试框架Selenium的设计与实现
  • 2
    【软件设计师备考 专题 】模块测试的方法和实践
  • 3
    【软件设计师备考 专题 】系统实施：程序设计和系统测试
  • 4
    【软件设计师备考 专题 】如何编写有效的测试报告
  • 5
    【软件设计师备考 专题 】测试要求说明书的编写和应用
  • 6
    【软件设计师备考 专题 】可靠性测试和可靠性评估
  • 7
    【软件设计师备考 专题 】系统集成测试的准备和执行
  • 8
    如何入门做物联网系统压测？
  • 9
    【软件设计师备考 专题 】性能测试和性能评估
  • 10
    渗透测试工程师面试题大全
  • 1
    【MySQL实战笔记】07 | 行锁功过：怎么减少行锁对性能的影响？-02 死锁和死锁检测
    13
  • 2
    数据库开发之mysql前言以及详细解析
    9
  • 3
    Python与MySQL数据库交互：面试实战
    25
  • 4
    【MySQL面试题pro版-12】
    13
  • 5
    【MySQL面试题pro版-10】
    13
  • 6
    【MySQL面试题pro版-9】
    13
  • 7
    【MySQL面试题pro版-8】
    11
  • 8
    【MySQL面试题pro版-7】
    17
  • 9
    【MySQL面试题pro版-6】
    16
  • 10
    MySQL面试题系列-16
    16

    • 相关课程

    更多
  • MySQL企业常见架构与调优经验分享
  • 云数据库MySQL版快速上手教程
  • 阿里云云原生数据仓库AnalyticDB MySQL版 使用教程
  • MySQL实战进阶
  • 数据库及SQL/MySQL基础
  • 云数据库MySQL快速入门

    • 相关电子书

    更多
  • 搭建电商项目架构连接MySQL
  • 搭建4层电商项目架构，实战连接MySQL
  • PolarDB MySQL引擎重磅功能及产品能力盛大发布

    • 相关实验场景

    更多
  • 测试场景（RDS无优惠）
  • MySQL引擎及架构优化
  • 基于EBS部署高性能的MySQL服务
  • 使用Python完成RDS数据开发及分析
  • Excel文件转存到RDS数据库
  • 使用CloudLens采集RDS日志并进行审计分析

    • 推荐镜像

    更多
  • mysql
  • mariadb
  • postgresql
    • 下一篇
    部署LAMP环境（Alibaba Cloud Linux 3）