mysql kill进程后出现killed死锁问题及解决
目录
- mysql kill进程后出现killed死锁
- 经常会出现这样的场景
- 场景
- 通过下列语句查询事务情况
- 查看表锁信息
- 总结
mysql kill进程后出现killed死锁
经常会出现这样的场景
有一张3亿的表，现在要对这张表进行删除1亿行，于是有人开始运行
delete from table limit 100000000;
毫无疑问这是一个愚蠢的删除方式，于是有人开始变更删除方式：
delete from table where id<100000000;
然而运行一段时间后，又发现批量删除的效率可能会更高，所以kill掉了上一条运行了一段时间的sql，开始批量删除， 由于是大sql，晚上点击运行想第二天早上来看结果的DBA就会遗憾的发现新执行的sql被锁给挡了回来，并没有运行，导致浪费http://www.devze.com了一晚上的时间。
但是盲目的等待锁释放心里没底，所以我们可以通过下面的方式计算出这个锁什么时候能够释放，我们就可以使用表了。
场景
一个巨大的delete语python句 执行一小时后kill ，
show processlist出现killed进程 ，
不要盲目重启！ 重启MySQL后进程消失但锁依然存在！
重启MySQL后进程消失但锁依然存在，因为回滚还要继续，这是mysql对数据的保护机制
通过下列语句查询事务情况
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 #代表锁影响的行数，当数值为0时，锁将会释放
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: 代表锁影响的行数，当数值为0时，锁将会释放
查看表锁信息
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 | | | |#拥有锁的事务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 | |#被锁的表空间号 | lock_page | bigint(21) unsigned | YES | | NULL | |#被锁的页号 | lock_rec | bigint(21) unsigned | YES | | NULL | |#被锁的记录号 | lock_data | varchar(8192) | YES | | NULL | |#被锁的数据 +————-+———————+——+—–+———+——-+ 10 rows in set (0.00 sec) desc innodb_lock_waits; +——————-+————-+——+—–+———+——-+ | Field | Type | Null | Key | Default | Extra | +——————-+————-+——+—–+———+——-+ | requesting_trx_id | varchar(18) | NO | | | |#请求锁的事务ID | requested_lock_id | varchar(81) | NO | | | |#请求锁的锁ID | blocking_trx_id | varchar(18) | NO | | | |#当前拥有锁的事务ID | blocking_lock_id | varchar(81) | NO | | | |#当前拥有锁的锁ID +——————-+————-+——+—–+———+——-+ 4 rows in set (0.00 sec) desc innodb_trx ; +—————————-+———————+——+—–+———————+——-+ | Field | Type | Null | Key | Default | Extra | +—————————-+———————+——+—–+———————+——-+ | trx_id | varchar(18) | NO | | | |#事务ID | trx_state | varchar(13) | NO | | | |#事务状态： | trx_started | datetime | NO | | 0000-00-00 00:00:00 | |#事务开始时间； python| trx_requested_lock_id | varchar(81) | YES | | NULL | |#innodb_locks.lock_id | trx_wait_started | datetime | YES | | NULL | |#事务开始等待的时间 | trx_weight | bigint(21) unsigned | NO | | 0 | |# | trx_mysql_thread_id | bigint(21) unsigned | NO | | 0 | |#事务线程ID | trx_query | varchar(1024) | YES | | NULL | |#具体SQL语句 | trx_operation_state | varchar(64) | YES | | NULL | |#事务当前操作状态 | trx_tables_in_use | bigint(21) unsigned | NO | | 0 | |#事务中有多少个表被使用 | trx_tables_locked | bigint(21) unsigned | NO | | 0 | |#事务拥有多少个锁 | trx_lock_structs | bigint(21) unsigned | NO | | 0 | |# | trx_lock_memory_bytes | bigint(21) unsigned | NO | | 0 | |#事务锁住的内存大小（B） | trx_rows_locked | bigint(21) unsigned | NO | | 0 | |#事务锁住的行数 | trx_rows_modified | bigint(21) unsigned | NO | | 0 | |#事务更改的行数 | trx_concurrency_tickets | bigint(21) unsigned | NO | | 0 | |#事务并发票数 | trx_isolation_level | varchar(16) | NO | | | |#事务隔离级别 | trx_unique_checks | int(1) | NO | | 0 | |#是否唯一性检查 | trx_foreign_key_checks | int(1) | NO | | 0 | php |#是否外键检查 | trx_last_foreign_key_error | varchar(256) | YES | | NULL | |#最后的外键错误 | trx_adaptive_hash_latched | int(1) | NO | | 0 | php|# | trx_adaptive_hash_timeout | bigint(21) unsigned | NO | | 0 | |# +—————————-+———————+——+—–+———————+——-+ 22 rows in set (0.01 sec)
结论：
时间过长的update、delete等语句在kill之后会进行回滚操作，会锁表，经常有人更换不同方式对大数据进行修改删除，然而盲目的杀死正在长时间运行的进程后并不能马上对表进行新的操作，后果只能是等待之前的操作回滚结束，本想用更快的方式操作表结果得不偿失，所以还是建议选择好对表修改操作方式然后一次运行，不再修改。
后续测试了innodb_force_recovery参数的修改：
结论：非紧急情况不允许把innodb_force_recovery修改成非0值！
补充：
今天又遇到一个新的情况：
当我在改一个小表的表结构时发现本应该瞬间完成的语句一直没有执行成功，连接数据库端查看发现此语句正在等待锁释放；
于是开始寻找加锁的源头
show processlist; select * from information_schema.processlist where time>100 and command<>'sleep';
果然查到了一个删除语句；
但是发现该语句已经被kill 状态显示为killed,并且已经执行了50000s+ 也就是几天前了；（之所以没有监控到是因为这个我的过滤条件写的是where command=‘Query’ 所以这个killed进程就没有捕捉到）此时我使用上述查询查找锁记录发现查找结果是空的
SELECT * FROM information_schema.INNODB_TRX\G
查询show engine innodb status 也没有找到rolling back关键字的大回滚事务；
（我中间还测了一下收回账户权限这个查询会不会消失 ，然而和我推测的一样，并没有什么锤子用）
于是我决定重启；既然没有回滚想必重启应该不会有什么大问题；
所以我就shutdown了一下，mysql登陆不了了，但是进程很奇怪的还在；我又手动kill -9 了一下；
然后重新启动，成功启动；
new结论：
遇到killed语句先
SELECT * FROM information_schema.INNODB_TRX\G
看看有没有什么事务正在回滚或被锁住
如果有就安心的等它回滚结束，暂时不要用这个表，如果非要用就新建一个别名表 在从库把备份拿过来写进去，让程序先换个表读取；
如果查询没有结果，再次确认一下show engine innodb status 有没有正在回滚的事务 如果进程太多可以重定向出来：
mysql -u -p -P 3306 -e “show engine innodb status” >/tmp/status.txt
斜杠/查找是否存在关键字 rolling back
如果确定没有回滚语句，就可以安心的重启了 ；
总结
以上为个人经验，希望能给大家一个参考，也希望大家多多支持编程客栈(www.devze.com)。
加载中,请稍侯......
精彩评论