MySQL默认隔离级别下SELECT不加锁，UPDATE会触发行锁；快照读无锁，当前读加行锁或间隙锁；索引缺失会导致表级阻塞；INSERT...ON DUPLICATE KEY UPDATE有死锁风险；长事务引发Undo膨胀与性能下降。

mysql中事务的并发控制与数据一致性保证 第1张

MySQL 默认隔离级别下 SELECT 不加锁，但 UPDATE 会触发行锁

MySQL 的 InnoDB 引擎在 REPEATABLE READ（默认隔离级别）下，普通 SELECT 是快照读（snapshot read），不加锁，也不会阻塞其他事务；而 UPDATE、DELETE、INSERT 或带 FOR UPDATE/LOCK IN SHARE MODE 的 SELECT 是当前读（current read），会加行级记录锁（record lock）或间隙锁（gap lock）。

常见误判是以为“没写 FOR UPDATE 就绝对安全”，其实只要语句命中索引且涉及修改，InnoDB 就会基于聚簇索引加锁——哪怕你只 UPDATE 一个字段，整行记录仍被锁定。

  • 若 WHERE 条件未命中索引，可能升级为表锁（全表扫描 + 每行加锁），并发性能骤降
  • UPDATE t SET x = x + 1 WHERE id = 100 和 UPDATE t SET x = 10 WHERE id = 100 加锁行为一致，锁的是 id = 100 对应的聚簇索引记录
  • 唯一索引等值查询只加 record lock；非唯一索引或范围查询会额外加 gap lock，防止幻读

用 SELECT ... FOR UPDATE 显式加锁时，必须确保走索引

显式加锁是控制并发最直接的手段，但效果完全依赖执行计划。如果 FOR UPDATE 语句无法使用索引，InnoDB 会退化为锁全表（实际是锁所有扫描到的索引页，但效果接近表级阻塞）。

可通过 EXPLAIN 验证是否走了索引：重点关注 type 字段是否为 const、ref 或 range，以及 key 是否显示实际索引名。

  • ✅ 正确：SELECT * FROM order WHERE order_no = 'ORD2024001' FOR UPDATE（order_no 有唯一索引）
  • ❌ 危险：SELECT * FROM order WHERE status = 'pending' FOR UPDATE（status 无索引 → 全表扫描 + 行锁堆积）
  • ⚠️ 注意：FOR UPDATE 在可重复读下也会加 gap lock，比如 WHERE id > 100 会锁住 (100, +∞) 的间隙，不只是已有记录

INSERT ... ON DUPLICATE KEY UPDATE 的原子性与死锁风险

该语句在存在唯一键冲突时自动转为 UPDATE，看似能避免先查后更的竞态，但它内部仍分两步：先尝试插入，失败则执行更新。整个过程是原子的，但加锁行为复杂——InnoDB 会对插入意向间隙锁（insert intention gap lock）和后续可能触发的记录锁同时持有。

高并发下容易因锁顺序不一致引发死锁，尤其当多个事务按不同顺序操作同一组主键/唯一键时。

  • 死锁日志中常出现 lock_mode X locks rec but not gap waiting 和 lock_mode X locks gap before rec insert intention waiting 并存
  • 缓解方式：确保所有业务逻辑按相同字段顺序（如始终按 user_id 升序）批量处理；或改用 INSERT IGNORE + 应用层重试
  • 注意：ON DUPLICATE KEY UPDATE 中的 UPDATE 部分不会触发 BEFORE/AFTER UPDATE 触发器

长事务导致 MVCC 快照过旧，引发 Undo Log 膨胀与查询变慢

InnoDB 通过 Undo Log 维护多版本，每个事务开始时确定自己的“快照视图”。如果一个事务长时间不提交（比如应用端忘记 COMMIT 或卡在慢查询里），它持有的低水位（low watermark）会阻止系统清理早于该时间点的 undo 日志，导致 ibdata1 或独立 undo 表空间持续增长，甚至撑爆磁盘。

更隐蔽的影响是：其他事务的 SELECT 需要回溯更多版本链，CPU 和 buffer pool 压力上升，简单查询响应变慢。

  • 监控活跃长事务：SELECT * FROM information_schema.INNODB_TRX WHERE TIME_TO_SEC(TIMEDIFF(NOW(), TRX_STARTED)) > 60
  • 设置超时强制回滚：SET SESSION innodb_lock_wait_timeout = 30（仅对锁等待生效），或应用层统一加事务超时控制
  • 避免在事务内做 HTTP 请求、文件读写、sleep 等外部耗时操作
SELECT 
  trx_id,
  trx_state,
  trx_started,
  trx_wait_started,
  TIME_TO_SEC(TIMEDIFF(NOW(), trx_started)) AS duration_sec,
  trx_query
FROM information_schema.INNODB_TRX 
WHERE TIME_TO_SEC(TIMEDIFF(NOW(), trx_started)) > 120
ORDER BY duration_sec DESC;

真正难处理的不是锁本身，而是锁和业务逻辑节奏错配：比如一个本该秒级完成的扣库存事务，因为上游调用方重试策略激进，导致同一订单被多个线程反复争抢同一条记录，间隙锁不断叠加，最终拖垮整个订单库。这种问题光看 SQL 语法看不出毛病，得结合压测时的 SHOW ENGINE INNODB STATUS 输出和应用调用链一起看。