Re: Nested Transactions, Abort All

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.

Let me jump in on this.

The initial proposal from Alvaro was to do SUBBEGIN/SUBCOMMIT. This has
the advantage of allowing BEGIN/COMMIT to commit the entire transaction,
and it is a keyword we can use in plpgsql that doesn't confuse
BEGIN/END.

The disadvantages are:

o adds prefix to keyword (SUB) which we don't do other places
o doesn't work well with other xact synonyms like BEGIN/END or
START TRANSACTION/COMMIT TRANSACTION.

Alvaro wants BEGIN/COMMIT to remain spec-compliant and commit the entire
transaction. One idea was to do BEGIN NESTED/COMMIT NESTED, but does
that allow plpgsql to use it? If not, it seems pretty useless. Imagine:

BEGIN
NESTED = 3;

or something like that.

As far as savepoints, yes, we should support them. Josh is saying our
implementation isn't 100% spec compliant. In what way does it differ?

As far as implementing only savepoints, look at this:

BEGIN;
BEGIN;
INSERT INTO ...;
COMMIT;
BEGIN;
INSERT INTO ...;
COMMIT;
BEGIN;
INSERT INTO ...;
COMMIT;

With savepoints, it looks pretty strange:

BEGIN;
SAVEPOINT x1;
INSERT INTO ...;
SAVEPOINT x2;
INSERT INTO ...;
SAVEPOINT x3;
INSERT INTO ...;

or with RELEASE:

BEGIN;
SAVEPOINT x1;
INSERT INTO ...;
RELEASE SAVEPOINT x1;
SAVEPOINT x1;
INSERT INTO ...;
RELEASE SAVEPOINT x1;
SAVEPOINT x1;
INSERT INTO ...;
RELEASE SAVEPOINT x1;

Yea, I guess it works. With nested transactions, the SQL mimics the
nested structure of many application languages, while savepoints look
like an add-on to SQL.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073