Hello Rik,

RB> Several of the tables need to only insert if the record isn't already
RB> there. I always need the PK back returned because I need to insert it into
RB> cross-reference relationship tables (I don't know the correct name, I mean
RB> a table that holds the PK from one table and the PK from another table)

RB> Even though someone advised me against it, the inserts are being done using
RB> a selectable stored procedure.

RB> PROCEDURE FIND_OR_ADD_ARTIST(
RB> NAME CHAR(100) CHARACTER SET NONE)
RB> RETURNS(
RB> ARTISTID BIGINT,
RB> ADDED SMALLINT)
RB> AS
RB> DECLARE VARIABLE FOUNDID BIGINT;
RB> BEGIN
RB> FoundID = -1;
RB> Added=0;
RB> SELECT ID FROM ARTISTS WHERE (Artists.Name = :NAME) INTO :FoundID;

RB> if (:FoundID = -1) THEN
RB> BEGIN
RB> SELECT gen_id(ARTISTS_ID_GEN,1) FROM RDB$DATABASE INTO :FoundID;
RB> INSERT INTO Artists (ID, Name) VALUES (:FoundID, :NAME);
RB> Added=1;
RB> END

RB> ArtistID = :FoundID;
RB> SUSPEND;
RB> END;

RB> I'm calling it with the following query:
RB> SELECT ARTISTID, ADDED FROM FIND_OR_ADD_ARTIST ('Bob')

RB> It seemed to be working perfectly. Then during a mass insert from a bunch
RB> of threads, one of the threads started failing the inserts with: "multiple
RB> rows in singleton select." (Error -811)

That's the first SELECT in the procedure that fails the procedure, I
guess. And that was because transactions, which isolated from each
other, so it seems that two transactions from two different threads in
the application inserted the same name simultaneously: one transaction
didn't "see" changes of the second transaction, and vise versa, until
they commit.

RB> So I've got duplicates where no duplicates should be. I figure this is
RB> because each thread is so isolated from the view of what the others are
RB> doing that one can add a record for artist "Bob", get a unique ID back,
RB> then another thread does exactly the same and adds a second Bob. There's
RB> no room for 2 Bobs in my database.

Then create unique constraint for the field. After that you may simply
try to insert a name in the SP, and catch exception in your
application - in that case, you don't have to create critical sections
in your application, FB does it all for you.

Or, you may catch and process the exception right in the SP (use
WHEN...DO - refer to documentation), and return correct value for
Added, like:
-----
PROCEDURE FIND_OR_ADD_ARTIST(
NAME CHAR(100) CHARACTER SET NONE)
RETURNS(
ARTISTID BIGINT,
ADDED SMALLINT)
AS
BEGIN
Added = 1;
ARTISTID = gen_id(ARTISTS_ID_GEN, 1);
INSERT INTO Artists (ID, Name) VALUES (:ARTISTID, :NAME);
WHEN ANY /* you may provide a specific reason code here instead of ANY */ DO
BEGIN
Added = 0; /* failed */
END
SUSPEND;
END;
-----


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