幾個常見而嚴重的 MySQL 問題分析 | 運維進階

【摘要】踩坑不可怕，可怕的是重複踩坑，避免它是一位程式設計師的基本修養。很多時候發生資料庫報錯時，不一定就是資料庫的問題，不一定非得急著呼叫資料庫人員解決。我們要形成這樣一種意識，我們不只是寫應用的，我們是寫金融系統的，我們理應具備一定的問題排查解決能力。本文將抽取幾個常見而嚴重的MySQL問題進行分析，並給出深度解答。藉以大家幫助思考。

【作者】一力搜索，某銀行分布式資料庫架構師，重點負責行內分布式資料庫領域及私有雲，個人微信公眾號：一力搜索。

1.1 SQLException: Lock wait timeout exceeded; try restarting transaction,please rollback!  

再發生這樣的錯誤時，別很自豪的說資料庫出問題了，呼叫DBA ...（uat多次出現）

第一個問題目前發生的原因有：

1. 磁碟空間滿，事務無法提交成功。(磁碟滿是一個很危險的操作，會引起binlog寫壞，備庫無法同步進而需要恢復備庫) 

2. 更新事務未正常提交而產生排他鎖，造成其他更新事務一直獲取不到該鎖而事務超時。

1.2 條件查詢卡住了，怎麼重跑都通不過，怎麼辦，急死人了(遷移後比對實際出現)。

Truncate table過程中CTRL +C 終止了。 有分片上存在truncate 事務一直存在，進而對該表的所有操作均會超時。 

1.3 查詢卡住，更新卡住...殊不知，你前面的Alter table都沒成功.

DBProxy的問題不在此文討論，查詢事務沒有正常提交而佔據共享鎖時，同樣會造成alter table獲取不到MDL鎖，而造成一直等待。 提示為：Waiting fortable metadata lock (show processlist中可查)。 

2 原理詳細分析2.1 什麼是MDL鎖?

為了在並發環境下維護表元數據的數據一致性，在表上有活動事務（顯式或隱式）的時候，不可以對元數據進行寫入操作。因此從MySQL5.5版本開始引入了MDL鎖（metadata lock），來保護表的元數據信息，用於解決或者保證DDL操作與DML操作之間的一致性。

對於引入MDL，其主要解決了2個問題，一個是事務隔離問題，比如在可重複讀隔離級別下，會話A在2次查詢期間，會話B對表結構做了修改，兩次查詢結果就會不一致，無法滿足可重複讀的要求；另外一個是數據複製的問題，比如會話A執行了多條更新語句期間，另外一個會話B做了表結構變更並且先提交，就會導致slave在重做時，先重做alter，再重做update時就會出現複製錯誤的現象。

所以在對表進行上述操作時，如果表上有活動事務（未提交或回滾），請求寫入的會話會等待在Metadata lock wait 。例如下面的這種情形：

若沒有MDL鎖的保護，則事務2可以直接執行DDL操作，並且導致事務1出錯，5.1版本即是如此。5.5版本加入MDL鎖就在於保護這種情況的發生，由於事務1開啟了查詢，那麼獲得了MDL鎖，鎖的模式為SHARED_READ，事務2要執行DDL，則需獲得EXCLUSIVE鎖，兩者互斥，所以事務2需要等待。

註：支持事務的InnoDB引擎表和不支持事務的MyISAM引擎表，都會出現Metadata Lock Wait等待現象。一旦出現Metadata Lock Wait等待現象，後續所有對該表的訪問都會阻塞在該等待上，導致連接堆積，業務受影響。

MySQL的設計：在設置的autocommit=0；read_commited的時候，無論session的第一條語句是select還是dml，都開始一個事務，然後直到commit，所持有的MDL鎖也一直維持到commit結束。

Oracle的設計：在session的第一條更新語句發起時，才創建transaction，在讀多的系統上，減少了阻塞的發生可能性。特別是在開發人員發起select語句時，認為沒有更新，就不再commit。但在MySQL上，發起select語句，而忘記commit，是非常危險的。

