Is END TRANSACTION is necessary to mention in BEGIN TRAN... END TRAN... routine? 2

[Rajesh Karunakaran]

Dear Team!

I see there are many discussions on BEGIN TRAN.... END TRAN...
However, I have a straight query which may sound weird.

Is an "END TRANSACTION" necessary in a BEGIN TRANSACTION...END TRANSACTION routine?
When we use ROLLBACK to reverse the changes, is there a need to mention END TRANSACTION at the end?
To be more clearer, please see the code below:

BEGIN TRANSACTION
   lgood = .T.
   if lGood
      select emp
      lGood = TABLEUPDATE(.T.)
   endif
   if lGood
      select salary
      lGood = TABLEUPDATE(.T.)
   endif
   if lGood
      select allowances
      lGood = TABLEUPDATE(.T.)
   endif

   if NOT lGood
      ROLLBACK
   endif
   
END TRANSACTION

Is the code above correct? Or should/can we write the end portion as below?

if NOT lGood
   ROLLBACK
else
   END TRANSACTION
endif

Thanks in advance
Rajesh

 

[Mike Lewis]

Rajesh, your second code block will work fine. If you execute a ROLLBACK, it is not necessary to actually execute the END TRANSACTION.

In fact, it's not essential to even include END TRANSACTION in your code. The ROLLBACK will be enough. But I would always include it anyway, if only to avoid complications with nested transactions.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Mike Lewis]

Just to clarify my previous answer, the following code should work fine:

SELECT Contacts
BEGIN TRANSACTION
  REPLACE ALL Notes WITH "Hello world"
ROLLBACK

The effect of the above code will be zero. Nothing will be updated. But it does demonstrate the point that a ROLBACK is sufficient to terminate a transaction.

Mike





__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Actually the simplest code demo is this:

Begin Transaction
RollBack
End Transaction && Error: END TRANSACTION command cannot be issued without a corresponding BEGIN TRANSACTION command.

This will error on the end transaction, as the END TRANSACTION needs an open transaction. The only chance it doesn't error is when you have nested transactions, that means at an earlier time another BEGIN TRANSACTION was done without it being either committed or rolled back.

So it's an error to have transactions the way you proposed, you end a VFP transaction either by ROLLBACK or by END TRANSACTION. That says, on one hand, ROLLBACK is ending a transaction by keeping involved tables as they were before BEGIN TRANSACTION, while on the other hand END TRANSACTION compares to COMMIT in other languages and ends the transaction by committing the changes.

Also, BEGIN/END TRANSACTION is not about code nesting like IF...ENDIF, DO...ENDDO etc. or TRY...ENDTRY, though it's close to the meaning of the latter. It isn't defining a code block, it's rather like SQLCONNNECT vs SQLDISCONNECT, which might also even be in separate methods of a class. Likewise, you could start a transaction anytime and end it anytime later anywhere else in another method. In comparison with an TRY..ENDTRY block: Rollback is NOT the catch, you decide when a rollback happens, this is not caused by an exception. Both END TRANSACTION and ROLLBACK compare to the ENDDTRY, also END TRANSACTION does not compare to the FINALLY section of a TRY..ENDTRY block. This overall isn't about code nesting but nesting of changes of the data.

Typically you'd just have this in the same method, because it's recommended to use buffering for keeping your changes in memory and finally save within a transaction you only start for saving the buffered changes, so the transaction only covers the saving process and you therefore start and end it in a save routine.

So END TRANSACTION after ROLLBACK either errors or causes the commit of the then current higher level transaction in case of nested transactions.

Look into TXNLEVEL() and monitor how that changes when you execute the different transaction related commands.

And a last edit - simply also look into what the Wiki teaches:

http://fox.wikis.com/wc.dll?Wiki~TransactionsAndBuffering

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Rajesh Karunakaran]

Mike / Olaf,

Immediately after I posted it, when I tried my first code example, I got that error!
In fact, I have been using these routines. But, never had code with END TRANSACTION after a ROLLBACK.
So, was just wondering!

Thank you both for your time!

Rajesh