Re: Foreign key order evaluation

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.

> 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. For some
applications this might be a problem, especially if one wants to
take advantage of 8.0.0's savepoints (e.g., an application might
want to know if a foreign key constraint has been violated so it
can roll back only the offending statement).

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/