SELECT ... FOR UPDATE 不能直接用于非唯一索引，是因为InnoDB会升级为间隙锁或临键锁，锁住索引区间而非单行，导致性能下降、误阻塞及死锁；订单号order_no必须建UNIQUE索引才能精准行锁。
SELECT ... FOR UPDATE 为什么不能直接用在非唯一索引上？
在订单状态机里，你试图用 SELECT * FROM orders WHERE order_no = 'ORD123' FOR UPDATE 加锁，但发现并发更新时仍出现状态覆盖——大概率是因为 order_no 没建唯一索引。InnoDB 的行锁在非唯一索引或无索引条件下会升级为间隙锁（Gap Lock）甚至临键锁（Next-Key Lock），锁住一片范围，不仅性能差，还可能意外阻塞无关订单。
实操建议：
- 必须给
order_no加UNIQUE索引，否则FOR UPDATE锁不住单行，而是锁住索引区间 - 避免用
SELECT ... FOR UPDATE查询status IN (1,2)这类范围条件，它会锁住所有匹配的索引项+间隙，极易引发死锁 - 执行前用
EXPLAIN确认是否走了唯一索引：如果type是range或index，说明锁粒度已失控
如何用事务 + SELECT FOR UPDATE 实现「检查-更新」原子性？
订单状态变更不是单纯 SET status=2，而是要满足前置条件：比如只允许从 pending → confirmed，且不能重复确认。靠应用层先查再更新必然有竞态，必须把「读取当前状态」和「条件更新」绑在同一事务中。
正确写法是：
START TRANSACTION; SELECT status FROM orders WHERE order_no = 'ORD123' AND status = 1 FOR UPDATE; -- 如果上面查不到结果，说明状态不合法，直接 ROLLBACK UPDATE orders SET status = 2, updated_at = NOW() WHERE order_no = 'ORD123' AND status = 1; COMMIT;
关键点：
-
SELECT ... FOR UPDATE必须带和UPDATE完全一致的WHERE条件（包括status = 1），否则锁了行但更新时可能因条件不匹配而失效 - 不要分开两个事务执行：查一次、再开事务更新一次——中间窗口期就是状态机被破坏的缺口
- 应用层需检查
UPDATE的影响行数：返回 0 表示状态已变，不可再推进，此时应抛出业务异常而非静默忽略
为什么 UPDATE ... WHERE status = ? 比 SELECT + UPDATE 更快但更危险？
有人图省事，跳过显式加锁，直接写 UPDATE orders SET status = 2 WHERE order_no = 'ORD123' AND status = 1。它确实能保证原子性，也快，但问题在于：你完全不知道这次更新是否成功，除非检查返回值；更重要的是，它没锁住行，下一条语句（比如扣减库存）可能读到旧状态。
典型风险场景：
- 两个请求同时执行该
UPDATE，一个成功，一个失败（影响行数 0）——这没问题 - 但失败的那个请求，如果紧接着又去查
status，可能读到刚被成功更新后的新值，误判为“已处理完毕”，跳过后续逻辑 - 更糟的是，若后续有
SELECT ... FOR UPDATE基于新状态做操作，而前序没锁住，就可能读到中间态
所以，纯 UPDATE 适合幂等终态设置（如标记为 cancelled），但涉及多步状态流转（pending → confirmed → shipped）时，必须用显式事务加锁控制读视图。
死锁检测与重试策略怎么写才不伤业务？
即使锁用对了，高并发下仍可能因加锁顺序不一致触发死锁，MySQL 会主动回滚其中一个事务并报错 Deadlock found when trying to get lock。硬编码重试 3 次不是好办法——可能把慢查询拖成超时，也可能让下游重复消费。
推荐做法：
- 应用捕获
ER_LOCK_DEADLOCK（错误码 1213），仅对这类错误做指数退避重试（如 10ms、30ms、100ms） - 重试前重新生成事务上下文，避免复用旧连接状态
- 给整个状态推进操作设总超时（比如 500ms），超过即放弃并返回明确错误（如
OrderStatusTransitionTimeout），由上游决定是否告警或人工介入 - 切忌在存储过程中写循环重试——MySQL 不支持事务内嵌套重试，容易锁表或耗尽连接
悲观锁本身不复杂，难的是把锁的边界和业务状态流对齐。最常被忽略的一点：锁住的那行数据，必须是整个状态机推进路径里最先依赖的实体——比如订单主表锁住了，但库存记录还在另一张表里没加锁，那状态机照样会断在中间。
