mysql存储引擎与事务管理中的常见问题与解决

MyISAM不支持事务，START TRANSACTION和ROLLBACK无效；InnoDB需确保索引使用、避免长事务、理解幻读机制及锁行为。

MyISAM 不支持事务，START TRANSACTION 无效

如果你在 MyISAM 表上执行 START TRANSACTION 或 ROLLBACK，MySQL 不会报错，但也不会真正回滚——所有 DML 操作仍会立即生效。这是因为 MyISAM 根本没有事务日志和行级锁机制。

• 检查当前表引擎：

  SHOW CREATE TABLE `your_table`;

  看 ENGINE=MyISAM 还是 ENGINE=InnoDB
• 转换引擎（需确保无外键依赖）：

  ALTER TABLE `your_table` ENGINE = InnoDB;

• 注意：MyISAM 的 AUTO_INCREMENT 是表级锁，高并发插入时可能成为瓶颈；InnoDB 是行级锁 + MVCC，更适合事务场景

InnoDB 中 SELECT ... FOR UPDATE 锁行为不按预期生效

SELECT ... FOR UPDATE 只在事务内、且隔离级别不低于 REPEATABLE READ 时才起作用；如果查询条件未命中索引，InnoDB 会升级为表锁，导致严重阻塞。

• 必须确认查询走了索引：用

  EXPLAIN SELECT ... FOR UPDATE;

  查看 key 字段是否非 NULL
• 避免在 WHERE 中使用函数或隐式类型转换（如 WHERE id = '123' 而 id 是整型），否则索引失效
• 若只读不更新，优先用 SELECT ... LOCK IN SHARE MODE 降低锁粒度

事务长时间未提交导致 Undo Log 膨胀与 ibdata1 文件持续增长

InnoDB 的 Undo Log 在事务提交前不能清理，长事务会阻止 purge 线程回收旧版本数据，最终拖慢查询、撑爆磁盘。

• 查活跃长事务：

  SELECT * FROM information_schema.INNODB_TRX WHERE TIME_TO_SEC(TIMEDIFF(NOW(), TRX_STARTED)) > 60;

• 避免在应用层开启事务后「先查再等用户输入」这类交互式操作
• 设置超时防护：innodb_lock_wait_timeout = 50（单位秒），配合应用层重试逻辑
• 注意：MySQL 5.7+ 默认 innodb_undo_log_truncate = ON，但仅对超过 innodb_max_undo_log_size 的独立 undo 表空间有效；共享表空间 ibdata1 中的 undo 永远不会自动收缩

幻读在 REPEATABLE READ 隔离级别下仍可能发生

很多人误以为 REPEATABLE READ 能完全避免幻读，其实 MySQL 的实现是通过间隙锁（Gap Lock）+ Next-Key Lock 抑制大部分幻读，但仅限于「当前读」（如 SELECT ... FOR UPDATE）。快照读（普通 SELECT）仍基于 MVCC，不加锁，因此不会感知新插入的记录。

• 验证幻读场景：事务 A 执行 SELECT * FROM t WHERE id > 100（快照读），事务 B 插入 id = 105 并提交，事务 A 再次执行相同 SELECT 仍看不到该行——这不是幻读；但如果 A 改用 SELECT ... FOR UPDATE，B 的插入会被阻塞或报死锁
• 真正需要串行化语义时，不要依赖隔离级别，而应显式加锁或用应用层分布式锁
• 注意：MySQL 8.0 开始，READ COMMITTED 下间隙锁默认被禁用（innodb_locks_unsafe_for_binlog = OFF），进一步削弱幻读控制能力

事务管理不是开关一开就万事大吉的事，InnoDB 的锁、日志、隔离级别之间耦合极深，一个参数调错或一条 SQL 写偏，就可能让并发表现和预期天差地别。尤其要注意那些“不报错但没效果”的情况，比如 MyISAM 上的 ROLLBACK，或者没走索引的 FOR UPDATE。