0

I have a Spring application which uses an SQL database. In the application I use rollback for Transactions. When an exception occurs, the transaction gets rolled back as it should and all the created rows get rolled back. However, the IDs keep incrementing.

For example, if it inserts IDs 5,6,7, and then rolls back everything, despite the rollback next time the ID starts from 8. I would like it to start from 5.

Is there a way to handle this so that the ID counter gets rolled back too? Is this possible in NoSQL databases?

Mark Rotteveel
  • 90,369
  • 161
  • 124
  • 175
Dulat Aldazharov
  • 40
  • 2
  • 6
  • For suggestion what will be if you write logic everything is okay then save this data into database – Jahongir Sabirov Sep 14 '20 at 06:07
  • Does this answer your question? [SQL Identity (autonumber) is Incremented Even with a Transaction Rollback](https://stackoverflow.com/questions/282451/sql-identity-autonumber-is-incremented-even-with-a-transaction-rollback) – Mark Rotteveel Sep 14 '20 at 11:23
  • Also: [How to reset Autoincremented Id when rollback occurs in sql](https://stackoverflow.com/questions/16358966/how-to-reset-autoincremented-id-when-rollback-occurs-in-sql) – Mark Rotteveel Sep 14 '20 at 11:24

1 Answers1

0

Auto increments like SEQUENCE or IDENTITY are never regenerated because some other sessions can have been take new values in the continuity

This is the SQL standard and every RDBMS have the same behaviour.

SQLpro
  • 2,501
  • 1
  • 4
  • 11