Re: [HACKERS] Nested Transactions, Abort All

[Thomas Swan]

Alvaro Herrera wrote:
On Fri, Jul 09, 2004 at 10:38:15AM -0500, Thomas Swan wrote:
 

visibility issue and how far do you unwind the depth of subtransactions 
or transactions?

BEGIN
UPDATE A
SAVEPOINT X
BEGIN
  BEGIN
    UPDATE B
    BEGIN
      UPDATE C
      ROLLBACK TO SAVEPOINT X
   

What happens here is that the user will go nuts.  We will have a
prominent entry in the docs: "using both nested transactions and
savepoints inside a transaction can cause confusion.  We recommend you
stick to one or the other."  Or something like that.
(What would really happen: when ROLLBACK TO SAVEPOINT X is executed,
nested transactions created after the SAVEPOINT will be closed.)
So this is another reason why we should use COMMIT to close a nested
transaction: it may refer to a transaction that is already closed
because the user got confused.
 

Technically, a ROLLBACK TO SAVE POINT X would be an ABORT on all nested 
transactions.  COMMIT means that if the parent transaction commits then 
the child transaction will also commit.

BEGIN
 BEGIN
   UPDATE A
 ROLLBACK
 UPDATE B
COMMIT
The changes from UPDATE A will not commit with the changes from UPDATE B.
BEGIN
 BEGIN
   UPDATE A
 COMMIT
 UPDATE B
COMMIT
The changes from UPDATE A will commit with the changes from UPDATE B.
BEGIN
 BEGIN
   UPDATE A
 COMMIT
 UPDATE B
ROLLBACK
The changes from UPDATE A will not commit with the changes from UPDATE 
B, and the changes from UPDATE B will not commit either.

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
              http://archives.postgresql.org