这可能不是答案，而是一些信息.

This may not be the answer but some info.

在使用 SELECT ... FOR UPDATE 语句时，您可能会观察到锁争用(死锁等).

When using SELECT ... FOR UPDATE statements, you may observe lock contention(dead locks etc..).

select … for update where x <= y

它的范围扫描与 <=数据库锁定所有行

its that range scan with <= the database locks all rows <= y, including any gaps so if you have rows with y like this: 1, 3, 5 it locks even the empty space between 1 and 3 in the index its called gap locking

可以看到这个命令的问题:

can see the issue with this command:

SHOW ENGINE INNODB STATUS;

---TRANSACTION 72C, ACTIVE 755 sec
4 lock struct(s), heap size 1248, 3 row lock(s), undo log entries 1
MySQL thread id 3, OS thread handle 0x7f84a78ba700, query id 163 localhost msandbox
TABLE LOCK table test.t trx id 72C lock mode IX
RECORD LOCKS space id 19 page no 4 n bits 80 index age of table test.t trx id 72C lock_mode X
RECORD LOCKS space id 19 page no 3 n bits 80 index GEN_CLUST_INDEX of table test.t trx id 72C lock_mode X locks rec but not gap
RECORD LOCKS space id 19 page no 4 n bits 80 index age of table test.t trx id 72C lock_mode X locks gap before rec

最后一行

如果您的事务中有很多间隙锁会影响并发性和性能，您可以通过两种不同的方式禁用它们:

If you have lot of gaps locks in your transactions affecting the concurrency and the performance you can disable them in two different ways:

1- Change the ISOLATION level to READ COMMITTED. In this isolation level, it is normal and expected that query results can change during a transaction, so there is no need to create locks to prevent that from happening.

2- innodb_locks_unsafe_for_binlog = 1. Disables the gap locks except for foreign-key constraint checking or duplicate-key checking.

https://www.percona.com/blog/2012/03/27/innodbs-gap-locks/