Re: deadlock

From: rihad <rihad(at)mail(dot)ru>
To: craig(at)postnewspapers(dot)com(dot)au
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: deadlock
Date: 2008-04-03 18:00:56
Message-ID: 47F51B58.2040207@mail.ru
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Browse pgsql-general by date

  From Date Subject
Next Message Craig Ringer 2008-04-03 18:10:54 Re: choosing the right locking mode
Previous Message Scott Marlowe 2008-04-03 17:59:10 Re: choosing the right locking mode