Re: foreign key locks, 2nd attempt

On Mon, Mar 12, 2012 at 01:28:11PM -0400, Robert Haas wrote:
> I spent some time thinking about this over the weekend, and I have an
> observation, and an idea. Here's the observation: I believe that
> locking a tuple whose xmin is uncommitted is always a noop, because if
> it's ever possible for a transaction to wait for an XID that is part
> of its own transaction (exact same XID, or sub-XIDs of the same top
> XID), then a transaction could deadlock against itself. I believe
> that this is not possible: if a transaction were to wait for an XID
> assigned to that same backend, then the lock manager would observe
> that an ExclusiveLock on the xid is already held, so the request for a
> ShareLock would be granted immediately. I also don't believe there's
> any situation in which the existence of an uncommitted tuple fails to
> block another backend, but a lock on that same uncommitted tuple would
> have caused another backend to block. If any of that sounds wrong,
> you can stop reading here (but please tell me why it sounds wrong).

When we lock an update-in-progress row, we walk the t_ctid chain and lock all
descendant tuples. They may all have uncommitted xmins. This is essential to
ensure that the final outcome of the updating transaction does not affect
whether the locking transaction has its KEY SHARE lock. Similarly, when we
update a previously-locked tuple, we copy any locks (always KEY SHARE locks)
to the new version. That new tuple is both uncommitted and has locks, and we
cannot easily sacrifice either property. Do you see a way to extend your
scheme to cover these needs?