> Thanks. Pavel pointed this out too. I had thought a PK Select was

likely

> to be faster, so if the record was already in the table, I'd have saved
> some time.

Not really. You have a constraint declared on the field. That means
that if Firebird is doing its job properly, it must prevent you
inserting a duplicate value. The only way it can do that is if it
checks first. So even if you do a check first, Firebird isn't going to
trust you on your word, and because of the isolation of your
transaction, you can not totally trust the result of the select as a
proof it does not exist.

It doesn't hurt to select first, it just takes time and doesn't
achieve anything.

> >After receiving the exception, you say you wait. You do not say
> >whether it is the same transaction though.
>
> Mostly because I'm not sure. I think I might have to stop using

Zeos as my

> connection or spend a day reading up on it. I'm not explicitly

starting a

> transaction in SQL, I've been relying on its autocommit and the

transaction

> isolation level to handle all transactions for me. Likewise, I don't
> rollback at the moment. I've just been opening a query, reading the
> results then closing it again.

With Firebird, you need to understand transactions. There is no such
thing as a query without a transaction. A transaction is a unit of
work. Your queries must be executed within the context of one, and
every query you execute is treated as atomic. That is, when you
commit, they all immediately succeed, when you rollback, they all
immediately fail.

If each adding of an artist is a separate unit of work (which I
suspect to be the case), then you pretty much go

Start Transaction
try
Execute Procedure
...
Commit
except
Rollback
raise;
end

I generally prefer the approach to dump all the exceptions into a
queue to process later. This has the same affect as waiting for a
period of time but at least you get others done as well.

>
> >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.
>
> That made my head snap up. This is a the crux of the whole problem,

isn't

> it? I'm using the wrong isolation level. What I need to be using

is Read

> Committed.

This will certainly help, although it is still possible to end up with
a conflict.

>
> >Don't use AutoCommit. You can't be confident of when it happens, and
> >for your model you need that confidence.
>
> I think this is my lack of knowledge showing again. I tried that as

a last

> ditch attempt yesterday, but clearly did it wrong. I turned off
> Autocommit, added ";Commit;" to the end of all my inserts and the

SQL blew

> up. I planned to try this again today.
>
> >Out of curiousity, can you see that record that you are continuously
> >delaying for from another database browsing tool?
>
> Yes I can.

Clearly then the record is in the database and your isolation level is
why no ID was returned.

Adam