Hi All,

I have a table:

CREATE GENERATOR enum1_generator;
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.

To do this I have created a procedure:

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,'');
WHEN SQLCODE -803 DO
BEGIN
SELECT enum1_id FROM enum1 WHERE enum1_name = :enum1 INTO :group_row;
END
END
SUSPEND;
END!!


It goes without saying that I get a lot of deadlocks. I define my
insert transacitons with:
IBASE_WRITE|IBASE_COMMITTED|IBASE_REC_NO_VERSION|IBASE_NOWAIT

and when I get a deadlock, I simply back off and try again.

Is this a good way to accomplish inserts with my usage scenario?
is there a better(faster, more stable) way that will avoid the
deadlocks? maybe using
explicit locks?

thank you,

stan