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?
假设我有一个表,其中最后一个id为PRIMARY_KEY AUTO_INCREMENT值为56,那么这个插入查询总是创建3条带有ids 57,58,59的记录。这个操作是原子的吗?
Or, if another query writes on the same table, could the ids not increment always by 1?
或者,如果另一个查询写在同一个表上,那么id不会总是增加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.
当然,AUTO_INCREMENT安全地增加一个,我知道。
The point is:
重点是:
Let's say I have the following table called table:
假设我有下表叫做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.
取决于首先查询两个MySQL计划。
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)?
或任何其他组合!= 3)和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?
我认为1)和2)是原子的。是否始终保证我总是以1)或2)结束并且永远不会以3)或4)或任何其他组合结束?对于MyISAM和InnoDB,如果是(我将总是以1)或2))结束?
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?
如果我做SELECT LAST_INSERT_ID();例如我得到7,它是否自动意味着我的查询也插入了id为8和9的行,而不是另一个人的查询?
5 个解决方案
#1
7
The answer is: well, it depends.
答案是:嗯,这取决于。
In case of myisam, the answer is a definite yes, since myisam sequences insert requests.
在myisam的情况下,答案是肯定的,因为myisam序列插入请求。
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.
但是,对于innodb,从mysql v5.1开始,行为是可配置的。在v5.1之前,然后对InnoDB的回答也是肯定的,之后它依赖于innodb_autoinc_lock_mode设置。有关详细信息,请参阅InnoDB auto_increment配置的mysql文档。
To give you the highlights, there are 3 innodb_autoinc_lock_mode settings:
为了给你提供亮点,有3个innodb_autoinc_lock_mode设置:
- traditional (0)
- consequtive (1) - default
- interleaved (2)
连续的(1) - 默认
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.
将innodb_autoinc_lock_mode设置为0(“传统”)或1(“连续”)时,任何给定语句生成的自动递增值将是连续的,没有间隙,因为表级别的AUTO-INC锁定一直保持到结束声明,一次只能执行一个这样的声明。
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.
将innodb_autoinc_lock_mode设置为2(“interleaved”)时,“批量插入”生成的自动增量值可能存在间隙,但前提是同时执行“INSERT-like”语句。
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.
对于锁定模式1或2,在连续语句之间可能出现间隙,因为对于批量插入,可能不知道每个语句所需的确切数量的自动增量值,并且可能过高估计。
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.
如果已回滚事务,则可以在auto_increment值中体验进一步的差距。批量插入件只能作为一个整体回滚。
UPDATE: As described above, you will get scenario 1) or 2), if you use
更新:如上所述,如果您使用,您将获得方案1)或2)
- myisam table engine
- or innodb pre mysql v5.1
- or innodb with mysql v5.1 or newer and the
innodb_autoinc_lock_modeis 0 or 1
myisam表引擎
或者innodb pre mysql v5.1
或innodb与mysql v5.1或更新版本,innodb_autoinc_lock_mode为0或1
There is no way of telling which gets inserted first.
没有办法告诉哪个先插入。
You may get scenario 3) or 4) if you use
如果您使用,您可能会获得方案3)或4)
- innodb with
innodb_autoinc_lock_mode2
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.
同样,没有办法告诉mysql如何以及为什么混合记录的顺序。
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.
因此,如果您的问题与使用批量插入插入3条记录并且last_insert_id()仅返回第一个插入记录的auto_increment值这一事实有关,并且您希望通过简单添加获得其他2条记录的ID是您的可能需要根据表引擎和使用的mysql版本来检查mysql的配置。
#2
0
If you define any column primary key aut_increment then it will automatically increase value start from 1, you don't need to define this column in insert query then it will automatically insert incremented value in primary key column.
如果您定义任何列主键aut_increment然后它将自动增加值从1开始,您不需要在插入查询中定义此列,然后它将自动在主键列中插入递增值。
#3
0
auto_increment is safe in concurrent environment. It's job is to give unique values, no matter how many people you have connected and working on a table. You can control the offset for incrementing, by default it's 1.
auto_increment在并发环境中是安全的。无论您有多少人连接并在桌子上工作,它的工作就是提供独特的价值观。您可以控制递增的偏移量,默认为1。
Now what does this actually mean - it means that what's written in the table doesn't have to be incremented by 1. This is the famous "gap" problem.
现在这意味着什么 - 它意味着表中所写的内容不必增加1.这就是着名的“差距”问题。
Suppose that you and I are writing to your table at the same time. I wrote records 10, 11, 12 and you wrote 13, 14, 15. However, something bad could have happened (a deadlock, or transaction failed) and my results aren't persisted - the queries failed and the auto_increment got spent. In this scenario, your records (13, 14, 15) are written to the disk and my aren't.
假设你和我同时写信给你的桌子。我写了记录10,11,12,你写了13,14,15。然而,可能发生了一些不好的事情(死锁或事务失败),我的结果没有持久化 - 查询失败并且auto_increment花了。在这种情况下,您的记录(13,14,15)将写入磁盘,而我的记录则不会。
This is normal behaviour. Your table doesn't have to contain numbers that are incremented by 1. It will contain unique numbers and that's the job of auto_increment.
这是正常行为。您的表不必包含递增1的数字。它将包含唯一的数字,这是auto_increment的工作。
#4
0
mysql treat multi insertion query as a transaction or one query, all rows will be inserted or if it failed there is no rows will be inserted, so if you insert this query:
mysql将多插入查询视为事务或一个查询,将插入所有行,如果失败,则不会插入任何行,因此如果插入此查询:
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 one query, if your id auto incremental it will take your ids 57,58,59. if the other user pass insert query in the same time, it will 2 probability if other user query take more time than your query your query will take 57,58,59 if your query take more time than the other user so your ids will start from the other user query end. so whatever the case the multi insert query when id is auto incremental will be sorted in the query.
mysql会将此作为一个查询运行,如果你的id为自动增量,它会占用你的id为57,58,59。如果其他用户同时传递插入查询,那么如果其他用户查询花费的时间超过您的查询,则查询将花费578,59,如果您的查询花费的时间比其他用户多,那么将有2个概率,因此您的ID将启动从其他用户查询结束。所以无论如何,当id是自动增量时,多插入查询将在查询中排序。
#5
0
If you insert value in one statement scenario 3 and 4 don't appear even if you are using them innodb_autoinc_lock_mode = 2. From documentation https://dev.mysql.com/doc/refman/5.7/en/innodb-auto-increment-handling.html
如果在一个语句场景3和4中插入值,即使您使用它们也不会出现innodb_autoinc_lock_mode = 2.来自文档https://dev.mysql.com/doc/refman/5.7/en/innodb-auto-increment -handling.html
innodb_autoinc_lock_mode = 2 In this lock mode, auto-increment values are guaranteed to be unique and monotonically increasing across all concurrently executing “INSERT-like” statements.
innodb_autoinc_lock_mode = 2在这种锁定模式下,自动增量值保证是唯一的,并且在所有同时执行的“INSERT-like”语句中单调递增。
Tested
#1
7
The answer is: well, it depends.
答案是:嗯,这取决于。
In case of myisam, the answer is a definite yes, since myisam sequences insert requests.
在myisam的情况下,答案是肯定的,因为myisam序列插入请求。
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.
但是,对于innodb,从mysql v5.1开始,行为是可配置的。在v5.1之前,然后对InnoDB的回答也是肯定的,之后它依赖于innodb_autoinc_lock_mode设置。有关详细信息,请参阅InnoDB auto_increment配置的mysql文档。
To give you the highlights, there are 3 innodb_autoinc_lock_mode settings:
为了给你提供亮点,有3个innodb_autoinc_lock_mode设置:
- traditional (0)
- consequtive (1) - default
- interleaved (2)
连续的(1) - 默认
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.
将innodb_autoinc_lock_mode设置为0(“传统”)或1(“连续”)时,任何给定语句生成的自动递增值将是连续的,没有间隙,因为表级别的AUTO-INC锁定一直保持到结束声明,一次只能执行一个这样的声明。
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.
将innodb_autoinc_lock_mode设置为2(“interleaved”)时,“批量插入”生成的自动增量值可能存在间隙,但前提是同时执行“INSERT-like”语句。
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.
对于锁定模式1或2,在连续语句之间可能出现间隙,因为对于批量插入,可能不知道每个语句所需的确切数量的自动增量值,并且可能过高估计。
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.
如果已回滚事务,则可以在auto_increment值中体验进一步的差距。批量插入件只能作为一个整体回滚。
UPDATE: As described above, you will get scenario 1) or 2), if you use
更新:如上所述,如果您使用,您将获得方案1)或2)
- myisam table engine
- or innodb pre mysql v5.1
- or innodb with mysql v5.1 or newer and the
innodb_autoinc_lock_modeis 0 or 1
myisam表引擎
或者innodb pre mysql v5.1
或innodb与mysql v5.1或更新版本,innodb_autoinc_lock_mode为0或1
There is no way of telling which gets inserted first.
没有办法告诉哪个先插入。
You may get scenario 3) or 4) if you use
如果您使用,您可能会获得方案3)或4)
- innodb with
innodb_autoinc_lock_mode2
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.
同样,没有办法告诉mysql如何以及为什么混合记录的顺序。
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.
因此,如果您的问题与使用批量插入插入3条记录并且last_insert_id()仅返回第一个插入记录的auto_increment值这一事实有关,并且您希望通过简单添加获得其他2条记录的ID是您的可能需要根据表引擎和使用的mysql版本来检查mysql的配置。
#2
0
If you define any column primary key aut_increment then it will automatically increase value start from 1, you don't need to define this column in insert query then it will automatically insert incremented value in primary key column.
如果您定义任何列主键aut_increment然后它将自动增加值从1开始,您不需要在插入查询中定义此列,然后它将自动在主键列中插入递增值。
#3
0
auto_increment is safe in concurrent environment. It's job is to give unique values, no matter how many people you have connected and working on a table. You can control the offset for incrementing, by default it's 1.
auto_increment在并发环境中是安全的。无论您有多少人连接并在桌子上工作,它的工作就是提供独特的价值观。您可以控制递增的偏移量,默认为1。
Now what does this actually mean - it means that what's written in the table doesn't have to be incremented by 1. This is the famous "gap" problem.
现在这意味着什么 - 它意味着表中所写的内容不必增加1.这就是着名的“差距”问题。
Suppose that you and I are writing to your table at the same time. I wrote records 10, 11, 12 and you wrote 13, 14, 15. However, something bad could have happened (a deadlock, or transaction failed) and my results aren't persisted - the queries failed and the auto_increment got spent. In this scenario, your records (13, 14, 15) are written to the disk and my aren't.
假设你和我同时写信给你的桌子。我写了记录10,11,12,你写了13,14,15。然而,可能发生了一些不好的事情(死锁或事务失败),我的结果没有持久化 - 查询失败并且auto_increment花了。在这种情况下,您的记录(13,14,15)将写入磁盘,而我的记录则不会。
This is normal behaviour. Your table doesn't have to contain numbers that are incremented by 1. It will contain unique numbers and that's the job of auto_increment.
这是正常行为。您的表不必包含递增1的数字。它将包含唯一的数字,这是auto_increment的工作。
#4
0
mysql treat multi insertion query as a transaction or one query, all rows will be inserted or if it failed there is no rows will be inserted, so if you insert this query:
mysql将多插入查询视为事务或一个查询,将插入所有行,如果失败,则不会插入任何行,因此如果插入此查询:
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 one query, if your id auto incremental it will take your ids 57,58,59. if the other user pass insert query in the same time, it will 2 probability if other user query take more time than your query your query will take 57,58,59 if your query take more time than the other user so your ids will start from the other user query end. so whatever the case the multi insert query when id is auto incremental will be sorted in the query.
mysql会将此作为一个查询运行,如果你的id为自动增量,它会占用你的id为57,58,59。如果其他用户同时传递插入查询,那么如果其他用户查询花费的时间超过您的查询,则查询将花费578,59,如果您的查询花费的时间比其他用户多,那么将有2个概率,因此您的ID将启动从其他用户查询结束。所以无论如何,当id是自动增量时,多插入查询将在查询中排序。
#5
0
If you insert value in one statement scenario 3 and 4 don't appear even if you are using them innodb_autoinc_lock_mode = 2. From documentation https://dev.mysql.com/doc/refman/5.7/en/innodb-auto-increment-handling.html
如果在一个语句场景3和4中插入值,即使您使用它们也不会出现innodb_autoinc_lock_mode = 2.来自文档https://dev.mysql.com/doc/refman/5.7/en/innodb-auto-increment -handling.html
innodb_autoinc_lock_mode = 2 In this lock mode, auto-increment values are guaranteed to be unique and monotonically increasing across all concurrently executing “INSERT-like” statements.
innodb_autoinc_lock_mode = 2在这种锁定模式下,自动增量值保证是唯一的,并且在所有同时执行的“INSERT-like”语句中单调递增。
Tested