select ... for update 加排他锁，阻塞所有并发修改与共享锁；select ... lock in share mode 加共享锁，允许多个事务共存但阻塞排他锁及同级共享锁，二者锁类型均由索引、条件和隔离级别共同决定。
MySQL 中 SELECT ... FOR UPDATE 和 SELECT ... LOCK IN SHARE MODE 的实际行为差异
这两条语句不是“加锁类型”的开关，而是事务内显式加锁的触发方式，底层都依赖 InnoDB 的行级锁（Record Lock）或间隙锁（Gap Lock），具体加什么锁由查询是否命中索引、是否为唯一条件、隔离级别共同决定。
常见误判：以为 LOCK IN SHARE MODE 只阻塞写不阻塞读——错。它会阻塞其他事务的 FOR UPDATE 和同级别 LOCK IN SHARE MODE（除非是可重复读下非唯一二级索引的特殊情况）。
-
FOR UPDATE在唯一索引 + 等值查询时只锁匹配行；若走非唯一索引或范围查询，可能触发间隙锁，导致幻读抑制甚至锁住不存在的区间 -
LOCK IN SHARE MODE同样受索引影响，但不会与自身冲突（多个事务可同时持共享锁），而FOR UPDATE是互斥的 - 在
READ COMMITTED隔离级别下，InnoDB 不使用间隙锁（除外键检查和唯一约束检查），此时两种语句都只加记录锁，锁范围更小但幻读风险上升
如何避免 UPDATE 语句隐式升级为表锁
当 UPDATE 或 DELETE 语句无法使用索引（例如 WHERE status = 'pending' 但 status 列无索引），InnoDB 会退化为聚簇索引全扫描，对所有扫描过的记录加锁——这在大表上等价于逻辑表锁，极易引发长事务阻塞和死锁。
实操建议：
- 用
EXPLAIN检查执行计划，确认type字段不是ALL，key字段显示实际使用的索引 - 对高频过滤字段（如
order_status,user_id）建立联合索引时，把等值条件列放前面，范围条件列放后面（如INDEX (user_id, created_at)支持WHERE user_id = ? AND created_at > ?） - 避免在
WHERE子句中对字段做函数操作（如WHERE DATE(created_at) = '2024-01-01'），会导致索引失效 - 批量更新时，拆成小批次（如每次 500 行），配合
LIMIT和主键范围控制，减少单次锁持有时间
乐观锁实现中 version 字段与 CAS 更新的陷阱
应用层常用 UPDATE t SET status = ?, version = version + 1 WHERE id = ? AND version = ? 实现乐观锁，但该语句本身仍需加行锁——InnoDB 对 WHERE 条件中的主键或唯一索引列会加记录锁，即使最终 ROW_COUNT() 返回 0。
这意味着高并发下大量失败请求仍在排队争抢同一行锁，只是没修改成功而已，不能缓解锁竞争压力。
- 真正降低锁冲突的方式是：让并发修改落在不同数据行上（如分库分表、按用户 ID 分片），而非靠乐观锁“掩盖”争抢
-
version字段必须是整型且有默认值（如0），否则NULL + 1结果为NULL，导致条件恒假 - 不要在同一个事务里多次读取
version再拼 SQL，应确保读取和更新原子性；若中间有其他逻辑，考虑用SELECT ... FOR UPDATE加锁后判断再更新
死锁日志里看到 WAITING FOR THIS LOCK TO BE GRANTED 说明什么
这是 InnoDB 死锁检测机制捕获到循环等待后的标准输出，出现在 SHOW ENGINE INNODB STATUS 的 LATEST DETECTED DEADLOCK 区域。关键要看两部分：TRANSACTION 块里的事务 ID 和状态，以及 WAITING FOR THIS LOCK TO BE GRANTED 下方列出的 lock_mode、lock_type、lock_table 和被锁的 lock_index。
典型线索：
- 若
lock_mode是X（排他锁）且lock_type是RECORD，说明是行锁冲突；若是INSERT INTENTION，大概率是插入意向锁与间隙锁冲突（常见于并发插入相同范围） - 对比两个事务的 SQL，看是否以不同顺序访问相同几行（如事务 A 先锁 a 再锁 b，事务 B 先锁 b 再锁 a）——这是最常见死锁成因
- 如果锁在二级索引上（
lock_index显示非 PRIMARY），注意二级索引更新会先锁二级索引记录，再锁对应主键记录，路径更长，更容易卷入死锁
优化方向永远优先从应用逻辑入手：固定 DML 访问顺序、缩小事务粒度、避免在事务中做 RPC 或文件 IO，而不是调大 innodb_lock_wait_timeout。
