为什么 MySQL 自动增量会在插入失败时增加?

2021-11-20 00:00:00 mysql auto-increment innodb

一位同事刚刚让我意识到 MySQL 的一个非常奇怪的行为.

A co-worker just made me aware of a very strange MySQL behavior.

假设您有一个带有 auto_increment 字段和另一个设置为唯一字段的表(例如用户名字段).当尝试使用表中已有的用户名插入一行时，插入失败，正如预期的那样.然而，当您在多次尝试失败后插入有效的新条目时，可以看出 auto_increment 值会增加.

Assuming you have a table with an auto_increment field and another field that is set to unique (e.g. a username-field). When trying to insert a row with a username thats already in the table the insert fails, as expected. Yet the auto_increment value is increased as can be seen when you insert a valid new entry after several failed attempts.

例如，当我们的最后一个条目看起来像这样......

For example, when our last entry looks like this...

ID: 10
Username: myname

...我们在下一次插入时尝试五个具有相同用户名值的新条目，我们将创建一个新行，如下所示:

...and we try five new entries with the same username value on our next insert we will have created a new row like so:

ID: 16
Username: mynewname

虽然这本身并不是什么大问题，但正如 MySQL 参考手册所述:

While this is not a big problem in itself it seems like a very silly attack vector to kill a table by flooding it with failed insert requests, as the MySQL Reference Manual states:

如果 [...] 值变得大于可以存储在指定整数类型中的最大整数，则未定义自动递增机制的行为."

"The behavior of the auto-increment mechanism is not defined if [...] the value becomes bigger than the maximum integer that can be stored in the specified integer type."

这是预期的行为吗?

推荐答案

InnoDB 是一个事务引擎.

这意味着在以下场景中:

This means that in the following scenario:

  1. Session A 插入记录 1
  2. Session B 插入记录 2
  3. Session A 回滚
  1. Session A inserts record 1
  2. Session B inserts record 2
  3. Session A rolls back

，要么存在间隙，要么 session B 会锁定，直到 session A 提交或回滚.

, there is either a possibility of a gap or session B would lock until the session A committed or rolled back.

InnoDB 设计者(与大多数其他事务引擎设计者一样)选择允许间隙.

InnoDB designers (as most of the other transactional engine designers) chose to allow gaps.

来自文档:

当访问自增计数器时，InnoDB 使用一个特殊的表级AUTO-INC 锁，它一直保持到当前SQL 语句，不会到事务结束.引入了特殊的锁释放策略以提高插入到包含 AUTO_INCREMENT 列

When accessing the auto-increment counter, InnoDB uses a special table-level AUTO-INC lock that it keeps to the end of the current SQL statement, not to the end of the transaction. The special lock release strategy was introduced to improve concurrency for inserts into a table containing an AUTO_INCREMENT column

…

InnoDB 就会使用内存中自动递增计数器.当服务器停止并重新启动时，InnoDB 为第一个 INSERT 表重新初始化每个表的计数器，如前所述.

InnoDB uses the in-memory auto-increment counter as long as the server runs. When the server is stopped and restarted, InnoDB reinitializes the counter for each table for the first INSERT to the table, as described earlier.

如果您害怕 id 列环绕，请将其设为 BIGINT(8 字节长).

If you are afraid of the id column wrapping around, make it BIGINT (8-byte long).

相关文章