Re: Foreign key order evaluation

sszabo@megazone.bigpanda.com (Stephan Szabo) writes:

> On Tue, 28 Sep 2004, Randy Yates wrote:
>
>> Michael Fuhr <mike@fuhr.org> writes:
>>
>> > On Tue, Sep 28, 2004 at 01:30:08PM +0000, Randy Yates wrote:
>> >> Randy Yates <yates@ieee.org> writes:
>> >> >
>> >> > I'm confused. Where is the lock? Is it on the 1 record in the model table?
>> >
>> > Yes.
>> >
>> >> > If so, why is that record locked? Is it possible in Postgresql to update
>> >> > the primary key of a record?
>> >
>> > When you insert a row that has a foreign key reference, PostgreSQL
>> > does a SELECT FOR UPDATE on the referenced row in the foreign table;
>> > the lock prevents other transactions from changing the referenced
>> > row before this transaction completes.  Unfortunately it also
>> > prevents other transactions from acquiring a lock on the same row,
>> > so those transactions will block until the transaction holding the
>> > lock completes.
>>
>> Well, yeah - sure it does. Given that the locking mechanism's
>> granularity is record-level, it MUST if it is to guarantee referential
>> integrity.
>
> But it doesn't need to prevent other transactions that want to just see if
> the row is there from continuing (as opposed to ones that want to actually
> modify that row). We just simply don't have that lock currently.

I see the light. You mean it would be nice to be able to have a "LOCK-FOR-UPDATE-ONLY"
lock as well as a "LOCK-FOR-UPDATE-OR-READ" lock, but all you have now is
"LOCK-FOR-UPDATE-OR-READ" and that gets applied even when you don't care if
someone else reads the record?
--
%  Randy Yates                  % "So now it's getting late,
%% Fuquay-Varina, NC            %    and those who hesitate
%%% 919-577-9882                %    got no one..."
%%%% <yates@ieee.org>           % 'Waterfall', *Face The Music*, ELO
http://home.earthlink.net/~yatescr