Auto_increment temporary gaps in MySQL?

Question

Let's say I have a table defined by:

CREATE TABLE People (
    id SERIAL,
    name TEXT
);

If I first find the maximum id in the table and then run the following query:

SELECT (id, name)
FROM People
WHERE id <= [maximum id found before];

I'll get a list of people. If I run the same query again with the same maximum id:

1. Am I guaranteed to get the same results?
2. Or is it possible that the first query returned a list with gaps which were filled in before the second query was executed, causing the second query to have more rows?

Assume that no changes are made to the database except sequential insert operations from any number of concurrent connections.

EDIT:

I'll try to clarify the specific case I'm concerned about. Let's say MySQL gets five transactions at around the same time. Transactions A and B both insert a person into People. Transaction C finds the maximum id. Transactions D and E both perform the query written above.

Is it possible for this sequence of events:

1. A is assigned an id
2. B is assigned the next id
3. B is committed
4. C is committed and returns the id of B
5. D is committed and returns a list that does not include the row inserted by A
6. A is committed
7. E is committed and returns a list that does include the row inserted by A

EDIT:

I'm thinking this scenario is impossible due to atomicity, but I'm hoping for confirmation from someone who understands ACID a little better than I do.

Answer 1

I think you are guaranteed to get the same list, gaps should never be filed in unless you're manually inserting them somehow. (although, if there are gaps i assume rows can be deleted, so it may not be exactly the same list because more may have been deleted.)

from How to reset AUTO_INCREMENT in MySQL?

You can reset the counter with:

ALTER TABLE tablename AUTO_INCREMENT = 1 For InnoDB you cannot set the auto_increment value lower or equal to the highest current index. (quote from ViralPatel):

Note that you cannot reset the counter to a value less than or equal to any that have already been used. For MyISAM, if the value is less than or equal to the maximum value currently in the AUTO_INCREMENT column, the value is reset to the current maximum plus one. For InnoDB, if the value is less than the current maximum value in the column, no error occurs and the current sequence value is not changed. See How to Reset an MySQL AutoIncrement using a MAX value from another table? on how to dynamically get an acceptable value.