Re: Nested Transactions, Abort All

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. Rollback inner 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.
>