mysql查看鎖表及殺進(jìn)程

查看進(jìn)程

登錄mysqlmysql -uroot -ppassword查看進(jìn)程mysql> show processlist;

各字段的含義

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

state列的含義

• 1.analyzing 比如進(jìn)行analyze table時 
• 2.checking table 線程正在執(zhí)行表檢查操作 
• 3.cleaning up 正準(zhǔn)備釋放內(nèi)存 
• 4.closing tables 應(yīng)該是一個快速的操作，如果不是這樣的話，則應(yīng)該檢查硬盤空間是否已滿或者磁盤io是否達(dá)到瓶頸 
• 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ù)器正準(zhǔn)備進(jìn)行自然語言全文索引 
• 10.sending data 線程正在讀取和處理一條select語句的行，并且將數(shù)據(jù)發(fā)送至客戶端，在此期間會執(zhí)行大量的磁盤訪問 
• 11.sorting index 線程正在對索引頁進(jìn)行排序 
• 12.updating 線程尋找更新匹配的行進(jìn)行更新 
• 13.waiting for lock_type lock 等待各個種類的表鎖 

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

殺進(jìn)程

1.殺單個進(jìn)程

mysql>?kill 127402;

2.殺多個進(jìn)程，組裝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;

查看當(dāng)前的事務(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ù)庫使用獨(dú)占式鎖機(jī)制，當(dāng)執(zhí)行上面的語句時，對表進(jìn)行鎖住，直到發(fā)生commit或者rollback或者退出數(shù)據(jù)庫用戶；

鎖表的原因：

• A程序執(zhí)行了對table_1的insert、update、delete，并還未commit時，B程序也對table_1進(jìn)行insert、update、delete`時會發(fā)生資源正忙的異常，也就是鎖表；
• 鎖表常發(fā)生與并發(fā)而不是并行（并行時，一個線程操作數(shù)據(jù)庫時，另一個線程是能操作數(shù)據(jù)庫的，cpu和i/o分配原則）
• 鎖表也發(fā)生在事務(wù)嵌套，外層事務(wù)對table_1進(jìn)行了insert、update、delete，內(nèi)層事務(wù)（PROPAGATION_REQUIRES_NEW）也對table_1進(jìn)行了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 ;
-- 查看當(dāng)前被使用的表，查詢是否有鎖表
-- SHOW OPEN TABLES：列舉在表緩存中當(dāng)前被打開的非TEMPORARY表。
-- In_use：表當(dāng)前被查詢使用的次數(shù)。如果該數(shù)為零，則表是打開的，但是當(dāng)前沒有被使用。
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;
-- 查詢當(dāng)前會話等待事務(wù)鎖超時時間
SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';
-- 查看進(jìn)程id，然后用kill id殺掉進(jìn)程
show processlist;
SELECT * FROM information_schema.PROCESSLIST;
-- 查詢正在執(zhí)行的進(jìn)程
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表代替
-- 殺掉鎖表進(jìn)程
kill 5601

事務(wù)嵌套引起的死鎖

這時候就不能簡單的kill掉進(jìn)程了，需要review代碼，找出問題代碼

總結(jié)

以上為個人經(jīng)驗(yàn)，希望能給大家一個參考，也希望大家多多支持腳本之家。

最新評論