MySQL：理解MDL Lock

---

本文基於原始碼版本5.7.14
水平有限，有誤請諒解
筆者已經將加好MDL 獲取過程和釋放過程的版本放到了github如下：
https://github.com/gaopengcarl/percona-server-locks-detail-5.7.22
供參考

---

作者深入理解主從原理專欄

https://www.jianshu.com/nb/43148932

一、MDL Lock綜述

MySQL中MDL鎖一直是一個比較讓人比較頭疼的問題，我們談起堵塞一般更加傾向於InnoDB層的row lock（gap lock/next key lock/key lock），因為它很好理解也很好觀察。而對於MDL Lock考慮就少一些，因為它實在不好觀察，只有出現問題檢視show processlist的時候，可以看到簡單的所謂的‘Waiting for table metadata lock’之類的狀態，其實MDL Lock是MySQL上層一個非常複雜的子系統，有自己的死鎖檢測機制。
大家一般說是不是鎖表了很大一部分就和MDL Lock有關，可見的它的關鍵性和嚴重性，筆者也是根據自己的需求學習了一些，且沒有能力閱讀全部的程式碼，但是筆者通過增加一個TICKET的列印函式讓語句的MDL Lock加鎖流程全部列印出來方便學習，下面從一些基礎概念說起然後告訴大家筆者是如何做的列印功能，最後對每種MDL TYPE可能出現的語句進行測試和分析。如果大家對基本概念和增加列印函式不感興趣可直接參考第五部分語句加MDL Lock測試和分析，希望這些測試能夠幫助到大家診斷問題。

剛好最近筆者遇到一次MDL Lock出現死鎖的情況會在下篇文章中給出案例，本文只看理論。

• 處於層次：MySQL層，實際上早在open_table函式中MDL LOCK就開始獲取了。
• 最早獲取階段: THD::enter_stage: ‘Opening tables’
• 呼叫棧幀

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

• 死鎖檢測出錯碼

  { "ER_LOCK_DEADLOCK", 1213, "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 status’沒有死鎖資訊。

二、重要資料結構和概念

1、MDL Lock型別

我們主要研究的型別如下：

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)

第五部分會對每種型別進行詳細的測試和解釋。

2、MDL Lock 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

本文我們主要對GLOBAL/SCHEMA/TABLE namespace進行描述，而對於COMMIT namespace是提交的時候會用到的如果遇到等待，狀態為‘Waiting for commit lock’，一般為FTWRL堵塞COMMIT。可參考我的《深入理解MySQL主從原理》15節。其他namespace不做描述。

3、MDL Lock實現分類

• scope lock:一般對應全域性MDL Lock，如flush table with read lock 會獲取namespace space：GLOBAL type：S和namespace space：COMMIT type：S的MDL Lock。它包含GLOBAL, COMMIT, TABLESPACE和SCHEMA
• object lock:如其名字所示，物件級別的MDL Lock，比如TABLE級別的MDL Lock，這也是本文的討論核心。 它包含其他的namespace。

下面是原始碼註釋：

  /**
    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 Lock相容矩陣

這裡相容矩陣是學習鎖堵塞的重點，型別很多比Innodb row lock型別要多很多，不用記住，只需要遇到能知道。

5、MDL Lock duration（MDL Lock持續週期）

這個對應原始碼的enum_mdl_duration，通常我們需要關注MDL Lock是事務提交後釋放還是語句結束後釋放，實際上就是這個，這對MDL lock堵塞的範圍很重要。我直接複製原始碼的解釋。

• 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）和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 withsome 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申請，大概包含如下一些屬性：

/** Type of metadata lock. */
  enum enum_mdl_type type; //需求的型別
  /** Duration for requested lock. */
  enum enum_mdl_duration duration; //持續週期
  /**
    Pointers for participating in the list of lock requests for this context.
  */
  MDL_request *next_in_list; //雙向連結串列實現
  MDL_request **prev_in_list;
  /**
    Pointer to the lock ticket object for this lock request.
    Valid only if this lock request is satisfied.
  */
  MDL_ticket *ticket; //注意這裡如果申請成功(沒有等待)，會指向一個實際的TICKET，否則為NULL
  /** A lock is requested based on a fully qualified name and type. */

8、MDL_key結構部分屬性

就是實際的namespace+DB+OBJECT_NAME，整個放到一個char陣列裡面，他會在MDL_LOCK和MDL_REQUEST中出現。

private:
uint16m_length;
uint16m_db_name_length;
charm_ptr[MAX_MDLKEY_LENGTH];//放到了這裡

9、MDL_ticket結構部分屬性

