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

查看進(jìn)程

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

各字段的含義

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

state列的含義

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

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

殺進(jìn)程

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

mysql>?kill 127402;

2.殺多個(gè)進(jìn)程，組裝kill語(yǔ)句

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

其他有用命令

查看被鎖的表

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鎖表原因及解決

問(wèn)題如圖

鎖表發(fā)生原因

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

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

鎖表的原因：

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

mysql鎖表解決

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

-- 查詢(xún)?nèi)值却聞?wù)鎖超時(shí)時(shí)間
SHOW GLOBAL VARIABLES LIKE 'innodb_lock_wait_timeout';
-- 設(shè)置全局等待事務(wù)鎖超時(shí)時(shí)間
SET  GLOBAL innodb_lock_wait_timeout=100;
-- 查詢(xún)當(dāng)前會(huì)話(huà)等待事務(wù)鎖超時(shí)時(shí)間
SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';
-- 查看進(jìn)程id，然后用kill id殺掉進(jìn)程
show processlist;
SELECT * FROM information_schema.PROCESSLIST;
-- 查詢(xún)正在執(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ù)嵌套引起的死鎖

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

總結(jié)

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