mysql delete大量数据表锁死，kill的线程后线程处于killed状态问题解决
一、事件起因
删除一张500G的表，没有添加任何约束条件，结果好久都没反应，查询锁之后，使用kill杀掉了进程，再次查询的时候，锁还在，trx_state的状态是ROLLING BACK，使用show processlist查询的时候显示进程已经是killed，但是锁仍然存在。
查看当前线程处理情况
show processlist
使用如下语句筛选查看具体情况
select * from information_schema.processlist where time>500;
Id # 连接标识符。这ID与INFORMATION_SCHEMA PROCESSLIST表列中显示的PROCESSLIST_ID值、Performance Schemathreads 表列中显示的CONNECTION_ID()值以及线程内函数返回的值相同。
User # MySQL 用户
Host # 发出语句的客户端的主机名（除了system user没有主机的 ）
db # 线程的默认数据库
Command # 线程代表客户端执行的命令类型，或者Sleep会话是否空闲。
Time # 线程处于当前状态的时间（以秒为单位）。对于副本 SQL 线程，该值是上次复制事件的时间戳与副本主机的实时时间之间的秒数。
State # 指示线程正在执行的操作的操作、事件或状态。
Info # 线程执行的sql语句，如果没有语句执行则为null。这个语句可以使客户端发来的执行语句也可以是内部执行的语句
二、故障处理
kill有问题的sql线程
查询执行时间超过2分钟的线程，然后拼接成 kill 语句
select *,concat('kill ', id, ';')
from information_schema.processlist
where command != 'Sleep'
and time > 2*60
order by time desc
kill语句执行之后show processlist发现还有一个已执行16530s的delete会话处于killed状态
此时不要盲目重启， 重启MySQL后进程消失但锁依然存在！
重启MySQL后进程消失但锁依然存在，因为回滚还要继续，为了保证数据的一致性。
但是盲目的等待锁释放心里没底，所以我们可以通过下面的方式计算出这个锁什么时候能够释放，我们就可以使用表了。
查看innodb事务信息表
查看内部执行的每个事务的信息，包括事务是否正在等待锁定、事务何时开始以及事务正在执行的 SQL 语句（如果有）
SELECT * FROM information_schema.INNODB_TRX;
# 查看内部执行的每个事务的信息，包括事务是否正在等待锁定、事务何时开始以及事务正在执行的 SQL 语句（如果有）
# SELECT * FROM information_schema.INNODB_TRX\G;
......
*************************** 91. row ***************************
trx_id: 994701900248
trx_state: ROLLING BACK
trx_started: 2021-10-16 01:30:15
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 28257129
trx_mysql_thread_id: 179422242
trx_query: update xxxx
trx_operation_state: rollback
trx_tables_in_use: 1
trx_tables_locked: 1
trx_lock_structs: 7764183
trx_lock_memory_bytes: 999306792
trx_rows_locked: 117272180 # 此事务锁定的大致数量或行数。该值可能包括物理上存在但对事务不可见的删除标记行。
trx_rows_modified: 20492946 # 此事务中修改和插入的行数，为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_adaptive_hash_timeout: 10000
trx_is_read_only: 0
trx_autocommit_non_locking: 0
trx_rows_modified: 代表锁影响的行数，当数值为0时，锁将会释放。其中trx_rows_locked值不用关注。
查看表锁信息
SELECT * FROM information_schema.INNODB_LOCKS
SELECT * FROM information_schema.INNODB_LOCK_waits
总结: 因为delete数据量大，没加limit或where没有命中索引，所以删要加limit会更安全。删除大量数据，会在临时数据区生成用于回滚的数据，delete操作被kill时，就会根据这些回滚数据还原。数据量越大，这个过程就越慢。时间过长的update、delete等语句在kill之后会进行回滚操作，会锁表，此时不要盲目的变换方式去对该表进行操作，先使用SELECT * FROM information_schema.INNODB_TRX;语句查看有没有什么事务正在回滚或被锁住，如果有最好等待之前的操作回滚结束。如果实在要着急使用该表，可以把主库表拷贝过来换个表名读取。
Reference:
https://dev.mysql.com/doc/refman/5.6/en/show-processlist.html
https://dev.mysql.com/doc/refman/5.6/en/information-schema-innodb-trx-table.html
https://blog.csdn.net/qq_25854057/article/details/120801744
