公眾號 MySQL學習

###一、問題來源
這是一位客戶的提供的案例如下，show processlist截圖如下：
![image.png](https://upload-images.jianshu.io/upload_images/7398834-82e9b0bbd7244fbe.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/1240)

出現這種問題除非手動干預，殺掉FTWRL的session主從方可以進行。
版本社群版5.7.26

###二、堵塞圖

如果分析上面的堵塞可以畫圖如下：

![未命名檔案 (8).png](https://upload-images.jianshu.io/upload_images/7398834-8a20c1d9b1e3c384.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/1240)

##三、關於woker執行緒w1和w3的等待

這裡我們需要重點關注引數slave_preserve_commit_order，在我將要出版的《深入理解MySQL主從原理》一書中做了詳細描述，這裡簡單說明如下：

- 這個引數是為了保證從庫group commit中的每個工作執行緒的事務提交順序和主庫事務執行的順序一致。它在order commit的flush階段前就生效。工作執行緒的事務在等待獲取自己提交許可權期間會堵塞在狀態‘Waiting for preceding transaction to commit’下。

但是我們知道在order commit的flush之前就會獲取MDL_key::COMMIT。因此這裡w1和w3工作執行緒正在等待自己提交許可權的到來，但是遺憾的是w2的事務由於不能獲取global read lock而遲遲不能提交。同時它們堵塞了FTWRL。

###四、關於FTWRL的等待

這個我也多次描述過了，FTWRL的過程大概如下：
**第一步：** 加MDL LOCK型別為GLOBAL 級別為S。如果出現等待狀態為‘Waiting for global read lock’。注意select語句不會上GLOBAL級別上鎖，但是DML/DDL/FOR UPDATE語句會上GLOBAL級別的IX鎖，IX鎖和S鎖不相容會出現這種等待。下面是這個相容矩陣：

```
          | Type of active   |
  Request |   scoped lock    |
   type   | IS(*)  IX   S  X |
 ---------+------------------+
 IS       |  +      +   +  + |
 IX       |  +      +   -  - |
 S        |  +      -   +  - |
 X        |  +      -   -  - |
```

**第二步：**推進全域性表快取版本。原始碼中就是一個全域性變數 refresh_version++。
**第三步：**釋放沒有使用的table 快取。可自行參考函式close_cached_tables函式。
**第四步：**判斷是否有正在佔用的table快取，如果有則等待，等待佔用者釋放。等待狀態為'Waiting for table flush'。這一步會去判斷table快取的版本和全域性表快取版本是否匹配，如果不匹配則等待如下：
 ```
for (uint idx=0 ; idx < table_def_cache.records ; idx++) 
      {
        share= (TABLE_SHARE*) my_hash_element(&table_def_cache, idx); //尋找整個 table cache shared hash結構
        if (share->has_old_version()) //如果版本 和 當前 的 refresh_version 版本不一致
        {
          found= TRUE;
          break; //跳出第一層查詢 是否有老版本 存在
        }
      }
...
if (found)//如果找到老版本，需要等待
    {
      /*
        The method below temporarily unlocks LOCK_open and frees
        share's memory.
      */
      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;
      }
    }
```
而等待的結束就是佔用的table快取的佔用者釋放，這個釋放操作存在於函式close_thread_table中，如下：
```
if (table->s->has_old_version() || table->needs_reopen() ||
      table_def_shutdown_in_progress)
  {
    tc->remove_table(table);//關閉 table cache instance
    mysql_mutex_lock(&LOCK_open);
    intern_close_table(table);//去掉 table cache define
    mysql_mutex_unlock(&LOCK_open);
  }
```

最終會呼叫函式MDL_wait::set_status將FTWRL喚醒，也就是說對於正在佔用的table快取釋放者不是FTWRL會話而是佔用者自己。不管怎麼樣最終整個table快取將會被清空，如果經過FTWRL後去檢視Open_table_definitions和Open_tables將會發現重新計數了。下面是喚醒函式的程式碼，也很明顯：
```
bool MDL_wait::set_status(enum_wait_status status_arg) open_table
{
  bool was_occupied= TRUE;
  mysql_mutex_lock(&m_LOCK_wait_status);
  if (m_wait_status == EMPTY)
  {
    was_occupied= FALSE;
    m_wait_status= status_arg;
    mysql_cond_signal(&m_COND_wait_status);//喚醒
  }
  mysql_mutex_unlock(&m_LOCK_wait_status);//解鎖
  return was_occupied;
}
```

**第五步：**加MDL LOCK型別COMMIT 級別為S。如果出現等待狀態為‘Waiting for commit lock’。如果有大事務的提交很可能出現這種等待。

**注意**這裡的第五步，正是因為w1和w3獲取了MDL LOCK COMMIT，而又在等待w2的事務提交因此FTWRL也不得不等待。

###五、關於woker執行緒w2的等待

這裡可能的原因有2個：
- 多執行緒並行的情況下，執行緒執行的順序本生就是不定的，很可能執行緒由於丟失CPU而落後其他執行緒的處理，因為CPU排程的最小單位是執行緒。如果保證某個共享記憶體操作的完整性需要用到mutex、原子變數、記憶體屏障等技術。
- 如果w2中的事務本生就包含了多個DML語句，那麼獲取GLOBAL READ LOCK本生就是間歇性的，也就是每個語句結束都會釋放，然後下一個語句開始的時候再次open table來獲取。

我們來看看第二點，只考慮row_format格式的binlog。

我們知道一個事務可以包含多個語句，每條語句都會包含一個map Event和多個DML Event，當本Event是語句的最後一個Event的時候會使用STMT_END_F進行標記，也正是在這個時候會釋放GLOBAL READ LOCK，原始碼有如下：
```
if (get_flags(STMT_END_F))
  {
    if((error= rows_event_stmt_cleanup(rli, thd)))

棧：
#0  MDL_context::release_lock (this=0x7fffa8000a08, duration=MDL_STATEMENT, ticket=0x7fffa800ea40) at /opt/percona-server-locks-detail-5.7.22/sql/mdl.cc:4350
#1  0x0000000001464bf1 in MDL_context::release_locks_stored_before (this=0x7fffa8000a08, duration=MDL_STATEMENT, sentinel=0x0) at /opt/percona-server-locks-detail-5.7.22/sql/mdl.cc:4521
#2  0x000000000146541b in MDL_context::release_statement_locks (this=0x7fffa8000a08) at /opt/percona-server-locks-detail-5.7.22/sql/mdl.cc:4813
#3  0x0000000001865c75 in Relay_log_info::slave_close_thread_tables (this=0x341e8b0, thd=0x7fffa8000970) at /opt/percona-server-locks-detail-5.7.22/sql/rpl_rli.cc:2014
#4  0x0000000001865873 in Relay_log_info::cleanup_context (this=0x341e8b0, thd=0x7fffa8000970, error=false) at /opt/percona-server-locks-detail-5.7.22/sql/rpl_rli.cc:1886
#5  0x00000000017e8fc7 in rows_event_stmt_cleanup (rli=0x341e8b0, thd=0x7fffa8000970) at /opt/percona-server-locks-detail-5.7.22/sql/log_event.cc:11782
#6  0x00000000017e8c79 in Rows_log_event::do_apply_event (this=0x7fffa8017dc0, rli=0x341e8b0) at /opt/percona-server-locks-detail-5.7.22/sql/log_event.cc:11660
#7  0x00000000017cfdcd in Log_event::apply_event (this=0x7fffa8017dc0, rli=0x341e8b0) at /opt/percona-server-locks-detail-5.7.22/sql/log_event.cc:3570
#8  0x00000000018476dc in apply_event_and_update_pos (ptr_ev=0x7fffec14f880, thd=0x7fffa8000970, rli=0x341e8b0) at /opt/percona-server-locks-detail-5.7.22/sql/rpl_slave.cc:4766
#9  0x0000000001848d9a in exec_relay_log_event (thd=0x7fffa8000970, rli=0x341e8b0) at /opt/percona-server-locks-detail-5.7.22/sql/rpl_slave.cc:5300
#10 0x000000000184f9cc in handle_slave_sql (arg=0x33769a0) at /opt/percona-server-locks-detail-5.7.22/sql/rpl_slave.cc:7543
(gdb) p ticket->m_lock->key.mdl_namespace()
$1 = MDL_key::GLOBAL
(gdb) p ticket->m_type
$2 = MDL_INTENTION_EXCLUSIVE
(gdb) p ticket->m_duration
$3 = MDL_STATEMENT
```
如果下一條語句開始又會重新獲取GLOBAL READ LOCK，這就是我說的間歇性獲取。

---
到這裡死鎖條件已經成熟，只要遇到這種情況就可能需要人為介入才能繼續了。

###六、關於mysqldump

社群版在如下情況下需要增加FTWRL：
- 設定了master-data
- 設定了singal-transaction和flush-logs

percona版在如下情況需要增加FTWRL：
- 設定了singal-transaction和flush-logs

我們來大概看看社群版的程式碼如下（程式碼版本8.0.21），下面是從FTWRL倒UNLOCK的過程：
```
 if ((opt_lock_all_tables || opt_master_data || //如果設定了 master data 設定flush table with read lock
       (opt_single_transaction && flush_logs)) &&//如果設定了single transaction和flush logs 設定flush table with read lock
      do_flush_tables_read_lock(mysql)) //設定flush table with read lock
    goto err;
  /*
  /*
    Flush logs before starting transaction since
    this causes implicit commit starting mysql-5.5.
  */
  if (opt_lock_all_tables || opt_master_data || 
      (opt_single_transaction && flush_logs) || opt_delete_master_logs) {
    if (flush_logs || opt_delete_master_logs) {//如果設定了 flush logs 進行日誌重新整理
      if (mysql_refresh(mysql, REFRESH_LOG)) { //進行日誌重新整理
        DB_error(mysql, "when doing refresh");
        goto err;
      }
      verbose_msg("-- main : logs flushed successfully!\n");
    }

/* Not anymore! That would not be sensible. */
    flush_logs = false;
  }

if (opt_delete_master_logs) {
    if (get_bin_log_name(mysql, bin_log_name, sizeof(bin_log_name))) goto err;
  }

if (opt_single_transaction && start_transaction(mysql)) goto err; //開啟事務 RR

/* Add 'STOP SLAVE to beginning of dump */
  if (opt_slave_apply && add_stop_slave()) goto err;

/* Process opt_set_gtid_purged and add SET @@GLOBAL.GTID_PURGED if required.
   */
  if (process_set_gtid_purged(mysql)) goto err; //設定GTID，如果設定了gtid_purged 這個函式會跳過

if (opt_master_data && do_show_master_status(mysql)) goto err; //獲取主庫binlog位置
  if (opt_slave_data && do_show_slave_status(mysql)) goto err; //slave_data 設定相關 從show slave中獲取
  if (opt_single_transaction &&
      do_unlock_tables(mysql)) /* unlock but no commit! */
    goto err;
```
percona版本中增加了判斷函式check_consistent_binlog_pos如下（不過多描述）：
```
  if (opt_single_transaction && opt_master_data)
  {
    /*
       See if we can avoid FLUSH TABLES WITH READ LOCK with Binlog_snapshot_*
       variables.
    */
    consistent_binlog_pos= check_consistent_binlog_pos(NULL, NULL);
  }

if ((opt_lock_all_tables || (opt_master_data && !consistent_binlog_pos) ||//consistent_binlog_pos 0 需要 1 不需要
       (opt_single_transaction && flush_logs)))
  {
    if (do_flush_tables_read_lock(mysql))
      goto err;
  }
```

###七、如何解決

總結如下：
- master-data 一般備份都會增加，因此只能在低峰期進行備份，儘量減少影響。
- 考慮關閉引數slave_preserve_commit_order。但是FTWRL的堵塞還是存在，但是不會產生死鎖。
- 如果壓力不大可以考慮關閉MTS。但是FTWRL的堵塞還是存在，但是不會產生死鎖。

MySQL：MTS和mysqldump死鎖

相關文章