mysql kill進(jìn)程后出現(xiàn)killed死鎖問題及解決
mysql kill進(jìn)程后出現(xiàn)killed死鎖
經(jīng)常會(huì)出現(xiàn)這樣的場(chǎng)景
有一張3億的表，現(xiàn)在要對(duì)這張表進(jìn)行刪除1億行，于是有人開始運(yùn)行
delete from table limit 100000000;
毫無疑問這是一個(gè)愚蠢的刪除方式，于是有人開始變更刪除方式：
delete from table where id<100000000;
然而運(yùn)行一段時(shí)間后，又發(fā)現(xiàn)批量刪除的效率可能會(huì)更高，所以kill掉了上一條運(yùn)行了一段時(shí)間的sql，開始批量刪除， 由于是大sql，晚上點(diǎn)擊運(yùn)行想第二天早上來看結(jié)果的DBA就會(huì)遺憾的發(fā)現(xiàn)新執(zhí)行的sql被鎖給擋了回來，并沒有運(yùn)行，導(dǎo)致浪費(fèi)了一晚上的時(shí)間。
但是盲目的等待鎖釋放心里沒底，所以我們可以通過下面的方式計(jì)算出這個(gè)鎖什么時(shí)候能夠釋放，我們就可以使用表了。
場(chǎng)景
一個(gè)巨大的delete語句 執(zhí)行一小時(shí)后kill ，
show processlist出現(xiàn)killed進(jìn)程 ，
不要盲目重啟！ 重啟MySQL后進(jìn)程消失但鎖依然存在！
重啟MySQL后進(jìn)程消失但鎖依然存在，因?yàn)榛貪L還要繼續(xù)，這是mysql對(duì)數(shù)據(jù)的保護(hù)機(jī)制
通過下列語句查詢事務(wù)情況
SELECT * FROM information_schema.INNODB_TRX\G
*************************** 1. row ***************************
trx_id: 715674773
trx_state: ROLLING BACK
trx_started: 2018-09-24 23:17:30
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 540574
trx_mysql_thread_id: 0
trx_query: NULL
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 1
trx_lock_structs: 3
trx_lock_memory_bytes: 1136
trx_rows_locked: 2
trx_rows_modified: 540571 #代表鎖影響的行數(shù)，當(dāng)數(shù)值為0時(shí)，鎖將會(huì)釋放
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_is_read_only: 0
trx_autocommit_non_locking: 0
1 row in set (0.00 sec)
trx_rows_modified: 代表鎖影響的行數(shù)，當(dāng)數(shù)值為0時(shí)，鎖將會(huì)釋放
查看表鎖信息
SELECT * FROM information_schema.INNODB_LOCKS SELECT * FROM information_schema.INNODB_LOCK_waits
desc innodb_locks; +————-+———————+——+—–+———+——-+ | Field | Type | Null | Key | Default | Extra | +————-+———————+——+—–+———+——-+ | lock_id | varchar(81) | NO | | | |#鎖ID | lock_trx_id | varchar(18) | NO | | | |#擁有鎖的事務(wù)ID | lock_mode | varchar(32) | NO | | | |#鎖模式 | lock_type | varchar(32) | NO | | | |#鎖類型 | lock_table | varchar(1024) | NO | | | |#被鎖的表 | lock_index | varchar(1024) | YES | | NULL | |#被鎖的索引 | lock_space | bigint(21) unsigned | YES | | NULL | |#被鎖的表空間號(hào) | lock_page | bigint(21) unsigned | YES | | NULL | |#被鎖的頁號(hào) | lock_rec | bigint(21) unsigned | YES | | NULL | |#被鎖的記錄號(hào) | lock_data | varchar(8192) | YES | | NULL | |#被鎖的數(shù)據(jù) +————-+———————+——+—–+———+——-+ 10 rows in set (0.00 sec) desc innodb_lock_waits; +——————-+————-+——+—–+———+——-+ | Field | Type | Null | Key | Default | Extra | +——————-+————-+——+—–+———+——-+ | requesting_trx_id | varchar(18) | NO | | | |#請(qǐng)求鎖的事務(wù)ID | requested_lock_id | varchar(81) | NO | | | |#請(qǐng)求鎖的鎖ID | blocking_trx_id | varchar(18) | NO | | | |#當(dāng)前擁有鎖的事務(wù)ID | blocking_lock_id | varchar(81) | NO | | | |#當(dāng)前擁有鎖的鎖ID +——————-+————-+——+—–+———+——-+ 4 rows in set (0.00 sec) desc innodb_trx ; +—————————-+———————+——+—–+———————+——-+ | Field | Type | Null | Key | Default | Extra | +—————————-+———————+——+—–+———————+——-+ | trx_id | varchar(18) | NO | | | |#事務(wù)ID | trx_state | varchar(13) | NO | | | |#事務(wù)狀態(tài)： | trx_started | datetime | NO | | 0000-00-00 00:00:00 | |#事務(wù)開始時(shí)間； | trx_requested_lock_id | varchar(81) | YES | | NULL | |#innodb_locks.lock_id | trx_wait_started | datetime | YES | | NULL | |#事務(wù)開始等待的時(shí)間 | trx_weight | bigint(21) unsigned | NO | | 0 | |# | trx_mysql_thread_id | bigint(21) unsigned | NO | | 0 | |#事務(wù)線程ID | trx_query | varchar(1024) | YES | | NULL | |#具體SQL語句 | trx_operation_state | varchar(64) | YES | | NULL | |#事務(wù)當(dāng)前操作狀態(tài) | trx_tables_in_use | bigint(21) unsigned | NO | | 0 | |#事務(wù)中有多少個(gè)表被使用 | trx_tables_locked | bigint(21) unsigned | NO | | 0 | |#事務(wù)擁有多少個(gè)鎖 | trx_lock_structs | bigint(21) unsigned | NO | | 0 | |# | trx_lock_memory_bytes | bigint(21) unsigned | NO | | 0 | |#事務(wù)鎖住的內(nèi)存大小（B） | trx_rows_locked | bigint(21) unsigned | NO | | 0 | |#事務(wù)鎖住的行數(shù) | trx_rows_modified | bigint(21) unsigned | NO | | 0 | |#事務(wù)更改的行數(shù) | trx_concurrency_tickets | bigint(21) unsigned | NO | | 0 | |#事務(wù)并發(fā)票數(shù) | trx_isolation_level | varchar(16) | NO | | | |#事務(wù)隔離級(jí)別 | trx_unique_checks | int(1) | NO | | 0 | |#是否唯一性檢查 | trx_foreign_key_checks | int(1) | NO | | 0 | |#是否外鍵檢查 | trx_last_foreign_key_error | varchar(256) | YES | | NULL | |#最后的外鍵錯(cuò)誤 | trx_adaptive_hash_latched | int(1) | NO | | 0 | |# | trx_adaptive_hash_timeout | bigint(21) unsigned | NO | | 0 | |# +—————————-+———————+——+—–+———————+——-+ 22 rows in set (0.01 sec)
結(jié)論：
時(shí)間過長的update、delete等語句在kill之后會(huì)進(jìn)行回滾操作，會(huì)鎖表，經(jīng)常有人更換不同方式對(duì)大數(shù)據(jù)進(jìn)行修改刪除，然而盲目的殺死正在長時(shí)間運(yùn)行的進(jìn)程后并不能馬上對(duì)表進(jìn)行新的操作，后果只能是等待之前的操作回滾結(jié)束，本想用更快的方式操作表結(jié)果得不償失，所以還是建議選擇好對(duì)表修改操作方式然后一次運(yùn)行，不再修改。
后續(xù)測(cè)試了innodb_force_recovery參數(shù)的修改：
結(jié)論：非緊急情況不允許把innodb_force_recovery修改成非0值！
補(bǔ)充：
今天又遇到一個(gè)新的情況：
當(dāng)我在改一個(gè)小表的表結(jié)構(gòu)時(shí)發(fā)現(xiàn)本應(yīng)該瞬間完成的語句一直沒有執(zhí)行成功，連接數(shù)據(jù)庫端查看發(fā)現(xiàn)此語句正在等待鎖釋放；
于是開始尋找加鎖的源頭
show processlist; select * from information_schema.processlist where time>100 and command<>'sleep';
果然查到了一個(gè)刪除語句；
但是發(fā)現(xiàn)該語句已經(jīng)被kill 狀態(tài)顯示為killed,并且已經(jīng)執(zhí)行了50000s+ 也就是幾天前了；（之所以沒有監(jiān)控到是因?yàn)檫@個(gè)我的過濾條件寫的是where command=‘Query’ 所以這個(gè)killed進(jìn)程就沒有捕捉到）此時(shí)我使用上述查詢查找鎖記錄發(fā)現(xiàn)查找結(jié)果是空的
SELECT * FROM information_schema.INNODB_TRX\G
查詢show engine innodb status 也沒有找到rolling back關(guān)鍵字的大回滾事務(wù)；
（我中間還測(cè)了一下收回賬戶權(quán)限這個(gè)查詢會(huì)不會(huì)消失 ，然而和我推測(cè)的一樣，并沒有什么錘子用）
于是我決定重啟；既然沒有回滾想必重啟應(yīng)該不會(huì)有什么大問題；
所以我就shutdown了一下，mysql登陸不了了，但是進(jìn)程很奇怪的還在；我又手動(dòng)kill -9 了一下；
然后重新啟動(dòng)，成功啟動(dòng)；
new結(jié)論：
遇到killed語句先
SELECT * FROM information_schema.INNODB_TRX\G
看看有沒有什么事務(wù)正在回滾或被鎖住
如果有就安心的等它回滾結(jié)束，暫時(shí)不要用這個(gè)表，如果非要用就新建一個(gè)別名表 在從庫把備份拿過來寫進(jìn)去，讓程序先換個(gè)表讀??；
如果查詢沒有結(jié)果，再次確認(rèn)一下show engine innodb status 有沒有正在回滾的事務(wù) 如果進(jìn)程太多可以重定向出來：
mysql -u -p -P 3306 -e “show engine innodb status” >/tmp/status.txt
斜杠/查找是否存在關(guān)鍵字 rolling back
如果確定沒有回滾語句，就可以安心的重啟了 ；
總結(jié)
以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。
相關(guān)文章
在使用Spring Cloud Alibaba搭建的微服務(wù)架構(gòu)中,項(xiàng)目采用ShardingSphere進(jìn)行分庫分表,MyBatis-Plus作為持久層,線上環(huán)境突發(fā)大量預(yù)編譯語句過多的數(shù)據(jù)庫告警,導(dǎo)致系統(tǒng)性能下降,所以本文給大家介紹了MySQL預(yù)編譯語句過多告警排查及解決方案,需要的朋友可以參考下2025-01-01
MySQL去重中distinct和group?by的區(qū)別淺析
今天無意中聽到有同事在討論,distinct和group by有什么區(qū)別,下面這篇文章主要給大家介紹了關(guān)于MySQL去重中distinct和group?by區(qū)別的相關(guān)資料,需要的朋友可以參考下2022-11-11
在很多時(shí)間我們需要把字符串的數(shù)字給取出來，通常大家會(huì)用php,asp等這類來操作，本文章介紹了在sql中取字符中的數(shù)字辦法，有需要的朋友可以參考一下2012-04-04
索引下推（Index Condition Pushdown）是指讓索引參與更多的篩選工作,減少回表查詢的次數(shù),從而提高查詢效率,本文介紹MySQL的索引下推相關(guān)知識(shí),感興趣的朋友跟隨小編一起看看吧2025-12-12
詳解MySQL中DROP,TRUNCATE 和DELETE的區(qū)別實(shí)現(xiàn)mysql從零開始
注意:這里說的delete是指不帶where子句的delete語句 相同點(diǎn): truncate和不帶where子句的delete, 以及drop都會(huì)刪除表內(nèi)的數(shù)據(jù)2008-04-04
行是表中存儲(chǔ)數(shù)據(jù)的行,也稱為記錄,MySQL的行上限主要受限于兩個(gè)因素,表的大小和操作系統(tǒng)的限制,這篇文章主要給大家介紹了關(guān)于MySQL表列數(shù)和行大小限制的相關(guān)資料,需要的朋友可以參考下2024-04-04
MySQL使用LOAD_FILE()函數(shù)方法總結(jié)
在本篇文章里小編給大家分享了關(guān)于MySQL使用LOAD_FILE()函數(shù)方法和相關(guān)知識(shí)點(diǎn)，需要的朋友們學(xué)習(xí)下。2019-03-03
這篇文章通過實(shí)例跟大家介紹了Mysql中幾種插入方法的效率對(duì)比，這其中包括逐條插入、基于事務(wù)的批量插入、單條語句一次插入多組數(shù)據(jù)以及導(dǎo)入數(shù)據(jù)文件等四種方法的對(duì)比，文中通過實(shí)例代碼介紹很詳細(xì)，需要的朋友們下來來一起看看吧。2017-04-04
聯(lián)合索引的結(jié)構(gòu)與電話簿類似，人名由姓和名構(gòu)成，電話簿首先按姓氏進(jìn)行排序，然后按名字對(duì)有相同姓氏的人進(jìn)行排序，這篇文章主要介紹了MySQL單列索引和聯(lián)合索引,需要的朋友可以參考下2022-09-09
計(jì)算機(jī)管理服務(wù)中找不到mysql的服務(wù)的解決辦法
MySQL是一種流行的開源關(guān)系型數(shù)據(jù)庫管理系統(tǒng),用于存儲(chǔ)和管理大量數(shù)據(jù),在計(jì)算機(jī)管理中,啟動(dòng)MySQL服務(wù)是一項(xiàng)重要的任務(wù),因?yàn)樗梢源_保數(shù)據(jù)庫系統(tǒng)的順利運(yùn)行,這篇文章主要給大家介紹了關(guān)于計(jì)算機(jī)管理服務(wù)中找不到mysql的服務(wù)的解決辦法,需要的朋友可以參考下2023-05-05