Re: nested transactions

pgman wrote:
> > AFAICS the problem lies in updating several pg_clog bits at once.  How
> > can this be done without holding a potentially long lasting lock?
> 
> Yes, locking is one possible solution, but no one likes that.  One hack
> lock idea would be to create a subtransaction-only lock, so if you see
> the special 4-th xact state (about to be committed as part of a
> subtransaction) you have to wait on that lock (held by the backend
> twiddling the xact bits), then look again.  That basically would
> serialize all the bit-twiddling for subtransactions.  I am sure I am
> going to get a "yuck" from the audience on that one, but I am not sure
> how long that bit twiddling could take.  Does xact twiddle every cause
> I/O?  I think it could, which would be a pretty big performance problem.
> It would serialize the subtransaction commits _and_ block anyone trying
> to get the status of those subtransactions.  We would not use the the
> 4th xid status during the transaction, only while we were twiddling the
> bits on commit.

Let me correct this.  Transaction state readers _don't_ have to block
while the subtransaction is twiddling bits.  Logic would be:
Set all aborted subtransaction status bitsGrab subxact lockSet subxact global status bit to in progressSet all
subtransactionxids to SUBXACT_TO_COMMITSet subxact global status bit to committed (commit happens here)Set all
SUBXACT_TO_COMMITxids to COMMITTEDRelease subxact lock
 

Any transaction looking up a subtransaction that has an
SUBXACT_TO_COMMIT state has to consult the global subxact status bit,
which is a global variable in shared memory.

What this basically does is to funnel all subxid lookups into a single
global subxid status bit.  In fact, even the outer transaction has to be
set to SUBXACT_TO_COMMIT so it commits at the same time as the
subtransactions.

On crash recovery, all SUBXACT_TO_COMMIT have to be cleaned up, somehow,
perhaps using WAL.
The only downside to this approach is that subtransaction bit twiddling
is serialized.

This is an interesting idea becuase it has overhead only to backends
using subtransactions.  It does kill our "multiple commits at the same
time" that we can do with normal transactions.

--  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