MySQL 理解MDL Lock

2024-07-24 12:32:10

字體：大中小

供稿：網(wǎng)友

MySQL 理解MDL Lock：

一、MDL Lock綜述
MySQL中MDL鎖一直是一個(gè)比較讓人比較頭疼的問題，我們談起堵塞一般更加傾向于InnoDB層的row lock（gap lock/next key lock/key lock），因?yàn)樗芎美斫庖埠芎糜^察。而對(duì)于MDL Lock考慮就少一些，因?yàn)樗鼘?shí)在不好觀察，只有出現(xiàn)問題查看show processlist的時(shí)候，可以看到簡單的所謂的‘Waiting for table metadata lock’之類的狀態(tài)，其實(shí)MDL Lock是MySQL上層一個(gè)非常復(fù)雜的子系統(tǒng)，有自己的死鎖檢測(cè)機(jī)制。
大家一般說是不是鎖表了很大一部分就和MDL Lock有關(guān)，可見的它的關(guān)鍵性和嚴(yán)重性，筆者也是根據(jù)自己的需求學(xué)習(xí)了一些，且沒有能力閱讀全部的代碼，但是筆者通過增加一個(gè)TICKET的打印函數(shù)讓語句的MDL Lock加鎖流程全部打印出來方便學(xué)習(xí)，下面從一些基礎(chǔ)概念說起然后告訴大家筆者是如何做的打印功能，最后對(duì)每種MDL TYPE可能出現(xiàn)的語句進(jìn)行測(cè)試和分析。如果大家對(duì)基本概念和增加打印函數(shù)不感興趣可直接參考第五部分語句加MDL Lock測(cè)試和分析，希望這些測(cè)試能夠幫助到大家診斷問題。

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

處于層次：MySQL層，實(shí)際上早在open_table函數(shù)中MDL LOCK就開始獲取了。
最早獲取階段: THD::enter_stage: ‘Opening tables’
調(diào)用棧幀
#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
死鎖檢測(cè)出錯(cuò)碼
{ "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
可以發(fā)現(xiàn)MDL Lock的死鎖拋錯(cuò)和Innodb死鎖一模一樣，不同的只是‘show engine innodb status’沒有死鎖信息。
二、重要數(shù)據(jù)結(jié)構(gòu)和概念
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)

第五部分會(huì)對(duì)每種類型進(jìn)行詳細(xì)的測(cè)試和解釋。

2、MDL Lock namespace
在MDL中MDL_KEY按照namespace+DB+OBJECT_NAME的方式進(jìn)行表示，所謂的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
本文我們主要對(duì)GLOBAL/SCHEMA/TABLE namespace進(jìn)行描述，而對(duì)于COMMIT namespace是提交的時(shí)候會(huì)用到的如果遇到等待，狀態(tài)為‘Waiting for commit lock’，一般為FTWRL堵塞COMMIT。可參考我的《深入理解MySQL主從原理》15節(jié)。其他namespace不做描述。

3、MDL Lock實(shí)現(xiàn)分類
scope lock:一般對(duì)應(yīng)全局MDL Lock，如flush table with read lock 會(huì)獲取namespace space：GLOBAL type：S和namespace space：COMMIT type：S的MDL Lock。它包含GLOBAL, COMMIT, TABLESPACE和SCHEMA
object lock:如其名字所示，對(duì)象級(jí)別的MDL Lock，比如TABLE級(jí)別的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兼容矩陣
MySQL：理解MDL Lock

MySQL：理解MDL Lock

這里兼容矩陣是學(xué)習(xí)鎖堵塞的重點(diǎn)，類型很多比Innodb row lock類型要多很多，不用記住，只需要遇到能知道。

5、MDL Lock duration（MDL Lock持續(xù)周期）
這個(gè)對(duì)應(yīng)源碼的enum_mdl_duration，通常我們需要關(guān)注MDL Lock是事務(wù)提交后釋放還是語句結(jié)束后釋放，實(shí)際上就是這個(gè)，這對(duì)MDL lock堵塞的范圍很重要。我直接復(fù)制源碼的解釋。

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）
使用兩種不同的方式目的在于優(yōu)化MDL Lock的實(shí)現(xiàn)，下面是源碼的注釋，可做適當(dāng)了解：

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結(jié)構(gòu)部分屬性
也就是通過語句解析后需要獲得的MDL Lock的需求，然后通過這個(gè)類對(duì)象在MDL子系統(tǒng)中進(jìn)行MDL Lock申請(qǐng)，大概包含如下一些屬性：

