Re: postgresql93-9.3.5: deadlock when updating parent table expected?

Hi,

If you feel FOR UPDATE is taking much time, then I believe,we can solve
this kind of issues using advisory locks
<http://www.postgresql.org/docs/9.1/static/explicit-locking.html>, .

Regards,
Dinesh
manojadinesh.blogspot.com

On Wed, Feb 18, 2015 at 10:45 AM, Bill Moran <wmoran(at)potentialtech(dot)com>
wrote:

> On Wed, 18 Feb 2015 18:30:09 +0000
> Dmitry O Litvintsev <litvinse(at)fnal(dot)gov> wrote:
> >
> > Yes, deadlock occurs when there are multiple processes insert
> > concurrently into file table with the same volume id field.
> > I used "sometimes" as opposed to "all the time".
>
> I resonded in that way since I frequently hear people complaining,
> "we're seeing all kinds of deadlocks" as if the whole world is
> collapsing under the deadlocks, but when pressed for more information
> it turns out they're only seeing a few deadlocks per hour when the
> system is under the heaviest load -- that scenario is hardly
> unexpected. As a result, having more detailed information than
> just "sometimes" helps to understand what's really going on.
>
> > I think you advise to retry transaction or add select for update prior
> > to insert. I will pursue this (together with upgrade to 9.3.6 suggested
> by
> > Alvaro).
>
> The nice thing about a retry strategy is that it always works.
> The problem with a retry strategy is that it's easy to do wrong
> (i.e. it may be more than just the transaction that needs to
> restart ... depending on what data has changed, calculations may
> need to be redone, the user requeried for certain information,
> etc).
>
> The problem with the SELECT ... FOR UPDATE is that it's a bit
> slower, and can be significantly slower unders some circumstances,
> but it's easier to implement correctly.
>
> The good news form Alvaro is that this is probably happening more
> frequently than necessary because of the bug he mentioned ... so
> upgrading may cause the problem to happen infrequently enough that
> you don't really care about it. The solutions I suggest are still
> relevent, they just might not be as immediately important.
>
> --
> Bill Moran
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>