SQL 如何通过设计避免死锁？

死锁源于事务加锁顺序不一致，需统一按主键或唯一索引升序加锁；避免长事务、缺失索引及跨表混排；关键在锁顺序控制与索引优化。

死锁不是 sql 语句写得对不对的问题，而是事务访问资源的顺序冲突

死锁无法靠单条 select 或 UPDATE 规避，它发生在多个事务并发执行时，彼此持有对方需要的锁、又等待对方释放。核心矛盾是「加锁顺序不一致」。比如事务 A 先锁 user_id=100 再锁 order_id=50，事务 B 反过来先锁 order_id=50 再锁 user_id=100，就极易形成环路等待。

强制统一 DML 操作的行级锁顺序

只要所有事务按相同规则排序待更新的行，就能切断死锁环。常见做法是：在 UPDATE / delete 前，用子查询或应用层预排序，确保 WHERE 条件中的主键（或唯一索引列）升序排列。

例如要批量更新用户状态：

UPDATE users SET status = 'active'  WHERE id IN (   SELECT id FROM (     SELECT id FROM users WHERE id IN (105, 101, 103) ORDER BY id   ) t );

而不是直接写 WHERE id IN (105, 101, 103) —— 数据库优化器可能按任意顺序加锁。

• 只依赖主键或唯一索引列排序；非唯一字段排序不能保证加锁顺序
• 避免在 IN 列表里混用不同表的 ID，否则跨表排序失效
• mysql 8.0+ 支持 ORDER BY + LIMIT 在子查询中生效，低版本需用临时表或应用层排序

减少事务粒度与锁持有时间

长事务 + 多语句更新 = 死锁温床。重点不是“要不要加事务”，而是“哪些操作必须包在一起”。比如「扣库存 + 写订单」必须原子，但「发短信通知」完全可以拆出去异步做。

• 把 SELECT ... for UPDATE 放在事务最开始，且紧挨着后续 UPDATE，不要中间穿插业务逻辑或网络调用
• 避免在事务内执行慢查询、外部 API 调用、文件读写
• 用 SELECT ... FOR UPDATE SKIP LOCKED（MySQL 8.0+/postgresql）跳过已被锁的行，防止无谓等待

索引设计直接影响锁范围，缺失索引会升级为表锁或间隙锁冲突

没有索引的 WHERE 条件会让数据库无法精确定位行，只能锁住整个索引范围甚至整张表。例如 UPDATE orders SET paid=1 WHERE status='unpaid'，若 status 无索引，InnoDB 可能对所有行加间隙锁，极大提高死锁概率。

• 所有出现在 WHERE、JOIN、ORDER BY 中的字段，只要并发 DML 频繁，就该有对应索引
• 复合索引要注意最左前缀匹配；(a,b) 索引无法支撑 WHERE b=1 的精确加锁
• 使用 EXPLAIN format=jsON 查看执行计划里的 key 和 rows_examined，确认是否走了预期索引

真正难处理的是多表关联更新时的隐式锁顺序，以及应用层分页批量操作中未显式排序的 ID 列表——这两处最容易被忽略，却贡献了大部分线上死锁案例。