一、锁超时

  • 先查看当前事务,看看有没有事务时间超时的

    SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

    查到如下结果:

    INSERT INTO information_schema.INNODB_TRX (trx_id, trx_state, trx_started, trx_requested_lock_id, trx_wait_started, trx_weight, trx_mysql_thread_id, trx_query, trx_operation_state, trx_tables_in_use, trx_tables_locked, trx_lock_structs, trx_lock_memory_bytes, trx_rows_locked, trx_rows_modified, trx_concurrency_tickets, trx_isolation_level, trx_unique_checks, trx_foreign_key_checks, trx_last_foreign_key_error, trx_adaptive_hash_latched, trx_adaptive_hash_timeout, trx_is_read_only, trx_autocommit_non_locking) VALUES ('560205677', 'RUNNING', '2022-08-13 12:07:28', null, null, 4, 8297971, null, null, 0, 1, 3, 1136, 2, 1, 0, 'READ COMMITTED', 1, 1, null, 0, 0, 0, 0);

    如果trx_started这个时间距离现在已经过去很久了,说明这个事务一直没提交,我们需要把这个进程杀掉,这个放在最后处理,因为我们还要查看多少事务被这个影响了,以及查到这个长事务正在执行什么sql?

  • 查看有没有被锁住的事务

    SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

  • 查看等待锁的事务

    SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

  • 查看超时的事务是在执行什么sql

    SELECT t.trx_mysql_thread_id AS connection_id ,t.trx_id AS trx_id ,t.trx_state AS trx_state ,t.trx_started AS trx_started ,TIMESTAMPDIFF(SECOND,t.trx_started, now()) AS "trx_run_time(s)" ,t.trx_requested_lock_id AS trx_requested_lock_id ,t.trx_operation_state AS trx_operation_state ,t.trx_tables_in_use AS trx_tables_in_use ,t.trx_tables_locked AS trx_tables_locked ,t.trx_rows_locked AS trx_rows_locked ,t.trx_isolation_level AS trx_isolation_level ,t.trx_is_read_only AS trx_is_read_only ,t.trx_autocommit_non_locking AS trx_autocommit_non_locking ,e.event_name AS event_name ,e.timer_wait / 1000000000000 AS timer_wait ,e.sql_text FROM information_schema.innodb_trx t, performance_schema.events_statements_current e, performance_schema.threads c WHERE t.trx_mysql_thread_id = c.processlist_id AND e.thread_id = c.thread_id;

  • 最后查到结果后,把长事务给杀掉

    把第一步查到的trx_mysql_thread_id拿出来,kill掉

    kill trx_mysql_thread_id;

  • mysql无法检测长事务,会导致线上sql一直被这个事务占据

    我们可以使用pt-kill写个脚本去自动处理,具体操作这里不明细