记mysql事务锁分析

问题：

业务反馈更新数据表时报错：Error updating database.  Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction. 说明要修改的数据被锁了，且未等到锁释放；

 

分析：

1. 对于云端数据库：

相对内网来说较简单，直接看锁分析定位问题事务及会话ID，下载审计日志对应查找即可，思路与下述一致；

 

2. 对于内网数据库：

查找持续时间过长的问题事务：

SELECT trx_id, trx_mysql_thread_id, trx_state, 
       TIMESTAMPDIFF(SECOND, trx_started, NOW()) as duration_sec,
       trx_query, trx_rows_locked
FROM information_schema.INNODB_TRX 
WHERE trx_state = 'RUNNING' 
ORDER BY duration_sec DESC;

# 或可根据下述命令，查看完整分析，包含状态分析，最近发生的一次死锁，以及运行中的锁；
SHOW ENGINE INNODB STATUS;

观察 会话ID trx_id,  线程ID trx_mysql_thread_id，根据会话ID查找事务详细信息：

SELECT trx_id, trx_state, trx_started, 
       TIMESTAMPDIFF(SECOND, trx_started, NOW()) as duration_sec,
       trx_mysql_thread_id, trx_query 
FROM information_schema.INNODB_TRX 
WHERE trx_mysql_thread_id = 6286576;

trx_query 字段会显示它最后一次执行的SQL语句，如果为空，说明可能是事务执行后未正确提交（设置autocommit=0后忘记提交，默认autocommit=1），导致锁表；

thread_id可能无用，因为未开启Performance Schema。生产环境不建议开启，会导致性能下降。

SHOW VARIABLES LIKE 'performance_schema'

如果 trx_query 为空，则通过数据库审计日志，根据会话ID查找其中的DDL操作（select和心跳报文可忽略），可定位问题语句；

另关于审计日志，有多种临时开启日志、binlog分析、或安装插件的方式，暂未试过，此处不再赘述。

 

3. 临时恢复：

定位问题根因后，kill 会话ID，终止该事务，原sql会回滚。

 

4. 优化 - 自动kill长事务：

1）pt-kill（生产环境推荐，但不适用于云产品）

2）MySQL Event + 存储过程（无额外依赖）

此处主要尝试第二种方式：

a. 开启事件调度器

-- 全局开启
SET GLOBAL event_scheduler = ON;

-- 确认状态
SHOW VARIABLES LIKE 'event_scheduler';

b. 创建存储过程（自动KILL超过5分钟的事务）

DROP PROCEDURE IF EXISTS `kill_long_transactions_in_db`;

DELIMITER $$

CREATE PROCEDURE `kill_long_transactions_in_db`()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE trx_id VARCHAR(50);
    DECLARE thread_id BIGINT;
    -- 声明一个游标，只查询指定数据库的长事务
    DECLARE cur CURSOR FOR 
        SELECT trx.trx_id, trx.trx_mysql_thread_id
        FROM information_schema.INNODB_TRX trxWHERE TIMESTAMPDIFF(SECOND, trx.trx_started, NOW()) > 300;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO trx_id, thread_id;
        IF done THEN
            LEAVE read_loop;
        END IF;
        -- 动态构造 KILL 命令
        SET @kill_sql = CONCAT('KILL ', thread_id);
        PREPARE stmt FROM @kill_sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END LOOP;
    CLOSE cur;
END$$

DELIMITER ;

手动执行一次检验：

CALL kill_long_transactions_in_db();

c. 创建Event并启用

CREATE EVENT `auto_kill_long_trx`
ON SCHEDULE EVERY 1 MINUTE
STARTS CURRENT_TIMESTAMP
DO
    CALL kill_long_transactions();

-- 查看Event状态
SHOW EVENTS;