06-mysql常见问题

查看查询语句是否是自动提交事务

-- 查看当前会话的自动提交状态
SELECT @@autocommit;

• 返回 1：自动提交已开启（每条 SQL 独立提交事务）
• 返回 0：自动提交已关闭（需显式提交事务）

定位与处理长事务

-- 查找因查询导致的长事务
SELECT 
  trx.trx_id,
  trx.trx_started,
  TIMEDIFF(NOW(), trx.trx_started) AS duration,
  pl.user,
  pl.host,
  pl.info AS current_sql
FROM information_schema.INNODB_TRX trx
JOIN information_schema.PROCESSLIST pl 
  ON trx.trx_mysql_thread_id = pl.id
WHERE trx.trx_query IS NULL  -- 当前不在执行SQL
  AND pl.command = 'Sleep'   -- 连接空闲
  AND TIME_TO_SEC(TIMEDIFF(NOW(), trx.trx_started)) > 60;

-- 查看运行超过60秒的事务
SELECT * FROM information_schema.INNODB_TRX 
WHERE TIME_TO_SEC(TIMEDIFF(NOW(), trx_started)) > 60
ORDER BY trx_started;

-- 查看关联线程
SELECT p.ID, p.USER, p.HOST, p.DB, p.TIME, p.COMMAND 
FROM information_schema.PROCESSLIST p
JOIN information_schema.INNODB_TRX t 
  ON p.ID = t.trx_mysql_thread_id
WHERE t.trx_started < NOW() - INTERVAL 60 SECOND;