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

Alvaro Herrera wrote:

>On Fri, Jul 02, 2004 at 01:37:46PM -0500, Thomas Swan wrote:
>  
>
>>Alvaro Herrera wrote:
>>    
>>
>
>  
>
>>>>Then again, since a statement is always transactionally wrapped, would
>>>>it be required to always issue SUBBEGIN if issued from within a
>>>>function?  This would address my concern.
>>>>        
>>>>
>>Isn't this counterintuitive.   It seems that BEGIN and COMMIT/ABORT 
>>should be sufficient regardless of the level.  If you are inside a 
>>current transaction those commands start a new transaction inside of the 
>>current transaction level, just like pushing on and popping off elements 
>>on a stack.  
>>    
>>
>
>No, the first level is quite different from any other, and that's why it
>should use a different syntax.  Really any level above level 1 is not a
>transaction at all; it's a unit that you can rollback independently but
>nothing more; you can't commit it independently.  I think a better term
>than "subtransaction" or "nested transaction" is "rollback unit" or some
>such.
>
>  
>
>>Some functions and procedures may not be called inside of transactions  
>>or subtransactions.
>>    
>>
>
>No.  Some functions cannot be called inside a transaction block.
>Whether you are or not inside a subtransaction within the transaction
>block is not important.  In fact, the application doesn't care what
>nesting level it is in; the only thing that it cares about is if it is
>in a transaction block or not.
>
>Please note that I'm using the term "transaction block" and not
>"transaction."  The distinction is important because everything is
>always inside a transaction, though it may be an implicit one.  A
>transaction block, on the other hand, is always an explicit thing.
>And a subtransaction is also an explicit thing.
>  
>
This is the reason the outermost block is irrelevant to the point.  
Inner transactions (including the implicit ones mentioned) commit only 
if their parent transactions commit.   If there is an implicit 
begin/commit, then everything underneath should be subbegin/subcommit.   
If it is sometimes implicit then the subbegin/begin state is 
non-deterministic.   Without the underlying or stack depth, it is 
difficult to predict.   In psql, autocommit (on/off) behavoir becomes a 
little muddy if you go with the SUBBEGIN and SUBCOMMIT construct. 

Below should BEGIN (1) be a SUBBEGIN or a BEGIN?  Both examples would 
give equivalent results.

--
BEGIN (implicit) BEGIN (1)   BEGIN     SOMETHING     BEGIN       SOMETHING     ROLLBACK   ROLLBACK COMMIT (1)
COMMIT (implicit)
--
BEGIN (1) BEGIN    SOMETHING    BEGIN       SOMETHING    ROLLBACK ROLLBACK
COMMIT (1)
--



>  
>
>>Unless you have some way to tell (by query) the state you are in is a 
>>subtransaction and how many levels you are deep into the nested 
>>transaction, deciding whether to use SUBBEGIN and SUBCOMMIT/SUBABORT vs 
>>the traditional BEGIN COMMIT/ABORT becomes nondeterministic.
>>    
>>
>
>The application always has to keep track if it is inside a transaction
>block or not.  This has always been true and it continues to be so.
>Whether you are inside a subtransaction or not is not really important.
>If you want to commit the whole transaction block just issue COMMIT, and
>all levels will be committed.  
>
psql will tell me how deep I am in transactions?

>Similarly if you want to abort.  But if
>you want to retry a subtransaction which has just failed you better know
>whether you are on a subtransaction or not ... I mean if the app
>doesn't know that then it isn't using subtransactions, is it?
>
>  
>
That's an rather big assumption?  It may not be the app, it may include 
stored procedures and functions as well.  Imagine a little function 
called dance( ).   Dance begins a transaction, does a little work, and 
then aborts.  If I am not in a transaction and I write dance as a 
subtransaction then I have the problem and call it then I have a 
problem.   If I am in a transaction and I write dance as a transaction, 
then I have a problem.   There's no universal way to write the function, 
without having to refer to an external state unless I make the scope 
universal.   Hence, SUBBEGIN and SUBCOMMIT are bad ideas.

>Knowing just the nesting level will not help you -- the app has to know
>_what_ to retry.  And if it isn't going to retry anything then there's
>no point in using subtransactions at all.
>  
>
If you have the nesting level, then you know how many commits/rollbacks 
to perform to get to an entrance state.