Re: DeadLocks..., DeadLocks...

Tom Allison wrote:
>
> Gregory Stark wrote:
>>
>> I'm still not precisely clear what's going on, it might help if you
>> posted the
>> actual schema and the deadlock message which lists the precise locks that
>> deadlocked.
>>
>> Are any of the DML you mention on other tables on those tables with
>> foreign
>> key references to this one?
>>
>> It's impossible for two inserts on the same table to deadlock against
>> each
>> other so there must be more going on than what you've described. It's
>> hard to
>> help much without a complete picture.
>>
>
> I think I found the problem. And it's not at all where I thought it was.
> Process 17583 waits for ShareLock on transaction 306841;
> blocked by process 17725.
> Process 17725 waits for ShareLock on transaction 306840;
> blocked by process 17583.
>
> Where I'm at a lost is the deadlocks reported are on different tables.
> However, getting back to the Foreign Key question
> history_token does have a foreign key constraint on tokens.token_idx on
> delete cascade.
>
> So is the INSERT statement on history_token getting deadlocked by the
> token UPDATE statement? Looks that way and the only think I can see
> causing that might be a foreign key issue.
>
> Am I correctly identifying the problem?
> Any options?
>
>

HISTORY_TOKEN:
{
eval{$dbh->do($sql)};
if ($@) {
if ($@ =~ /deadlock detected/) {
warn "$$: deadlock detected on HISTORY_TOKEN\n";
usleep 150_000;
warn "$$: retrying HISTORY_TOKEN\n";
redo HISTORY_TOKEN;
}
croak "$sql\n$dbh->errstr\n$(at)\n";
}
};

This seems to help a lot.
At least it's getting done.

Now, is there a shorter usleep time I can use safely or should I just leave well
enough alone?