GreatSQL 的刷新鎖前言因為運維小伙伴執行dump備份命令，導致資料庫卡住，很多會話都在waiting for table flush，基於這一故障，我對GreatSQL的刷新鎖進行了研究。感興趣的小伙伴請隨我一探究竟吧。刷新鎖的癥狀刷新鎖問題的主要癥狀是資料庫會進入嘎然而止的狀態，所 ...

GreatSQL 的刷新鎖

file

前言

因為運維小伙伴執行dump備份命令，導致資料庫卡住，很多會話都在waiting for table flush，基於這一故障，我對GreatSQL的刷新鎖進行了研究。感興趣的小伙伴請隨我一探究竟吧。

刷新鎖的癥狀

刷新鎖問題的主要癥狀是資料庫會進入嘎然而止的狀態，所有需要使用部分或全部表的新查詢都停下來等待刷新鎖。要尋找的信號如下：

1.新查詢的查詢狀態為Waiting for table flush。這可能出現在所有新查詢中，也可能只出現在訪問特定表的新查詢中。

2.資料庫連接數增多，最終可能由於連接數用盡，新連接失敗。

3.至少有一個查詢的運行時間晚於最早的刷新鎖請求。

4.進程列表中可能有flush table語句，也可能flush table語句已經超時（超過lock_wait_timeout設置）或被取消(會話被Ctr +C 終止或被kill)。

刷新鎖構建

本實驗使用的GreatSQL版本： 8.0.32-25 GreatSQL (GPL)。

創建四個連接，第一個連接執行一個慢查詢，第二個連接用於執行flush tables語句，第三個連接執行第一個連接中慢查詢語句相關表的快速查詢。第四個連接執行其他表的查詢和插入：

Connection 1> select count(*) ,sleep(100) from t1;
Connection 2> flush tables;   (flush tables with read lock;)
Connection 3> select count(*) from t1;
Connection 4> select count(*) from t2;
             insert into t2 values(5,'a');

刷新鎖爭用問題診斷及解決

1.flush tables 實驗

Connection 2 執行flush tables 時，Connection 3 受阻塞，Connection 4成功執行。

使用sys.session視圖來輸出各會話運行情況，也可以使用show processlist 來展示。預設輸出是按執行時間降序排列，這讓查詢刷新鎖爭用之類的問題變得容易。

[root@GreatSQL][test]>select thd_id,conn_id,state,current_statement,statement_latency from sys.session where command='Query';
+--------+---------+-------------------------+-------------------------------------------------------------------+-------------------+
| thd_id | conn_id | state                   | current_statement                                                 | statement_latency |
+--------+---------+-------------------------+-------------------------------------------------------------------+-------------------+
|    116 |      61 | User sleep              | select count(*),sleep(100) from t1                                | 10.81 s           |
|    117 |      62 | Waiting for table flush | flush tables                                                      | 8.15 s            |
|    109 |      57 | Waiting for table flush | select count(*) from t1                                           | 3.91 s            |
|    118 |      63 | NULL                    | select thd_id,conn_id,state,cu ... .session where command='Query' | 71.49 ms          |
+--------+---------+-------------------------+-------------------------------------------------------------------+-------------------+
4 rows in set (0.07 sec)

從上面會話的查詢結果可以看出，flush tables會話的狀態是Waiting for table flush，在它之前有一個運行時間較長的查詢，這是阻塞flush tables完成的查詢。第三個查詢的狀態也是Waiting for table flush，說明flush tables語句又阻塞了其他查詢。

而Connection4 未受影響，說明flush tables不影響其他表的讀寫操作。

當等待刷新鎖成為問題時，這意味著有一條或多條查詢阻塞了flush tables 語句獲得刷新鎖。由於flush tables語句需要一個排他鎖，因此又會阻塞後續會話對相關表的共用鎖或排他鎖。

手動Ctr+C中斷flush tables 會話後，再次查詢各會話的運行情況。

[root@GreatSQL][test]>select thd_id,conn_id,state,current_statement,statement_latency from sys.session where command='Query';
+--------+---------+-------------------------+-------------------------------------------------------------------+-------------------+
| thd_id | conn_id | state                   | current_statement                                                 | statement_latency |
+--------+---------+-------------------------+-------------------------------------------------------------------+-------------------+
|    116 |      61 | User sleep              | select count(*),sleep(100) from t1                                | 20.10 s           |
|    109 |      57 | Waiting for table flush | select count(*) from t1                                           | 13.19 s           |
|    118 |      63 | NULL                    | select thd_id,conn_id,state,cu ... .session where command='Query' | 68.14 ms          |
+--------+---------+-------------------------+-------------------------------------------------------------------+-------------------+
3 rows in set (0.07 sec)

從查詢結果可以看出，被flush table阻塞的查詢依然還被阻塞著，這時候解決問題的辦法就是結束第一個阻塞flush tables會話的慢查詢。

2.flush table with read lock實驗

Connection2 執行flush table with read lock 時，Connection 3 受阻塞，Connect 4的select成功，insert 被阻塞。

使用sys.session視圖來輸出各會話運行情況

[root@GreatSQL][test]>select thd_id,conn_id,state,current_statement,statement_latency from sys.session where command='Query';
+--------+---------+------------------------------+-------------------------------------------------------------------+-------------------+
| thd_id | conn_id | state                        | current_statement                                                 | statement_latency |
+--------+---------+------------------------------+-------------------------------------------------------------------+-------------------+
|    116 |      61 | User sleep                   | select count(*),sleep(100) from t1                                | 52.74 s           |
|    117 |      62 | Waiting for table flush      | flush table with read lock                                        | 26.36 s           |
|    109 |      57 | Waiting for table flush      | select count(*) from t1                                           | 22.00 s           |
|    124 |      69 | Waiting for global read lock | insert into t2 values(8,'b')                                      | 6.01 s            |
|    118 |      63 | NULL                         | select thd_id,conn_id,state,cu ... .session where command='Query' | 82.90 ms          |
+--------+---------+------------------------------+-------------------------------------------------------------------+-------------------+
5 rows in set (0.09 sec)

