mysql查看鎖表及殺進程問題

更新時間：2023年07月27日 09:37:09 作者：a2589293499

這篇文章主要介紹了mysql查看鎖表及殺進程問題，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教

mysql查看鎖表及殺進程

查看進程

登錄mysqlmysql -uroot -ppassword查看進程mysql> show processlist;

各字段的含義

1.id 該進程的標識；
2.user 顯示當前用戶
3.host 顯示來源IP和端口
4.db 顯示當前連接的數(shù)據(jù)庫
5.command 顯示當前連接的執(zhí)行的命令，休眠 sleep ，查詢 query ，連接 connect
6.time 此這個狀態(tài)持續(xù)的時間，單位是秒
7.state列顯示使用當前連接的sql語句的狀態(tài)，很重要的列，詳見下面state列的含義
8.info 顯示sql語句，長sql可能顯示不全

state列的含義

1.analyzing 比如進行analyze table時
2.checking table 線程正在執(zhí)行表檢查操作
3.cleaning up 正準備釋放內(nèi)存
4.closing tables 應(yīng)該是一個快速的操作，如果不是這樣的話，則應(yīng)該檢查硬盤空間是否已滿或者磁盤io是否達到瓶頸
5.copy to tmp table 線程正在處理一個alter table語句
6.copying to tmp table 線程將數(shù)據(jù)寫入內(nèi)存中的臨時表
7.copying to tmp table on disk 線程正在將數(shù)據(jù)寫入磁盤中的臨時表。與tmp_table_size參數(shù)有關(guān)系
8.creating sort index 線程正在使用內(nèi)部臨時表處理一個select操作
9.fulltext initialization 服務(wù)器正準備進行自然語言全文索引
10.sending data 線程正在讀取和處理一條select語句的行，并且將數(shù)據(jù)發(fā)送至客戶端，在此期間會執(zhí)行大量的磁盤訪問
11.sorting index 線程正在對索引頁進行排序
12.updating 線程尋找更新匹配的行進行更新
13.waiting for lock_type lock 等待各個種類的表鎖

當state列為waiting for lock_type lock時，表示某個SQL正在query導(dǎo)致別的SQL等待鎖，需要根據(jù)id殺進程。

殺進程

1.殺單個進程

mysql>?kill 127402;

2.殺多個進程，組裝kill語句

select concat('kill ',id,';') from information_schema.processlist where user='root' and state='waiting for lock_type lock';執(zhí)行組裝后的kill語句

其他有用命令

查看被鎖的表

mysql>?show open tables where in_use > 0;

查看當前的事務(wù)

mysql>?select * from information_schema.innodb_trx;

查看被鎖的事務(wù)

mysql>?select * from information_schema.innodb_locks;

查看等鎖的事務(wù)

mysql>?select * from information_schema.innodb_lock_waits;

mysql鎖表原因及解決

問題如圖

在這里插入圖片描述

鎖表發(fā)生原因

鎖表發(fā)生在 insert、update、delete中；

鎖表的原理是數(shù)據(jù)庫使用獨占式鎖機制，當執(zhí)行上面的語句時，對表進行鎖住，直到發(fā)生commit或者rollback或者退出數(shù)據(jù)庫用戶；

鎖表的原因：

A程序執(zhí)行了對table_1的insert、update、delete，并還未commit時，B程序也對table_1進行insert、update、delete`時會發(fā)生資源正忙的異常，也就是鎖表；
鎖表常發(fā)生與并發(fā)而不是并行（并行時，一個線程操作數(shù)據(jù)庫時，另一個線程是能操作數(shù)據(jù)庫的，cpu和i/o分配原則）
鎖表也發(fā)生在事務(wù)嵌套，外層事務(wù)對table_1進行了insert、update、delete，內(nèi)層事務(wù)（PROPAGATION_REQUIRES_NEW）也對table_1進行了insert、update、delete，內(nèi)層事務(wù)commit的時需要等待外層事務(wù)先commit釋放資源（但是是不可能的），最終導(dǎo)致死鎖（本次問題就是事務(wù)嵌套導(dǎo)致）。多查幾次SELECT * FROM information_schema.innodb_trx ;如果鎖跟著業(yè)務(wù)結(jié)束（connect超時）鎖沒了，那么基本上可以確定是業(yè)務(wù)代碼導(dǎo)致，需要分析業(yè)務(wù)代碼。

mysql鎖表解決

-- 找到超時的表，查詢超時的SQL
SELECT * FROM information_schema.innodb_trx ;
-- 查看當前被使用的表，查詢是否有鎖表
-- SHOW OPEN TABLES：列舉在表緩存中當前被打開的非TEMPORARY表。
-- In_use：表當前被查詢使用的次數(shù)。如果該數(shù)為零，則表是打開的，但是當前沒有被使用。
show OPEN TABLES where In_use > 0;

-- 查詢?nèi)值却聞?wù)鎖超時時間
SHOW GLOBAL VARIABLES LIKE 'innodb_lock_wait_timeout';
-- 設(shè)置全局等待事務(wù)鎖超時時間
SET  GLOBAL innodb_lock_wait_timeout=100;
-- 查詢當前會話等待事務(wù)鎖超時時間
SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';
-- 查看進程id，然后用kill id殺掉進程
show processlist;
SELECT * FROM information_schema.PROCESSLIST;
-- 查詢正在執(zhí)行的進程
SELECT * FROM information_schema.PROCESSLIST where length(info) >0 ;
-- 查看被鎖住的
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
-- 等待鎖定
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
-- innodb_locks表在8.0.13版本中由performance_schema.data_locks表所代替，innodb_lock_waits表則由performance_schema.data_lock_waits表代替
-- 殺掉鎖表進程
kill 5601