When I INSERT multiple rows into a MySQL table, will the ids be increment by 1 everytime?

Question

if I have a query like the following:

INSERT INTO table (col1,col2,col3) VALUES
('col1_value_1', 'col2_value_1', 'col3_value_1'),
('col1_value_2', 'col2_value_2', 'col3_value_2'),
('col1_value_3', 'col2_value_3', 'col3_value_3');

Suppose that I have a table where the last id PRIMARY_KEY AUTO_INCREMENT value is 56, then will this insert query always create 3 records with ids 57, 58, 59. Is this operation atomic?

Or, if another query writes on the same table, could the ids not increment always by 1?

Thanks for the attention!

EDIT: Please read the following because maybe I wasn't so clear.

Of course AUTO_INCREMENT increments by one safely, I know that.

The point is:

Let's say I have the following table called table:

 ___________________________________
| id | col1       | col2             |
-------------------------------------
|  1 | "some val" | "some other val" |
|  2 | "some val" | "some other val" |
|  3 | "some val" | "some other val" |
|  4 | "some val" | "some other val" |
|  5 | "some val" | "some other val" |
|  6 | "some val" | "some other val" |
|____________________________________|

If I know run the query:

INSERT INTO table (col1,col2) VALUES
('some val', 'some other val'),
('some val', 'some other val'),
('some val', 'some other val')

I will end up with the following table:

 ___________________________________
| id | col1       | col2             |
-------------------------------------
|  1 | "some val" | "some other val" |
|  2 | "some val" | "some other val" |
|  3 | "some val" | "some other val" |
|  4 | "some val" | "some other val" |
|  5 | "some val" | "some other val" |
|  6 | "some val" | "some other val" |
|  7 | "some val" | "some other val" |
|  8 | "some val" | "some other val" |
|  9 | "some val" | "some other val" |
|____________________________________|

Nothing to say here. But if me and another guy run the same query at the same time, are these queries atomic?, meaning that we will always end up with:

1)

 ___________________________________
| id | col1       | col2             |
-------------------------------------
|  1 | "some val" | "some other val" |
|  2 | "some val" | "some other val" |
|  3 | "some val" | "some other val" |
|  4 | "some val" | "some other val" |
|  5 | "some val" | "some other val" |
|  6 | "some val" | "some other val" |
|  7 | "some val" | "some other val" |<-- My 1st inserted record
|  8 | "some val" | "some other val" |<-- My 2nd inserted record
|  9 | "some val" | "some other val" |<-- My 3rd inserted record
| 10 | "some val" | "some other val" |<-- Another guy's 1st inserted record
| 11 | "some val" | "some other val" |<-- Another guy's 2nd inserted record
| 12 | "some val" | "some other val" |<-- Another guy's 3rd inserted record
|____________________________________|

Or with:

2)

 ___________________________________
| id | col1       | col2             |
-------------------------------------
|  1 | "some val" | "some other val" |
|  2 | "some val" | "some other val" |
|  3 | "some val" | "some other val" |
|  4 | "some val" | "some other val" |
|  5 | "some val" | "some other val" |
|  6 | "some val" | "some other val" |
|  7 | "some val" | "some other val" |<-- Another guy's 1st inserted record
|  8 | "some val" | "some other val" |<-- Another guy's 2nd inserted record
|  9 | "some val" | "some other val" |<-- Another guy's 3rd inserted record
| 10 | "some val" | "some other val" |<-- My 1st inserted record
| 11 | "some val" | "some other val" |<-- My 2nd inserted record
| 12 | "some val" | "some other val" |<-- My 3rd inserted record
|____________________________________|

Depending on which query of the two MySQL schedules first.

Or could the following abnormalities arise too?:

3)

 ___________________________________