2.2 常見MDL鎖場景和詳細解釋

1）當前有執行DML操作時執行ALTRE操作

2）當前有對表的長時間查詢或使用mysqldump/mysqlpump時，使用alter會被堵住

3）顯示或者隱式開啟事務後未提交或回滾，比如查詢完成後未提交或者回滾，使用alter會被堵住

4）表上有失敗的查詢事務，比如查詢不存在的列，語句失敗返回，但是事務沒有提交，此時alter仍然會被堵住

詳細測試解釋說明：

mysql> insert into yetest2 select * from yetest1;mysql> alter table yetest2 add yeColumn int;   //等待SESSION A執行完;mysql> show processlist;++-+--+-+----+---+-+--| Id  | User | Host| db  | Command | Time | State                       | Info                              |++-+-+ +----+---+--+--| 267 | root | localhost | sbtest | Query   |    7 | Sending data                    | insert into yetest2 select * from yetest1 || 271 | root | localhost | sbtest | Query   |    3 | Waiting for table metadata lock | alter table  yetest2 add yeColumn int         || 272 | root | localhost | NULL   | Query   |    0 | starting                        | show processlist                          |++-+-+---+----+-+---+---+3 rows in set (0.00 sec) mysql> select * from yetest2 limit 10;    //等待元數據鎖;mysql> show processlist;++-+--+-+----+---+-+--| Id  | User | Host| db  | Command | Time | State                       | Info                              |++-+-+ +----+---+--+--| 267 | root | localhost | sbtest | Query   |   20 | Sending data                    | insert into sbtest2 select * from sbtest1 || 271 | root | localhost | sbtest | Query   |   13 | Waiting for table metadata lock | alter table yetest2 add  yeColumn int         || 272 | root | localhost | NULL   | Query   |    0 | starting                        | show processlist                          || 308 | root | localhost | sbtest | Query   |    3 | Waiting for table metadata lock | select * from yetest2 limit 10            |++-+-+---+----+-+---+---+4 rows in set (0.00 sec)
由於事務1開啟了查詢，那麼獲得了MDL鎖，鎖的模式為SHARED_READ，事務2要執行DDL，則需獲得EXCLUSIVE鎖，兩者互斥，所以事務2需要等待。 查詢都能卡住，是不是很鬱悶？我們上次遷移就是這種場景，truncate table屬於DDL，會lock table metadata，甚至可以可以由鎖表升級到鎖庫。 
3）顯示或者隱式開啟事務後未提交或回滾，比如查詢完成後未提交或者回滾，使用alter會被堵住
mysql> begin;mysql> select * from test2;mysql> alter table test2 add test3 int;   //等待SESSION A執行完;mysql> show processlist;++-+--+-+----+---+-+--| Id  | User | Host| db  | Command | Time | State                       | Info                              |++-+-+ +----+---+--+--| 267 | root | localhost | sbtest | Sleep   |   36 |                                 | NULL                              || 271 | root | localhost | sbtest | Query   |   30 | Waiting for table metadata lock | alter table test2 add test3 int || 272 | root | localhost | NULL   | Query   |    0 | starting                        | show processlist                  |++-+-+---+----+-+---++3 rows in set (0.00 sec)
4 ) 表上有失敗的查詢事務，比如查詢不存在的列，語句失敗返回，但是事務沒有提交，此時alter仍然會被堵住
mysql> begin;mysql> select error from  test2; ERROR 1054 (42S22): Unknown column 'error' in 'field list'mysql> alter table test2 add test3 int;    //等待SESSION A提交或回滾;mysql> show processlist;++-+--+-+----+---+-+--| Id  | User | Host| db  | Command | Time | State                       | Info                              |++-+-+ +----+---+--+--| 267 | root | local | test |  Sleep   |    7 |                             |NULL              | 271 | root | local | test |  Query   |    3 | Waiting for table metadata lock | alter table test2 add test3 int || 272 | root | local | NULL| Query   |    0 | starting                      | show processlist             | 311 | root | local | NULL | Sleep   |  413 |                            | NULL           ++-+-+---+----+-+---+----4 rows in set (0.00 sec)mysql> select * from information_schema.innodb_trx;Empty set (0.00 sec)
其實SESSION A中的事務並未開啟，但是由於select獲取表元數據的語句，語法上是有效的，雖然執行失敗了，但是任然不會釋放元數據鎖，故而導致SESSION B的alter動作被阻塞。
通過SESSION D查看當前打開事務時，你會發現沒有，從而找不到原因。所以當出現這種場景時，如何判斷是哪個進程導致的呢，我們可以嘗試查看表performance_schema. events_statements_current，分析進程狀態來進行判斷。
mysql> select * from performance_schema. events_statements_current\G*************************** 1. row ***************************              THREAD_ID: 293               EVENT_ID: 32           END_EVENT_ID: 32             EVENT_NAME: statement/sql/select                 SOURCE: socket_connection.cc:211            TIMER_START: 212721717099954000              TIMER_END: 212721717213807000             TIMER_WAIT: 113853000              LOCK_TIME: 0               SQL_TEXT: select error from test2                 DIGEST: 0bbb2d5d1be45e77debea68111264885            DIGEST_TEXT: SELECT ERROR FROM `test2`          CURRENT_SCHEMA: test            OBJECT_TYPE: NULL          OBJECT_SCHEMA: NULL            OBJECT_NAME: NULL  OBJECT_INSTANCE_BEGIN: NULL            MYSQL_ERRNO: 1054      RETURNED_SQLSTATE: 42S22           MESSAGE_TEXT: Unknown column 'error' in 'field list'                 ERRORS: 1
然後找到其sid, kill掉該session，也可以kill掉DDL所在的session解決可以解決此問題。
另外，測試時SESSION A要顯式開啟一個事務，否則查詢會隱式回滾結束，無法重現上面的場景。SESSION B執行alter後，沒有立即阻塞住，而是立馬開始copy to tmp table，這個過程結束後，才進行了MDL鎖等待。執行alter操作主要分為創建臨時新表->插入老表的數據->臨時新表rename to老表三個步驟，在這種情況下，到最後一步才需要MDL鎖，所以copy過程中不會阻塞。由於沒有查詢在進行，而且查詢也沒有進入innodb層 (失敗返回)，所以show processlist和information_schema.innodb_trx沒有可以參考的信息。
出現以上幾種情況時，這個時候如果進行如下操作就會引起MDL：
1.創建、刪除索引。
2.修改表結構。
3.表維護操作（optimize table、repair table等）。
4.刪除表。
5.獲取表上表級寫鎖 (lock table tab_name write)。
 
三. 總結
很多時候發生資料庫報錯時，不一定就是資料庫的問題，不一定非得急著呼叫資料庫人員解決。 我們要形成這樣一種意識，我們不只是寫應用的，我們是寫金融系統的，我們理應具備一定的問題排查解決能力。如果只做問題的搬運工，這是否也是對我們自身水平的一種質疑？ 只有當大家提的問題都很有水平了，卡中心的系統才可能穩如泰山。 
當然，MySQL本身和Oracle對比存在不少的缺陷和不完善之處。而分布式的引入又進一步引入了系統的複雜性。 這也對我們運維人員的水平提出了更大的考驗。運維人員同樣應該踏踏實實從實踐中學習和思考，遇到問題才能從容不亂，提出行之有效的解決方法。 
覺得本文有用，請轉發、點讚或點擊「在看」，讓更多同行看到
歡迎關注社區 "MySQL"技術主題 ，將會不斷更新優質資料、文章。地址：
https://www.talkwithtrend.com/Topic/3873
下載 twt 社區客戶端 APP

長按識別二維碼即可下載
或到應用商店搜索「twt」
*本公眾號所發布內容僅代表作者觀點，不代表社區立場