Re: deadlock

rihad wrote:
> 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.

If there are only two processes, and each is waiting for an ACCESS
EXCLUSIVE lock on the same relation and being blocked by the other one,
then presumably both have weaker locks that conflict with ACCESS
EXCLUSIVE on that relation.

Process 1 can't proceed with the ACCESS EXCLUSIVE lock because process 2
has a lesser lock on the table.

Process 2 can't proceed with the ACCESS EXCLUSIVE lock because process 1
has a lesser lock on the table.

Deadlock.

I don't see any other way the situation could arise, but I'm *very* far
from an expert.

Note that many statements take out fairly weak locks on a table.
See: http://www.postgresql.org/docs/8.3/static/explicit-locking.html
In particular, even a basic SELECT takes out an ACCESS SHARE, which
conflicts with ACCESS EXCLUSIVE.

If you are going to lock the table with ACCESS EXCLUSIVE you need to either:

- Take out the ACCESS EXCLUSIVE lock before doing anything else with the
table;
- Rewrite to avoid the need for the ACCESS EXCLUSIVE lock (say, by using
appropriate SELECT ... FOR UPDATE/SHARE row level locking); or
- Be prepared to retry transactions when deadlocks arise

I'd prefer to avoid the exclusive lock entirely if possible, and failing
that I'd want to take it out before doing anything else.

> After the exclusive lock, there is also exactly one SELECT

But what about BEFORE the LOCK statement? That's what matters.

> 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.

Yep, and since ACCESS EXCLUSIVE is the most restrictive lock you need to
take it out before doing ANYTHING else if you can't prove through other
means (say, knowledge about locking on other tables/records) that a
deadlock won't arise.

> 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 be VERY surprised.

--
Craig Ringer