Re: Nested transaction workaround?

On Wed, Jan 14, 2004 at 02:45:38PM +0700, Chris Travers wrote:
> I was thinking about the nested transaction problem, and I came across an
> interesting insight. While it is true you could use dblink to perform db
> operations outside the transaction (which could be useful for logging, etc.)
> what is lacking is a way to roll back the internal transactions when the
> parent rolls back.

Well, you could not commit the dblink'ed transaction until the parent
commits. The problem is that the dblink'ed statement cannot see the parent
transaction's uncommitted changes.

> It also occured to me that the main problem with nested transactions is that
> the hard part is this inherited rollback/commit, esp. with MVCC which places
> constraints on how one could look at managing these commit/rollbacks without
> paying huge performance costs even where, as in the majority of cases, this
> feature is not used. I am assuming that part of the problem is how the
> visibility/transaction information is handled via MVCC. Is my understanding
> correct?

Well, actually, the problem appears to be that people want to be able to
roll back each individual statement without killing the parent transaction,
and they want to make this the default behaviour. This takes it out of the
"never used" category to "everybody does it" category.

So you have to make something that works even if you use it all the time.

> My final, albeit half-baked, conclusion is that one of the things that would
> make nested transactions MUCH easier would be a two-phase commit (2PC)
> framework which would be stored on the transaction level. Something like a
> transaction status storage which contains the following information: Status
> (in progress, committed, rolled back, pending commit as in 2PC), and
> "depends on xid" where you can then have the pending commit become
> 'committed' when transaction xid is commited. Again this is just off the
> top of my head.

I think something like that has been suggested, check the archives. There is
a bit of discussion on how to actually store that info in a way that can be
checked efficiently because remember, visibility needs to be checked for
every tuple on every sequential scan in every process that runs subsequently,
so it needs to be *fast*.

> Also an 2PC framework if added into the protocol would allow for true nested
> transactions via DBLink.

Dt doesn't solve the visibility problem though.

Hope this helps,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> (... have gone from d-i being barely usable even by its developers
> anywhere, to being about 20% done. Sweet. And the last 80% usually takes
> 20% of the time, too, right?) -- Anthony Towns, debian-devel-announce