您现在的位置是：网站首页> 编程资料编程资料
MYSQL METADATA LOCK(MDL LOCK) 理论及加锁类型测试_Mysql_
2023-05-27
381人已围观
简介 MYSQL METADATA LOCK(MDL LOCK) 理论及加锁类型测试_Mysql_
MYSQL METADATA LOCK(MDL LOCK)学习 理论知识和加锁类型测试
前言：
源码版本: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'
调用栈帧：
#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:Sobject 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
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
如同门票一样，如果获取了M
