Hi Pavel,

Thanks for replying.

>I don't get the point here: why do you try to select firstly? I
>suppose that is redundant select, because you can get the same info
>when you select from the SP trying to insert a new name: you either
>get -1\0 (if it failed) or ArtistID\1 (if it succeeded).

It's actually on my todo list to look up. I wasn't sure if a SELECT was a
cheaper operation than an INSERT. Also, even though I've read that you
can't run out of generators, it just feels wasteful to increment it when
it's not needed. *8) Since both you and Adam are questioning my initial
select, I guess it's not quicker.

>It seems that your both problems have the same source. I don't know
>how Zeos works with transactions, but in your case it's better to
>process transactions explicitly, keeping them as short as possible.

I agree completely. That's what I've been trying to do.

>RB> "lock conflict on no wait transaction violation of PRIMARY or UNIQUE KEY
>RB> constraint "UQ_ARTIST_NAME" on table "ARTISTS". Error Code: -901.
>RB> Unsuccessful execution caused by system error that does not preclude
>RB> successful execution of subsequent statements".
>
>Are you sure that -803 is the right code above? Maybe it should be
>-901? (I don't know for sure, but it seems that you get in your
>application the exception which you want to catch in the SP)

I originally had "WHEN ANY DO", but when that wasnt working, I removed the
error handler completely and logged the exceptions. It was either -803
(Can't add duplicate rows) or that -901 error shown above. I'm fairly
convinced they're related errors, but when your core knowledge is the size
of an egg cup, you tread extra carefully.

>RB> The second problem is worse. As soon as the SP returns -1 indicating that
>RB> the record is already there, but not yet transactionally visible, no
>matter
>RB> how long I sleep the threads for, the follow on SELECT _never_ finds the
>RB> record, so the thread loops, goes back to sleep for a random period of
>RB> time, before trying to get the record again.... forever.
>
>Here, I don't get your point again: if you know that the name is in
>the table already, why do you try to insert the same name one more
>time (knowing that there is a unique constraint for that)? Maybe
>another thread deletes names from the table time to time...

I don't. I try and select. If the initial insert fails then I know it's
in the table uncommitted. I need the ID, so I sleep for a random period to
allow the other thread to commit and then perform a Select.

>Does your business-logic require commit after each insert? Or, commit
>after trying to insert a batch of records?

In this case, each insert must commit, otherwise it's entirely possible to
deadlock the threads.

Thread1 Thread2
Select ID for Bob (not found) Select ID for Cheese (not found)
Insert Bob (no commit) Insert Cheese (no commit)
Select ID for Cheese (not found) Select ID for Bob (not found)
Insert Cheese (constraint error) Insert Bob (constraint error)
While ID not found While ID not found
Sleep Sleep
Select ID for Cheese Select ID for Bob
end end

Cheers,
Rik