CODEWITHSUNDEEP
mysqlsqllocks
lluisrojass
lluisrojass

Reputation: 439

Confusion about MySQL locks relating to their documentation

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?

Upvotes: 2

Views: 66

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562951

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.

Upvotes: 2

Related Questions