CockroachDB 行级锁异常：唯一索引加FOR UPDATE锁阻塞其他行查询

这个现象是CockroachDB的锁机制与索引访问路径强绑定的设计导致的，和传统单机数据库的行锁、间隙锁逻辑有明显区别，本质是查询走不同索引路径时，锁的覆盖范围存在本质差异。

走二级唯一索引加锁时的阻塞来源 #

• CockroachDB的默认隔离级别为可串行化，为了避免唯一约束冲突、写偏斜等并发一致性问题，当SELECT ... FOR UPDATE语句走二级索引做等值查询时，不会只锁定单个匹配的索引项，而是会在对应索引键的前后区间加Next-Key范围锁。
• 你测试表的col_with_unique_index字段上建立了唯一索引unique_idx，两个测试值val1和val2在这个索引上是相邻的有序键值。第一个会话用col_with_unique_index = 'val1'做FOR UPDATE查询时，加的范围锁覆盖了val1到下一个相邻索引键之间的全部区间。
• 第二个会话用col_with_unique_index = 'val2'做普通SELECT时，默认串行隔离级别下需要先在unique_idx上获取对应索引范围的读意向锁，刚好落在前一个会话加的写范围锁的覆盖区间内，因此会被阻塞，直到前一个事务提交释放锁。
• 这个范围锁的设计是为了保证并发正确性：如果不锁这个间隙，其他事务就可以在锁持有期间插入重复的val1索引值、或者删除val1索引项，破坏唯一约束和事务一致性。

走主键查询不阻塞的原因 #

• 主键索引是CockroachDB的主存储路径，针对主键的等值FOR UPDATE查询，只会在匹配的单个主键键值上加精确的行级写锁，不会触发覆盖相邻键的间隙范围锁。
• 你复测时用主键id做过滤条件的查询，访问路径完全走主键索引，根本不会碰到unique_idx上的那个范围锁，因此可以正常返回结果，不会被阻塞。

关于“是否只有主键过滤才能实现不阻塞的行级锁”的说明 #

核心影响因素是查询的访问路径，而非过滤条件本身：

• 如果优化器为查询选择了主键索引作为访问路径，哪怕过滤条件涉及二级索引列（比如通过多条件匹配、全表扫描触发主键路径），加锁时也只会锁定单个目标行，不会阻塞其他行的查询。
• 如果优化器为查询选择了二级索引作为访问路径，哪怕过滤条件是主键等值匹配（极端情况下优化器选错路径），加锁时也会触发带间隙的范围锁，可能阻塞相邻索引项的查询。
• 你可以通过执行EXPLAIN语句查看具体的执行计划，确认查询的扫描路径是走主键还是二级索引，提前判断锁的覆盖范围。

内容的提问来源于stack exchange，提问作者Shawnbam