根本原因是无索引字段导致行锁退化为临键锁甚至表锁；应通过EXPLAIN确认执行计划类型，为高频查询列建联合索引，并避免在索引列上使用函数。
为什么 SELECT ... FOR UPDATE 会卡住其他事务
问题往往不在锁本身，而在于锁的“落点”不对。当你在一个没有索引的字段上执行行锁时，MySQL为了保证数据一致性，会退而求其次，使用临键锁（Next-Key Lock）来锁住一个范围，极端情况下甚至会直接锁住整张表。想想看，一个简单的 WHERE status = 'pending' 查询，如果 status 列没有索引，哪怕你只想更新一行，也可能让整张表的写入操作都陷入等待。
那么，如何避免这种情况？关键在于确保查询能精准地“命中”目标行：
- 第一步，先用
EXPLAIN看看执行计划。重点关注type字段，理想情况应该是ref或range，如果看到ALL（全表扫描）或index（全索引扫描），就得敲响警钟了。 - 第二步，为高频查询的列建立合适的索引。优先考虑联合索引，排列顺序遵循“等值查询 → 最左前缀 → 范围查询”的原则。例如，一个
(user_id, status, created_at)的索引，就能高效服务于多种查询组合。 - 最后，警惕那些让索引“隐形”的操作。比如
WHERE DATE(created_at) = '2024-01-01'这种写法，会让索引失效。正确的做法是改用范围查询：WHERE created_at >= '2024-01-01' AND created_at。
如何快速定位正在争用的行锁
遇到锁等待，别一头扎进 SHOW ENGINE INNODB STATUS\G 那冗长的输出里。更高效的办法是直接查询系统表，特别是 information_schema.INNODB_TRX 和 INNODB_LOCK_WAITS 的组合，它能清晰地告诉你：谁在等、在等谁、具体等哪一行。
具体可以按这个流程来排查：
- 首先，执行
SELECT * FROM information_schema.INNODB_TRX WHERE trx_state = 'LOCK WAIT';，找出所有正在等待锁的事务。 - 接着，关联查询
INNODB_LOCK_WAITS表，找到阻塞它的blocking_trx_id，再反查INNODB_TRX就能拿到持锁事务的线程ID（trx_mysql_thread_id）。 - 然后，如果开启了
performance_schema，可以通过SELECT * FROM performance_schema.threads WHERE THREAD_ID = ?来定位该线程正在执行的SQL语句。 - 需要提醒的是，系统视图中的
trx_query字段可能为空或被截断，最可靠的SQL文本还是要从应用日志或慢查询日志中进行交叉验证。
UPDATE 语句没走索引却锁了整张表
这并非MySQL的bug，而是一种基于成本的“理性选择”。当优化器预估扫描的行数超过表总行数的一定比例（通常在20%左右）时，它会认为走索引不如全表扫描划算，于是转而使用主键聚簇索引进行全扫。问题在于，UPDATE 语句会对所有扫描到的主键记录加上排他锁（X锁），其结果就相当于锁住了整张表。
要解决这个问题，可以从几个方面入手：
- 深入分析执行计划。使用
EXPLAIN FORMAT=JSON查看filtered值，如果低于10就非常可疑；同时检查rows预估行数是否远大于实际匹配行数。 - 更新表的统计信息。有时候，陈旧的统计信息会误导优化器，执行一次
ANALYZE TABLE往往能带来意想不到的效果。 - 谨慎使用索引提示。像
UPDATE t USE INDEX (idx_status) SET ...这样的写法可以作为临时排查手段，但不建议长期依赖，因为它可能妨碍优化器未来的最佳选择。 - 对于低区分度的字段（例如状态字段只有三五个枚举值），可以考虑引入冗余字段并建立条件索引。比如，新增一个
is_pending TINYINT字段并由应用维护，然后为该字段建立索引，查询效率会高得多。
高并发下 INSERT ... ON DUPLICATE KEY UPDATE 的锁行为
这个语法糖虽然用起来方便，但背后的锁机制并不简单。它并非原子操作，而是分步执行：先检查唯一键，再判断是否冲突，最后决定插入或更新。每一步都可能与其他事务产生锁竞争，尤其是在批量操作的场景下，极易引发死锁或导致间隙锁范围无谓扩大，最终触发 Lock wait timeout exceeded 错误。
要确保其在高并发下稳定运行，有几个实践要点：
- 确保冲突检测的字段上有唯一索引。如果只是普通索引，间隙锁的范围会更大，锁冲突的概率也随之增加。
- 批量执行时，将待插入的数据按照主键或唯一键升序排序。这个简单的操作能极大降低不同事务间锁等待形成环路的概率。
- 尽量避免在同一个事务中混用
INSERT ... ON DUPLICATE KEY UPDATE和普通的UPDATE语句，特别是当它们操作同一张表的不同行时，锁的相互作用会变得复杂。 - 评估业务需求。如果目的仅仅是防止重复插入，并且可以接受极少量插入失败，那么使用
INSERT IGNORE是更轻量的选择，它只加插入意向锁，而不加共享锁（S锁）。
最后，有一个概念至关重要：间隙锁（Gap Lock）锁住的不是一条已有的记录，而是一个“不存在”的空档。你看不见它，也杀不掉它。因此，一旦数据库出现性能抖动，首先要排查的，就是是否在缺乏索引的字段上执行了范围查询或更新。这才是从根源上规避锁争用的关键所在。
