Hello Rik,

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

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

I don't get the point here: why do you try to select firstly? I
suppose that is redundant select, because you can get the same info
when you select from the SP trying to insert a new name: you either
get -1\0 (if it failed) or ArtistID\1 (if it succeeded).

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

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

It seems that your both problems have the same source. I don't know
how Zeos works with transactions, but in your case it's better to
process transactions explicitly, keeping them as short as possible.

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

Are you sure that -803 is the right code above? Maybe it should be
-901? (I don't know for sure, but it seems that you get in your
application the exception which you want to catch in the SP)

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

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

Here, I don't get your point again: if you know that the name is in
the table already, why do you try to insert the same name one more
time (knowing that there is a unique constraint for that)? Maybe
another thread deletes names from the table time to time...

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

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

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

Does your business-logic require commit after each insert? Or, commit
after trying to insert a batch of records?


--
Best regards,
Pavel Menshchikov
http://www.ls-software.com