MySQL的AUTO_INCREMENT不会回滚

Question

我正在使用MySQL的AUTO_INCREMENT字段和InnoDB来支持事务。我注意到当我回滚事务时，AUTO_INCREMENT字段没有回滚？我发现这是设计上的问题，但有没有任何解决方法？

- Gian Paulo

---

只是一点提示：但是在服务器重置后，auto_increment值将重置为该列的max+1。 - J.D. Fitz.Gerald

这不是仅适用于MySQL，Postgres的行为方式也相同。这些解释很有道理。 - Nils

Answer 1

这样是行不通的。请考虑以下情况：

• 程序一：您打开一个事务并向具有自动增量主键的表 FOO 中插入记录（任意地，我们假设它的主键值为 557）。
• 程序二启动，它打开一个事务并将记录插入到表 FOO 中，获得 558。
• 程序二将记录插入到具有外键列指向 FOO 的表 BAR 中。因此，现在 558 在 FOO 和 BAR 中都存在。
• 程序二现在提交。
• 程序三开始并从表 FOO 生成报告。报告包括 558 这条记录。
• 之后，程序一回滚了事务。

数据库如何重新获取 557 的值？它会进入 FOO 并将大于 557 的所有其他主键减少吗？它如何修复 BAR？它如何消除程序三输出中的 558？

Oracle 的序列号也独立于事务，原因相同。

如果您有一个要求，即您的自动递增字段永远不会有间隙（例如出于审计目的）。那么您不能回滚事务。相反，您需要在记录上拥有状态标志。在第一次插入时，记录的状态为“未完成”，然后开始事务，进行工作并将状态更新为“完成”（或其他您需要的内容）。然后，在提交时，记录将生效。如果事务回滚，则未完成的记录仍然存在于表中进行审计。这将带来许多其他问题，但是这是处理审计跟踪的一种方法。

- jmucchiello

Answer 2

让我指出一些非常重要的事情：

您永远不应该依赖于自动生成的键的数值特征。

也就是说，除了用等号（=）或不等号（<>）进行比较以外，您不应该做任何其他操作。不要使用关系运算符（<，>），不要通过索引排序等。如果您需要按“添加日期”排序，请设置一个“添加日期”列。

将它们视为苹果和橙子：询问苹果是否与橙子相同有意义吗？有。问一个苹果是否比橙子大有意义吗？不。 （实际上，它确实有意义，但是你明白我的意思。）

如果您遵循这个规则，自动生成的索引的连续性间隔不会导致问题。

- DrJokepu

---

你能稍微解释一下吗？我的意思是，为什么要添加一个完整的新列，还要承担所有随之而来的开销（索引管理、磁盘使用、更多的IO等等），当已经有一个完美的值可以在autoinc列中使用了呢？按定义，它提供了一个唯一的值，不会重复，并且随着记录的添加而始终递增。它唯一不连续，但只要你假设序列中存在间隔，我认为没有问题使用它来对一组记录按照插入顺序进行排序。事实上，我认为从性能和清晰度方面考虑，这是最好的方法。 - mr. w

这只是一条建议，而非答案。 - Joaquín L. Robles

添加日期是在提交时还是插入时发生的？如果多个线程插入到表中，由于执行时间的延迟，是否可能出现后面的时间戳先于较早的时间戳可见？ - user2505915

作为一名多年的开发者，在阅读了这个答案之后，我感觉自己受到了启迪！ - evilReiko

@JoaquínL.Robles 的建议非常好，以至于我忘记了答案。 - Peter Chaula

Answer 3

我曾有一个客户需要在一张发票表中对ID进行回滚，要求订单必须是连续的。

我的MySQL解决方案是移除自增列并从表中获取最新的ID，加上1（+1），然后手动插入。

如果表名为"TableA"，自增列名为"Id"。

INSERT INTO TableA (Id, Col2, Col3, Col4, ...)
VALUES (
(SELECT Id FROM TableA t ORDER BY t.Id DESC LIMIT 1)+1,
Col2_Val, Col3_Val, Col4_Val, ...)

- Michael Corrigan

---

也许这样会更好：(SELECT MAX(id) AS maxval FROM table_name)+1，意思是查询表中最大的 ID 值，并加上 1。 - vinsa

这不会是一个原子操作，肯定会有一个小的时间窗口，在这个时间窗口内，可能会有多个查询获得相同的最大ID。 - Ouroboros

@P.Prasad - 你不能使用LOCK TABLES来防止这种情况吗？ - Michael Corrigan

那应该放在答案中。 - Ouroboros

你可以使用LOCK TABLES，但这意味着同时只有一个事务可以访问你的表。在许多应用程序中，有几十个或数百个并发会话正在插入或更新表中的行。自增量仅在提交事务之前保持锁定，它仅会短暂地锁定足够增加id。(这是一个旧答案，但今天刚好出现在我的信息流中) - Bill Karwin

Answer 4

你为什么在意它是否回滚？AUTO_INCREMENT主键字段本来就不应该有任何含义，所以你真的不应该关心使用了什么值。

如果你想保留信息，也许需要添加另一个非主键列。

- Paul Lefebvre

