2

MYSQL locking docs tell me that a statement 

SELECT * FROM child WHERE id = 100;

would cause a proceeding gap lock if id is a non-unique or non-indexed row. However, this is the syntax for a consistent non-locking select statement. I thought that these statements used their own snapshot and avoided locking. Is the preceding gap lock only applicable during query time? Where am i going wrong?

Zoe
  • 27,060
  • 21
  • 118
  • 148
lluisrojass
  • 439
  • 1
  • 3
  • 12

1 Answers1

2

You understand correctly, a non-locking SELECT creates no locks, and would not create any gap locks.

The example is in error, or at best it is unclear.

A locking SELECT includes either LOCK IN SHARE MODE (to create S-locks) or FOR UPDATE (to create X-locks). Because there are two possible locking clauses, perhaps the author intended to write something to say either one would result in gap locks, but then they forgot to write that.

At the start of the section on Gap Locks, the text does use an example statement SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE; and this is more clearly an example of a locking SELECT.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • 1
    Or serializable isolation mode is enabled, which turns every select into a for update one. – Shadow Jan 02 '18 at 20:55
  • @Shadow Yes that is true, but it is not described on the doc page we're talking about. – Bill Karwin Jan 02 '18 at 21:20
  • @BillKarwin I know it is not, but I think it should be. Just one more addition to support your point on the doc not being clear enough. – Shadow Jan 02 '18 at 21:49
  • @BillKarwin I would appreciate if you could take a look at this question (not mine, just an interesting one): https://stackoverflow.com/questions/48068213/why-are-there-auto-increment-gaps-in-mysql-when-violating-a-unique-key-constrain – Shadow Jan 02 '18 at 22:40