--- In firebird-support@yahoogroups.com, Rik Barker <rik.barker@...>
wrote:

>
> Hi,
>
> I've got a problem inserting records that is definitely down to bad

design

> on my part. I'm hopeful someone can slap some sense into me before

I make

> matters any worse.
>
> Someone hinted this was wrong when helping me with a problem with

embedded

> server a few days ago, but I stumbled on blindly. *8)
>
> To get to the problem: This is using Firebird 1.5.3 superserver (to
> eventually run embedded). It has multiple threads inserting

thousands of

> records into the database. Each thread maintains its own

connection. In

> case it's relevant, this is coded in Delphi 7 using ZeosLib to connect.
>
> Several of the tables need to only insert if the record isn't already
> there. I always need the PK back returned because I need to insert

it into

> cross-reference relationship tables (I don't know the correct name,

I mean

> a table that holds the PK from one table and the PK from another table)
>
> Even though someone advised me against it, the inserts are being

done using

> a selectable stored procedure.
>
> PROCEDURE FIND_OR_ADD_ARTIST(
> NAME CHAR(100) CHARACTER SET NONE)
> RETURNS(
> ARTISTID BIGINT,
> ADDED SMALLINT)
> AS
> DECLARE VARIABLE FOUNDID BIGINT;
> BEGIN
> FoundID = -1;
> Added=0;
> SELECT ID FROM ARTISTS WHERE (Artists.Name = :NAME) INTO :FoundID;
>
> if (:FoundID = -1) THEN
> BEGIN
> SELECT gen_id(ARTISTS_ID_GEN,1) FROM RDB$DATABASE INTO :FoundID;
> INSERT INTO Artists (ID, Name) VALUES (:FoundID, :NAME);
> Added=1;
> END
>
> ArtistID = :FoundID;
> SUSPEND;
> END;
>
> I'm calling it with the following query:
> SELECT ARTISTID, ADDED FROM FIND_OR_ADD_ARTIST ('Bob')
>
> It seemed to be working perfectly. Then during a mass insert from a

bunch

> of threads, one of the threads started failing the inserts with:

"multiple

> rows in singleton select." (Error -811)
>
> So I've got duplicates where no duplicates should be. I figure this is
> because each thread is so isolated from the view of what the others are
> doing that one can add a record for artist "Bob", get a unique ID back,
> then another thread does exactly the same and adds a second Bob.

There's

> no room for 2 Bobs in my database.
>
> So, I've got a design issue. I can see a potential fix, but I don't

want

> to code myself into the wrong corner, I'd rather get the advice first.
>
> I could alter the SELECT to only "SELECT FIRST 1...". Now the code

won't

> fail, but I'll still have too many Bobs. Hardly a good solution/
>
> I could make all the fields in the table unique, but then I'd have

to trap

> for the error and I haven't found how to do that in a stored procedure
> yet. I prefer the 2nd approach, but I don't know how to do it. I'm
> guessing given the gaping void that represents my lack of knowledge

that

> there's probably several other ways to do this too.
>
> I'm trying to avoid asking stupid questions and I'm doing a lot of
> background reading, but the majority of resources assume you already

have a

> semblance of a clue.
>
> Any thoughts appreciated. Sorry, I think I've rambled on a bit.

Hi Rik,

Don't worry at this stage about the select vs execute syntax. That is
not what is causing your problem. With a good naming convention, a
select from a stored procedure that does something is not ambiguous.
In fact, anything that audits your actions will work in this manner.

If you are going to use critical sections, then you might as well do
it from inside a single thread.

You should read Pavels response, because that is most likely what is
going on.

--- Time goes top to bottom.

Tra 1: look to see if 'bob' exists, nope nothing there.
Tra 1: insert into table 'bob' (not yet committed) -> returns 1
.
.
.
Tra 2: look to see if 'bob' exists, nope. (tra 1 not committed)
Tra 2: try to insert 'bob' -> returns 2
.
.
Tra 1: commits
Tra 2: commits

---

Now you have a table that looks like this

1 'Bob'
2 'Bob'

Tra 3: look to see if 'bob' exists, actually the query helps here:

SELECT ID FROM ARTISTS WHERE (Artists.Name = :NAME) INTO :FoundID;

Returns:

1
2

But because of your select into syntax, you can only return 1 value,
so you get your singleton error.

In any database where you get information from multiple sources, you
need to consider these scenarios. Select statements in Firebird are
all isolated to your transaction, so there is the potential for DML to
not see something that is there.

Constraints are transaction independent. If you create a unique
constraint on Artists.Name (if that is a valid business rule), then
Tra2 would have received an exception when it tried to run the insert
statement. Tra2 would still NOT see the ID of that artist, but at
least you wouldn't end up with two of them being inserted ever. Your
application or stored procedure would need to handle the exception, by
possible adding the item to a 'to do later' queue. Later on, when the
record is processed, it would see the value providing Tra1 commits.

There are a couple of ways to ensure you only get a single record
returned from a select into statement. If perhaps having two 'bob'
records in the table is not such a business concern, but the failure
of any query that tried to subsequently insert bob was a concern, then
you could change the query slightly.

SELECT FIRST 1 ID FROM ARTISTS WHERE (Artists.Name = :NAME) INTO :FoundID;

I imagine that is less effort than a Max test as only 1 record needs
to be considered.

Adam