/** Type of metadata lock. */
  enum enum_mdl_type type; //需求的類型
  /** Duration for requested lock. */
  enum enum_mdl_duration duration; //持續(xù)周期
  /**
    Pointers for participating in the list of lock requests for this context.
  */
  MDL_request *next_in_list; //雙向鏈表實(shí)現(xiàn)
  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; //注意這里如果申請(qǐng)成功(沒有等待)，會(huì)指向一個(gè)實(shí)際的TICKET，否則為NULL
  /** A lock is requested based on a fully qualified name and type. */
8、MDL_key結(jié)構(gòu)部分屬性
就是實(shí)際的namespace+DB+OBJECT_NAME，整個(gè)放到一個(gè)char數(shù)組里面，他會(huì)在MDL_LOCK和MDL_REQUEST中出現(xiàn)。

private:
uint16m_length;
uint16m_db_name_length;
charm_ptr[MAX_MDLKEY_LENGTH];//放到了這里
9、MDL_ticket結(jié)構(gòu)部分屬性
如同門票一樣，如果獲取了MDL Lock必然給MDL_request返回一張門票，如果等待則不會(huì)分配。源碼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.
    很明顯這里指向了這個(gè)ticket的擁有者也就是MDL_context，它是線程的屬性
  */
  MDL_context *m_ctx;
  /**
    Pointer to the lock object for this lock ticket. Externally accessible.
    很明顯這里是一個(gè)指向MDL_LOCK的一個(gè)指針
  */
  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方式不會(huì)在MDL LOCK中
    占用granted位圖和鏈表取而代之代之的是一個(gè)統(tǒng)計(jì)器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結(jié)構(gòu)部分屬性
每一個(gè)MDL_key都會(huì)對(duì)應(yīng)一個(gè)MDL_lock，其中包含了所謂的GRANTED鏈表和WAIT鏈表，考慮它的復(fù)雜性，可以直接參考源碼注釋也非常詳細(xì)，這里給出我所描述的幾個(gè)屬性如下：

/** 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結(jié)構(gòu)部分屬性
這是整個(gè)MySQL線程和MDL Lock子系統(tǒng)進(jìn)行交互的一個(gè)所謂的上下文結(jié)構(gòu),其中包含了很多方法和屬性，我比較關(guān)注的屬性如下：

/**
    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.
    這是一個(gè)不同MDL lock持續(xù)時(shí)間的一個(gè)鏈表數(shù)組。實(shí)際就是
    MDL_STATEMENT一個(gè)鏈表
    MDL_TRANSACTION一個(gè)鏈表
    MDL_EXPLICIT一個(gè)鏈表
  */
Ticket_list m_tickets[MDL_DURATION_END];
//這是一個(gè)父類指針指向子類對(duì)象，虛函數(shù)重寫的典型,實(shí)際他就指向了一個(gè)線程
/*
class THD :public MDL_context_owner,
           public Query_arena,
           public Open_tables_state
*/
MDL_context_owner *m_owner;
12、所有等待狀態(tài)
源碼給出了所有的等待標(biāo)記如下：

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級(jí)別的MDL Lock，具體根據(jù)兼容矩陣判斷參考第五節(jié)。
“Waiting for global read lock”：通常就是namespace GLOBAL級(jí)別的MDL Lock，通常和flush table with read lock有關(guān)，參考第五節(jié)。
“Waiting for commit lock”：通常就是namespace COMMIT級(jí)別的MDL Lock，通常和flush table with read lock有關(guān)，參考第五節(jié)。
三、增加打印函數(shù)my_print_ticket
學(xué)習(xí)MDL Lock最好的方式當(dāng)然是獲取一條語句鎖加的所有MDL Lock，包含加鎖、升級(jí)、降級(jí)和釋放的流程。雖然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
但是對(duì)于每個(gè)語句獲取的所有MDL Lock的流程仍然不好觀察，因此我加入了打印函數(shù)：

/*p_ticket in parameter*/
int my_print_ticket(const MDL_ticket* p_ticket)
并且在mdl_ticket類中增加了這個(gè)函數(shù)原型為友元函數(shù)：

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獲取
上面這些信息都在前面進(jìn)行過描述了。具體的輸出信息如下：

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
這實(shí)際上和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
一旦有了這個(gè)函數(shù)我們只需要在加鎖、升級(jí)、降級(jí)和釋放的位置進(jìn)行適當(dāng)添加就可以了。

