MySQL Kill出现Killed死锁处理

记一次线上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: 994701900248trx_state: ROLLING BACK         # 值为 RUNNING(运行), LOCK WAIT(等待锁), ROLLING BACK(正在回滚), 和 COMMITTING(正在提交)trx_started: 2021-10-16 01:30:15  # 事务开始时间trx_requested_lock_id: NULLtrx_wait_started: NULLtrx_weight: 28257129trx_mysql_thread_id: 179422242            # MySQL线程IDtrx_query: update xxxxtrx_operation_state: rollbacktrx_tables_in_use: 1                    # 事务执行SQL语句时使用的表的数量trx_tables_locked: 1                    # 当前SQL语句在多少张表(表的数量)上持有锁。因为这些是行锁，不是表锁，所以，尽管某些行被锁定，这些表仍然可以被多个事务读写。trx_lock_structs: 7764183              # 事务保留的锁数trx_lock_memory_bytes: 999306792trx_rows_locked: 117272180  # 此事务锁定的大致数量或行数。该值可能包括物理上存在但对事务不可见的删除标记行。trx_rows_modified: 20492946   # 此事务中修改和插入的行数，为0时，锁将会释放trx_concurrency_tickets: 0trx_isolation_level: REPEATABLE READtrx_unique_checks: 1trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULLtrx_adaptive_hash_latched: 0trx_adaptive_hash_timeout: 10000trx_is_read_only: 0
trx_autocommit_non_locking: 0

本次事务是回滚操作，trx_rows_modified 代表锁影响的行数，当数值为0时，锁将会释放。其中 trx_rows_locked 值不用关注。

• 查看表锁信息

# 当前出现的锁，各个事务请求的数据库锁但是仍然没有获取的数据库锁。这张表提供的最重要的信息是请求锁的事务id
> SELECT * FROM information_schema.INNODB_LOCKS;# 锁等待的对应关系,这张表中requesting_trx_id代表了申请锁资源的事务ID,requesting_lock_id代表申请的锁id,blocking_trx_id代表了阻塞事务requesting_trx_id的事务id,blocking_lock_id代表了阻塞事务requesting_trx_id的锁的ID
> SELECT * FROM information_schema.INNODB_LOCK_waits

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

Reference:

• .6/en/show-processlist.html
• .6/en/information-schema-innodb-trx-table.html

MySQL Kill出现Killed死锁处理

记一次线上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: 994701900248trx_state: ROLLING BACK         # 值为 RUNNING(运行), LOCK WAIT(等待锁), ROLLING BACK(正在回滚), 和 COMMITTING(正在提交)trx_started: 2021-10-16 01:30:15  # 事务开始时间trx_requested_lock_id: NULLtrx_wait_started: NULLtrx_weight: 28257129trx_mysql_thread_id: 179422242            # MySQL线程IDtrx_query: update xxxxtrx_operation_state: rollbacktrx_tables_in_use: 1                    # 事务执行SQL语句时使用的表的数量trx_tables_locked: 1                    # 当前SQL语句在多少张表(表的数量)上持有锁。因为这些是行锁，不是表锁，所以，尽管某些行被锁定，这些表仍然可以被多个事务读写。trx_lock_structs: 7764183              # 事务保留的锁数trx_lock_memory_bytes: 999306792trx_rows_locked: 117272180  # 此事务锁定的大致数量或行数。该值可能包括物理上存在但对事务不可见的删除标记行。trx_rows_modified: 20492946   # 此事务中修改和插入的行数，为0时，锁将会释放trx_concurrency_tickets: 0trx_isolation_level: REPEATABLE READtrx_unique_checks: 1trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULLtrx_adaptive_hash_latched: 0trx_adaptive_hash_timeout: 10000trx_is_read_only: 0
trx_autocommit_non_locking: 0

本次事务是回滚操作，trx_rows_modified 代表锁影响的行数，当数值为0时，锁将会释放。其中 trx_rows_locked 值不用关注。

• 查看表锁信息

# 当前出现的锁，各个事务请求的数据库锁但是仍然没有获取的数据库锁。这张表提供的最重要的信息是请求锁的事务id
> SELECT * FROM information_schema.INNODB_LOCKS;# 锁等待的对应关系,这张表中requesting_trx_id代表了申请锁资源的事务ID,requesting_lock_id代表申请的锁id,blocking_trx_id代表了阻塞事务requesting_trx_id的事务id,blocking_lock_id代表了阻塞事务requesting_trx_id的锁的ID
> SELECT * FROM information_schema.INNODB_LOCK_waits

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

Reference:

• .6/en/show-processlist.html
• .6/en/information-schema-innodb-trx-table.html