Re: How can I prevent duplicate inserts by multiple concurrent threads ?

Sean Davis wrote:
>
> Any suggestions, tips ? Am I at least thinking along the right lines ?
> Should I be getting a table lock instead ? What is the recommended way
> to prevent duplicate inserts in a concurrent situation like this ?
>
>
> Is there a reason that you cannot use a UNIQUE constraint?
>

Right, maybe that's what I should be doing. The reason I didn't do that
initially was because creating new data can be *very* expensive. We have
about a 100 different chunks and if say 10 threads hit the database and
each thread re-creates 100 chunks, we will end up trying to save 100*10
(1000) chunks, 900 of which will be rightly rejected because of the
uniqueness constraint, so that time spent in creating those chunks would
have been wasted to the annoyance of the clients.

It would be nice to see if that data already exists before we try to
re-insert it....but how ? the "select for update" doesn't seem to be
working as I hoped..

Best,
--j