0

I would like to have a sequence, that will be able to give me the same value again, if transaction was rolled back. This should work on clustered environment and be fast.

In other words i don't want to have a holes in my numeration.

Example:

I have a 5 transactions doing the same and in every transaction i generate next value.

  • Transaction 1: generated value 1.
  • Transaction 2: generated value 2.
  • Transaction 3: generated value 3.
  • Transaction 4: generated value 4.
  • Transaction 5: generated value 5.

Now lets suppose that transactions "2" and "4" were rollback. If i have normal sequence and I run next transaction generated value will be most propably "6". My sequence should return unused values so in our case "2" or "4".

Douglas B. Staple
  • 10,510
  • 8
  • 31
  • 58
androdevo
  • 752
  • 1
  • 8
  • 17
  • Have you considered concurrency? With concurrent transactions it is hardly possible to guarantee a gapless sequence of IDs. If transactions are strictly serial, it can be done. – Erwin Brandstetter Dec 02 '11 at 19:49
  • I considered concurrency. Transaction should block generated value and return it in case of rollback or mark as used if everything ok. – androdevo Dec 02 '11 at 20:00
  • fast + without gaps == impossible – JB Nizet Dec 02 '11 at 20:00

0 Answers0