Hi all,

I've been having a lot of trouble with deadlocks in 7.2.1 because of
foreign keys. I dug out a couple of messages from the list archives
which cover this topic.

One particular message indicates a fix was being worked on.
--------
Date: Thu, 11 Apr 2002 09:03:06 -0700 (PDT)
From: Stephan Szabo

Please see past disussions on the fact that the lock grabbed is too
strong.  I'm going to (when I get time to work on it) try out a lower
strength lock that Alex Hayward made a patch for that should
limit/prevent these cases.  Thanks for sending a nice simple test case
to try against :)
----------

My question is if this is now resolved for 7.3? I'm trying to figure out
what I can do with my 7.2.1 problems... Has anyone got a back-ported
patch, perhaps against 7.2.2?

Any help appreciated, thanks!

Tom.
-- 
Thomas O'Dowd. - Nooping - http://nooper.com
tom@nooper.com - Testing - http://nooper.co.jp/labs


26 Aug 2002 11:27:12 +0900
Thomas O'Dowd <tom@nooper.com>
Re: Deadlock situation using foreign keys (reproduceable)

On 26 Aug 2002, Thomas O'Dowd wrote:

> Hi all,
>
> I've been having a lot of trouble with deadlocks in 7.2.1 because of
> foreign keys. I dug out a couple of messages from the list archives
> which cover this topic.
>
> One particular message indicates a fix was being worked on.
> --------
> Date: Thu, 11 Apr 2002 09:03:06 -0700 (PDT)
> From: Stephan Szabo
>
> Please see past disussions on the fact that the lock grabbed is too
> strong.  I'm going to (when I get time to work on it) try out a lower
> strength lock that Alex Hayward made a patch for that should
> limit/prevent these cases.  Thanks for sending a nice simple test case
> to try against :)
> ----------
>
> My question is if this is now resolved for 7.3? I'm trying to figure out
> what I can do with my 7.2.1 problems... Has anyone got a back-ported
> patch, perhaps against 7.2.2?

It's actually not resolved because my attempts at the lower strength lock
opened up holes and fixing those opened up new deadlock situations, and
I ran out of time in the 7.3 cycle to try to make it work.


Sun, 25 Aug 2002 20:46:35 -0700 (PDT)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: Deadlock situation using foreign keys (reproduceable)
Thanks for your feedback Stephan. Seems like a tough fix. Pitty it won't
make it into 7.3. I presume there are other folk out there suffering
from the same problems that I'm having. What approaches if any have
people taken to work around this problem? I read in the list that one
user patched his postmaster to explictly ignore the RI "FOR UPDATE" on
tables he knew weren't changing. Can't find the detailed message in the
archives right now, but I read it earlier. Any other work-arounds?

Tom. 

On Mon, 2002-08-26 at 12:46, Stephan Szabo wrote:
> > My question is if this is now resolved for 7.3? I'm trying to figure out
> > what I can do with my 7.2.1 problems... Has anyone got a back-ported
> > patch, perhaps against 7.2.2?
> 
> It's actually not resolved because my attempts at the lower strength lock
> opened up holes and fixing those opened up new deadlock situations, and
> I ran out of time in the 7.3 cycle to try to make it work.
-- 
Thomas O'Dowd. - Nooping - http://nooper.com
tom@nooper.com - Testing - http://nooper.co.jp/labs


26 Aug 2002 13:33:39 +0900
Thomas O'Dowd <tom@nooper.com>
Re: Deadlock situation using foreign keys (reproduceable)
On 26 Aug 2002, Thomas O'Dowd wrote:

> Thanks for your feedback Stephan. Seems like a tough fix. Pitty it won't
> make it into 7.3. I presume there are other folk out there suffering
> from the same problems that I'm having. What approaches if any have
> people taken to work around this problem? I read in the list that one
> user patched his postmaster to explictly ignore the RI "FOR UPDATE" on
> tables he knew weren't changing. Can't find the detailed message in the
> archives right now, but I read it earlier. Any other work-arounds?

For many schemas setting the constraints to deferrable makes the problem
go away in practice (due to the fact the constraint locks are held for
less time).  Removing for update really only does work if you know that
one side of the constraint never changes concurrently with the other side.



Sun, 25 Aug 2002 21:58:23 -0700 (PDT)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: Deadlock situation using foreign keys (reproduceable)
I wrote this patch for my system, and it works fine. However, it's a really ugly workaround. I can publish the source
if anybody is interested.

Am Montag, 26. August 2002 06:33 schrieb Thomas O'Dowd:
> Thanks for your feedback Stephan. Seems like a tough fix. Pitty it won't
> make it into 7.3. I presume there are other folk out there suffering
> from the same problems that I'm having. What approaches if any have
> people taken to work around this problem? I read in the list that one
> user patched his postmaster to explictly ignore the RI "FOR UPDATE" on
> tables he knew weren't changing. Can't find the detailed message in the
> archives right now, but I read it earlier. Any other work-arounds?
>
> Tom.
>
> On Mon, 2002-08-26 at 12:46, Stephan Szabo wrote:
> > > My question is if this is now resolved for 7.3? I'm trying to figure
> > > out what I can do with my 7.2.1 problems... Has anyone got a
> > > back-ported patch, perhaps against 7.2.2?
> >
> > It's actually not resolved because my attempts at the lower strength lock
> > opened up holes and fixing those opened up new deadlock situations, and
> > I ran out of time in the 7.3 cycle to try to make it work.


Mon, 26 Aug 2002 08:50:42 +0200
Mario Weilguni <mweilguni@sime.com>
Re: Deadlock situation using foreign keys (reproduceable)

On Mon, 26 Aug 2002, Mario Weilguni wrote:

> I wrote this patch for my system, and it works fine. However, it's a
> really ugly workaround. I can publish the source if anybody is
> interested.

Which, the for update one or a lower strength lock? In either case,
certainly.



Mon, 26 Aug 2002 08:29:09 -0700 (PDT)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: Deadlock situation using foreign keys (reproduceable)