`UPDATE ... WHERE ... ` multiple rows locking in InnoDB

Question

I'm implementing a custom table-based sequence generator for MySQL database v5.7.16 with InnoDB engine.
The sequence_table looks as follows:

+-------------+-----------+
|sequence_name|next_value |
+-------------+-----------+
|  first_seq  |     1     |
+-------------+-----------+
|  second_seq |     1     |
+-------------+-----------+

sequence_name column is a primary key.
This sequence table contains multiple sequences for different consumers.

I use the following strategy for the sequence updates:

1. Select current sequence value: select next_val from sequence_table where sequence_name=?.
2. Add the allocation size to current sequence value.
3. Update the sequence value if it's current value matches the value selected in the first step: update sequence_table set next_val=? where sequence_name=? and next_val=?.
4. If the update is successful return the increased sequence value, otherwise repeat the process from step 1.

The documentation contains the following information:

UPDATE ... WHERE ... sets an exclusive next-key lock on every record the search encounters. However, only an index record lock is required for statements that lock rows using a unique index to search for a unique row. 14.5.3 Locks Set by Different SQL Statements in InnoDB

The part in bold is a bit confusing.
As you can see, I match the primary key in the WHERE clause of the UPDATE statement.

Is it possible that the search may encounter more than one record and therefore lock multiple rows in this sequence table?

In other words, will the update in the 3rd step of the algorithm block just one or multiple rows?

Answer 1

You didn't mention what transaction isolation level you're planning to use. Lets assume you're using repeatable read (in read committed no such a problem should exist)

From here:

For locking reads (SELECT with FOR UPDATE or LOCK IN SHARE MODE), UPDATE, and DELETE statements, locking depends on whether the statement uses a unique index with a unique search condition, or a range-type search condition

and

For a unique index with a unique search condition, InnoDB locks only the index record found, not the gap before it

So at least in theory it should lock only a single record and no next-key lock will be used.

More quotes from other docs pages to back my thoughts:

innodb-next-key-locks

link

A next-key lock is a combination of a record lock on the index record and a gap lock on the gap before the index record.

gap locks

link

Gap locking is not needed for statements that lock rows using a unique index to search for a unique row