SQL 高并发下事务如何设计？

应选 READ COMMITTED 隔离级别，因其避免间隙锁、降低死锁概率；UPDATE 必须走索引、禁全表扫描；事务内禁耗时操作；优先乐观锁，必要时才用最小粒度悲观锁。

事务隔离级别选 READ COMMITTED 而非 REPEATABLE READ

高并发下最常踩的坑是默认用 REPEATABLE READ（mysql 默认），它靠间隙锁（Gap Lock）防止幻读，但会大幅增加锁冲突概率，导致大量 Lock wait timeout exceeded 或死锁。实际业务中，多数场景并不需要强一致性幻读防护——比如订单列表分页、商品库存扣减后的状态查询，READ COMMITTED 已足够，且只锁命中行，不锁间隙，吞吐明显提升。

实操建议：

• MySQL 5.7+ 可在 session 级设置：SET session TRANSACTION ISOLATION LEVEL READ COMMITTED;
• spring Boot 中通过 @Transactional(isolation = Isolation.READ_COMMITTED) 显式声明
• 避免全局修改 transaction_isolation 系统变量，防止影响其他依赖强一致性的模块

UPDATE 必须带 WHERE 条件，且 WHERE 尽量走主键或唯一索引

没有索引支撑的 UPDATE 会触发全表扫描+行锁升级为表锁，高并发时直接拖垮整个表。例如 UPDATE orders SET status = 'paid' WHERE user_id = 123，若 user_id 无索引，InnoDB 会锁住所有行，后续任何对 orders 的写操作都会排队。

实操建议：

• 用 EXPLAIN 检查 UPDATE 语句是否命中索引，重点关注 type 字段是否为 const 或 eq_ref
• 批量更新优先拆成主键 ID 列表，用 WHERE id IN (…)，避免范围条件（如 created_at > '2024-01-01'）引发锁范围扩大
• 禁止在事务中执行无 WHERE 的 UPDATE 或 delete，CI/CD 流水线应加入 SQL 审计规则拦截

减少事务内耗时操作：别在事务里调 http、发消息、生成文件

事务持有数据库连接期间，只要没提交，锁就一直占着。如果在里面调第三方支付接口（可能耗时数秒），等于把数据库锁挂起几秒，QPS 直接腰斩。常见错误是把「扣库存 → 调支付 → 写订单」全包进一个事务。

实操建议：

• 事务只做纯粹的 DB 操作：校验 + 修改 + 记录日志（如 order_status_log 表）
• 支付回调、通知推送等异步动作，用本地消息表 + 定时任务/消息队列解耦，例如插入一条 message_queue 记录后立即提交事务
• 必要时用 select ... for UPDATE 加锁，但必须确保锁粒度最小、持有时间最短；避免在锁住行后做复杂计算

乐观锁比悲观锁更适合多数高并发更新场景

悲观锁（SELECT ... FOR UPDATE）本质是串行化，适合冲突率高、逻辑简单、持有锁时间极短的场景（如秒杀库存扣减）。但大多数业务更新冲突率低于 5%，用乐观锁更轻量：加个 version 字段或 updated_at 时间戳，在 UPDATE 时校验，失败则重试。

示例语句：UPDATE account SET balance = balance - 100, version = version + 1 WHERE id = 123 AND version = 5，返回 affected rows = 0 即表示版本冲突。

实操建议：

• 重试逻辑不要放在数据库层，应在应用层控制次数（通常 ≤ 3 次）和间隔（如指数退避）
• 避免在乐观锁更新中嵌套复杂子查询，否则可能因读取 stale data 导致校验失效
• 注意 MySQL 的 autocommit=1 下单条 UPDATE 本身就是事务，无需额外 BEGIN，但 ORM 框架（如 mybatis）可能隐式开启事务，需确认实际行为

事务设计真正的难点不在语法，而在判断哪条数据真正需要锁、锁多久、谁来承担冲突成本——这些没法靠框架自动推导，得看具体业务流程里「不可分割的原子性」到底落在哪一层。