GreatSQL 的重新整理鎖
前言
因為運維小夥伴執行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 😃
關於 GreatSQL
GreatSQL是適用於金融級應用的國內自主開源資料庫，具備高效能、高可靠、高易用性、高安全等多個核心特性，可以作為MySQL或Percona Server的可選替換，用於線上生產環境，且完全免費併相容MySQL或Percona Server。
相關連結： GreatSQL社群 Gitee GitHub Bilibili
GreatSQL社群：
社群部落格有獎徵稿詳情：https://greatsql.cn/thread-100-1-1.html
技術交流群：
微信：掃碼新增
GreatSQL社群助手微信好友，傳送驗證資訊加群。