如同門票一樣，如果獲取了MDL Lock必然給MDL_request返回一張門票，如果等待則不會分配。原始碼MDL_context::acquire_lock可以觀察到。部分屬性如下：

/**
    Pointers for participating in the list of lock requests for this context.
    Context private.正如解釋這裡是context中連結串列連結串列的形成，是執行緒私有的
  */
  MDL_ticket *next_in_context;
  MDL_ticket **prev_in_context;
  /**
    Pointers for participating in the list of satisfied/pending requests
    for the lock. Externally accessible.正如解釋這裡是MDL_LOCK中連結串列連結串列的形成，是全域性的
  */
  MDL_ticket *next_in_lock;
  MDL_ticket **prev_in_lock;
/**
    Context of the owner of the metadata lock ticket. Externally accessible.
    很明顯這裡指向了這個ticket的擁有者也就是MDL_context，它是執行緒的屬性
  */
  MDL_context *m_ctx; 
  /**
    Pointer to the lock object for this lock ticket. Externally accessible.
    很明顯這裡是一個指向MDL_LOCK的一個指標
  */
  MDL_lock *m_lock;
  /**
    Indicates that ticket corresponds to lock acquired using "fast path"
    algorithm. Particularly this means that it was not included into
    MDL_lock::m_granted bitmap/list and instead is accounted for by
    MDL_lock::m_fast_path_locks_granted_counter
    這裡就代表了是否是FAST PATH從註釋來看fast path方式不會在MDL LOCK中
    佔用granted點陣圖和連結串列取而代之代之的是一個統計器m_fast_path_locks_granted_counter
    這樣一來開銷肯定更小
  */
  bool m_is_fast_path;
  /**
    Indicates that ticket corresponds to lock request which required
    storage engine notification during its acquisition and requires
    storage engine notification after its release.
  */

10、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;

11、MDL_context結構部分屬性

這是整個MySQL執行緒和MDL Lock子系統進行互動的一個所謂的上下文結構,其中包含了很多方法和屬性，我比較關注的屬性如下：

/**
    If our request for a lock is scheduled, or aborted by the deadlock
    detector, the result is recorded in this class.
  */
  MDL_wait m_wait;
/**
    Lists of all MDL tickets acquired by this connection.
    這是一個不同MDL lock持續時間的一個連結串列陣列。實際就是
    MDL_STATEMENT一個連結串列
    MDL_TRANSACTION一個連結串列
    MDL_EXPLICIT一個連結串列
  */
Ticket_list m_tickets[MDL_DURATION_END];
//這是一個父類指標指向子類物件，虛擬函式重寫的典型,實際他就指向了一個執行緒
/*
class THD :public MDL_context_owner,
           public Query_arena,
           public Open_tables_state
*/
MDL_context_owner *m_owner;

12、所有等待狀態

原始碼給出了所有的等待標記如下：

PSI_stage_info MDL_key::m_namespace_to_wait_state_name[NAMESPACE_END]=
{
  {0, "Waiting for global read lock", 0},
  {0, "Waiting for tablespace metadata lock", 0},
  {0, "Waiting for schema metadata lock", 0},
  {0, "Waiting for table metadata lock", 0},
  {0, "Waiting for stored function metadata lock", 0},
  {0, "Waiting for stored procedure metadata lock", 0},
  {0, "Waiting for trigger metadata lock", 0},
  {0, "Waiting for event metadata lock", 0},
  {0, "Waiting for commit lock", 0},
  {0, "User lock", 0}, /* Be compatible with old status. */
  {0, "Waiting for locking service lock", 0},
  {0, "Waiting for backup lock", 0},
  {0, "Waiting for binlog lock", 0}
};

我們常見的是：

• “Waiting for table metadata lock”：通常就是namespace TABLE級別的MDL Lock，具體根據相容矩陣判斷參考第五節。
• “Waiting for global read lock”：通常就是namespace GLOBAL級別的MDL Lock，通常和flush table with read lock有關，參考第五節。
• “Waiting for commit lock”：通常就是namespace COMMIT級別的MDL Lock，通常和flush table with read lock有關，參考第五節。

三、增加列印函式my_print_ticket

學習MDL Lock最好的方式當然是獲取一條語句鎖加的所有MDL Lock，包含加鎖、升級、降級和釋放的流程。雖然5.7加入診斷MDL Lock的方法：

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的流程仍然不好觀察，因此我加入了列印函式：

