From: Tom Lane <tgl@sss.pgh.pa.us>
To: schen@graciousstyle.com
Cc: pgsql-general@postgresql.org
Subject: Re: [ADMIN] how to find transaction associated with a lock
Date: Thu, 22 Jul 2004 18:37:21 -0400
Message-ID: <13323.1090535841@sss.pgh.pa.us> (raw)
In-Reply-To: <4100353D.9080202@graciousstyle.com>
References: <41001296.3090609@graciousstyle.com>
<12341.1090529103@sss.pgh.pa.us>
<4100353D.9080202@graciousstyle.com>
Si Chen <schen@graciousstyle.com> writes:
> Do you think I'm looking down the wrong path?
I'd bet that the problem is contention for a row referenced by a foreign
key. When you insert a row that has a foreign key reference to another
table, we need to lock the referenced row to ensure it doesn't get
deleted until the inserting transaction commits. (Once you commit, your
inserted row is visible and it's then the responsibility of a deleting
transaction to notice the foreign-key violation. But until then, a
deleting transaction can't even *see* your row so we need another way.)
The real problem here is that we only have exclusive locks at the row
level, so the only way to do this is to take an exclusive lock, and that
means that transactions inserting references to the same referenced row
block each other. So I think your INSERT is waiting on commit of some
other transaction's INSERT that references the same foreign key row.
Fixing this is on the TODO list but I don't expect any solution in the
near future :-(
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: tgl@sss.pgh.pa.us, schen@graciousstyle.com, pgsql-general@postgresql.org
Subject: Re: [ADMIN] how to find transaction associated with a lock
In-Reply-To: <13323.1090535841@sss.pgh.pa.us>
References: <41001296.3090609@graciousstyle.com> <12341.1090529103@sss.pgh.pa.us> <4100353D.9080202@graciousstyle.com> <13323.1090535841@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox