526互联

MySQL 使用Navicat delete/insert into/update 大量数据表锁死，kill的线程后线程处于killed状态问题解决

发布时间 2023-08-30 17:52:35作者: zhuzhu&you

    MySQL 使用delete/insert into/update 大量数据表锁死，kill的线程后线程处于killed状态问题解决

  1. 实际生产环境问题描述：
  • 使用Navicat备份BigData数据表时不小心点到了取消按钮，导致数据表被锁。  
  • 查看MySQL线程队列，找到刚刚执行的SQL看是属于什么状态。
show processlisth
  • 或使用下列语句查看具体情况
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。这个语句可以使客户端发来的执行语句也可以是内部执行的语句w我这里
  • 我这里的Command状态当时是Killed状态：
  • 因为我Show processlisth 之后kill id 掉了insert into进程，由于数据量过大，kill后处于等待状态(因为数据表是处于锁定状态)找了老半天才找到问题他喵的竟然触发了事务，导致数据回滚。当前线程一直停不下来。
  • 提示：

　　　　生产环境一切留心眼，不要找不到问题就reboot DB. 因为重启MySQL后进程虽然会消失但锁依然存在！因为回滚还要继续，为了保证数据的一致性。

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

        查看innodb事务信息表

   说明： trx_rows_modified: 代表锁影响的行数，当数值为0时，锁将会释放。其中trx_rows_locked值不用关注。

SELECT * FROM information_schema.INNODB_TRX;
# 查看内部执行的每个事务的信息，包括事务是否正在等待锁定、事务何时开始以及事务正在执行的 SQL 语句（如果有）

# SELECT * FROM information_schema.INNODB_TRX;

......
*************************** 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

　　总结：问题既然已经排查出来，就等着数据回滚释放表了。

借鉴文章：https://www.dianjilingqu.com/657011.html
MySQL官网推荐： https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_process