/*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的加鎖資訊列印到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中的資訊差不多，如下：

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

一旦有了這個函式我們只需要在加鎖、升級、降級和釋放的位置進行適當新增就可以了。

四、在合適的位置增加my_print_ticket列印函式

既然我們要研究MDL Lock的加鎖?升級?降級，那麼我們就必要找到他們的函式入口，然後在合適的位置增加列印函式my_print_ticket進行觀察，下面標示出列印位置。

1、加鎖：MDL_context::acquire_lock

bool
MDL_context::acquire_lock(MDL_request *mdl_request, ulong lock_wait_timeout)
{
  if (mdl_request->ticket) //獲取成功獲得ticket
  {
    /*
      We have managed to acquire lock without waiting.
      MDL_lock, MDL_context and MDL_request were updated
      accordingly, so we can simply return success.
    */
    //REQUESET獲取TICKET成功 此處列印
    return FALSE;
  }
  /*
    Our attempt to acquire lock without waiting has failed.
    As a result of this attempt we got MDL_ticket with m_lock
    member pointing to the corresponding MDL_lock object which
    has MDL_lock::m_rwlock write-locked.
  */
  //獲取不成功加入MDL_lock 等待佇列
  lock= ticket->m_lock;
  lock->m_waiting.add_ticket(ticket);
  will_wait_for(ticket); //死鎖檢測
  /* There is a shared or exclusive lock on the object. */
  DEBUG_SYNC(get_thd(), "mdl_acquire_lock_wait");
  find_deadlock();
  //此處列印TICKET進入了等待流程
  if (lock->needs_notification(ticket) || lock->needs_connection_check())
  {
   }
  done_waiting_for();//等待完成對死鎖檢測等待圖進行調整去掉本等待邊edge(無向圖)
  //當然到這裡也是通過等待後獲得成功了狀態為GRANTED
  DBUG_ASSERT(wait_status == MDL_wait::GRANTED);
  m_tickets[mdl_request->duration].push_front(ticket);
  mdl_request->ticket= ticket;
  MySQL_mdl_set_status(ticket->m_psi, MDL_ticket::GRANTED);
  //此處列印通過等待REQUEST獲得了TICKET
  return FALSE;
}

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

void MDL_ticket::downgrade_lock(enum_mdl_type new_type)
{
  /* Only allow downgrade from EXCLUSIVE and SHARED_NO_WRITE. */
  DBUG_ASSERT(m_type == MDL_EXCLUSIVE ||
              m_type == MDL_SHARED_NO_WRITE);
//此處列印出降級前的TICKET
  if (m_hton_notified)
  {
    MySQL_mdl_set_status(m_psi, MDL_ticket::POST_RELEASE_NOTIFY);
    m_ctx->get_owner()->notify_hton_post_release_exclusive(&m_lock->key);
    m_hton_notified= false;
    MySQL_mdl_set_status(m_psi, MDL_ticket::GRANTED);
  }
//函式結尾答應出降級後的TICKET
}

3、升級：MDL_context::upgrade_shared_lock(MDL_ticket *mdl_ticket,enum_mdl_type new_type, ulong lock_wait_timeout)

bool
MDL_context::upgrade_shared_lock(MDL_ticket *mdl_ticket,
                                 enum_mdl_type new_type,
                                 ulong lock_wait_timeout)
{
  MDL_REQUEST_INIT_BY_KEY(&mdl_new_lock_request,
                          &mdl_ticket->m_lock->key, new_type,
                          MDL_TRANSACTION);//構造一個request
 //此處列印出來的TICKET型別 
  if (acquire_lock(&mdl_new_lock_request, lock_wait_timeout)) //嘗試使用新的LOCK_TYPE進行加鎖
    DBUG_RETURN(TRUE);
  is_new_ticket= ! has_lock(mdl_svp, mdl_new_lock_request.ticket);
  lock= mdl_ticket->m_lock;
  //下面進行一系列對MDL_LOCK的維護並且對所謂的合併操作
  /* Code below assumes that we were upgrading to "obtrusive" type of lock. */
  DBUG_ASSERT(lock->is_obtrusive_lock(new_type));
  /* Merge the acquired and the original lock. @todo: move to a method. */
  MySQL_prlock_wrlock(&lock->m_rwlock);
  if (is_new_ticket)
  {
    m_tickets[MDL_TRANSACTION].remove(mdl_new_lock_request.ticket);
    MDL_ticket::destroy(mdl_new_lock_request.ticket);
  }
 //此處列印出來的升級後TICKET型別 
  DBUG_RETURN(FALSE);
}

4、釋放：略

五、常見MDL Lock型別加鎖測試

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.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：

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

