Re: Re: inserting, index and no index - speed

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