> When a member is deleted and creates a hole, the next new member
> fills it.

That is not covered by the AUTOINC that is being proposed.

> I call an SP in a transaction and lock the table for the instant I
> am getting the
> next sequential number. The SP adds the new record with the Id, the Id
> returned for use in detail records, the transaction commits and lock
> released. Any simultaneous accesses for same reason
> repeatedly attempt to lock the table for the next Id until lock
> released or timed out.

Given the previous comment, is this the next sequential
number, or the next available number? My membership systems
avoid re-using old numbers because past history is corrupted
- again it's down to end user requirements.

In my other case - order entry - a sales clerk can be 5 to
10 minutes working on an order while several other orders
are added and completed. We did go through a phase of
'updating' the order number when finally committed, but the
problem then is updating each transaction record ( stock
movement ), so we now live with the 'gaps' which are flagged
as cancelled orders and even the VAT inspector is happy. We
needed the cancel function anyway for the quite regular
'cancel and reorder' so the old order number had to flag the
new one.

I can probably give as many examples where holes are
acceptable - at the end of the day it's WHEN the client
application asks for the next number that will determine
sequentiallity. Ask as part of the commit, and use a
temporary generator for 'work in progress' and we have a
simple solution that could be 'automated'.

People will still do their own thing anyway, it's just a
matter of how much work is expended on Firebird to make
savings for those who do use it.

> I have tested with 7 clients and seems to work fine.

Put 40 of them on a Britsh Telecom Frame Relay net with an
up to 30 second responce time, and try waiting <g>

--
Lester Caine
-----------------------------
L.S.Caine Electronic Services