mysql MDL锁如何解决_MYSQL METADATA LOCK(MDL LOCK)MDL锁问题分析

一、前言

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'

调用栈帧

#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 0x0000000001516e17 in open_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 没有死锁信息。

----涉及代码: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申请，大概包含如下一些属性

/** 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. */

MDL_key key;//注意这里是一个MDL_KEY类型，主要的就是前面说的NAMESPACE+DB+OBJECT_NAME

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

可以观察到。当然这也是我主要观察的一个类

/**

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.

*/

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_