四、在合適的位置增加my_print_ticket打印函數(shù)
既然我們要研究MDL Lock的加鎖?升級(jí)?降級(jí)，那么我們就必要找到他們的函數(shù)入口，然后在合適的位置增加打印函數(shù)my_print_ticket進(jìn)行觀察，下面標(biāo)示出打印位置。

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 等待隊(duì)列
  lock= ticket->m_lock;
  lock->m_waiting.add_ticket(ticket);
  will_wait_for(ticket); //死鎖檢測(cè)
  /* There is a shared or exclusive lock on the object. */
  DEBUG_SYNC(get_thd(), "mdl_acquire_lock_wait");
  find_deadlock();
  //此處打印TICKET進(jìn)入了等待流程
  if (lock->needs_notification(ticket) || lock->needs_connection_check())
  {
   }
  done_waiting_for();//等待完成對(duì)死鎖檢測(cè)等待圖進(jìn)行調(diào)整去掉本等待邊edge(無向圖)
  //當(dāng)然到這里也是通過等待后獲得成功了狀態(tài)為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、降級(jí)：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);
//此處打印出降級(jí)前的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);
  }
//函數(shù)結(jié)尾答應(yīng)出降級(jí)后的TICKET
}
3、升級(jí)：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);//構(gòu)造一個(gè)request
//此處打印出來的TICKET類型
  if (acquire_lock(&mdl_new_lock_request, lock_wait_timeout)) //嘗試使用新的LOCK_TYPE進(jìn)行加鎖
    DBUG_RETURN(TRUE);
  is_new_ticket= ! has_lock(mdl_svp, mdl_new_lock_request.ticket);
  lock= mdl_ticket->m_lock;
  //下面進(jìn)行一系列對(duì)MDL_LOCK的維護(hù)并且對(duì)所謂的合并操作
  /* 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);
  }
//此處打印出來的升級(jí)后TICKET類型
  DBUG_RETURN(FALSE);
}
4、釋放：略
五、常見MDL Lock類型加鎖測(cè)試
1、MDL_INTENTION_EXCLUSIVE(IX)
這個(gè)鎖會(huì)在很多操作的時(shí)候都會(huì)出現(xiàn)，比如做任何一個(gè)DML/DDL操作都會(huì)觸發(fā)，實(shí)際上DELTE/UPDATE/INSERT/FOR UPDATE等DML操作會(huì)在GLOBAL 上加IX鎖，然后才會(huì)在本對(duì)象上加鎖。而DDL 語句至少會(huì)在GLOBAL 上加IX鎖，對(duì)象所屬 SCHEMA上加IX鎖，本對(duì)象加鎖。

下面是 DELETE 觸發(fā)的 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
我們注意一樣它的持續(xù)周期為語句級(jí)別。

下面是 ALETER 語句觸發(fā)的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
所以這個(gè)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，那么很顯然根據(jù)兼容性原則scope lock的MDL IX和MDL S 不兼容， flush tables with read lock 就會(huì)堵塞所有DELTE/UPDATE/INSERT/FOR UPDATE等DML和DDL操作，并且也會(huì)堵塞commit操作。

3、MDL_SHARED_HIGH_PRIO(SH)
這個(gè)鎖基本上大家也是經(jīng)常用到只是沒感覺到而已，比如我們一般desc操作，兼容矩陣如下：
MySQL：理解MDL Lock

操作記錄如下：

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
這中類型的優(yōu)先級(jí)比較高，但是其和X不兼容。注意持續(xù)時(shí)間為MDL_TRANSACTION 。

4、MDL_SHARED_READ(SR)
這把鎖一般用在非當(dāng)前讀取的select中，兼容性如下：
MySQL：理解MDL Lock

操作記錄如下：

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
這里還是要提及一下平時(shí)我們偶爾會(huì)出現(xiàn)select也堵住的情況(比如DDL的某個(gè)階段需要對(duì)象MDL X鎖)。我們不得不抱怨MySQL居然會(huì)堵塞select其實(shí)這里也就是object mdl lock X 和SR 不兼容的問題(參考前面的兼容矩陣)。注意持續(xù)時(shí)間為MDL_TRANSACTION 。

5、MDL_SHARED_WRITE(SW)
這把鎖一般用于DELTE/UPDATE/INSERT/FOR UPDATE等操作對(duì)table的加鎖(當(dāng)前讀)，不包含DDL操作，但是要注意DML操作實(shí)際上還會(huì)有一個(gè)GLOBAL的IX的鎖，前面已經(jīng)提及過了，這把鎖只是對(duì)象上的，兼容性如下：

MySQL：理解MDL Lock

操作記錄如下：

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 d