Mysql的meta data lock 源码分析-初篇
之前有较深入的研究过mysql的innodb在行上的加锁机制，但未曾仔细研究过metadata-lock的加锁机制，猛然发现，metadata-lock的加锁机制远比dml锁复杂，瞬间崩溃。...
之前有较深入地研究过mysql的innodb 在行上的加锁机制，但未曾仔细研究过metadata-lock的机制，因为metadata-lock等待出现的场景常见的就两种：
1。Flush table 导致了meta-data lock wait.
2. 变更表结构导致了metadata lock 等待 . 类比oracle, mysql的metadata lock相似于oracle的数据字典锁。
出现metadata lock等待的现象跟原因，一般都非常直观且好理解，因此以为其实现方式也比较简单。 经过研究源码（注：版本mysql 5.7.10），作者发现之前的想法幼稚了。实际上其相关的逻辑跟数据结构比dml锁更复杂-----------瞬间崩溃。（注：这个是作者的学习笔记，学到哪里写到哪里，供各位朋友参考）。 首先我们来看一下这个metadata lock 种类的枚举类型enum_mdl_type ，总共有MDL_INTENTION_EXCLUSIVE，MDL_SHARED，MDL_SHARED_HIGH_PRIO,MDL_SHARED_READ,MDL_SHARED_WRITE, MDL_SHARED_WRITE_LOW_PRIO, MDL_SHARED_UPGRADABLE, MDL_SHARED_READ_ONLY, MDL_SHARED_NO_WRITE,MDL_SHARED_NO_READ_WRITE,MDL_EXCLUSIVE 总共10种--------居然有这么多？ 比dml锁的类型多很多吧。
下面是源码中enum_mdl_type的定义以及注释，请各位朋友仔细查看各种锁类型的作用-----估计会比较晕。 晕，没有关系，我们先了解个大概，知道整个meta-data lock锁实现的原理，届时遇到真正出现无法定位的meta-data-lock等待的问题时，再具体问题再具体分析。
.............................省略代码部分....................................................................................
上面是整个meta data lock 类型的枚举数据结构。 跳跃一下，下面是作者在进行调式时，设置的一些重要的函数断点。 b MDL_context::upgrade_shared_lock thread 3 b MDL_context::acquire_locks thread 3b MDL_context::owns_equal_or_stronger_lock thread 3b MDL_context::find_lock_owner thread 3b MDL_ticket::get_key thread 3 b MDL_key::init_psi_keys thread 3b MDL_lock::create thread 3 b MDL_context::acquire_lock thread 3b MDL_context::try_acquire_lock_impl thread 3b MDL_lock::get_incompatible_waiting_types_bitmap_idx thread 3b MDL_lock::can_grant_lock thread 3b MDL_ticket::has_stronger_or_equal_type thread 3(gdb) p m_strategy->m_waiting_incompatible[m_current_waiting_incompatible_idx] $30 = {0, 1024, 0, 1536, 1792, 1920, 1024, 1552, 1024, 1024, 0}(gdb) p m_strategy->m_granted_incompatible $38 = {0, 1024, 1024, 1536, 1920, 1920, 1856, 1584, 1904, 2040, 2046}
下面这个函数就是用来检查是否可以授予申请的enum_mdl_type 类型的meta-data 锁。 bool MDL_ticket::is_incompatible_when_granted(enum_mdl_type type) const {return (MDL_BIT(m_type) &
m_lock->incompatible_granted_types_bitmap()[type]); }
继续回到文章的常规上下文。。 最开始我们介绍了meta data lock的类型， 我们申请锁时，需要验证各种类型的锁之间是否兼容，代码层面是如何实现的呢？ 实际上，就是通过位图来表示每一种类型与其他类型的兼容型，两个类型的锁的兼容位图取&， 如果得到的值为非0，则冲突。 因此，当某种锁类型的兼容位图越多位为1，则跟越多的其他的类型的锁冲突。 例如，MDL_SHARED 类型的锁，其冲突位图为MDL_BIT(MDL_EXCLUSIVE)， 通过前面的枚举类型我们知道，MDL_EXCLUSIVE 排在第10位，则其冲突位图为1000000000，仅仅第10位为1， 也就是仅仅跟MDL_EXCLUSIVE 冲突。 相反， MDL_EXCLUSIVE 类型的锁的冲突位图如下，取与后的值为1111111111， 所有位都1， 表示跟上述任何类型的锁冲突。 MDL_BIT(MDL_EXCLUSIVE) | MDL_BIT(MDL_SHARED_NO_READ_WRITE) | MDL_BIT(MDL_SHARED_NO_WRITE) | MDL_BIT(MDL_SHARED_READ_ONLY) | MDL_BIT(MDL_SHARED_UPGRADABLE) | MDL_BIT(MDL_SHARED_WRITE_LOW_PRIO) | MDL_BIT(MDL_SHARED_WRITE) | MDL_BIT(MDL_SHARED_READ) | MDL_BIT(MDL_SHARED_HIGH_PRIO) | MDL_BIT(MDL_SHARED) 下面是源码， 请查看代码以及相关的注释。 特别说明一下，MDL_lock::m_object_lock_strategy冲突数组包含好几组，其中最容易被感知的两组：一组表示申请的锁类型跟已经授权的类型是否冲突 ，另外一组是申请的类型是否跟等待中的锁类型是否冲突。 还有其他的数组，这些另外再作说明，有兴趣的朋友可以先看一下注释。 经过简单的了解之后，发现meta-data lock 的锁机制还真心比dml锁复杂，光冲突数据就有怎么多组，真晕。 第一组： 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 | - - - - - - - - - - |
第二组：
Request | Pending requests for lock |
type | S SH SR SW SWLP SU SRO SNW SNRW X |
----------+--------------------------------------------+
S | + + + + + + + + + - |
SH | + + + + + + + + + + |
SR | + + + + + + + + - - |
SW | + + + + + + + - - - |
SWLP | + + + + + + - - - - |
SU | + + + + + + + + + - |
SRO | + + + - + + + + - - |
SNW | + + + + + + + + + - |
SNRW | + + + + + + + + + - |
X | + + + + + + + + + + |
+表示兼容 -表示不兼容
上面说明的，仅仅是对象类的metadata lock 的兼容数组，此外，还有范围类的metadata lock, 范围类的metadata lock的类型有global, commit,tablespace, schema namespaces.
其兼容矩阵跟对象类型的metadata lock完全不一样。有兴趣的朋友请查看源代码中的const MDL_lock::MDL_lock_strategy MDL_lock::m_scoped_lock_strategy 的结构。
。。。。。。。。。。。。。。。。。。
点到为止， 有兴趣的朋友自己研究，写不下去了。。。。。。。。，太复杂了。
最后扩展一个比较有用的实例。 大家都知道从mysql 5.6 开始，支持在线的ddl , 那请问，是不是ddl与dml 是完全没有干扰的呢？ 这个答案是否定的，一定有干扰，但在什么时候会干扰，为啥？
以 alter table unlockt drop e; sql 为例。下面是跟踪的信息，看不懂直接忽略过，看作者文字。
$44 = {type = MDL_INTENTION_EXCLUSIVE, duration = MDL_STATEMENT, next_in_list = 0x7f42e8006670, prev_in_list = 0x7f439eeec278, ticket = 0x0, ke
$45 = {type = MDL_INTENTION_EXCLUSIVE, duration = MDL_TRANSACTION, next_in_list = 0x7f42e8006430
$47 = {type = MDL_SHARED_UPGRADABLE, duration = MDL_TRANSACTION, next_in_list = 0x0, prev_in_list = 0x7f42e8006678
MDL_context::upgrade_shared_lock (this=0x7f42e8000c08, mdl_ticket=0x7f42e80116e0, new_type=MDL_EXCLUSIVE, lock_wait_timeout=31536000)
(gdb) bt
#0 MDL_context::upgrade_shared_lock (this=0x7f42e8000c08, mdl_ticket=0x7f42e80116e0, new_type=MDL_EXCLUSIVE, lock_wait_timeout=31536000)
at /data/mysql/mysql-5.7.10/sql/mdl.cc:3784
#1 0x0000000001568797 in mysql_inplace_alter_table (thd=0x7f42e8000b70, table_list=0x7f42e8006090, table=0x7f42e89aee80, altered_table=0x7f42e893aff0,
ha_alter_info=0x7f439eeeca10, inplace_supported=HA_ALTER_INPLACE_NO_LOCK_AFTER_PREPARE, target_mdl_request=0x7f439eeec450, alter_ctx=0x7f439eeecfa0)
at /data/mysql/mysql-5.7.10/sql/sql_table.cc:7195
#2 0x000000000156d993 in mysql_alter_table (thd=0x7f42e8000b70, new_db=0x7f42e8006618 "xcytest", new_name=0x0, create_info=0x7f439eeede30, table_list=0x7f42e8006090,
alter_info=0x7f439eeedd80) at /data/mysql/mysql-5.7.10/sql/sql_table.cc:9383
#3 0x00000000016c3623 in Sql_cmd_alter_table::execute (this=0x7f42e8006648, thd=0x7f42e8000b70) at /data/mysql/mysql-5.7.10/sql/sql_alter.cc:316
#4 0x00000000014ddc79 in mysql_execute_command (thd=0x7f42e8000b70, first_level=true) at /data/mysql/mysql-5.7.10/sql/sql_parse.cc:4796
#5 0x00000000014df9b0 in mysql_parse (thd=0x7f42e8000b70, parser_state=0x7f439eeef6a0) at /data/mysql/mysql-5.7.10/sql/sql_parse.cc:5508
#6 0x00000000014d5468 in dispatch_command (thd=0x7f42e8000b70, com_data=0x7f439eeefdf0, command=COM_QUERY) at /data/mysql/mysql-5.7.10/sql/sql_parse.cc:1425
#7 0x00000000014d43fd in do_command (thd=0x7f42e8000b70) at /data/mysql/mysql-5.7.10/sql/sql_parse.cc:993
#8 0x00000000015feadf in handle_connection (arg=0x73ee250) at /data/mysql/mysql-5.7.10/sql/conn_handler/connection_handler_per_thread.cc:301
#9 0x0000000001c24b20 in pfs_spawn_thread (arg=0x7635a90) at /data/mysql/mysql-5.7.10/storage/perfschema/pfs.cc:2192
#10 0x00007f4438366df5 in start_thread () from /lib64/libpthread.so.0
#11 0x00007f44372321ad in clone () from /lib64/libc.so.6
发现有通过函数MDL_context::upgrade_shared_lock 将锁升级的过程，将MDL_SHARED_UPGRADABLE 升级为MDL_EXCLUSIVE 锁。之后又有降级的过程。
Breakpoint 29, MDL_ticket::has_stronger_or_equal_type (this=0x7f42e80116e0, type=MDL_SHARED_UPGRADABLE) at /data/mysql/mysql-5.7.10/sql/mdl.cc:2706
2706 granted_incompat_map= m_lock->incompatible_granted_types_bitmap();
(gdb) bt
#0 MDL_ticket::has_stronger_or_equal_type (this=0x7f42e80116e0, type=MDL_SHARED_UPGRADABLE) at /data/mysql/mysql-5.7.10/sql/mdl.cc:2706
#1 0x00000000013f39f3 in MDL_ticket::downgrade_lock (this=0x7f42e80116e0, new_type=MDL_SHARED_UPGRADABLE) at /data/mysql/mysql-5.7.10/sql/mdl.cc:4357
#2 0x0000000001568b5a in mysql_inplace_alter_table (thd=0x7f42e8000b70, table_list=0x7f42e8006090, table=0x7f42e89aee80, altered_table=0x7f42e893aff0,
ha_alter_info=0x7f439eeeca10, inplace_supported=HA_ALTER_INPLACE_NO_LOCK_AFTER_PREPARE, target_mdl_request=0x7f439eeec450, alter_ctx=0x7f439eeecfa0)
at /data/mysql/mysql-5.7.10/sql/sql_table.cc:7304
#3 0x000000000156d993 in mysql_alter_table (thd=0x7f42e8000b70, new_db=0x7f42e8006618 "xcytest", new_name=0x0, create_info=0x7f439eeede30, table_list=0x7f42e8006090,
alter_info=0x7f439eeedd80) at /data/mysql/mysql-5.7.10/sql/sql_table.cc:9383
#4 0x00000000016c3623 in Sql_cmd_alter_table::execute (this=0x7f42e8006648, thd=0x7f42e8000b70) at /data/mysql/mysql-5.7.10/sql/sql_alter.cc:316
#5 0x00000000014ddc79 in mysql_execute_command (thd=0x7f42e8000b70, first_level=true) at /data/mysql/mysql-5.7.10/sql/sql_parse.cc:4796
#6 0x00000000014df9b0 in mysql_parse (thd=0x7f42e8000b70, parser_state=0x7f439eeef6a0) at /data/mysql/mysql-5.7.10/sql/sql_parse.cc:5508
#7 0x00000000014d5468 in dispatch_command (thd=0x7f42e8000b70, com_data=0x7f439eeefdf0, command=COM_QUERY) at /data/mysql/mysql-5.7.10/sql/sql_parse.cc:1425
#8 0x00000000014d43fd in do_command (thd=0x7f42e8000b70) at /data/mysql/mysql-5.7.10/sql/sql_parse.cc:993
#9 0x00000000015feadf in handle_connection (arg=0x73ee250) at /data/mysql/mysql-5.7.10/sql/conn_handler/connection_handler_per_thread.cc:301
#10 0x0000000001c24b20 in pfs_spawn_thread (arg=0x7635a90) at /data/mysql/mysql-5.7.10/storage/perfschema/pfs.cc:2192
#11 0x00007f4438366df5 in start_thread () from /lib64/libpthread.so.0
#12 0x00007f44372321ad in clone () from /lib64/libc.so.6
通过函数MDL_ticket::downgrade_lock 将MDL_EXCLUSIVE降级为MDL_SHARED_UPGRADABLE，但在最后又升级为MDL_EXCLUSIVE
Breakpoint 29, MDL_ticket::has_stronger_or_equal_type (this=0x7f42e80116e0, type=MDL_EXCLUSIVE) at /data/mysql/mysql-5.7.10/sql/mdl.cc:2706
2706 granted_incompat_map= m_lock->incompatible_granted_types_bitmap();
(gdb) bt
#0 MDL_ticket::has_stronger_or_equal_type (this=0x7f42e80116e0, type=MDL_EXCLUSIVE) at /data/mysql/mysql-5.7.10/sql/mdl.cc:2706
#1 0x00000000013f2c46 in MDL_context::upgrade_shared_lock (this=0x7f42e8000c08, mdl_ticket=0x7f42e80116e0, new_type=MDL_EXCLUSIVE, lock_wait_timeout=31536000)
at /data/mysql/mysql-5.7.10/sql/mdl.cc:3781
#2 0x0000000001453296 in wait_while_table_is_used (thd=0x7f42e8000b70, table=0x7f42e89aee80, function=HA_EXTRA_PREPARE_FOR_RENAME)
at /data/mysql/mysql-5.7.10/sql/sql_base.cc:2515
#3 0x0000000001568c0b in mysql_inplace_alter_table (thd=0x7f42e8000b70, table_list=0x7f42e8006090, table=0x7f42e89aee80, altered_table=0x7f42e893aff0,
ha_alter_info=0x7f439eeeca10, inplace_supported=HA_ALTER_INPLACE_NO_LOCK_AFTER_PREPARE, target_mdl_request=0x7f439eeec450, alter_ctx=0x7f439eeecfa0)
at /data/mysql/mysql-5.7.10/sql/sql_table.cc:7318
#4 0x000000000156d993 in mysql_alter_table (thd=0x7f42e8000b70, new_db=0x7f42e8006618 "xcytest", new_name=0x0, create_info=0x7f439eeede30, table_list=0x7f42e8006090,
alter_info=0x7f439eeedd80) at /data/mysql/mysql-5.7.10/sql/sql_table.cc:9383
#5 0x00000000016c3623 in Sql_cmd_alter_table::execute (this=0x7f42e8006648, thd=0x7f42e8000b70) at /data/mysql/mysql-5.7.10/sql/sql_alter.cc:316
#6 0x00000000014ddc79 in mysql_execute_command (thd=0x7f42e8000b70, first_level=true) at /data/mysql/mysql-5.7.10/sql/sql_parse.cc:4796
#7 0x00000000014df9b0 in mysql_parse (thd=0x7f42e8000b70, parser_state=0x7f439eeef6a0) at /data/mysql/mysql-5.7.10/sql/sql_parse.cc:5508
#8 0x00000000014d5468 in dispatch_command (thd=0x7f42e8000b70, com_data=0x7f439eeefdf0, command=COM_QUERY) at /data/mysql/mysql-5.7.10/sql/sql_parse.cc:1425
#9 0x00000000014d43fd in do_command (thd=0x7f42e8000b70) at /data/mysql/mysql-5.7.10/sql/sql_parse.cc:993
#10 0x00000000015feadf in handle_connection (arg=0x73ee250) at /data/mysql/mysql-5.7.10/sql/conn_handler/connection_handler_per_thread.cc:301
#11 0x0000000001c24b20 in pfs_spawn_thread (arg=0x7635a90) at /data/mysql/mysql-5.7.10/storage/perfschema/pfs.cc:2192
#12 0x00007f4438366df5 in start_thread () from /lib64/libpthread.so.0
#13 0x00007f44372321ad in clone () from /lib64/libc.so.6
因此，在升级为MDL_EXCLUISVE的过程中，容易造成metadata lock wait 现象。
关注个人公众号，或者访问http://www.xuchunyang.com ，及时获取作者分享的纯干货原创文章。
关注 数据库随笔
微信扫一扫关注公众号
