MYSQL METADATA LOCK(MDL LOCK)學習(1) 理論知識和加鎖型別測試

gaopengtttt發表於2017-08-04
MySql型別
原創，水平有限如有錯誤請指出共同探討
本文中某些結論性的東西我用黃色標記出來請大家多多留意一下
另外我家二娃剛剛出生，大家祝福我一下吧。謝謝！^_^

本文網址
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 };
作者微信:

MYSQL METADATA LOCK(MDL LOCK)學習(1) 理論知識和加鎖型別測試

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/7728585/viewspace-2143093/，如需轉載，請註明出處，否則將追究法律責任。

相關文章