Обсуждение: Deadlock and FK triggers
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
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
[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.
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
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
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
> >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
> >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
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
Magnus Hagander wrote: >I know that part - I have my tickets already. I meant attendants, not >speakers. > > Ups sorry :-) /BL