從上面結果看出， flush table with read lock 的會話狀態為Waiting for table flush，Connection3 狀態同樣為Waiting for table flush，而Connect4的狀態為Waiting for global read lock。

手動Ctr+C中斷flush tables 會話後，再次查詢各會話的運行情況。

[root@GreatSQL][test]>select thd_id,conn_id,state,current_statement,statement_latency from sys.session where command='Query';
+--------+---------+------------------------------+-------------------------------------------------------------------+-------------------+
| thd_id | conn_id | state                        | current_statement                                                 | statement_latency |
+--------+---------+------------------------------+-------------------------------------------------------------------+-------------------+
|    116 |      61 | User sleep                   | select count(*),sleep(100) from t1                                | 1.37 min          |
|    109 |      57 | Waiting for table flush      | select count(*) from t1                                           | 51.58 s           |
|    124 |      69 | Waiting for global read lock | insert into t2 values(8,'b')                                      | 35.57 s           |
|    118 |      63 | NULL                         | select thd_id,conn_id,state,cu ... .session where command='Query' | 65.26 ms          |
+--------+---------+------------------------------+-------------------------------------------------------------------+-------------------+
4 rows in set (0.06 sec)

發現Connection 3，Connection4 仍然受到阻塞。

Connection 1 查詢結束後查詢各會話運行情況

[root@GreatSQL][test]>select thd_id,conn_id,state,current_statement,statement_latency from sys.session where command='Query';
+--------+---------+------------------------------+-------------------------------------------------------------------+-------------------+
| thd_id | conn_id | state                        | current_statement                                                 | statement_latency |
+--------+---------+------------------------------+-------------------------------------------------------------------+-------------------+
|    124 |      69 | Waiting for global read lock | insert into t2 values(8,'b')                                      | 57.44 s           |
|    118 |      63 | executing                    | select thd_id,conn_id,state,cu ... .session where command='Query' | 2.06 ms           |
+--------+---------+------------------------------+-------------------------------------------------------------------+-------------------+
2 rows in set (0.06 sec)

Connection 3 成功執行，Connection 4的insert 仍然受到阻塞。

顯示執行unlock tables 命令後，Connection4的insert才執行完成。

實驗結論：

由上面兩個實驗得出，診斷刷新鎖爭用的問題時，只要有會話處於 Waiting for table flush狀態，說明曾發生過刷新表的操作，無論當前能否看到flush tables的相關會話，而通常處於Waiting for table flush狀態的會話之前發生的慢查詢都有可能是造成後續阻塞的原因。

flush tables with read lock語句要獲取的全局讀鎖，在等待獲取鎖時，癥狀與flush tables語句差不多，不同的是：

1.flush tables with read lock等待獲取鎖及得到鎖之後，都會阻止所有表的寫入，而flush tables只是在執行過程中持有鎖，它不會阻止長查詢之外的其他表寫操作

2.flush tables with read lock需要通過unlock tables 顯示釋放鎖，而flush tables不需要。

為什麼flush table或者flush tables with read lock 會話都結束了，後續的查詢還是會被阻塞呢？

這是低版本表定義緩存（TDC）的原因，這兩條命令都會close all open tables，將表版本推高（refresh_version +1）, 但因為長查詢線程的存在，導致舊表無法被close，在訪問舊表時都會認為是舊版本，等待 TABLE cache flush，而refresh_version 的推高是不可逆的結果，也就是說即使發出flush table或flush tables with read lock 的會話中斷了，但是實際產生的 TABLE flush 的效果還是存在的。

另外這個癥狀與隔離級別關係不大，筆者測試了READ COMMITTED, REPEATABLE READ兩種隔離級別，癥狀都相同。

通常除了手動發出這兩個命令，使用mysqldump工具進行備份時也會發出這兩個命令。

mysqldump備份加哪種選項會觸發命令flush tables

打開general log，進行多次dump測試實驗，發現有以下幾種情況會觸發flush tables命令

1.--flush-logs,--single-transaction 一起使用時，觸發flush tables，flush tables with read lock

2.--source-data 不和--single-transaction搭配使用時，觸發FLUSH /*!40101 LOCAL */ TABLES, FLUSH TABLES WITH READ LOCK

3.--flush-logs,--single-transaction,--source-data 這三個選項同時使用時，會觸發FLUSH /*!40101 LOCAL */ TABLES, FLUSH TABLES WITH READ LOCK

DBA小伙伴要熟悉備份工具各個選項或者選項組合使用時帶來效果，儘量避免在業務高峰進行備份操作。

結語

Flush table 的功能是關閉所有已經打開的表，強制關閉所有正在使用的表，然而，正在使用的表對象是不能關閉的，所以Flush Tables操作會被正在運行的SQL請求阻塞，而在Flush table 之後的SQL請求又會被Flush table會話阻塞，即使Flush table會話被取消了，這些發生在Flush table之後的SQL請求也還是會被阻塞。所以當會話出現大量waiting for table flush時，無論當前是否還存在flush table 命令，查詢耗時比這些waiting會話更久的慢查詢，將其kill掉才能解決問題。

Enjoy GreatSQL