On Tue, 2006-03-14 at 13:57 +0100, Sándor Tamás (HostWare Kft.) wrote:

> > In a concurrent system, you cannot guarantee "no gaps", even with
> your
> > semaphore (which is a bad idea, verging on evil).
> Can you explain, why is it so bad idea?

First, even if it would work, it is less of a guarantee of no gaps than
using a trigger.

Second, it introduces another cause of error, and a possible source of
deadlock.

Third, in a generational database like Firebird (or Oracle) your
semaphore would not even be seen by other transactions until after the
transaction was completed. Effectively, it would never be seen to
change except by the transaction that has most recently changed it.

> I know, transaction can be bottleneck, I have to guarantee that
> getting the
> next value has to be immediately before posting records, in one short
> transaction.

The problem is that the transaction can always roll back between
fetching the next value and the completion of the commit. In a
concurrent system, you have no control over which transaction is
processed to completion when the OS task switches. While you can
accomplish some of this in your app code with OS level semaphores and
critical sections (not DBMS level), the RDBMS is a separate process that
is not subject to those constraints, and generally resides on separate
hardware.

The best you can do is minimize the opportunity for dropped values, and
log all rollbacks and dropped values in a manner that you can identify
which values got dropped. This way, when audit rolls around, you run a
report that joins your drop log to the list of values and your audit
requirements are met (the dropped values are listed as dropped due to
technical failures) and you don't spend the next 50 years trying to plug
holes that are beyond your control.