mysql中事务的性能调优与并发控制技巧

mysql事务隔离级别选错会引发幻读或锁冲突，高并发下REPEATABLE READ易因间隙锁阻塞INSERT；长事务拖慢性能并阻塞purge；批量写入需权衡语法与事务边界；死锁反映资源访问顺序不一致。

事务隔离级别选错会导致幻读或锁冲突

MySQL 默认的 REPEATABLE READ 隔离级别在多数 OLTP 场景下合理，但不是万能解。比如高并发更新同一张订单表时，若业务只要求“读已提交”，却坚持用 REPEATABLE READ，InnoDB 会启用间隙锁（gap lock），导致 INSERT 被意外阻塞——这不是死锁，而是锁范围扩大引发的隐性排队。

实操建议：

• 确认业务是否真需要可重复读：如报表类查询可降为 READ COMMITTED，关闭间隙锁，减少锁竞争
• 用 select ... for UPDATE 前先 EXPLaiN 对应的 WHERE 条件，确保走索引；否则会升级为表级锁
• 避免在 REPEATABLE READ 下执行无索引条件的 delete 或 UPDATE，它会锁住整个索引范围

长事务是性能杀手，必须主动截断

事务持续时间越长，Undo Log 越大，MVCC 可见性判断越慢，同时阻塞 purge 线程清理旧版本。一个运行 10 分钟的事务，可能让 information_schema.INNODB_TRX 中的 TRX_ROWS_LOCKED 持续飙升，拖慢所有并发写入。

实操建议：

• 在应用层设置事务超时：spring Boot 中用 @Transactional(timeout = 5)；php pdo 设置 PDO::ATTR_TIMEOUT
• 禁用自动提交后，务必显式 COMMIT 或 ROLLBACK，不要依赖连接池回收时隐式提交
• 监控长事务：定期查 SELECT * FROM information_schema.INNODB_TRX WHERE TIME_TO_SEC(NOW()) - TIME_TO_SEC(TRX_STARTED) > 60

批量写入别用单条 INSERT，但也不能盲目堆 LOAD DATA

事务内执行 1000 次 INSERT INTO t VALUES (...)，会产生 1000 次 redo log fsync 和锁竞争；而全用 LOAD DATA INFILE 又会绕过行级触发器、外键约束，且无法控制事务边界。

实操建议：

• 批量插入优先用多值语法：INSERT INTO t (a,b) VALUES (1,2),(3,4),(5,6); —— 单事务内最多 1000 行，避免 undo 日志膨胀
• 确认 innodb_log_file_size 足够：若单次批量写入超过 256MB，需调大该值，否则频繁 checkpoint 影响吞吐
• 写入密集场景下，临时关闭唯一性校验（仅限可信数据）：SET unique_checks=0;，导入后再开

死锁不是异常，是并发设计信号

MySQL 的死锁检测开销极小，Deadlock found when trying to get lock 报错本身不致命，但高频出现说明访问资源顺序不一致。比如服务 A 先更新用户表再更新订单表，服务 B 反过来操作，就必然死锁。

实操建议：

• 从 SHOW ENGINE INNODB STATUSG 提取 LATEST DETECTED DEADLOCK 区块，重点看两事务的 SQL 执行顺序和锁类型（record lock / gap lock）
• 统一资源访问顺序：约定按主键 ID 升序更新多行，或按表名字母序加锁
• 避免在事务中调用外部服务（如 http 请求），它会拉长事务窗口，放大死锁概率

SELECT    trx_id,   trx_state,   trx_started,   trx_weight,   trx_query FROM information_schema.INNODB_TRX ORDER BY trx_started LIMIT 5;

真正难调的从来不是参数，而是事务边界的划分粒度——一行 update 是否该独立成事务，取决于它是否构成完整业务语义。锁冲突背后，往往藏着没对齐的领域模型。