Re: DeadLocks...

On 6/14/2007, "Scott Marlowe" <smarlowe(at)g2switchworks(dot)com> wrote:

>
>tom(at)tacocat(dot)net 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.

It's "deadlock detected" and rolling back.
I could prevent this if I only had one INSERT process running for all
that I'm doing, but that kind of negates the idea of having multiple
processes.