本篇內(nèi)容介紹了“常見而嚴(yán)重的MySQL問題有哪些”的有關(guān)知識(shí)，在實(shí)際案例的操作過程中，不少人都會(huì)遇到這樣的困境，接下來就讓小編帶領(lǐng)大家學(xué)習(xí)一下如何處理這些情況吧！希望大家仔細(xì)閱讀，能夠?qū)W有所成！
創(chuàng)新互聯(lián)主營鶴城網(wǎng)站建設(shè)的網(wǎng)絡(luò)公司,主營網(wǎng)站建設(shè)方案,app開發(fā)定制,鶴城h5微信小程序定制開發(fā)搭建,鶴城網(wǎng)站營銷推廣歡迎鶴城等地區(qū)企業(yè)咨詢
1.1 SQLException: Lock wait timeout exceeded; try restarting transaction,please rollback!
再發(fā)生這樣的錯(cuò)誤時(shí)，別很自豪的說數(shù)據(jù)庫出問題了，呼叫DBA ...（uat多次出現(xiàn)）
第一個(gè)問題目前發(fā)生的原因有：
磁盤空間滿，事務(wù)無法提交成功。(磁盤滿是一個(gè)很危險(xiǎn)的操作，會(huì)引起binlog寫壞，備庫無法同步進(jìn)而需要恢復(fù)備庫)
更新事務(wù)未正常提交而產(chǎn)生排他鎖，造成其他更新事務(wù)一直獲取不到該鎖而事務(wù)超時(shí)。
1.2 條件查詢卡住了，怎么重跑都通不過，怎么辦，急死人了(遷移后比對(duì)實(shí)際出現(xiàn))。
Truncate table過程中CTRL +C 終止了。 有分片上存在truncate 事務(wù)一直存在，進(jìn)而對(duì)該表的所有操作均會(huì)超時(shí)。
1.3 查詢卡住，更新卡住...殊不知，你前面的Alter table都沒成功......
DBProxy的問題不在此文討論，查詢事務(wù)沒有正常提交而占據(jù)共享鎖時(shí)，同樣會(huì)造成alter table獲取不到MDL鎖，而造成一直等待。 提示為：Waiting fortable metadata lock (show processlist中可查)。
2.1 什么是MDL鎖?
為了在并發(fā)環(huán)境下維護(hù)表元數(shù)據(jù)的數(shù)據(jù)一致性，在表上有活動(dòng)事務(wù)（顯式或隱式）的時(shí)候，不可以對(duì)元數(shù)據(jù)進(jìn)行寫入操作。因此從MySQL5.5版本開始引入了MDL鎖（metadata lock），來保護(hù)表的元數(shù)據(jù)信息，用于解決或者保證DDL操作與DML操作之間的一致性。
對(duì)于引入MDL，其主要解決了2個(gè)問題，一個(gè)是事務(wù)隔離問題，比如在可重復(fù)讀隔離級(jí)別下，會(huì)話A在2次查詢期間，會(huì)話B對(duì)表結(jié)構(gòu)做了修改，兩次查詢結(jié)果就會(huì)不一致，無法滿足可重復(fù)讀的要求；另外一個(gè)是數(shù)據(jù)復(fù)制的問題，比如會(huì)話A執(zhí)行了多條更新語句期間，另外一個(gè)會(huì)話B做了表結(jié)構(gòu)變更并且先提交，就會(huì)導(dǎo)致slave在重做時(shí)，先重做alter，再重做update時(shí)就會(huì)出現(xiàn)復(fù)制錯(cuò)誤的現(xiàn)象。
所以在對(duì)表進(jìn)行上述操作時(shí)，如果表上有活動(dòng)事務(wù)（未提交或回滾），請(qǐng)求寫入的會(huì)話會(huì)等待在Metadata lock wait 。例如下面的這種情形：
若沒有MDL鎖的保護(hù)，則事務(wù)2可以直接執(zhí)行DDL操作，并且導(dǎo)致事務(wù)1出錯(cuò)，5.1版本即是如此。5.5版本加入MDL鎖就在于保護(hù)這種情況的發(fā)生，由于事務(wù)1開啟了查詢，那么獲得了MDL鎖，鎖的模式為SHARED_READ，事務(wù)2要執(zhí)行DDL，則需獲得EXCLUSIVE鎖，兩者互斥，所以事務(wù)2需要等待。
注：支持事務(wù)的InnoDB引擎表和不支持事務(wù)的MyISAM引擎表，都會(huì)出現(xiàn)Metadata Lock Wait等待現(xiàn)象。一旦出現(xiàn)Metadata Lock Wait等待現(xiàn)象，后續(xù)所有對(duì)該表的訪問都會(huì)阻塞在該等待上，導(dǎo)致連接堆積，業(yè)務(wù)受影響。
MySQL的設(shè)計(jì)：在設(shè)置的autocommit=0；read_commited的時(shí)候，無論session的第一條語句是select還是dml，都開始一個(gè)事務(wù)，然后直到commit，所持有的MDL鎖也一直維持到commit結(jié)束。
Oracle的設(shè)計(jì)：在session的第一條更新語句發(fā)起時(shí)，才創(chuàng)建transaction，在讀多的系統(tǒng)上，減少了阻塞的發(fā)生可能性。特別是在開發(fā)人員發(fā)起select語句時(shí)，認(rèn)為沒有更新，就不再commit。但在MySQL上，發(fā)起select語句，而忘記commit，是非常危險(xiǎn)的。
2.2 常見MDL鎖場(chǎng)景和詳細(xì)解釋
1）當(dāng)前有執(zhí)行DML操作時(shí)執(zhí)行ALTRE操作
2）當(dāng)前有對(duì)表的長時(shí)間查詢或使用mysqldump/mysqlpump時(shí)，使用alter會(huì)被堵住
3）顯示或者隱式開啟事務(wù)后未提交或回滾，比如查詢完成后未提交或者回滾，使用alter會(huì)被堵住
4）表上有失敗的查詢事務(wù)，比如查詢不存在的列，語句失敗返回，但是事務(wù)沒有提交，此時(shí)alter仍然會(huì)被堵住
詳細(xì)測(cè)試解釋說明：
1）當(dāng)前有執(zhí)行DML操作時(shí)執(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)
由于事務(wù)1開啟了查詢，那么獲得了MDL鎖，鎖的模式為SHARED_READ，事務(wù)2要執(zhí)行DDL，則需獲得EXCLUSIVE鎖，兩者互斥，所以事務(wù)2需要等待。 查詢都能卡住，是不是很郁悶？我們上次遷移就是這種場(chǎng)景，truncate table屬于DDL，會(huì)lock table metadata，甚至可以可以由鎖表升級(jí)到鎖庫。
3）顯示或者隱式開啟事務(wù)后未提交或回滾，比如查詢完成后未提交或者回滾，使用alter會(huì)被堵住
# 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 ) 表上有失敗的查詢事務(wù)，比如查詢不存在的列，語句失敗返回，但是事務(wù)沒有提交，此時(shí)alter仍然會(huì)被堵住
# 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)
其實(shí)SESSION A中的事務(wù)并未開啟，但是由于select獲取表元數(shù)據(jù)的語句，語法上是有效的，雖然執(zhí)行失敗了，但是任然不會(huì)釋放元數(shù)據(jù)鎖，故而導(dǎo)致SESSION B的alter動(dòng)作被阻塞。
通過SESSION D查看當(dāng)前打開事務(wù)時(shí)，你會(huì)發(fā)現(xiàn)沒有，從而找不到原因。所以當(dāng)出現(xiàn)這種場(chǎng)景時(shí)，如何判斷是哪個(gè)進(jìn)程導(dǎo)致的呢，我們可以嘗試查看表performance_schema. events_statements_current，分析進(jìn)程狀態(tài)來進(jìn)行判斷。
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解決可以解決此問題。
另外，測(cè)試時(shí)SESSION A要顯式開啟一個(gè)事務(wù)，否則查詢會(huì)隱式回滾結(jié)束，無法重現(xiàn)上面的場(chǎng)景。SESSION B執(zhí)行alter后，沒有立即阻塞住，而是立馬開始copy to tmp table，這個(gè)過程結(jié)束后，才進(jìn)行了MDL鎖等待。執(zhí)行alter操作主要分為創(chuàng)建臨時(shí)新表->插入老表的數(shù)據(jù)->臨時(shí)新表rename to老表三個(gè)步驟，在這種情況下，到最后一步才需要MDL鎖，所以copy過程中不會(huì)阻塞。由于沒有查詢?cè)谶M(jìn)行，而且查詢也沒有進(jìn)入innodb層 (失敗返回)，所以show processlist和information_schema.innodb_trx沒有可以參考的信息。
出現(xiàn)以上幾種情況時(shí)，這個(gè)時(shí)候如果進(jìn)行如下操作就會(huì)引起MDL：
1.創(chuàng)建、刪除索引。
2.修改表結(jié)構(gòu)。
3.表維護(hù)操作（optimize table、repair table等）。
4.刪除表。
5.獲取表上表級(jí)寫鎖 (lock table tab_name write)。
“常見而嚴(yán)重的MySQL問題有哪些”的內(nèi)容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業(yè)相關(guān)的知識(shí)可以關(guān)注創(chuàng)新互聯(lián)網(wǎng)站，小編將為大家輸出更多高質(zhì)量的實(shí)用文章！
重慶分公司
重慶分公司