| id | col1       | col2             |
-------------------------------------
|  1 | "some val" | "some other val" |
|  2 | "some val" | "some other val" |
|  3 | "some val" | "some other val" |
|  4 | "some val" | "some other val" |
|  5 | "some val" | "some other val" |
|  6 | "some val" | "some other val" |
|  7 | "some val" | "some other val" |<-- My 1st inserted record
|  8 | "some val" | "some other val" |<-- My 2nd inserted record
|  9 | "some val" | "some other val" |<-- Another guy's 1st inserted record - WTF???
| 10 | "some val" | "some other val" |<-- My 3rd inserted record 
| 11 | "some val" | "some other val" |<-- Another guy's 2nd inserted record
| 12 | "some val" | "some other val" |<-- Another guy's 3rd inserted record
|____________________________________|

Or something like this:

4)

 ___________________________________
| id | col1       | col2             |
-------------------------------------
|  1 | "some val" | "some other val" |
|  2 | "some val" | "some other val" |
|  3 | "some val" | "some other val" |
|  4 | "some val" | "some other val" |
|  5 | "some val" | "some other val" |
|  6 | "some val" | "some other val" |
|  7 | "some val" | "some other val" |<-- Another guy's 1st inserted record
|  8 | "some val" | "some other val" |<-- My 1st inserted record - WTF???
|  9 | "some val" | "some other val" |<-- Another guy's 2nd inserted record
| 10 | "some val" | "some other val" |<-- My 2nd inserted record - WTF^2???
| 11 | "some val" | "some other val" |<-- Another guy's 3rd inserted record
| 12 | "some val" | "some other val" |<-- My 3rd inserted record - WTF^3???
|____________________________________|

Or any other combination != 3) and 4)?

I consider 1) and 2) as atomic. Is it always guaranteed that I will always end up with 1) or 2) and never ever end up with 3) or 4) or any other combination? And if yes (I will always end up with 1) or 2)), both for MyISAM and InnoDB?

If I do SELECT LAST_INSERT_ID(); and e.g. I get 7, does it automatically mean that the rows with id 8 and 9 were also inserted by my query and not by the query of the other guy?

Answer 1

The answer is: well, it depends.

In case of myisam, the answer is a definite yes, since myisam sequences insert requests.

In case of innodb, however, the behaviour is configurable since mysql v5.1. before v5.1, then answer for InnoDB is also yes, after that it depends the on the innodb_autoinc_lock_mode setting. See mysql documentation on InnoDB auto_increment configuration for details.

To give you the highlights, there are 3 innodb_autoinc_lock_mode settings:

1. traditional (0)
2. consequtive (1) - default
3. interleaved (2)

With innodb_autoinc_lock_mode set to 0 (“traditional”) or 1 (“consecutive”), the auto-increment values generated by any given statement will be consecutive, without gaps, because the table-level AUTO-INC lock is held until the end of the statement, and only one such statement can execute at a time.

With innodb_autoinc_lock_mode set to 2 (“interleaved”), there may be gaps in the auto-increment values generated by “bulk inserts,” but only if there are concurrently executing “INSERT-like” statements.

For lock modes 1 or 2, gaps may occur between successive statements because for bulk inserts the exact number of auto-increment values required by each statement may not be known and overestimation is possible.

Further gaps can be experience in the auto_increment value, if a transactions has been rolled back. A bulk insert can only be rolled back as a whole.

UPDATE: As described above, you will get scenario 1) or 2), if you use

• myisam table engine
• or innodb pre mysql v5.1
• or innodb with mysql v5.1 or newer and the innodb_autoinc_lock_mode is 0 or 1

There is no way of telling which gets inserted first.

You may get scenario 3) or 4) if you use

• innodb with innodb_autoinc_lock_mode 2

Again, there is no way of telling how and why mysql mixes up the order of the records.

So, if your question is related to the fact that you insert 3 records with bulk insert and last_insert_id() returns the auto_increment value of the first inserted record only, and you want get the ids of the other 2 records by simple addition is that you may need to check mysql's configuration based on the table engine and mysql version used.