MySQL Kill出现Killed死锁处理

ゝ一纸荒年。 2022-09-16 06:08 179阅读 0赞

> 记一次线上MySQL故障排查处理方式，数据库版本为MySQL 5.6.37。

#### 一、事件起因 ####

在线下降，查看程序日志出现锁等待超时日志，进入MySQL查看

# 查看当前线程处理情况
    > show processlist
    
    # 使用如下语句筛选查看具体情况
    > select * from information_schema.processlist where time>500;

show processlist 输出列值：

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发现还有一个已执行6530s的update会话处于killed状态

此时不要盲目重启， **重启MySQL后进程消失但锁依然存在！**

重启MySQL后进程消失但锁依然存在，因为回滚还要继续，为了保证数据的一致性。

但是盲目的等待锁释放心里没底，所以我们可以通过下面的方式计算出这个锁什么时候能够释放，我们就可以使用表了。

*  查看innodb事务信息表

# 查看内部执行的每个事务的信息，包括事务是否正在等待锁定、事务何时开始以及事务正在执行的 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

**总结:** 时间过长的update、delete等语句在kill之后会进行回滚操作，会锁表，此时不要盲目的变换方式去对该表进行操作，先使用\*\*> SELECT \* FROM information\_schema.INNODB\_TRX\\G;\*\*语句查看有没有什么事务正在回滚或被锁住，如果有最好等待之前的操作回滚结束。如果实在要着急使用该表，可以把主库表拷贝过来换个表名读取。

**Reference:**

*  [https://dev.mysql.com/doc/refman/5.6/en/show-processlist.html][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_dev.mysql.com_doc_refman_5.6_en_information-schema-innodb-trx-table.html]

[https_dev.mysql.com_doc_refman_5.6_en_show-processlist.html]: 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://dev.mysql.com/doc/refman/5.6/en/information-schema-innodb-trx-table.html