Answer 5

如果在回滚或删除后将auto_increment设置为1，则在下一次插入时，MySQL会发现1已经被使用，而会获取MAX()值并将其加1。

这将确保如果具有最后一个值的行被删除（或插入被回滚），它将被重用。

要将auto_increment设置为1，请执行以下操作：

ALTER TABLE tbl auto_increment = 1

这种做法不如简单地继续使用下一个数字高效，因为MAX()可能很昂贵，但如果您很少删除/回滚并且痴迷于重复使用最高值，则这是一种可行的方法。

请注意，这不会防止在中间删除记录或在您将自动增量设置回1之前发生其他插入导致间隙。

- Marcus Adams

---

请注意，如果这样做，您应该在之后调用 ANALYZE TABLE tbl，否则旧的 AUTO_INCREMENT 值将在 information_schema.TABLES 中仍然出现直到它过期。 - hackel

Answer 6

我不知道有什么方法可以做到这一点。根据MySQL文档，无论是哪种innodb_autoinc_lock_mode锁定模式，都会出现这种情况。具体的文字如下：

在所有锁定模式（0、1和2）中，如果生成自增值的事务回滚，则这些自增值将“丢失”。一旦为自增列生成了值，无论“类似于INSERT”的语句是否完成，以及包含该事务是否回滚，都无法回滚该值。这些丢失的值不会被重用。因此，在表的AUTO_INCREMENT列中存储的值可能存在间隙。

- gpojd

Answer 7

INSERT INTO prueba(id) 
VALUES (
(SELECT IFNULL( MAX( id ) , 0 )+1 FROM prueba target))

如果表中没有任何值或者行数为零

为mysql类型更新设置FROM语句的目标，添加SELECT错误的目标。

- danis

Answer 8

解决方案：

让我们以“tbl_test”作为示例表，假设字段“Id”具有AUTO_INCREMENT属性。

CREATE TABLE tbl_test (
Id int NOT NULL AUTO_INCREMENT ,
Name varchar(255) NULL ,
PRIMARY KEY (`Id`)
)
;

假设表格中已经插入了一百或一千行，您不想再使用AUTO_INCREMENT；因为当您回滚事务时，“Id”字段总是会将AUTO_INCREMENT值加1。 因此，为了避免这种情况，您可以这样做:

- 删除“Id”列的AUTO_INCREMENT值（这不会删除您插入的行）:

ALTER TABLE tbl_test MODIFY COLUMN Id int(11) NOT NULL FIRST;

- 最后，我们创建一个BEFORE INSERT触发器来自动生成“Id”值。但是即使您回滚任何事务，也不会影响您的Id值。

CREATE TRIGGER trg_tbl_test_1 BEFORE INSERT ON tbl_test FOR EACH ROW BEGIN SET NEW.Id= COALESCE((SELECT MAX(Id) FROM tbl_test),0) + 1; END;

完成了！

- mld.oscar

---

我喜欢你的解决方案，它在并发请求下会如何表现？特别是@Ouroboros在https://dev59.com/DnRB5IYBdhLWcg3w-8A9#RaygEYcBWogLw_1blfDA中描述的情况，它是否可以解决这个问题？ - KumZ

@KumZ 这可能有助于你的答案 https://stackoverflow.com/a/42683736/4564384 - mld.oscar

Answer 9

如果您需要按数字顺序分配ID而没有间隙，那么您不能使用自动递增列。您需要定义一个标准整数列，并使用存储过程计算插入序列中的下一个数字并在事务内插入记录。如果插入失败，则下次调用该过程时将重新计算下一个ID。

话虽如此，依赖于ID以某种特定顺序且没有间隙是一个坏主意。如果您需要保留排序，您应该在插入时（可能还要更新）为行加上时间戳。

- tvanfosson

---

这意味着同时操作同一张表的两个事务永远无法同时成功提交。这可能会成为一个巨大的瓶颈。如果您有很多外键，当它们具有多对多关系时，您很容易遇到死锁问题。我的间隙处理答案只会稍微有点影响。 :-) - jmucchiello

Answer 10

针对这个特定问题（我也遇到过），具体的解决方案如下：

1）创建一个表格，用于存储不同文件（发票、收据、RMA等）的不同计数器；为每个文件插入一条记录，并将初始计数器设置为0。

2）在创建新文件之前，按照以下步骤操作（例如，对于发票）：

UPDATE document_counters SET counter = LAST_INSERT_ID(counter + 1) where type = 'invoice'

3）获取刚更新的最后一个值，如下：

SELECT LAST_INSERT_ID()

或者只需使用您的PHP（或其他）mysql_insert_id（）函数即可获得相同的结果

4）插入您的新记录以及刚从数据库中获取的主ID。这将覆盖当前的自动增量索引，并确保您的记录之间没有ID间隔。

当然，整个过程需要包装在一个事务中。这种方法的美妙之处在于，当您回滚一个事务时，步骤2中的UPDATE语句将被回滚，并且计数器不会再次更改。其他并发事务将被阻塞，直到第一笔交易被提交或回滚，因此在所有其他事务完成之前，它们将无法访问旧计数器或新计数器。

- teomor