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