Thread: Deadlock and FK triggers

Deadlock and FK triggers

From
Bo Lorentsen
Date:
HI ...

I have been using PG for a large project at my work (soon in
production), and I use PG and a lot for FK/RI checks. This works nice
when using one or two threads (cuncurrent connections), but more than
this, and I start getting deadlocks in my FK triggers.

After this, I have been reading all the mail threads I can find in the
PG mailing lists, on this subject and I found some some minor fixes. One
have made a patch for PG, but the patch also remove some of the
garanties. The other is the DEFERRABLE fix, that fix or postpone some of
this. But no real solutions, as far as I can see.

Now that we have version 8.0, and this have been a know problem for some
time, I like to know what the status of this is ? Will there be another
way for a trigger to lock the reffered table other that "FOR UPDATE",
that is more like a RW lock, or will we get a way to control the
sequence in which FK triggers are executed ? As a last solution, all FK
triggers could be executed in a alnumeric sequence, based on the tabel name.
So ... what is the plan ?

When designing a somewhat complex DB this become a relativly large
problem, and this don't sound nice to my boss if I have to tell his
these thing :-(

Regards

/BL

Re: Deadlock and FK triggers

From
Bruce Momjian
Date:
Bo Lorentsen wrote:
> HI ...
>
> I have been using PG for a large project at my work (soon in
> production), and I use PG and a lot for FK/RI checks. This works nice
> when using one or two threads (cuncurrent connections), but more than
> this, and I start getting deadlocks in my FK triggers.
>
> After this, I have been reading all the mail threads I can find in the
> PG mailing lists, on this subject and I found some some minor fixes. One
> have made a patch for PG, but the patch also remove some of the
> garanties. The other is the DEFERRABLE fix, that fix or postpone some of
> this. But no real solutions, as far as I can see.
>
> Now that we have version 8.0, and this have been a know problem for some
> time, I like to know what the status of this is ? Will there be another
> way for a trigger to lock the reffered table other that "FOR UPDATE",
> that is more like a RW lock, or will we get a way to control the
> sequence in which FK triggers are executed ? As a last solution, all FK
> triggers could be executed in a alnumeric sequence, based on the tabel name.
> So ... what is the plan ?
>
> When designing a somewhat complex DB this become a relativly large
> problem, and this don't sound nice to my boss if I have to tell his
> these thing :-(

We hope to have shared row locks in 8.1 for this usage.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: Deadlock and FK triggers

From
Csaba Nagy
Date:
[snip]
> We hope to have shared row locks in 8.1 for this usage.

This feature is one I'm really waiting for ! Is there any estimate when
8.1 comes out ?
I know, I know, the preferred answer is "whenever it's ready", but if
8.1 is not that far away, and shared row locking will be in it, I won't
bother patching 8.0 to skip FK parent row locking altogether (which is
not the most orthodox way to avoid the FK related deadlocks)...

Thanks to all the developers,
Csaba.



Re: Deadlock and FK triggers

From
Bo Lorentsen
Date:
Bruce Momjian wrote:

>We hope to have shared row locks in 8.1 for this usage.
>
>
Thanks, and good news, and just what I hoped for. Are there some kind af
priority orderet todo list somewhere ?

Looking forward to, see You in copenhagen af LinuxForum 2005.

/BL

Re: Deadlock and FK triggers

From
Bruce Momjian
Date:
Bo Lorentsen wrote:
> Bruce Momjian wrote:
>
> >We hope to have shared row locks in 8.1 for this usage.
> >
> >
> Thanks, and good news, and just what I hoped for. Are there some kind af
> priority orderet todo list somewhere ?

Sorry, no priority because we are all volunteers, though Alvaro has
talked about doing it for 8.1 so there is some movement on that item.

> Looking forward to, see You in copenhagen af LinuxForum 2005.

Great.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: Deadlock and FK triggers

From
Bruce Momjian
Date:
Csaba Nagy wrote:
> [snip]
> > We hope to have shared row locks in 8.1 for this usage.
>
> This feature is one I'm really waiting for ! Is there any estimate when
> 8.1 comes out ?
> I know, I know, the preferred answer is "whenever it's ready", but if
> 8.1 is not that far away, and shared row locking will be in it, I won't
> bother patching 8.0 to skip FK parent row locking altogether (which is
> not the most orthodox way to avoid the FK related deadlocks)...

I would guess 8.1 is perhaps a year away.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: Deadlock and FK triggers

From
"Magnus Hagander"
Date:
> >We hope to have shared row locks in 8.1 for this usage.
> >
> >
> Thanks, and good news, and just what I hoped for. Are there
> some kind af priority orderet todo list somewhere ?
>
> Looking forward to, see You in copenhagen af LinuxForum 2005.

Speaking of which, are there any other interesting (or non-interesting)
people from the community that are gonig to be there? I know I am...

//Magnus

Re: Deadlock and FK triggers

From
"Magnus Hagander"
Date:
> >Speaking of which, are there any other interesting (or
> non-interesting)
> >people from the community that are gonig to be there? I know I am...
> >
> >
> Please take a look for yourself : http://www.linuxforum.dk/2005/en

I know that part - I have my tickets already. I meant attendants, not
speakers.

//Magnus

Re: Deadlock and FK triggers

From
Bo Lorentsen
Date:
Magnus Hagander wrote:

>Speaking of which, are there any other interesting (or non-interesting)
>people from the community that are gonig to be there? I know I am...
>
>
Please take a look for yourself : http://www.linuxforum.dk/2005/en

/BL

Re: Deadlock and FK triggers

From
Bo Lorentsen
Date:
Magnus Hagander wrote:

>I know that part - I have my tickets already. I meant attendants, not
>speakers.
>
>
Ups sorry :-)

/BL