tom@xxxxxxxxxxx wrote:
I found a problem with my application which only occurs under high loads
(isn't that always the case?).
snippets of perl...
insert into tokens (token)
select values.token
from (values TOKEN_LIST_STRING) as values(token)
left outer join tokens t using (token)
where t.token_idx is null
$sql =~ s/TOKEN_LIST_STRING/$string/
where $string is of the form (('one'),('two'))
This works 99% of the time.
But everyone once in a long while it seems that I hit simultaneaous
execute() statements that deadlock on the insertion.
Right now I know of no other way to handle this than to eval{ } the
execution and if it fails, sleep random milliseconds and retry... "wash
rinse repeat" for some number of times.
Is there any better way of doing this or some other means to manage
DEADLOCK?
Is this a deadlock that postgresql detects and causes one thread to roll
back and you can recover from, or are you talking about a deadlock that
isn't detected by postgresql and locks a thread?
What error messages are you seeing?
Generally speaking, if your operations have a potential for a deadlock,
the best you can do is to do what you're doing now, detect failure and
retry x times, then give up if it won't go through.
Or, redesign the way you're doing things.
