【Mysql】FLUSH TABLES WITH READ LOCK

原文地址：http://blog.csdn.net/zbszhangbosen/article/details/7434173

FLUSH TABLES WITH READ LOCK 作用

1. 對於myisam儲存引擎，從資料庫外部複製.frm，.fyd，.fyi檔案後，執行FLUSH TABLES/FLUSH TABLES WITH READ LOCK後，就可以使用新表了，這個功能非常有用，備份變得格外簡單
2. mysqldump中從主庫中拉出一個副本後透過change master後就可以快速搭建slave，為了獲得一個一致性點，一般會使用引數–single-transaction和–master-data，它會使用FLUSH TABLES WITH READ LOCK阻塞事務commit寫binlog來獲得transaction開始時主庫binlog位置
3. 與FLUSH TABLES相似，清空table_cache

除了這幾個重要作用外，目前還沒有發現其他用處，而且對於InnoDB儲存引擎，第一條就沒用了

FLUSH TABLES WITH READ LOCK vs. FLUSH TABLES

1. 後面多了 WITH READ LOCK使得這兩個SQL命令差很大，FLUSH TABLES WITH READ LOCK會在global read lock上加S鎖，這會導致整個系統變得只讀
2. FLUSH TABLES WITH READ LOCK後必須顯示執行UNLOCK TABLES，否則整個系統會一直處於只讀狀態，這個問題非常隱晦，因為FLUSH TABLES WITH READ LOCK完成後在“show processlist”中無法被觀察到，除非這個session自己執行unlock tables或者擁有root許可權使用者kill這個session，重複一次：FLUSH TABLES WITH READ LOCK後一定要UNLOCK TABLES，除非你想讓系統只讀！
3. FLUSH TABLES在以上三點都被用到，對於1和3很好理解，2中怎麼會用到呢？檢視mysqldump.c的程式碼及註釋：

static int do_flush_tables_read_lock(MYSQL *mysql_con) { /*
    We do first a FLUSH TABLES. If a long update is running, the FLUSH TABLES
    will wait but will not stall the whole mysqld, and when the long update is
    done the FLUSH TABLES WITH READ LOCK will start and succeed quickly. So,
    FLUSH TABLES is to lower the probability of a stage where both mysqldump
    and most client connections are stalled. Of course, if a second long
    update starts between the two FLUSHes, we have that bad stall.
  */ return ( mysql_query_with_error_report(mysql_con, 0, ((opt_master_data != 0) ? "FLUSH /*!40101 LOCAL */ TABLES" : "FLUSH TABLES")) || mysql_query_with_error_report(mysql_con, 0, "FLUSH TABLES WITH READ LOCK") ); }

解釋很清楚：FLUSH TABLES WITH READ LOCK開始後整個系統就是read only狀態了，為了使影響最小，先使用FLUSH TABLES “嘗試一次”，假設在FLUSH TABLES和FLUSH TABLES WITH READ LOCK之間存在DML/DDL的可能性較小，FLUSH TABLES WITH READ LOCK執行時間會很短，若不幸一個大DML/DDL正好在這之間發生了，事情就會變得很糟糕了：FLUSH TABLES WITH READ LOCK需要等待DML/DDL執行完，之後開啟的DML/DDL需要等待FLUSH TABLES WITH READ LOCK對應的UNLOCK TABLES被執行

FLUSH TABLES WITH READ LOCK實現細節

實現分三步：(函式：reload_acl_and_cache)

1. lock_global_read_lock --> mdl_request.init(MDL_key::GLOBAL, "", "", MDL_SHARED, MDL_EXPLICIT); 2. close_cached_tables（FLUSH TABLES只做這一步） 3. make_global_read_lock_block_commit --> mdl_request.init(MDL_key::COMMIT, "", "", MDL_SHARED, MDL_EXPLICIT);

注：
1. 對於任何DDL/DML，會首先申請MDL_INTENTION_EXCLUSIVE型別的global read lock：
–> mdl_request.init(MDL_key::GLOBAL, “”, “”, MDL_INTENTION_EXCLUSIVE, MDL_EXPLICIT); 然後申請對應表上的相應後設資料鎖
2. 對於任何DDL/DML，提交時會申請MDL_INTENTION_EXCLUSIVE型別的global commit lock:
–> mdl_request.init(MDL_key::COMMIT, “”, “”, MDL_INTENTION_EXCLUSIVE, MDL_EXPLICIT);(程式碼見：ha_commit_trans)
3. select操作不會申請任何型別的global read lock或者global commit lock

因此FLUSH TABLES WITH READ LOCK的三個步驟中：
1 會等待DDL/DML結束後才會開始，並且一旦開始後，整個系統變成只讀
2 這個步驟可能很長，因為會等待所有的表被關閉，如果系統中有大查詢，會一直等待…
3 會阻塞系統中已經開始的事務提交，從而保證binlog不增長，透過show master status就可以獲取當前的binlog file和binglog pos

FLUSH TABLES WITH READ LOCK vs. set global read_only=1

lock_global_read_lock被兩處呼叫：reload_acl_and_cache 和 fix_read_only，fix_read_only做的事情和FLUSH TABLES WITH READ LOCK基本一樣，lock_global_read_lock(同樣的，MDL_SHARED型別的global read lock)–>close_cached_tables–>make_global_read_lock_block_commit,除此之外，設定全域性變數read_only=1，由於它們在global read lock上加的都是MDL_SHARED鎖，因此這兩個命令是相容的，只是set global read_only=1後，如果有DML會報錯：the mysql server is running with the read-only option

一個線上案例

線上出現了一個問題，根據show processlist發現：
1. 所有的DML都無法執行，session狀態為：Waiting for global read lock
2. set global read_only=1阻塞， session狀態為：Waiting for table flush
出現1說明global read lock被某個session獲取了或者被某個session等待，比如以下場景：

故障總結

系統中的global read lock被其它執行緒獲取了（被加上了S鎖），因此DML在grl上面加IX鎖時會出現Waiting for global read lock，而set read_only在grl上加的是S鎖，因此此時是可以grant的，而此時系統中存在大查詢，set global read_only=1需要等待表被關閉，因此狀態是：Waiting for table flush

因此原因可以確定了：系統中存在flush tables with read lock但是沒有unlock tables

小技巧

在手動做FLUSH TABLES WITH READ LOCK/set global read_only=1之前，先執行一下FLUSH TABLES，能夠很大機率地減小系統變成只讀的時間

參考：http://blog.csdn.net/zbszhangbosen/article/details/7434173