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

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

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

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

查看進(jìn)程

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

各字段的含義

1.id 該進(jìn)程的標(biāo)識(shí)；
2.user 顯示當(dāng)前用戶
3.host 顯示來源IP和端口
4.db 顯示當(dāng)前連接的數(shù)據(jù)庫
5.command 顯示當(dāng)前連接的執(zhí)行的命令，休眠 sleep ，查詢 query ，連接 connect
6.time 此這個(gè)狀態(tài)持續(xù)的時(shí)間，單位是秒
7.state列顯示使用當(dāng)前連接的sql語句的狀態(tài)，很重要的列，詳見下面state列的含義
8.info 顯示sql語句，長(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è)快速的操作，如果不是這樣的話，則應(yīng)該檢查硬盤空間是否已滿或者磁盤io是否達(dá)到瓶頸
5.copy to tmp table 線程正在處理一個(gè)alter table語句
6.copying to tmp table 線程將數(shù)據(jù)寫入內(nèi)存中的臨時(shí)表
7.copying to tmp table on disk 線程正在將數(shù)據(jù)寫入磁盤中的臨時(shí)表。與tmp_table_size參數(shù)有關(guān)系
8.creating sort index 線程正在使用內(nèi)部臨時(shí)表處理一個(gè)select操作
9.fulltext initialization 服務(wù)器正準(zhǔn)備進(jìn)行自然語言全文索引
10.sending data 線程正在讀取和處理一條select語句的行，并且將數(shù)據(jù)發(fā)送至客戶端，在此期間會(huì)執(zhí)行大量的磁盤訪問
11.sorting index 線程正在對(duì)索引頁進(jìn)行排序
12.updating 線程尋找更新匹配的行進(jìn)行更新
13.waiting for lock_type lock 等待各個(gè)種類的表鎖

當(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語句

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í)行上面的語句時(shí)，對(duì)表進(jìn)行鎖住，直到發(fā)生commit或者rollback或者退出數(shù)據(jù)庫用戶；

鎖表的原因：

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ù)庫時(shí)，另一個(gè)線程是能操作數(shù)據(jù)庫的，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ù)嵌套導(dǎo)致）。多查幾次SELECT * FROM information_schema.innodb_trx ;如果鎖跟著業(yè)務(wù)結(jié)束（connect超時(shí)）鎖沒了，那么基本上可以確定是業(yè)務(wù)代碼導(dǎo)致，需要分析業(yè)務(wù)代碼。

mysql鎖表解決

-- 找到超時(shí)的表，查詢超時(shí)的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ù)鎖超時(shí)時(shí)間
SHOW GLOBAL VARIABLES LIKE 'innodb_lock_wait_timeout';
-- 設(shè)置全局等待事務(wù)鎖超時(shí)時(shí)間
SET  GLOBAL innodb_lock_wait_timeout=100;
-- 查詢當(dāng)前會(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;
-- 查詢正在執(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