Re: Foreign key order evaluation

Michael,

Thank you for your responses. Further questions below.

Michael Fuhr <mike(at)fuhr(dot)org> writes:

> On Tue, Sep 28, 2004 at 01:30:08PM +0000, Randy Yates wrote:
>> Randy Yates <yates(at)ieee(dot)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.

I don't see this as a problem with the database unless you want to
argue that the locking mechanism should have finer granularity. Given
the granularity, the problem must be solved in the application or
business rule logic, not the database.

>> Let me also ask why this is a problem. It may be a lock situation but
>> it isn't a DEADlock situation. I.e., the second transaction will just
>> have to wait until the first completes, and the first should complete
>> in milliseconds on a reasonable computer. Right?
>
> We don't know how long it will take for the first transaction to
> complete -- it might be part of a lengthy process, so performance
> might suffer. Also, there *is* the potential for deadlock. Take
> the table definitions in this message:
>
> http://archives.postgresql.org/pgsql-general/2004-09/msg00405.php
>
> You can create a deadlock situation that raises an error, as shown
> in this message:
>
> http://archives.postgresql.org/pgsql-general/2004-09/msg00442.php
>
> Here's what's happening:
>
> * Transaction 1 acquires a lock on foreign key 1.
> * Transaction 2 acquires a lock on foreign key 2.
> * Transaction 1 attempts to acquire a lock on foreign key 2, but that
> lock is already held by transaction 2 so transaction 1 blocks.
> * Transaction 2 attempts to acquire a lock on foreign key 1, but that
> lock is already held by transaction 1, so transaction 2 blocks.
>
> Transaction 1 is now waiting for a lock held by transaction 2, and
> transaction 2 is waiting for a lock held by transaction 1. Deadlock.
> PostgreSQL recognizes this and raises an exception in one of the
> transactions.
>
> The blocking and potential for deadlock can be avoided by deferring
> the foreign key constraints, but then foreign key violations won't
> be detected until the transaction attempts to commit.

This just defers the problem. Yeah, it may help in some situations, but
in either case the application level or business rule logic must decide
what to do.

In short, I don't see a problem with postgresql. The responsibility
is on the developer to handle such cases.
--
% Randy Yates % "Watching all the days go by...
%% Fuquay-Varina, NC % Who are you and who am I?"
%%% 919-577-9882 % 'Mission (A World Record)',
%%%% <yates(at)ieee(dot)org> % *A New World Record*, ELO
http://home.earthlink.net/~yatescr