mysql中读写锁与锁策略的优化与应用

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。