自增ID在请求失败时为何跳值？如何解决该问题？

表结构 #

some_table = Table(
    "some_table",
    metadata,
    Column("id", Integer, autoincrement=True, primary_key=True),
    Column("Courier_id", Integer, ForeignKey("courier.id")),
    Column("Order_id", Integer, ForeignKey("order.id"), index=True, unique=True),
    Column("Time", String),
    Column("Status", String)
)

操作流程与异常现象 #

1. 首次插入请求(courier_id=1, order_id=1, time="time", status="status")，表中数据：

id | courier_id | order_id | time | status
1  | 1          | 1        | time | status

2. 二次插入相同值，因Order_id唯一约束报错（符合预期）。
3. 三次插入新值(courier_id=2, order_id=2, time="time", status="status")，表中数据出现ID跳值：

id | courier_id | order_id | time | status
1  | 1          | 1        | time | status
5  | 2          | 2        | time | status

已尝试Sequence(Serial)和Identity，问题未解决。当前异步插入函数代码如下：

async def add_confirm_order(new_order: RequestConfirmOrderCreate, session: AsyncSession = Depends(get_async_session)):
    try:
        stmt = insert(confirm_order).values(**new_order.dict())
        await session.execute(stmt)
        await session.flush()
    except Exception:
        await session.rollback()
        raise HTTPException(status_code=400)
    finally:
        await session.close()
    await session.commit()

核心原因 #

1. 数据库自增序列的预分配机制：主流关系型数据库（如PostgreSQL、MySQL）的自增ID依赖序列/计数器实现，为提升性能会预分配一批ID（例如PostgreSQL默认缓存20个序列值）。当插入事务因约束报错回滚时，已预分配的ID会被消耗且不会回滚，后续插入会使用下一批ID，直接导致跳值。
2. 代码事务逻辑错误：现有代码存在明显的事务管理问题：
   • finally块中提前关闭了session，后续的await session.commit()无法执行，会引发隐性错误，同时错误的事务处理会加剧ID消耗；
   • FastAPI的get_async_session依赖通常会自动管理session生命周期，手动调用session.close()属于画蛇添足。

解决办法 #

1. 修正事务管理代码 #

调整代码逻辑，确保事务提交在session关闭前执行，同时遵循FastAPI的session管理规范：

async def add_confirm_order(new_order: RequestConfirmOrderCreate, session: AsyncSession = Depends(get_async_session)):
    try:
        stmt = insert(confirm_order).values(**new_order.dict())
        await session.execute(stmt)
        await session.commit()  # 执行事务提交
    except Exception:
        await session.rollback()
        raise HTTPException(status_code=400)
    # 无需手动关闭session，get_async_session会自动处理

2. 调整数据库序列缓存策略（可选） #

如果业务强依赖连续ID，可以修改数据库的序列缓存设置，但会牺牲部分性能：

• PostgreSQL：将对应序列的缓存值改为1：

  ALTER SEQUENCE some_table_id_seq CACHE 1;
  

• MySQL：确保auto_increment_increment和auto_increment_offset均设为1（默认值），同时可使用INSERT ... ON DUPLICATE KEY UPDATE语句避免事务回滚，减少ID消耗：

  INSERT INTO some_table (courier_id, order_id, time, status) 
  VALUES (2, 2, 'time', 'status') 
  ON DUPLICATE KEY UPDATE courier_id=VALUES(courier_id);
  

3. 接受ID跳值（推荐） #

自增ID的核心作用是唯一标识记录，而非保证连续。数据库的预分配机制是成熟的性能优化方案，跳值不会影响业务逻辑。若没有特殊业务需求，建议接受这种行为，避免为了连续ID牺牲数据库性能。

内容的提问来源于stack exchange，提问作者Blz