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.

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

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.

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:

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.

Not a lower strength lock, I would, but I'm not so familiar with the postgres
internals. I modified ri-triggers.c to exclude certain tables from the
locking itself (because I know the tables are not updated).

-----Ursprüngliche Nachricht-----
Von: Stephan Szabo [mailto:sszabo@megazone23.bigpanda.com]
Gesendet: Montag, 26. August 2002 17:29
An: Mario Weilguni
Cc: pgsql-hackers@postgresql.org
Betreff: Re: [HACKERS] 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.

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

On Mon, 26 Aug 2002, Mario Weilguni wrote:

Not a lower strength lock, I would, but I'm not so familiar with the postgres
internals. I modified ri-triggers.c to exclude certain tables from the
locking itself (because I know the tables are not updated).

It might help the op of this thread, so it's probably worth
posting. :)