On Sun, 10 Jun 2001, Vivek Khera wrote:
> Even if your transaction fails? That seems to counter the definition
> of a transaction that aborts; the state of the database is different
> than before.
Yes, except for the sequences.
Consider this example, transactions A, B, C, sequence S.
in A S.nextval = 1
in B S.nextval = 2
in C S.nextval = 3
transaction B then aborts, A and C succeed. Then, in your logic, nextval
of S should be 2, but really, to keep this kind of state, you need a table
listing 'currently unused values'. That, when your sequence gets to
millions, is a colossal waste of space.
If you want "maximum id that's not currently used in my table" use
max(id), if you want "give me a non-repeating number", use sequence.
There also are implications on concurrency when you use max(id), as only
one transaction can do it without danger of repeating IDs.
-alex