Re: nested transactions - Mailing list pgsql-hackers

Manfred Koizar wrote:
> [Sorry for the delay.  I'm a bit busy these days.]
> 
> On Fri, 29 Nov 2002 15:57:17 -0500 (EST), Bruce Momjian
> <pgman@candle.pha.pa.us> wrote:
> >> BTW, I think this *forces* us to replace the sub xid with the
> >> respective main xid in a tuple header, when we set
> >> XMIN/MAX_IS_COMMITTED.  Otherwise we'd have to look for the main xid,
> >> whenever a tuple is touched.
> >
> >Sorry, I don't follow this.
> 
> Probably because we've mixed up several proposals.  I'll try to pick
> them apart below.

OK.

> These cases are completely different.  If a (main or sub-) transaction
> is rolled back, its effects are invisible to all transactions; this
> status is immediately effective and final.  OTOH a subtransaction
> commit is only tentative.  It becomes effective when the main
> transaction commits.  (And the subtransaction's status turns to
> aborted, when the main transaction aborts.)

Right.

> >I see no
> >need to lookup the parent, and in fact we could clear the clog parent
> >xid offset so there is no way to access the parent anymore.
> 
> While a subtransaction is seen as "tentatively committed" other
> transactions have to look up its parent to find out its effective
> status.

Right.  And we need those lookups to parent from the start of the
subtransaction until the commit/abort of the main transaction.  If it
aborts, we can shorten that, but if they are all commit, we have to
wait, and they have to be visible because other backends have to know if
the "Running" status of the transaction is still associated with an
active transaction, and we can only stamp one xid on a backend because
shared memory is limited.

> Proposal A was:  Never show "tentatively committed" to outside
> transactions.  This would require neither any new flags in tuple
> headers or in pg_clog nor a globally visible pg_subtrans structure.
> But it only works, if we can commit a main transaction and all its
> subtransactions atomically, which is only possible if we hold a long
> lasting lock.  Did we agree that we don't want this?

Again, we still need the lookup to main transaction for other backend
lookups, so this idea is dead, and we don't want locking.

> All other solutions require a parent xid lookup at least during the
> time span while a subtransaction is marked "tentatively committed" and
> not yet known to be "finally committed".  IIRC we have three proposals
> how the "tentatively committed" status can be shown to outside
> transactions:

Yes.

> (B) Two flags in the tuple header (one for xmin, one for xmax) telling
> us "the xid is a subtransaction".  I don't like this very much,
> because it's not in Normal Form: "is a subtransaction" is NOT a
> property of a tuple.  OTOH we can declare it a denormalization for
> performance reasons (we don't have to look up the parend xid, if the
> flag is not set.)

I see no reason to do that when we have that 4th state available in
pg_clog.  They are going to lookup the xid status anyway, so why not
check that "is subtransaction" status at that point too.  Of course, we
can't mark "IS COMMITTED" on the tuple until the main transaction
commits, but that is simple logic.


> (C) Explicitly use the fourth possible status in pg_clog for
> "tentatively committed".  (Performance hack:  replace with "finally
> committed" as soon as the xid is visible to all active transactions.)

Yes, I think this is the only way to go.  If we need that 4th state
later, we can refactor the code, but for our purposes now, it is useful.

> (D) Only one kind of "committed" in pg_clog; always look for a parent
> in pg_subtrans; for performance reasons integrate pg_subtrans into
> pc_clog.

Seems that 4th state makes this an easy optimization, causing zero
overhead for backends that _don't_ use subtransactions, except for
backends looking up the status of other backends with subtransactions.

> Tom brought up the snapshot visibility problem which applies to B, C,
> and D.
> 
> While each of these proposals can be implemented (relatively) straight
> forward, the Black Art is:  When and how can we modify the stored
> state to avoid repeated parent xid lookups?  We'll find out ...

I think there is now general agreement that we want a separate table to
store parent xids for subtransactions that is only looked up when that
4th clog state is set, and once the main transaction commits, all those
4th state clog entries can be cleaned up to simple commit.  We can also
expire the pg_subtrans table for any xids less than the lowest running
backend xid, which is pretty significant optimization.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073