Subject Re: [ib-support] Generated ID and constraints
Author Ann W. Harrison
At 05:16 PM 12/19/2001 +0000, Douglas Tosi wrote:

> If any constraint causes an exeption user get error message but my
> generator already increased.

In the Before Insert trigger, do:
and in the After insert trigger:
DummyVar = GEN_ID(GEN, 1);

I'm not sure if this method works ok on a highly concurrent environment...

Unfortunately, it will work badly - there will be duplicates and
skipped numbers. The issue of "lost" generator values has been
discussed on this list and the old mers list and the even older
InterBase list. The upshot is this. There are a number of ways
to insure that all id values are used, but they are all transaction
based. Transaction based identifiers are a bottleneck at best and
lead to frequent deadlocks at worst. There are ways to reduce the
bottleneck, but they tend to lead to complicated code. Generators
are very cheap to use and can not deadlock. However if an operation
fails, they "lose" values.


We have answers.