🚀 go-pugleaf

Hi Francesco,

Thanks for descripting your approach to the problem. Personally i've adopted
another tehnique (sure it's not much portable, but it will be easily adapted
for other DBMS): instead of using a generator into a trigger to autoset the
primary key value (this causes problems trying to refresh the autoinc field)
i perform directly the query SELECT gen_id(generator_name,1), COUNT(*) FROM
table_name, where gen_id is a proprietary sintax of Interbase used to get an
unique id from a <generator_name> generator that doesn't suffer multiuser
troubles, and COUNT(*) is used as grouping function to avoid the result of
generating as many new id as the rows of the table <table_name>, used here
only to respect the select syntax.
I repeat, not so portable, but easily replaceable with your technique if
needed (just isolating the entire id generation process into a private
session).

Ciao... Umberto Uderzo

P.S. I think it's a bit dangerous to rollback the id rows values as you say.
To have a sure robust mechanism i suggest to increment always and anyway.
So: perform the increment step in a separate session.

"Formentz" <francesco@leotron.com> ha scritto nel messaggio
news:3b3c7c38_1@dnews...
> 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.