记录-MySql备份与select的冲突

背景

      由于在做数据迁移的工作，需要对数据进行对比，于是写了一个比较复杂的sql，对数据进行对比，直接运行了一遍，由于中间有其他事情，便没有再关注该SQL.没想到MySql抽取数据到hbase的模块在当天夜里直接抽不动了，连续2天接二连三的报警电话。。。。分析下之后是我的SQL查询巨慢查询和数据库实例备份冲突，阻塞了后续抽数的SELECT。为什么我的查询语句会堵塞其他的查询呢！这次事情特记录下来

关键词

      Mysql打开表，FLUSH TABLES WITH READ LOCK,MySql表锁

MySql的备份主流程     

 在上面MySql的过程中，我们很清晰的知道所有事务和DDL，DML的语句将都被锁住 。但是我们要探讨的问题是select为什么会锁住呢？原因就是备份过程中的第二步骤要关闭表。第二步骤关闭表对象时，必须等待正在使用的表对象要关闭，倘若有一个慢查询在运行，那只能等待了。。。由于对表对象很陌生，故特地查阅了一番资料。

MySql表对象

MySql运行起来时，会创建表对象，把表对象放在缓存里。每当需要操作表时，都会实例化一个表对象，然后把表实例放入到链表里。然后等操作结束以后再把表实例放入到未使用链表中。

操作表流程

1. old_version,则进行等待
2. share->ref_count++
3. 操作完毕，检查share->ref_count--是否为0
4. 若为0,并且检查发现有新版本号，则认为cache对象需要重载
5. 将cache对象摘除，调用MDL_wait::set_status唤醒所有等待的线程

关闭表对象流程

1. 关闭所有未使用的表对象
2. 更新全局字典的版本号
3. 对于在使用的表对象，逐一检查，若表还在使用中，通过（全局版本号<> 表对象版本号识别）调用MDL_wait::timed_wait进行等待
4. 将等待对象关联到table_cache对象中
5. 继续遍历使用的表对象
6. 直到所有表都不再使用，则关闭成功。

分析原因

       在上述MySql备份流程FLUSH TABLES WITH READ LOCK时，需要关闭所有的表对象，关闭表对象时更新全局版本号，然后根据版本号<> 全局版本号来判断需要更新关闭表对象需要等待所有表对象不再使用以后，才可以完全关闭。而且MySql在关闭表时会更新全局字典的版本号。倘若此时有一个耗时很慢的SQL,那将会一直等待下去，这也是备份流程被我的SQL堵塞原因所在。

为什么备份被堵塞之后，也会堵塞后续的SELECT语句，因为打开表的流程中会判断是否存在旧版本号，如果存在旧版本号，也会堵塞SELECT语句。

相关代码与分析

以下代码和代码解析摘抄于 Mysql查询阻塞初探 - 竹 石 - 博客园 (cnblogs.com)

会话已经加了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.         其余对于备份来说，更多的是保持数据的完整与一致。原则上对于MySql的InnoDB引擎来说，只要阻止事务的提交，也就是 commit锁就可以了，不明白为什么要上读锁以及对应的关闭所有表对象。
2. MySql打开表和关闭表为什么用到版本号

表查询：

        任何查询操作都可打开表对象，当然表对象在加载过程中不可访问。此时无需用到版本号以及并发所的控制。

表结构更改:

    加表结构锁，防止其他加锁语句。关闭表对象，先提升表对象的版本，然后等待表对象释放。暂停所有打开表的操作，如果表对象版本号低于全局版本号，则需要等待。

        

为什么会出现如此之慢的SQL

1.    SQL未命中索引，且数据量大，导致查询慢。
2. 由于是线下运行，且查询的是多源从库，便没有了敬畏之心，如果是线上应用里肯定会一再谨慎。

感谢

非常感谢同事们的帮助，在报警发生的两个晚上，多个同事一起分析数据抽取失败和主从复制的延迟原因从而避免了大范围数据抽取失败的原因。非常感谢！

参考内容

Mysql查询阻塞初探 - 竹 石 - 博客园 (cnblogs.com)

FLUSH TABLE WITH READ LOCK详解 - 天士梦 - 博客园 (cnblogs.com)

《MySql 运维内参》