Hi Adam,

Thanks for taking the time to reply to my overly long ramblings.

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

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. Since you're both raising your virtual eyebrows at me, I'm
going to remove that.

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

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

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

I'm using something called "EMS SQL Manager 2005 Lite for Iinterbase and
Firebird". Takes longer to say than it does to figure out how to
use. Really funky program.

I think you've pointed out where I'm going wrong, Adam. Above and beyond
the fact I'm doing erroneous inserts, not explicitly committing or rolling
back transactions and all the other things I'm doing wrong the real stomper
is that I'm using the wrong isolation level, so without backing out and
starting a new transaction the threads will never see the record committed
by the other thread.

Many thanks for taking the time to reply.
Cheers,
Rik