Re: Nested Transactions, Abort All - Mailing list pgsql-hackers

Alvaro,
> My proposal would be:>> 1. Begin main transaction: BEGIN { TRANSACTION | WORK }> 2. Commit main (all) transaction:
COMMIT{ TRANSACTION | WORK }> 3. Rollback main (all) transaction: ROLLBACK { TRANSACTION }>> 4. Begin inner
transaction:BEGIN NESTED { TRANSACTION | WORK }> 5. Commit inner transaction: COMMIT NESTED { TRANSACTION | WORK }> 6.
Rollbackinner transaction: ROLLBACK NESTED { TRANSACTION }>
 

I agree with your 1,2 and 3, for the reasons you specify.

I don't like your proposal for 5, because using the keyword COMMIT 
implies something that really isn't true IMHO.  This is due to the fact 
as you point out subtransactions aren't really transactions.  So when 
you 'commit' a subtransaction you are not making the changes permanent 
like a regular transaction.  Instead you are saying these changes are OK 
and the real transaction gets to decide if these changes should be 
committed (or not).  It is only the real transaction that ever does a 
COMMIT (i.e. makes the changes permanent for others to see).  IMHO it is 
for these reasons that the standard SAVEPOINT syntax doesn't have a 
concept of committing a savepoint, only of rolling back to a savepoint.

thanks,
--Barry


Alvaro Herrera wrote:
> On Thu, Jul 08, 2004 at 10:40:36AM -0700, Josh Berkus wrote:
> 
> 
>>This means that we CANNOT maintain compatibility with other databases without 
>>supporting SAVEPOINT syntax, which we are not yet ready to do.   As a result, 
>>I would propose the following syntax:
>>
>>Begin main transaction:   BEGIN { TRANSACTION | WORK }
>>Begin inner transaction:  BEGIN { TRANSACTION | WORK }
>>Commit inner transaction:  COMMIT { TRANSACTION | WORK }
>>Commit all transactions:  COMMIT ALL
>>Rollback inner transaction:  ROLLBACK { TRANSACTION }
>>Rollback all transanctions:  ROLLBACK ALL
> 
> 
> We can _not_ do this.  The reason is that COMMIT and ROLLBACK are
> defined per spec to end the transaction.  So they have to end the
> transaction.
> 
> Keep in mind that a nested transaction _is not_ a transaction.  You
> cannot commit it; it doesn't behave atomically w.r.t. other concurrent
> transactions.  It is not a transaction in the SQL meaning of a
> transaction.
> 
> So, when I say "it has to end the transaction" it cannot just end the
> current nested transaction.  It has to end the _real_ transaction.
> 
> 
> My proposal would be:
> 
> 1. Begin main transaction: BEGIN { TRANSACTION | WORK }
> 2. Commit main (all) transaction: COMMIT { TRANSACTION | WORK }
> 3. Rollback main (all) transaction: ROLLBACK { TRANSACTION }
> 
> 4. Begin inner transaction: BEGIN NESTED { TRANSACTION | WORK }
> 5. Commit inner transaction: COMMIT NESTED { TRANSACTION | WORK }
> 6. Rollback inner transaction: ROLLBACK NESTED { TRANSACTION }
> 
> 
> 1, 2 and 3 are not negotiable.  4, 5 and 6 are.
>