0

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

  • 1
    Obviously it is implemented in the InnoDB storage engine code, so it is deterministic. I.e. I feel confident in saying the locking code does not include a randomizer. But as the former engineering director for MySQL once told me: "there is no person on Earth who understands all of InnoDB locking behavior." – Bill Karwin Jan 10 '23 at 00:48
  • Maybe you would get better help on https://dba.stackexchange.com – Jorge Campos Jan 10 '23 at 00:49
  • hi @Bill Karwing, exactly, I think the problem lies in the fact that you will not know for sure WHAT INDEX RECORD MYSQL WILL SCAN and therefore apply next-key locks to those scans but DO NOT REMOVE THE GAP LOCK, ONLY THE RECORD LOCK. –  Jan 11 '23 at 12:34
  • 1
    Right, the optimizer can choose a different index than you expect, depending on its cost estimate. The same query form can change optimization based on which values you search for. – Bill Karwin Jan 11 '23 at 14:51
  • 1
    I'd recommend to use READ-COMMITTED isolation level to minimize gap locks if they are troubling you. But that changes the transaction isolation. Depending on your application needs, you might be required to use the default REPEATABLE-READ. – Bill Karwin Jan 11 '23 at 14:52
  • Exact @Bill Karwin, thank you for your complementary comments + 2 –  Jan 11 '23 at 22:18

1 Answers1

1

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.

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • Hi @Rick James, yes, the problem is because you don't know which records in the index MySQL will scan. –  Jan 11 '23 at 12:40