mysql事务回滚后，自增ID仍然增加

素颜马尾好姑娘i 2023-10-01 12:10 16阅读 0赞

## 事务回滚后，自增ID仍然增加 ##

回滚后，自增ID仍然增加。

比如当前ID是7，插入一条数据后，又回滚了。  
然后你再插入一条数据，此时插入成功，这时候你的ID不是8，而是9.  
因为虽然你之前插入回滚，但是ID还是自增了。

如果你认为自增ID不应该被事务化，那么其他事务不得不等待着，检查自增ID是被使用还是被回滚，这就导致阻塞。比如下面的例子，A表使用自增ID。

User 1
    ------------
    begin transaction
    insert into A ...
    insert into B ...
    update C ...
    insert into D ...
    commit

User 2
    -----------
    begin transaction
    insert into A ...
    insert into B ...
    commit

看以上的例子代码，如果自增ID也要被事务化，那么假设user 2 的事务在user 1执行后的1毫秒后执行，那么他的插入到A表不得不等待User 1的整个事务结束，检查第一个自增ID是不是被使用了。这就导致阻塞。

**自增ID不被事务化是设计使然，不是bug，如果需要紧密连续的自增序列，建议采用其他方法生成。**

--不連續沒關係,需要時候生成一列
    if object_id('tb')is not null drop table tb
    go
    create table tb(ID int )
    insert tb select 1
    insert tb select 2
    insert tb select 5
    insert tb select 18
    insert tb select 13
    select id ,[newid]=(select count(*) from tb where id<=t.id) from tb t order by [newid]
    /*id     newid   
    ----------- -----------
    1      1
    2      2
    5      3
    13     4
    18     5
    
    (影響 5 個資料列)
    */

## 为什么mysql事务回滚后，自增ID依然自增 ##

因为innodb的auto\_increament的计数器记录的当前值是保存在存内 存中的，并不是存在于磁盘上，当mysql  
server处于运行的时候，这个计数值只会随着insert改增长，不会随着delete而减少。而当mysql  
server启动时，当我们需要去查询auto\_increment计数值时，mysql便会自动执行：SELECT MAX(id) FROM 表名  
FOR UPDATE;语句来获得当前auto\_increment列的最大值，然后将这个值放到auto\_increment计数器中。所以就算  
Rollback MySQL的auto\_increament计数器也不会作负运算。