Mysql查询阻塞初探

第一次值班，报警打电话给我说，数据库复制延时一个多小时，那个时候是半夜啊，但我还是很清醒的起来，开机、vpn、登录、show processlist，结果发现情况是这样的：

红线框表示的是当前每个线程已经执行、等待的时间，最长的3962其实已经超过一个小时，再看其它的操作都是查询，另外还有一个线程在做flush table操作

从每个线程的状态可以看出，第一个线程为Copying to tmp table，可以看出这个线程正在做操作，这是一个查询操作。

现在的问题是数据库复制延时，那么在这个图片上面还有一个线程是在做插入操作，状态为Waiting for tables flush，时间也是3900多秒。由于当时截图只是上面一部分，所以这里说明一下。

其它的状态 都是Waiting for tables flush，嗯？所有的操作都在等待一个查询操作？难道查询会阻塞其它操作么？不能确定，这个一时半会儿没有想清楚，但现在先应该是解决问题。

 

从另一个方面，现在第一个操作时间最长，从这个方面也可以猜到应该是这个操作引起的阻塞，同时看了一下蓝色框内的用户名，看到是dm_team，我自己猜的，DM==data monitor，估计是晚上才执行的一些统计业务

根据这几点，我决定，还是杀吧，但这可不是“宁可错杀一千 不可放过一个”，杀错了有可能造成业务故障，不过通过上面三点推断，应该是这个没错。

 

所以执行下面这个操作：

kill 753037

杀了，通过show processlist, show slave status\G等命令得知，复制延迟已经在减小，当前执行的语句也不都是Waiting for tables flush状态了，看来我猜对了，问题解决

但是为什么一个查询会阻塞其它的查询呢？我想了一会儿，没有答案，还是睡觉吧 zzzz....

 

后来因为这个问题一直纠结，想想还是看看为什么一个查询会导致这么多的阻塞，但最基本的结论是，一个查询无论如何是不会阻塞的，与朋友讨论了一次，说是备份操作执行的flush table会影响到查询操作，那么这个就要细看了，至少找到一点门道，还是从源码入手，调试一把....

首先打开一个会话，执行了flush tables操作，然后开了另一个会话，执行查询操作，没有任何问题，不会阻塞，而做插入操作时，一直阻塞，但这里上面的问题中没有做update操作的，说明不是这种场景。

突然注意到，第一个查询语句执行了3962秒，而备份操作是3959秒，说明查询操作是先开始的，那么我知道了，应该先开始的是查询操作，这个查询时间比较长

构造场景：

用的调试工作是vs2010，首先通过字符串搜索功能，找到”Waiting for tables flush“的位置，它是在函数TABLE_SHARE::wait_for_old_version中的，函数内容为：

bool TABLE_SHARE::wait_for_old_version(THD *thd, struct timespec *abstime,

                                       uint deadlock_weight)

{

  MDL_context *mdl_context= &thd->mdl_context;

  Wait_for_flush ticket(mdl_context, this , deadlock_weight);

  MDL_wait::enum_wait_status wait_status;

 

  mysql_mutex_assert_owner(&LOCK_open);

  /*

    We should enter this method only when share's version is not

    up to date and the share is referenced. Otherwise our

    thread will never be woken up from wait.

  */

  DBUG_ASSERT(version != refresh_version && ref_count != 0);

 

  m_flush_tickets.push_front(&ticket);

 

  mdl_context->m_wait.reset_status();

 

  mysql_mutex_unlock(&LOCK_open);

 

  mdl_context->will_wait_for(&ticket);

 

  mdl_context->find_deadlock();

 

  wait_status= mdl_context->m_wait.timed_wait(thd, abstime, TRUE,

                                              "Waiting for table flush" );

  ....

}

 

从上面可以看出，这个是关于元数据锁的，metadata lock=MDL

那么这个时间首先执行对某一个表的查询操作，调试执行，等到加了元数据锁之后，也就是执行函数open_table_get_mdl_lock之后，再在另一个会话中执行另一个操作，操作为flush tables。

此时需要一步步调试，这样cpu才会有更多的机会被调度到去执行flush，因为此时另一个会话已经加了mdl的表锁了，锁类型当然为MDL_SHARED

