When I insert several rows into a MySQL table, will the identifiers increase by one each time?

Question

if I have a request:

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 I have a table where the last id value of PRIMARY_KEY AUTO_INCREMENT is 56 , then this insert request always creates 3 records with identifiers 57, 58, 59 . Is this operation an atom?

Or, if another query is written to the same table, can ids not always increase by 1?

Thank you for attention!

EDIT . Please read the following, because perhaps I was not so clear.

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

Point:

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 a query:

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

I will summarize 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 I and another guy run the same query at the same time, are these queries atomic ?, which means we will always end:

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 1st inserted record | 11 | "some val" | "some other val" |<-- Another guy 2nd inserted record | 12 | "some val" | "some other val" |<-- Another guy 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 1st inserted record | 8 | "some val" | "some other val" |<-- Another guy 2nd inserted record | 9 | "some val" | "some other val" |<-- Another guy 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 charts first.

Or can the following anomalies occur ?:

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 1st inserted record - WTF??? | 10 | "some val" | "some other val" |<-- My 3rd inserted record | 11 | "some val" | "some other val" |<-- Another guy 2nd inserted record | 12 | "some val" | "some other val" |<-- Another guy 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 1st inserted record | 8 | "some val" | "some other val" |<-- My 1st inserted record - WTF??? | 9 | "some val" | "some other val" |<-- Another guy 2nd inserted record | 10 | "some val" | "some other val" |<-- My 2nd inserted record - WTF^2??? | 11 | "some val" | "some other val" |<-- Another guy 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 always end 1) or 2) and never ever 3) or 4) or any other combination? And if so (I will always have 1) or 2) ), for both MyISAM and InnoDB ?

If I do SELECT LAST_INSERT_ID(); and for example, I get 7 , does this automatically mean that lines with id 8 and 9 were also inserted by my request , and not by the request of another guy?

Answer 1

Answer: well, it depends.

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

In the case of innodb, however, the behavior is configurable since mysql v5.1. before v5.1, then the answer for InnoDB is also yes, after that it depends on the innodb_autoinc_lock_mode parameter. For more information, see the mysql documentation in Auto_increment InnoDB Configuration .

To give you the main points, there are 3 innodb_autoinc_lock_mode settings:

• traditional (0)
• consequtive (1) - default
• alternating (2)

If innodb_autoinc_lock_mode is set to 0 ("traditional") or 1 ("sequential"), the auto-increment values ​​generated by any given statement will be sequential, without spaces, because the AUTO-INC lock table level is stored until the end of the instruction, and only one such statement can run simultaneously.

With innodb_autoinc_lock_mode set to 2 ("alternating"), there may be gaps in the values ​​of automatic growth generated by "volume inserts", but only if the instructions are executed "INSERT-like".

For lock modes 1 or 2, gaps may occur between consecutive statements because for volume inserts the exact number of auto-increase values ​​required by each statement may not be known, and reevaluation is possible.

Further spaces may result from the value of auto_increment if transactions have been canceled. The volume insert can be discarded only as a whole.

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

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

It is impossible to tell what exactly is being inserted.

You can get script 3) or 4) if you use

• innodb with innodb_autoinc_lock_mode 2

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

So, if your question is related to the fact that you are inserting 3 records with volumetric insertion, and last_insert_id () returns the auto_increment value of only the first inserted record, and you want to get the identifiers of the other 2 records with a simple addition, you may need to check mysql configuration based on the table engine and version of mysql used.

Answer 2

If you define any primary key of the aut_increment column, then it will automatically increase the value from 1, you do not need to define this column in the insert query, then it will automatically add an additional value in the primary key column.

Answer 3

auto_increment is safe in a parallel environment . The task is to give unique values, regardless of how many people you connect and work on the table. You can control the offset to increase, by default it is 1 .

Now, what does this mean, that means that what is written in the table does not need to be increased by 1. This is a known "gap" problem.

Suppose you and I write to your desk at the same time. I wrote notes 10, 11, 12 , and you wrote 13, 14, 15 . However, something bad could happen (deadlock or transaction failed), and my results are not saved - requests failed, and auto_increment was spent. In this case, your recordings ( 13, 14, 15 ) are written to disk, but mine are not.

This is normal behavior . Your table should not contain numbers that increase by 1. They will contain unique numbers and that task is auto_increment .

Answer 4

mysql processes a query with multiple inserts as a transaction or a single query, all rows will be inserted or if it fails, rows will not be inserted, so if you insert this query:

 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'); 

mysql will run this as a single request, if your identifier automatically increases, it will accept your identifiers 57,58,59. if another user misses an input request at the same time, there will be 2 chances if another user request takes longer than your request, your request will take 57.58.59 if your request takes longer than another user, so that your identifiers start at the other end of the user request. therefore, regardless of whether the request will be sorted by a request with several inserts, when the id is automatically incremental.

Answer 5

If you paste a value into one statement, scripts 3 and 4 are not displayed, even if you use innodb_autoinc_lock_mode = 2. From the documentation https://dev.mysql.com/doc/refman/5.7/en/innodb-auto-increment- handling.html

innodb_autoinc_lock_mode = 2 In this blocking mode, the automatic growth values ​​are guaranteed to be unique and monotonically increase in all simultaneous operations with instructions of the "INSERT" type.

Tested