MySQL中insert語句沒有響應的問題剖析

2024-07-24 12:32:04

字體：大中小

來源：轉載

供稿：網友

今天開發的一個同學問我一個MySQL的問題，說在測試數據庫中執行一條Insert語句之后很久沒有響應。我一看語句是一個很常規的insert into xxx values形式的語句?？雌饋碛行┎惶虾醭＠戆?，我對這類問題立馬來了興趣，準備好好看看到底是什么原因。

向開發同學了解了環境之后，我登錄到服務端，首先查看是否可能是磁盤空間不足導致的問題。結果df -h的結果顯示，空間還綽綽有余。

使用show proceslist查看線程情況。

MySQL中insert語句沒有響應的問題分析(r11筆記第21天)
可以看到大量的線程是Waiting for table level lock ,開發同學提交的SQL語句也被鎖住了，也是同樣的鎖。

| 253688 | webadmin | xxxx   | pt_test | Query   |    171 | Waiting for table level lock | insert into ptp_jgg(sub_type) values(9999)這類表級鎖好像在MyISAM中還是看到過，結果查看表的存儲引擎，發現都是InnoDB,

對于這類問題的一種解決方法，就是使用kill的方式殺掉線程。

mysql> SELECT
    -> a.*,CONCAT("kill " ,a.id,";")
    -> FROM
    -> information_schema.`PROCESSLIST` a
    -> WHERE
    -> a.STATE = 'Waiting for table level lock';

這樣就會生成很規律的kill語句。

當然我也沒有著急這么做，和開發同學簡單了解，他們之前碰到這類問題，是找系統運維的同學直接重啟MySQL的，看來這個問題之前也碰到過，這我就更有興趣了解了。

查看MySQL的error log也沒有發現什么明顯的錯誤，使用ps -ef|grep mysql查看進程的信息，突然發現系統中是設置了一個定時任務去備份數據，不過開始沒有引起我的注意，但是這些線索都逐一排除之后，我的注意力就很自然的落在了這個備份腳本上。

打開備份腳本，我就明白問題的原委了。

備份的核心語句是通過變量的方式調用mysqldump的。

mysqldump -uroot -p$passwd pt_test | $GZIP -9 > $dump_path/pt_test$date.gz

這樣一來這個語句毫無疑問就是這個鎖表的罪魁禍首。

默認mysqldump會調用--lock-all-tables這個選項，也就意味著master的binlog和postion信息寫入SQL文件的頭部，而通用的方式是使用--single-transaction

執行的時候會有短暫的全局讀鎖，影響要低得多。

    所以這個問題的解決方式就很直接，當前解決就是直接kill掉正在進行的mysqldump，殺掉備份的mysqldump之后，再次查看show processlist，這些線程馬上就是sleep狀態了。

    這樣一來，這個問題就算是基本解決了，我想以后至少不會因為這樣而無端重啟MySQL了。

（編輯：武林網）

上一篇：Centos 7.6 下Mysql8.0.16編譯安置

下一篇：MySQL級聯復制的同步難題