执行flush操作的函数是reload_acl_and_cache中的下面一段代码：

 

{

...

      if (thd->global_read_lock.lock_global_read_lock(thd))

                 return 1;                               // Killed

      if (close_cached_tables(thd, tables,

                              ((options & REFRESH_FAST) ?  FALSE : TRUE),

                              thd->variables.lock_wait_timeout))

 ... 

}

 

首先它会获取一个全局的mdl的MDL_SHARED锁，这是可以的，因为上面加的锁与这个是兼容的，这个锁成功加上之后，接着要做的就是close_cached_tables操作了

这个函数所做的是将表缓存中所有的表都关闭并清除。

因为这个操作会清除所有表的缓存，所以执行的操作如下：

 

{

  .....

   while (found && ! thd->killed)

  {

    TABLE_SHARE *share;

    found= FALSE;

    mysql_ha_flush(thd);

    DEBUG_SYNC(thd, "after_flush_unlock" );

 

    mysql_mutex_lock(&LOCK_open);

 

    if (!tables)

    {

      for (uint idx=0 ; idx < table_def_cache.records ; idx++)//遍历每一个缓存中的表

      {

        share= (TABLE_SHARE*) my_hash_element(&table_def_cache, idx);

        if (share->has_old_version())//只要当前这个表是有版本

        {

          found= TRUE;

          break ;

        }

      }

    }

    else

    {

      for (TABLE_LIST *table= tables; table; table= table->next_local)

      {

        share= get_cached_table_share(table->db, table->table_name);

        if (share && share->has_old_version())

        {

                  found= TRUE;

          break ;

        }

      }

    }

 

    if (found)

    {

      if (share->wait_for_old_version(thd, &abstime,

                                    MDL_wait_for_subgraph::DEADLOCK_WEIGHT_DDL))

      {

        mysql_mutex_unlock(&LOCK_open);

        result= TRUE;

        goto err_with_reopen;

      }

    }

 

    mysql_mutex_unlock(&LOCK_open);

  }

....

}

 

上面的代码是将所有的table_def_cache缓存中的表，只要是有版本差别的，就会去执行share->wait_for_old_version函数，而这个函数就是上面给出的报出"Waiting for table flush"的函数。

但这里有一个前提，就是只要是有版本差别的，那么现在是不是已经有了版本差别了呢？现在可以看看share->has_old_version()函数的实现方式：

   inline bool share::has_old_version() const

  {

    return version != refresh_version;

  }

上面的version是表缓存对象share中的值，表示当前表的一个版本，而refresh_version表示的是当前数据库服务器全局的一个版本，这里只要将所有表关闭一次，那么这个值会加1，代码如下：

bool close_cached_tables(THD *thd, TABLE_LIST *tables,

                         bool wait_for_refresh, ulong timeout)

