On Tue, Sep 28, 2004 at 01:30:08PM +0000, Randy Yates wrote: > Randy Yates <[EMAIL PROTECTED]> 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/ ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
