Hi,

Thanks to everyone who took the time to reply to my problem yesterday.
I've tried to understand and implement the advice given and I'm running
into a problem that I think must relate to a lack of commits.

Based on the advice given, I modified the table so it had a unique
constraint on the name field.

The full definition of the table is here:
CREATE TABLE ARTISTS (
ID BIGINT NOT NULL,
NAME CHAR(100) CHARACTER SET NONE COLLATE NONE);

ALTER TABLE ARTISTS ADD CONSTRAINT UQ_ARTIST_NAME UNIQUE (NAME);
ALTER TABLE ARTISTS ADD CONSTRAINT PK_ARTISTS PRIMARY KEY (ID);
CREATE INDEX NAME ON ARTISTS(NAME);

Then I modified the SP so it didn't attempt to select anymore, it just did
an insert and caught any duplicate constraint error.

CREATE PROCEDURE ADD_ARTIST(
NAME CHAR(100) CHARACTER SET NONE)
RETURNS(
ARTISTID BIGINT,
ADDED SMALLINT)
AS
BEGIN
Added=1;
ArtistID = gen_id(ARTISTS_ID_GEN, 1);
BEGIN
INSERT INTO Artists (ID, Name) VALUES (:ArtistID, :NAME);
WHEN SQLCODE -803 DO
BEGIN
ArtistID = -1;
Added=0;
END
END
SUSPEND;
END;

Then I changed my thread class so that before any insert, it first does a
SELECT for the record. If it's not found, then it calls the SP to add
it. If the SP returns -1, it assumes the record is being written but not
yet committed by another thread and sleeps for a random length of time (up
to 30 seconds) before SELECTing the record again.

In theory (to me) that looks like a thread-safe de-duping system for always
adding or getting the ID for a record. Problem is, it doesn't work.

There are 2 problems. The first is that sometimes the SP generates a lock
exception:

"lock conflict on no wait transaction violation of PRIMARY or UNIQUE KEY
constraint "UQ_ARTIST_NAME" on table "ARTISTS". Error Code: -901.
Unsuccessful execution caused by system error that does not preclude
successful execution of subsequent statements".

I'm guessing that's two attempts to add calling at the same time. I can
always get round that by changing the error handler in the SP if it's part
of the same problem.

The second problem is worse. As soon as the SP returns -1 indicating that
the record is already there, but not yet transactionally visible, no matter
how long I sleep the threads for, the follow on SELECT _never_ finds the
record, so the thread loops, goes back to sleep for a random period of
time, before trying to get the record again.... forever.

Now, that's gotta be because the thread that had the successful first add
still hasn't committed, doesn't it? Which is just melting my brain.

Every thread has its own connection. Every connection is set to
transaction level Repeatable Read. Every connection has AutoCommit=True,
which as far as I can tell from all the Zeos documentation means as soon as
a query is closed it is committed. Every select and every insert is
opened, read, then closed. I don't have a scooby as to why else I can't
see the record though.

So, my question (after yet another book length post) is this: What am I
missing?

Cheers,
Rik


[Non-text portions of this message have been removed]