Hi,
I've come across a strange deadlock that I need your help with. There
are two copies of the same Perl daemon running on a 2 cpu box. The
program is pretty simple (because I wrote it :)) so I can trace its
pathway fairly well: in it, there's a single "LOCK table foo" occurring
part way through a transaction that sometimes ends up as this:
DETAIL: Process 91376 waits for AccessExclusiveLock on relation 16488
of database 16386; blocked by process 92387.
Process 92387 waits for AccessExclusiveLock on relation 16488 of
database 16386; blocked by process 91376.
After the exclusive lock, there is also exactly one SELECT, and then one
UPDATE query involving table foo, among others, doing their usual
implicit locking on it. I've read in the manuals that it's okay to stack
locks this way as long as the more restrictive locks precede less
restrictive ones. Mind you, there may be many requests per second, and
some of them can and will happen at the same wall clock time due to 2
cpus at work. Can locking break under these circumstances? I'd rather
opt for an educated solution to this, than having to check and restart
the query.
PostgreSQL 8.3.1
FreeBSD 7.0
p5-DBI-1.60.1
p5-DBD-Pg-1.49
Thanks for any tips.
