From: Robert Haas <robertmhaas@gmail.com>
To: david@lang.hm
Cc: wangyuxiang <wyx6fox@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: foreign key constraint lock behavour in postgresql
Date: Thu, 4 Feb 2010 21:11:17 -0500
Message-ID: <603c8f071002041811o642c04e9j570cfdc879862622@mail.gmail.com> (raw)
In-Reply-To: <alpine.DEB.2.00.1002032134530.7512@asgard.lang.hm>
References: <4B6A478D.5060805@gmail.com>
<alpine.DEB.2.00.1002032134530.7512@asgard.lang.hm>
On Thu, Feb 4, 2010 at 12:40 AM, <david@lang.hm> wrote:
> I could be wrong in this (if so I know I'll be corrected :-)
>
> but Postgres doesn't need to lock anything for what you are describing.
>
> instead there will be multiple versions of the 'b1' row, one version will be
> deleted, one version that will be kept around until the first transaction
> ends, after which a vaccum pass will remove the data.
Just for kicks I tried this out and the behavior is as the OP
describes: after a little poking around, it sees that the INSERT grabs
a share-lock on the referenced row so that a concurrent update can't
modify the referenced column.
It's not really clear how to get around this. If it were possible to
lock individual columns within a tuple, then the particular update
above could be allowed since only the name is being changed. Does
anyone know what happens in Oracle if the update targets the id column
rather than the name column?
Another possibility is that instead of locking the row, you could
recheck that the foreign key constraint still holds at commit time.
But that seems like it could potentially be quite expensive.
...Robert
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 using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: robertmhaas@gmail.com, david@lang.hm, wyx6fox@gmail.com
Subject: Re: foreign key constraint lock behavour in postgresql
In-Reply-To: <603c8f071002041811o642c04e9j570cfdc879862622@mail.gmail.com>
* 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