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

>
> 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.

You don't need the initial select. Just insert and you will get an
exception if you violate the unique constraint.

After receiving the exception, you say you wait. You do not say
whether it is the same transaction though. If you are using a snapshot
transaction, you will never see that record. If you get an exception,
then commit/rollback as appropriate before your 30 second wait. When
you wake up, Start a new transaction.

>
> 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.

Don't use AutoCommit. You can't be confident of when it happens, and
for your model you need that confidence.

Out of curiousity, can you see that record that you are continuously
delaying for from another database browsing tool?

Adam