update ... set balance = balance + ? 仍会加 next-key lock 并非无锁；真正可控的是通过版本号乐观锁、原子条件更新或 insert ... on duplicate key update 缩小锁范围。
为什么直接用 UPDATE ... SET balance = balance + ? 仍然可能出错
很多人以为写 UPDATE accounts SET balance = balance + 100 WHERE user_id = 123 就是“无锁”，其实不然。MySQL 在执行这条语句时仍会对匹配的行加 next-key lock（间隙锁 + 行锁），尤其在可重复读（RR）隔离级别下。并发更新同一账户时，事务会排队等待，本质仍是行级锁阻塞，并非真正无锁。
用 SELECT FOR UPDATE + 应用层校验实现乐观控制
真正的“无锁”不是不加锁，而是把锁的粒度和时机交给业务逻辑控制——即先查后判再更新，配合版本号或条件检查，避免盲目覆盖。关键不是去掉锁，而是让锁只在必要路径上短暂停留。
- 给账户表加一个
version字段（BIGINT UNSIGNED NOT NULL DEFAULT 0） - 查询时带上版本：
SELECT balance, version FROM accounts WHERE user_id = 123 - 应用层计算新余额和新版本（
new_version = old_version + 1） - 用带条件的 UPDATE 原子提交：
UPDATE accounts SET balance = ?, version = ? WHERE user_id = ? AND version = ? - 检查
ROW_COUNT()：为 0 表示被其他事务抢先更新，需重试
用原子函数 + WHERE 条件规避负余额风险
仅靠乐观锁还不够。余额更新必须附带业务约束，比如禁止透支。MySQL 的 IF() 或 CASE WHEN 可以把判断压进单条语句，避免应用层与数据库之间的时间窗口漏洞。
UPDATE accounts
SET balance = IF(balance - 50 >= 0, balance - 50, balance),
version = version + 1
WHERE user_id = 123 AND version = 42;
注意：IF() 不会触发更新，但 version 仍会自增 —— 所以更稳妥的是用 CASE 包裹整个赋值，并确保 WHERE 同时校验余额和版本：
-
WHERE user_id = ? AND version = ? AND balance >= ?（扣款场景） - 失败时直接返回“余额不足”，无需重试逻辑
- 该语句在 RR 隔离级别下仍会加行锁，但锁持有时间极短（仅语句执行期）
真正无锁？只有 REPLACE INTO 或 INSERT ... ON DUPLICATE KEY UPDATE 能绕过行锁
如果业务允许将余额视为“最终状态快照”而非连续变更，可用日志表 + 汇总视图替代直接更新主表。例如：
- 写入流水：
INSERT INTO account_ledger (user_id, amount, created_at) VALUES (123, 100, NOW()) - 用
INSERT ... ON DUPLICATE KEY UPDATE更新汇总（需唯一索引user_id）：INSERT INTO account_balance (user_id, balance) VALUES (123, 100) ON DUPLICATE KEY UPDATE balance = balance + VALUES(balance) - 该语句在无冲突时走
fast path，不触发 full table scan，且冲突时自动重试，底层使用index-record lock而非 next-key，锁范围更小
但要注意：ON DUPLICATE KEY UPDATE 的 VALUES() 引用的是本次 INSERT 的值，不是当前行值；若需基于当前值计算，仍得回到带 WHERE 的 UPDATE。
实际中最容易被忽略的点是隔离级别和索引设计：没建好 user_id 的唯一索引，SELECT FOR UPDATE 会升级为表锁；设成读已提交（RC）能减少间隙锁，但幻读风险需由应用兜底。锁从来不是越少越好，而是刚好够用。
