The query that was correct but broke everything else
17 lock-wait timeouts in two hours. The SELECT was doing exactly what it was supposed to. The problem was what InnoDB did silently in addition: a gap lock on the write path, invisible in logs, invisible in EXPLAIN, visible only in production.