数据库内核月报－ 2017/09 - MySQL · 特性分析 · 利用gdb跟踪MDL加锁过程 - 《数据库内核月报》

MDL锁的简单示例

master：未提交的事务，但SQL已经完成(binlog也准备好了)，表schema发生更改，在commit的时候不会被察觉到.

slave：在binlog里是以事务提交顺序记录的，DDL隐式提交，因此在备库先执行DDL，后执行事务trx，由于trx作用的表已经发生了改变，因此trx会执行失败。在DDL时的主库DML压力越大，这个问题触发的可能性就越高

在5.5引入了MDL（meta data lock）锁来解决在这个问题

metadata lock也是一种锁。每个metadata lock都会定义锁住的对象，锁的持有时间和锁的类型

这些锁具有以下层级关系

会话1:

再开启第二个会话,利用gdb来跟踪mysql加MDL的过程会话2：


root      3336  2390  0 06:33 pts/2    00:00:01 /u02/mysql/bin/mysqld --basedir=/u02/mysql/ --datadir=/u02/mysql/data 
--plugin-dir=/u02/mysql//lib/plugin --user=root 
--log-error=/u02/mysql/tmp/error1.log --open-files-limit=10240 
--pid-file=/u02/mysql/tmp/mysql.pid 
--socket=/u02/mysql/tmp/mysql.sock --port=3306
[root@localhost mysql]# gdb -p 3336
----在GDB设置以下断点
(gdb) b MDL_context::acquire_lock
Breakpoint 1 at 0x730cab: file /u02/mysql-server-5.6/sql/mdl.cc, line 2187.
(gdb) b lock_rec_lock
Breakpoint 2 at 0xb5ef50: file /u02/mysql-server-5.6/storage/innobase/lock/lock0lock.cc, line 2296.
(gdb) c
Continuing.....

开启第三个会话

mysql> alter table ti stats_auto_recalc=1;
这个操作被hang住

在会话2中执行下面的操作

从上面的输出中，我只能看到申请了一个语句级别的MDL_INTENTION_EXCLUSIVE。并没有看到什么其他有意义的信息。我们继续gdb跟踪

(gdb) p *(mdl_request->next_in_list)
$3 = {type = MDL_INTENTION_EXCLUSIVE, duration = MDL_TRANSACTION, next_in_list = 0x7f697002a388, prev_in_list = 0x7f697d1c3bd8, ticket = 0x0, key = {m_length = 7, m_db_name_length = 4,
    m_ptr = "\001test\000\000\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217", 
static m_namespace_to_wait_state_name = {
{m_key = 101,
        m_name = 0xf125a2 "Waiting for global read lock", m_flags = 0}, 
    m_name = 0xf125c0 "Waiting for schema metadata lock", m_flags = 0}, 
        m_name = 0xf125e8 "Waiting for table metadata lock", m_flags = 0}, 
{m_key = 104, 
    m_name = 0xf12608 "Waiting for stored function metadata lock", m_flags = 0}, 
{m_key = 105,
        m_name = 0xf12638 "Waiting for stored procedure metadata lock", m_flags = 0}, 
{m_key = 106, 
    m_name = 0xf12668 "Waiting for trigger metadata lock", m_flags = 0}, 
{m_key = 107,
        m_name = 0xf12690 "Waiting for event metadata lock", m_flags = 0}, 
{m_key = 108, 
    m_name = 0xf126b0 "Waiting for commit lock", m_flags = 0}}}}

$4 = {type = MDL_SHARED_UPGRADABLE, duration = MDL_TRANSACTION, next_in_list = 0x0, prev_in_list = 0x7f697002a568, ticket = 0x0, key = {m_length = 9, m_db_name_length = 4,
    m_ptr = "\002test\000ti", '\000' <repeats 378 times>, 
static m_namespace_to_wait_state_name = {
{m_key = 101, 
    m_name = 0xf125a2 "Waiting for global read lock", m_flags = 0}, 
{m_key = 102,
    m_name = 0xf125c0 "Waiting for schema metadata lock", m_flags = 0}, 
{m_key = 103, 
    m_name = 0xf125e8 "Waiting for table metadata lock", m_flags = 0}, 
{m_key = 104,
        m_name = 0xf12608 "Waiting for stored function metadata lock", m_flags = 0}, 
{m_key = 105, 
    m_name = 0xf12638 "Waiting for stored procedure metadata lock", m_flags = 0}, 
{m_key = 106,
{m_key = 107, 
    m_name = 0xf12690 "Waiting for event metadata lock", m_flags = 0}, 
{m_key = 108,
        m_name = 0xf126b0 "Waiting for commit lock", m_flags = 0}}}}

从上面的输出中，我们可以看出最终是要在test数据库的ti对象上加一把MDL_SHARED_UPGRADABLE锁。在做DDL时会先加MDL_SHARED_UPGRADABLE锁，然后升级到MDL_EXCLUSIVE锁

我来执行下面的过程会话1

会话2

(gdb) p *mdl_request
$5 = {type = MDL_EXCLUSIVE, duration = MDL_TRANSACTION, next_in_list = 0x20302000000, prev_in_list = 0x200000001, ticket = 0x0, key = {m_length = 9, m_db_name_length = 4,
    m_ptr = "\002test\000ti\000\000\000\000@\031\220\003\000\000\000\000\333\361\254\000\000\000\000\000\260<\034}i\177\000\000\302\362\254\000\000\000\000\000\300<\034}i\177\000\000\060|\002pi\177\000\000\320<\034}i\177\000\000\360\236\344\000\000\000\000\000\000\t\000pi\177\000\000(}\002pi\177\000\000\360<\034}i\177\000\000\234\312\344\000\000\000\000\000H\245\002pi\177\000\000\333\361\254\000\000\000\000\000\023\360\000\001", '\000' <repeats 12 times>, "`S\005pi\177\000\000\060|\002p\000\000\001\000\060=\034}i\177\000\000>\240\344\000\000\000\000\000\000\t\000pi\177\000\000\000\t\000pi\177\000\000\200=\034}i\177\000\000\231\310\344\000\000\000\000\000\240=\034}i\177\000\000l-d0t\b\000\000H\344\000\001\000\000\000\000\023\360\000\001\000\000\000\000\226"..., 
static m_namespace_to_wait_state_name = {
{m_key = 101, 
    m_name = 0xf125a2 "Waiting for global read lock", m_flags = 0}, 
{m_key = 102, 
    m_name = 0xf125c0 "Waiting for schema metadata lock", m_flags = 0}, 
{m_key = 103,
        m_name = 0xf125e8 "Waiting for table metadata lock", m_flags = 0}, 
{m_key = 104, 
    m_name = 0xf12608 "Waiting for stored function metadata lock", m_flags = 0}, 
{m_key = 105,
        m_name = 0xf12638 "Waiting for stored procedure metadata lock", m_flags = 0}, 
{m_key = 106, 
    m_name = 0xf12668 "Waiting for trigger metadata lock", m_flags = 0}, 
{m_key = 107,
        m_name = 0xf12690 "Waiting for event metadata lock", m_flags = 0}, 
{m_key = 108, 
    m_name = 0xf126b0 "Waiting for commit lock", m_flags = 0}}}}

从上面的输出中，我们看到了最终是在test.ti上申请了事务级别的MDL_EXCLUSIVE锁。

会话3

mysql> alter table ti stats_auto_recalc=1;
Records: 0  Duplicates: 0  Warnings: 0