0

i've created a sequence that automatically creates id, and i've specified it to start from a specific number, say 100.

create sequence SEQ_I_LOVE_SQL start with 100 increment by 1

In my Java logic, the below sql statement is called

select I_LOVE_SQL.nextval from dual

This means that the id 101 is created. Let's say the logic changes, and a rollback from 101 to 100 is needed. What is the sql statement in my Java logic that can achieve that?

GriffeyDog
  • 8,186
  • 3
  • 22
  • 34
bouncingHippo
  • 5,940
  • 21
  • 67
  • 107
  • 3
    Whenever you think you need to changed whatever the next value of a sequence is, you are probably doing something unwise. – Dan Bracuk Jun 09 '14 at 19:42
  • Once you have fetched the next value of a sequence, there is no way to roll that back. You would have to drop and re-create the sequence to achieve that. Generally speaking, you really can't maintain a gapless ID sequence in practice, and there really shouldn't be any need to do so. – GriffeyDog Jun 09 '14 at 19:56
  • what if i get the max value of the column and +1 to that value, is that sufficiently safe? – bouncingHippo Jun 10 '14 at 14:16
  • @bouncingHippo Only if you're in a single-user environment. – GriffeyDog Jun 10 '14 at 19:41

1 Answers1

1

If you really need to rollback the generation of a sequence number, you have to work with a counter table instead of a sequence. But then you should keep in mind that you are serializing your transaction on this counter, no two of the can run in parallel. So first you should check if it is really a strict requirement more merely a wish to have id allocation to be transactional, because every implementation comes with a performance penalty (one use case I can think of are invoice numbers which in some legislations are required to be gapless). Once you have decided that you rellay need it, maintain a table with counter values and try to aquire the ID as late as possible, to keep the contention window small.

Drunix
  • 3,313
  • 8
  • 28
  • 50