MySQL InnoDB复合主键行级锁工作原理及Haversine查询场景解析

嘿，我来帮你拆解这个问题～ 先从InnoDB行锁的核心逻辑说起，再结合你的复合主键和查询场景一步步分析：

核心前提：InnoDB行锁是基于索引的 #

InnoDB的行级锁永远和索引绑定，不是直接锁行数据本身。不管是读锁还是写锁，都是加在索引记录上的；如果查询没用到任何索引，那InnoDB会退化成表锁（全表扫描时加的意向锁+行锁，相当于锁全表）。

复合主键的索引结构 #

你的表用了复合主键（假设是(trip_route_id, parent_id_2)这类结构，两个字段都是父表的一对一标识），InnoDB的主键是聚簇索引——所有行数据都存在这个索引的叶子节点里，索引的排序逻辑是：先按第一个主键列trip_route_id排序，再按第二个主键列排序。也就是说，相同trip_route_id的所有行，在聚簇索引里是连续存放的。

你的Haversine查询场景下的锁行为 #

现在看你执行的查询：WHERE子句指定trip_route_id，同时用Haversine公式做地理距离计算（通常涉及lat、lng这类非索引列），分两种情况讨论：

1. 只读查询（SELECT） #

• 普通SELECT：InnoDB默认用快照读，依赖MVCC机制读取数据的历史快照，不会加任何行级锁，完全不影响其他事务的读写。
• 加锁读（SELECT ... FOR UPDATE / LOCK IN SHARE MODE）：这时候是当前读，需要加锁。因为你指定了trip_route_id（复合主键的左前缀），InnoDB会直接定位到聚簇索引中所有trip_route_id = 指定值的索引记录，给这些记录加锁——注意：是所有匹配trip_route_id的行，哪怕其中有些行最后不满足Haversine公式的过滤条件，也会被加锁。因为InnoDB是先通过索引找到匹配的行，加锁后再做后续的过滤计算。

2. 写操作（UPDATE/DELETE） #

逻辑和加锁读类似：

• InnoDB首先通过复合主键的trip_route_id前缀，找到所有trip_route_id = 指定值的行，给这些行加排他锁；
• 然后执行Haversine公式的过滤，只修改符合条件的行；
• 所有被加锁的行，会直到你的事务提交或回滚后才释放锁。

注意事项 #

• 避免锁范围过大：如果你的Haversine过滤能结合地理索引（比如空间索引）先缩小数据范围，再匹配trip_route_id，可以减少被锁定的行数。但如果必须先按trip_route_id过滤，那锁定范围就是该trip_route_id下的所有行。
• 复合主键顺序很重要：如果你的复合主键顺序不是(trip_route_id, ...)而是反过来，那指定trip_route_id无法利用主键索引的前缀匹配，InnoDB可能会走全表扫描，这时候会锁定大量行甚至全表，一定要避免这种情况。

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