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.
>
Indeed, there is one SELECT and, conditionally, one UPDATE before the
exclusive LOCK, on the table. I've re-read the manual, particularly this
line:
"One should also ensure that the first lock acquired on an object in a
transaction is the highest mode that will be needed for that object."

Since SELECT & UPDATE come before LOCK on bw_pool, the bug is obvious.
Sadly I can't use any other locking as I need exclusive access to OS's
firewall after getting bw_id. Well, I thought I'd move LOCK further away
inside the transaction to better mimic fine-grained locking. So one
solution is to move it back to the beginning.

Thank you!