{

  bool result= FALSE;

  bool found= TRUE;

  struct timespec abstime;

  DBUG_ENTER( "close_cached_tables" );

  DBUG_ASSERT(thd || (!wait_for_refresh && !tables));

 

  mysql_mutex_lock(&LOCK_open);

  if (!tables)//如果是要关闭所有表

  {

    /*

      Force close of all open tables.

 

      Note that code in TABLE_SHARE::wait_for_old_version() assumes that

      incrementing of refresh_version and removal of unused tables and

      shares from TDC happens atomically under protection of LOCK_open,

      or putting it another way that TDC does not contain old shares

      which don't have any tables used.

    */

    refresh_version++;//这里就是将当前系统中全局版本号加1

    DBUG_PRINT( "tcache" , ("incremented global refresh_version to: %lu" ,

                          refresh_version));

    ......

  }

表缓存对象中的版本version与refresh_version的关系是，每次打开一个表，都将表的版本设置为当前refresh_version的值，所以如果没有被修改掉或者没有被全部关闭，则2个值是一样的。

那么现在可以知道，在close_cached_tables函数一进来就将系统版本加1，而当前这个表没有做任何修改，则它的版本还是1（假设），而refresh_version已经是2，所以版本是不同的。

那么现在说回来，正因为我们之前在第一个会话中正在执行一个已经加了表mdl锁的操作，所以在这里会去执行share->wait_for_old_version函数，函数体内容最上面已经给出。

 

因为表已经被第一个会话加了读锁，所以这里需要去等那个读锁被释放，然后才能关闭，所以要执行wait_status= mdl_context->m_wait.timed_wait(thd, abstime, TRUE,"Waiting for table flush" );语句。

这也就是为什么在最上面的图片中出现的第二个backupdb用户做备份的时候出现的状态信息。

 

那么这个问题已经搞清楚，flush table阻塞被阻塞，我们可以理解，因为它必须要等待第一个查询做完才行。

但下面还有更多的是查询语句，状态也是在Waiting for table flush，查询会被阻塞？为什么？

 

 

那么接着，再启动另一个会话，再执行一个查询，还是一样的，在第一个会话中慢慢的一步步的调试，让cpu有机会去做第三个会话的查询操作，等走到open_table_get_mdl_lock函数后可以慢慢看，因为这里是在获取锁

不出乎意料的是，这个元数据读锁是获得了，因为读锁是可以共享的，第一个会话已经得到了，所以第三个会话直接用就行了。

 

到这里，发现没有出现图片中的Waiting for table flush状态信息啊，继续往下走吧。。。

在函数open_table中，有下面一段代码：

     if (share->has_old_version())

    {

      /*

        We already have an MDL lock. But we have encountered an old

        version of table in the table definition cache which is possible

        when someone changes the table version directly in the cache

        without acquiring a metadata lock (e.g. this can happen during

        "rolling" FLUSH TABLE(S)).

        Release our reference to share, wait until old version of

        share goes away and then try to get new version of table share.

      */

      MDL_deadlock_handler mdl_deadlock_handler(ot_ctx);

      bool wait_result;

 

      release_table_share(share);

      mysql_mutex_unlock(&LOCK_open);

 

      thd->push_internal_handler(&mdl_deadlock_handler);

      wait_result= tdc_wait_for_old_version(thd, table_list->db,

                                            table_list->table_name,

                                            ot_ctx->get_timeout(),

                                            mdl_ticket->get_deadlock_weight());

      thd->pop_internal_handler();

  ....

 

这里判断了一次版本，哦哦哦，这里当然是有版本差别的啊，这里先将已经得到的表缓存放掉，然后再次去获取锁，通过函数tdc_wait_for_old_version实现，这个函数内容如下：

static bool

tdc_wait_for_old_version(THD *thd, const char *db, const char *table_name,

                         ulong wait_timeout, uint deadlock_weight)

{

  TABLE_SHARE *share;

  bool res= FALSE;

 

  mysql_mutex_lock(&LOCK_open);

  if ((share= get_cached_table_share(db, table_name)) &&

      share->has_old_version())

  {

    struct timespec abstime;

    set_timespec(abstime, wait_timeout);

    res= share->wait_for_old_version(thd, &abstime, deadlock_weight);

  }

  mysql_mutex_unlock(&LOCK_open);

  return res;

}

一看就明白了，现在又回到wait_for_old_version函数上面了，那一切都可以解决了。

 

总结：

1. 问题的根源不止是一个查询引起的，原来的结论不变，单一个查询无论如何不会引起查询操作阻塞，而是与一个flush table配合起来，将系统元数据版本修改之后一起产生的问题，正好最开始的查询是一个很慢的查询（mysql里面经常出现），所以才会有这样的问题，如果不杀掉，当这个查询完成，也就没事了。

2. mysql这样处理元数据锁及版本控制似乎伤及面太大，这样的问题很容易出现，因为晚上经常是做分析及备份的操作的，分析查询的话很多情况下是慢的，所以这样容易导致这个问题，所以以后最好要将备份与分析的时间段分开。

3. 有些问题很奇怪（在mysql中尤其多），同时又很难从现象层面去解决里面实现的问题，所以必须要从源码入手。

 

一直觉得mysql服务器层实现的元数据锁mdl是很复杂的，一直没有去认真看，现在通过这个问题看了一下，以后还要找时间将整个mdl部分看清楚，这个在运维工作中个人认为还是很重要的。