SQL如何根据业务场景选择悲观锁或乐观锁_写冲突频率评估

写冲突高时悲观锁更稳，但需精准锁行、事务要短；冲突低时乐观锁更优，需正确校验 version 并避免缓存复用；量化应看 UPDATE 影响行为 0 的比例，超 10% 需优化，超 30% 应切悲观锁。

写冲突频率高时，悲观锁更稳但别硬扛

高频写冲突（比如秒杀库存扣减、订单状态强一致更新）下，SELECT …… FOR UPDATE 或 UPDATE …… WHERE version = ? 这类操作失败率会明显上升。乐观锁靠重试兜底，但若每 3 次更新就有 2 次冲突，重试成本就压不住了——网络往返 + 业务逻辑重复执行 + 可能的副作用（如发了两次通知）。这时候直接上悲观锁更可控，但得注意： 锁范围必须精准 ，否则容易锁表或锁住无关行。

• 用 WHERE 条件确保只锁目标行（避免全表扫描导致锁升级）
• 事务尽量短，拿到锁后立刻更新，别在事务里做 HTTP 调用或复杂计算
• MySQL 默认是 REPEATABLE READ，间隙锁可能意外锁住范围，测试时用 SELECT …… FOR UPDATE + EXPLAIN 看执行计划

写冲突低且读多时，乐观锁省资源也少阻塞

用户资料修改、文章点赞数更新这类场景，冲突概率通常低于 5%。乐观锁用 version 字段或 timestamp 做条件更新，失败时抛 OptimisticLockException 或检查 ROW_COUNT()，由应用决定重试或提示。好处是没锁等待、不占锁资源、并发吞吐高；坏处是业务层得处理“更新失败”这个分支，不能假设 UPDATE 一定成功。

• 别把 version 放在缓存里复用——缓存可能 stale，导致误判冲突
• UPDATE t SET count = count + 1, version = version + 1 WHERE id = ? AND version = ? 这种语句，version 必须是查询时拿到的最新值
• PostgreSQL 的 SELECT …… FOR NO KEY UPDATE 在某些低冲突场景下可作折中，但不如乐观锁轻量

怎么量化“写冲突频率”而不是拍脑袋

不能靠“感觉”，得看真实数据。核心是统计单位时间内对同一行（或同一业务主键）的并发写请求中，实际发生冲突的比例。不是看总 QPS，而是看 UPDATE 返回影响行数为 0 的次数占比。

• 在 ORM 层（如 MyBatis、Hibernate）拦截 update 操作，记录 ROW_COUNT() == 0 的比例
• MySQL 可查 performance_schema.events_statements_summary_by_digest，过滤出带 WHERE …… version = 的语句，看 errors 和 rows_affected
• 如果某接口日志里 "updated 0 rows" 出现频率 > 10%，基本说明乐观锁开始吃力；> 30% 就该切悲观锁或重构

混合用法常见但容易漏掉版本校验兜底

有些系统用悲观锁保护关键路径（如支付扣款），其他非核心字段用乐观锁更新（如用户 last_login_time）。问题常出在：开了事务用了 FOR UPDATE，结果后续更新又加了 AND version = ?——这反而多余，还可能因事务中 version 未刷新导致误失败。

• 同一事务内，如果已用悲观锁锁定某行，后续对该行的 UPDATE 不需要再做乐观校验
• 跨事务操作才需要乐观锁，比如“先查再改”的场景，且中间无锁保护
• 别在悲观锁事务里调用另一个含乐观锁的 service 方法——容易形成双重校验 + 重复重试逻辑

真正难的不是选锁类型，而是确认哪几行算“同一业务单元”。库存、余额、状态字段经常绑在一起变，但锁粒度没对齐，冲突和死锁就都来了。