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$@\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?
