Re: Nested Transactions, Abort All

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.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
Essentially, you're proposing Kevlar shoes as a solution for the problem
that you want to walk around carrying a loaded gun aimed at your foot.
(Tom Lane)