如何降低MySQL长事务持锁时间减少锁等待-mysql教程-PHP中文网

长事务是锁等待的头号推手，因其持有锁直至commit/rollback，导致行锁、间隙锁长期不释放；应将http调用、pdf生成等非db操作移出事务，并拆分大更新为小批次提交。

如何降低mysql长事务持锁时间减少锁等待

直接结论：长事务是锁等待的头号推手，缩短它比调大 innodb_lock_wait_timeout 有效十倍。

为什么长事务会让锁等得更久

InnoDB 行锁不是“用完即放”，而是绑定在事务生命周期内——只要事务没 COMMIT 或 ROLLBACK，它加过的锁（记录锁、间隙锁、临键锁）就一直不释放。一个执行了 3 分钟的事务，哪怕只在第 1 秒更新了一行，那行（及周边间隙）可能就被锁了整整 3 分钟。

常见诱因包括：事务里调了外部 HTTP 接口、生成 PDF、写本地日志、做复杂计算，或者误把分页查询塞进事务里反复查。

用 SELECT * FROM information_schema.INNODB_TRX WHERE TIME_TO_SEC(TIMEDIFF(NOW(), trx_started)) > 60 能快速揪出“钉子户”事务
配合 SELECT * FROM sys.innodb_lock_waits\G 看谁在等谁、等了多久、SQL 是什么
特别注意 trx_state = 'RUNNING' 但 trx_started 很早的记录——它大概率正在干和数据库无关的事

把非数据库操作移出事务

这是见效最快的操作。事务只该做三件事：读、写、判断是否要回滚。其余一切耗时动作，必须挪到 COMMIT 之后。

反例：BEGIN; UPDATE order SET status=2 WHERE id=123; CALL pay_service(); COMMIT; —— pay_service() 失败前，order 行一直被锁
正例：BEGIN; UPDATE order SET status=2 WHERE id=123; COMMIT; CALL pay_service(); —— 锁在第 2 行就释放了
如果支付失败需要补偿，用异步任务或状态机兜底，别卡在事务里重试

拆分大事务为小批次更新

批量更新百万行？别一气呵成。单次更新几千行 + 显式 COMMIT，能大幅压缩锁持有窗口，也避免 undo log 暴涨。

MySQL

编写正确的MySQL查询，避免字符集、索引和锁方面的常见陷阱。

下载

推荐用主键范围切分，稳定且走索引：

SET @batch_size = 5000;
SET @min_id = (SELECT MIN(id) FROM user WHERE status = 0);
SET @max_id = (SELECT MAX(id) FROM user WHERE status = 0);
<p>WHILE @min_id </p>

每批 COMMIT 后锁立即释放，其他事务可立刻介入
避免用 LIMIT 方式（如 UPDATE ... LIMIT 5000），在高并发下可能漏数据或重复更新
批次大小从 1000 起测，观察 SHOW ENGINE INNODB STATUS 中的 TRANSACTIONS 部分锁等待时间变化

隔离级别与快照读的配合使用

不是所有读都需要加锁。在 REPEATABLE READ 下，普通 SELECT 是快照读（不加锁），而 SELECT ... FOR UPDATE 或 UPDATE 才真正加锁。滥用前者会人为延长锁期。

业务允许“已提交读”结果时，显式设 SET TRANSACTION ISOLATION LEVEL READ COMMITTED;，能减少间隙锁使用
确认是否真需要当前读：比如只是校验库存余量，用普通 SELECT quantity FROM stock WHERE sku='A' 就够了，别加 FOR UPDATE
若必须加锁，优先用 SELECT ... LOCK IN SHARE MODE 替代 FOR UPDATE，兼容性更好，冲突概率更低

真正难的不是写对 SQL，而是识别哪些逻辑“看起来像数据库操作，其实不该在事务里”。比如发短信、写 Kafka、调风控接口——它们失败不该拖垮订单表的并发能力。锁等待问题的根子，往往藏在事务边界设计里，而不是配置参数上。