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