Great information, thank you.

I have hundreds of inserts just like this one to make
from each of my clients. I do all the inserts from one
client in 1 transaction.

The reason that I use "WRITE|COMMITTED|REC_NO_VERSION|NOWAIT"
transaction is that if there is a conflict lets say on the 10th (out
of 200)insert I want to revert the transaction at this point and try
again, instead of wasting time by processing the rest of the 190
inserts and then getting a "conflict" when I commit.

It does not make sense to create separate transactions for
each insert, I tried this, and the performance is terrible.

am I missing something?

thank you,

stan

--- In firebird-support@yahoogroups.com, "Ann W. Harrison"
<aharrison@...> wrote:

>
> Stan wrote:
>
> > CREATE TABLE enum1
> > ( enum1_id BIGINT NOT NULL PRIMARY KEY,
> > enum1_name VARCHAR(128) NOT NULL UNIQUE,
> > enum1_description VARCHAR(128) DEFAULT '' );
> >
> > I am going to have 200+ clients simultaneously insert records into
> > this table. A lot of the inserts will be "duplicates": the
> > unique constraint will refuse the insert.
>
> What your currently doing is inviting live-lock - operations
> that run, fail, undo themselves, run, fail, undo, and so on,
> wasting innumerable cycles for no benefit.
>
> OK. Here's what I would do. 1) use a concurrency transaction.
> 2) use WAIT mode, not NO_WAIT. 3) don't use REC_NO_VERSION
>
> Define the procedure like this:
>
> CREATE OR ALTER PROCEDURE get_enum1_id( enum1 VARCHAR(128) )
> RETURNS( group_row BIGINT )
> AS BEGIN
> SELECT enum1_id FROM enum1 WHERE enum1_name = :enum1 INTO

:group_row;

> IF (:group_row IS NULL) THEN
> BEGIN
> group_row = GEN_ID (enum1_generator, 1);
> INSERT INTO enum1 VALUES(:group_row, :enum1,'');
> END
> SUSPEND;
> END!!
>
> Start a transaction, invoke the procedure, and commit.
>
> If the procedure gets an error, start a new transaction,
> and run it again. The second time, the select will succeed.
>
> This avoids serializing inserts, reports an error only when
> there's a conflicting insert in progress.
>
> Regards,
>
>
> Ann
>