[SOLVED] Are there PRECISE RULES to know when MySQL will apply gap blocking?

Are there PRECISE RULES to know when MySQL will apply gap blocking?

After several studies I see that MySQL applies gap blocking where it is not necessary. That's why I want to ask this question, is it something deterministic?

I asked this question and

BETWEEN SQL - why is there a gap lock?

article is not clear to me

https://vincepergolizzi.com/programming/2020/09/02/mysql-locking-reads.html

Solution

I like Bill's quote. Here's how I think of gap locking:

When it is easy for InnoDB to avoid gap locking, it does so.
When the complexity of some odd situation can be efficiently handled by gap locking, InnoDB will use it rather than adding more code and slowing things down.

Note that a "gap" may have lots of potential rows but there is no way to lock an individual row that does not currently exist. Suppose a table has no rows between 100 and 200. And one thread wants to deal with 123 and another wants to deal with 187, they may stumble due to gap locks. And the queries bay be some combination of Delete, Update, Select, Select FOR UPDATE, etc, and may be referring to just that non-existent row or a range, etc.