所以這個MDL Lock無所不在，而只有是否相容問題，如果不相容則堵塞。scope lock的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顯然他們是scope lock ，他們的TYPE為S，那麼很顯然根據相容性原則scope lock的MDL IX和MDL S 不相容， flush tables with read lock 就會堵塞所有DELTE/UPDATE/INSERT/FOR UPDATE等DML和DDL操作，並且也會堵塞commit操作。

3、MDL_SHARED_HIGH_PRIO(SH)

這個鎖基本上大家也是經常用到只是沒感覺到而已，比如我們一般desc操作，相容矩陣如下：

操作記錄如下：

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不相容。注意持續時間為MDL_TRANSACTION 。

4、MDL_SHARED_READ(SR)

這把鎖一般用在非當前讀取的select中，相容性如下：

操作記錄如下：

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 不相容的問題(參考前面的相容矩陣)。注意持續時間為MDL_TRANSACTION 。

5、MDL_SHARED_WRITE(SW)

這把鎖一般用於DELTE/UPDATE/INSERT/FOR UPDATE等操作對table的加鎖(當前讀)，不包含DDL操作，但是要注意DML操作實際上還會有一個GLOBAL的IX的鎖，前面已經提及過了，這把鎖只是物件上的，相容性如下：

操作記錄如下：

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

注意持續時間為MDL_TRANSACTION 。

6、MDL_SHARED_WRITE_LOW_PRIO(SWL)

這把鎖很少用到原始碼註釋只有如下：

Used by DML statements modifying tables and using the LOW_PRIORITY clause

不做解釋了。

7、MDL_SHARED_UPGRADABLE(SU)

這把鎖一般在ALTER TABLE語句中會用到，他可以升級為SNW,、SNRW、X，同時至少X鎖也可以降級為SU實際上在Innodb ONLINE DDL中非常依賴它，由於它的存在那麼DML(SW)和SELECT(SR)都不會堵塞，相容性如下：

我們有必要研究一下他的相容性，可以看到 OBJECT LOCK中(SELECT)SR和(DML)SW都是允許的，而在SCOPED LOCK中雖然DML DDL都會在GLOBAL 上鎖，但是其型別都是IX。所以這個SU鎖不堵塞DML/SELECT 讀寫操作進入Innodb引擎層，它是ONLINE DDL的基礎。如果不相容你都進入不了Innodb引擎層，更談不上什麼ONLINE DDL，注意我這裡說的ALGORITHM=INPLACE的ONLINE DDL。

操作日誌記錄：

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表上的MDL Lock大概流程如下：

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降級完成(SU)到2017-08-03T19:47:00這段時間，實際上是最耗時的實際上這裡就是實際的Inplace重建，但是這個過程實際在MDL SU模式下所以不會堵塞DML/SELECT操作。這裡再給大家提個醒，所謂的ONLINE DDL只是在Inplace重建階段不堵塞DML/SELECT操作，還是儘量在資料庫壓力小的時候操作，如果有DML沒有提交或者SELECT沒有做完這個時候SW或者SR必然堵塞X，而X為高優先順序能夠堵塞所有操作。這樣導致的現象就是由於DML未提交會堵塞DDL操作，而DDL操作會堵塞所有操作，基本對於這個TABLE的表全部操作堵塞（SW堵塞X，X堵塞所有操作）。
而對於ALGORITHM=COPY 在COPY階段用的是SNW鎖，接下來我就先來看看SNW鎖。

8、MDL_SHARED_NO_WRITE(SNW)

SU可以升級為SNW而SNW可以升級為X，如前面所提及的用於ALGORITHM=COPY 中，保護資料的一致性。先看看它的相容性如下：

從相容矩陣可以看到，本鎖不會堵塞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期間只能SELECT，而不能DML。也是ALGORITHM=COPY和ALGORITHM=INPLACE的一個關鍵區別。

9、MDL_SHARED_READ_ONLY(SRO)

用於LOCK TABLES READ 語句，相容性如下：

根據相容性可以發現，堵塞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語句，相容性如下：

可以看到DML(SW)和SELECT(SR)都被它堵塞，但是還可以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

除此之外可以發現語句還需要GLOBAL和SCHEMA上的IX鎖，換句話說flush tables with read lock; 會堵塞‘lock table testsort12 write’，但是‘lock table testsort12 read’卻不會堵塞。

11、MDL_EXCLUSIVE(X)

用於各種DDL操作，實際上基本全部的DDL都會涉及到這個鎖，即便是ONLINE DDL也會在準備和提交階段獲取本鎖，因此ONLINE DDL不是完全不堵塞的，只是堵塞時間很短很短，相容性如下：

我們在驗證SU和SNW MDL Lock型別的時候已經看到了操作記錄，不做補充了。

作者微信：gp_22389860