🚀 go-pugleaf

Hi Umberto,
I had the same problem with SQL Server and solved it with REPEATABLE READ
Transaction Isolation Level.
This isolation level doesn't let successive transactions to read from a
table that is being updated until the first transaction is committed or
rolled back.
If InterBase supports this type of transacptions you'll have only to start
the transaction, get the new value and increment the field which contains
the counter.
To accomplish a better security you should do, every time you need the
unique id :
1) start a repeatable read transaction;
2) increment the field in the counters table;
3) get the value from the counters table;
4) insert the rows you need;
5) if error rollback transaction;
6) else commit transaction.
In this way the first thing you do is to lock all the accesses to the
counters table (2) so you can safely read the new ID value (3) and insert
rows. If the insert goes ok you commit the trans, else you rollback and the
field in the counters table return to its prior value.

I tried it using 8 applications running together with a cycle that gets 400
ids and does the 400 inserts and no errors or repeated values occurred.

HTH
Ciao
    Formentz
"Umberto Uderzo" <umberto@uderzo.it> ha scritto nel messaggio
news:3b3c3ad2_1@dnews...
> Hi Mark,
>
> Sure you're right about the SELECT MAX, infact this must be protected by
> some sort of lock, but let's talk about the link you posted: the solution
is
> good but i think this will be better using a separate session to compute
> only the new id. So you can use transactions normally with your
application,
> and call a function for the id that works on his own session, that can be
> opened only once for working session and into wich you can perform as many
> COMMIT as you line without affecting the main data processing...
>
> Bye... Umberto Uderzo
>