Re: Question about RI checks

On Thu, Oct 23, 2014 at 2:41 PM, Florian Pflug <fgp(at)phlo(dot)org> wrote:
> The only reason we need the crosscheck snapshot to do that is because
> children may have been added (and the change committed) *after* the
> transaction which removed the parent has taken its snapshot, but *before*
> that transaction locks the parent row.
>
> My proposal is to instead extend the locking protocol to prevent that.
> Essentially, we have to raise a serialization error whenever
>
> 1) We attempt to exclusively lock a row (this includes updating or deleting
> it), and
>
> 2) somebody else did hold a share lock on that row recently, and
>
> 3) That somebody is invisible to us according to our snapshot.
>
> My initial attempt to do that failed, because we used to have very little
> means of storing the locking history - the only source of information was
> the xmax field, so any update of a tuple removed information about previous
> lock holders - even if that update was later aborted. I pondered using
> multi-xids for this, but at the time I deemed that too risky - back at the
> time, they had a few wraparound issues and the like which were OK for share
> locks, but not for what I intended.
>
> But now that we have KEY SHARE locks, the situation changes. We now rely on
> multi-xids to a much greater extent, and AFAIK multi-xid wraparound is now
> correctly dealt with. We also already ensure that the information contained
> in multi-xids are preserved across tuple upgrades (otherwise, updating a row
> on which someone holds a KEY SHARE lock would be broken).
>
> So all that is missing, I think, is
>
> 1) To make sure we only remove a multi-xid if none of the xids are invisible
> to any snapshot (i.e. lie before GlobalXmin or something like that).
>
> 2) When we acquire a lock (either explicitly or implicitly by doing an
> UPDATE or DELETE) check if all previous committed lock holders are
> visible according to our snapshot, and raise a serialization error
> if not.

I don't think you can count on being able to figure out all of the
recent lockers by looking at xmax; it can get overwritten. For
example, suppose transaction A locks the row and then commits. Then
transaction B comes along and again locks the row and commits. My
understanding is that B won't create a multi-xact since there's just
one locker; it'll just stomp on the previous xmax.

Now, you could change that, but I suspect it would have pretty drastic
implications on systems that have both foreign keys and long-running
transactions.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company