Re: Problem with foreign keys and locking

On Wed, 31 Mar 2004, William Reese wrote:

> As you can see, what is blocking, is the ShareLock on
> the transaction.  After reading through the code, I
> realized that this is the intended behavior for
> updates and deletes to the same row.  In this case,
> it's the "select for update" query that's run by
> postgresql to prevent deletes on the value that the
> foreign key is referencing, that causes this ShareLock
> on the transaction.  The RowShareLock on the
> referenced row will prevent any other transaction from
> obtaining an ExclusiveLock (needed to delete or
> update), so there is not really a need to "serialize"
> these transactions in cases such as this.  The code
> notices that xmax for that tuple is set to a valid
> transaction id, so it creates a ShareLock on the xmax
> transaction id (our first transaction) to make the
> second transaction wait for the first to complete.
> Since our first transaction is not updating or
> deleting that row, xmax should not have been updated
> (the select for update is the culprit).  If "select
> for update" did not update xmax, but still aquired the
> RowShareLock, foreign keys would work properly in
> postgresql (the locks would prevent bad things from
> happening).  I don't know if this would break other
> functionality, but if so, then it seems it would not
> be much harder to come up with a way of aquiring the
> correct locks but not updating xmax.

I think you're confused about the locks. RowShareLock (which is a table
lock despite its name) does not IIRC conflict with RowExclusiveLock
(which should be what's asked for by update or delete on the table).