常見而嚴重的MySQL問題有哪些

本篇內(nèi)容介紹了“常見而嚴重的MySQL問題有哪些”的有關知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓小編帶領大家學習一下如何處理這些情況吧！希望大家仔細閱讀，能夠?qū)W有所成！

1 問題回顧和思考

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

再發(fā)生這樣的錯誤時，別很自豪的說數(shù)據(jù)庫出問題了，呼叫DBA ...（uat多次出現(xiàn)）

第一個問題目前發(fā)生的原因有：

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

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

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

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

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

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

2 原理詳細分析

2.1 什么是MDL鎖?

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

對于引入MDL，其主要解決了2個問題，一個是事務隔離問題，比如在可重復讀隔離級別下，會話A在2次查詢期間，會話B對表結(jié)構(gòu)做了修改，兩次查詢結(jié)果就會不一致，無法滿足可重復讀的要求；另外一個是數(shù)據(jù)復制的問題，比如會話A執(zhí)行了多條更新語句期間，另外一個會話B做了表結(jié)構(gòu)變更并且先提交，就會導致slave在重做時，先重做alter，再重做update時就會出現(xiàn)復制錯誤的現(xiàn)象。

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

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

注：支持事務的InnoDB引擎表和不支持事務的MyISAM引擎表，都會出現(xiàn)Metadata Lock Wait等待現(xiàn)象。一旦出現(xiàn)Metadata Lock Wait等待現(xiàn)象，后續(xù)所有對該表的訪問都會阻塞在該等待上，導致連接堆積，業(yè)務受影響。

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

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

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

1）當前有執(zhí)行DML操作時執(zhí)行ALTRE操作

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

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

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

詳細測試解釋說明：

1）當前有執(zhí)行DML操作時執(zhí)行ALTRE操作

# SESSION Amysql> insert into yetest2 select * from yetest1;# SESSION Bmysql> alter table yetest2 add yeColumn int;   //等待SESSION A執(zhí)行完;# SESSION Cmysql> 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) 
# SESSION Dmysql> select * from yetest2 limit 10;    //等待元數(shù)據(jù)鎖;# SESSION Emysql> 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要執(zhí)行DDL，則需獲得EXCLUSIVE鎖，兩者互斥，所以事務2需要等待。 查詢都能卡住，是不是很郁悶？我們上次遷移就是這種場景，truncate table屬于DDL，會lock table metadata，甚至可以可以由鎖表升級到鎖庫。

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

# SESSION Amysql> begin;
mysql> select * from test2;# SESSION Bmysql> alter table test2 add test3 int;   //等待SESSION A執(zhí)行完;# SESSION Cmysql> 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仍然會被堵住

# SESSION Amysql> begin;
mysql> select error from  test2; 
ERROR 1054 (42S22): Unknown column 'error' in 'field list'# SESSION Bmysql> alter table test2 add test3 int;    //等待SESSION A提交或回滾;# SESSION Cmysql> 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)
# SESSION D
mysql> select * from information_schema.innodb_trx;
Empty set (0.00 sec)

其實SESSION A中的事務并未開啟，但是由于select獲取表元數(shù)據(jù)的語句，語法上是有效的，雖然執(zhí)行失敗了，但是任然不會釋放元數(shù)據(jù)鎖，故而導致SESSION B的alter動作被阻塞。

通過SESSION D查看當前打開事務時，你會發(fā)現(xiàn)沒有，從而找不到原因。所以當出現(xiàn)這種場景時，如何判斷是哪個進程導致的呢，我們可以嘗試查看表performance_schema. events_statements_current，分析進程狀態(tài)來進行判斷。

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:211TIMER_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: NULLOBJECT_NAME: NULL
  OBJECT_INSTANCE_BEGIN: NULLMYSQL_ERRNO: 1054  RETURNED_SQLSTATE: 42S22           MESSAGE_TEXT: Unknown column 'error' in 'field list' ERRORS: 1

然后找到其sid, kill掉該session，也可以kill掉DDL所在的session解決可以解決此問題。

另外，測試時SESSION A要顯式開啟一個事務，否則查詢會隱式回滾結(jié)束，無法重現(xiàn)上面的場景。SESSION B執(zhí)行alter后，沒有立即阻塞住，而是立馬開始copy to tmp table，這個過程結(jié)束后，才進行了MDL鎖等待。執(zhí)行alter操作主要分為創(chuàng)建臨時新表->插入老表的數(shù)據(jù)->臨時新表rename to老表三個步驟，在這種情況下，到最后一步才需要MDL鎖，所以copy過程中不會阻塞。由于沒有查詢在進行，而且查詢也沒有進入innodb層 (失敗返回)，所以show processlist和information_schema.innodb_trx沒有可以參考的信息。

出現(xiàn)以上幾種情況時，這個時候如果進行如下操作就會引起MDL：

1.創(chuàng)建、刪除索引。

2.修改表結(jié)構(gòu)。

3.表維護操作（optimize table、repair table等）。

4.刪除表。

5.獲取表上表級寫鎖 (lock table tab_name write)。

“常見而嚴重的MySQL問題有哪些”的內(nèi)容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業(yè)相關的知識可以關注億速云網(wǎng)站，小編將為大家輸出更多高質(zhì)量的實用文章！