BEGIN/COMMIT with software crash

[crooksey]

Hi all,
If multiple statements are surrounded by BEGIN and COMMIT transaction commands - are all transactions only committed once the outer COMMIT command is reached?
There are nested SQL transactions in my process which also have BEGIN and COMMIT transaction surrounds, I believe that these transactions are been committed even if the outer (final) commit is not reached.
This presents a problem when there is a very occasional crash in the software and essentially what happens is that only the first half of the whole transaction has been committed (when none of it should if there's a crash - there's a rollback in the outer command).

Is it best to name the BEGIN/COMMIT transactions so that SQL is of no doubt which commit statement has been reached or will this not make a difference in this case?

To outline the problem, the process is a stock movement and follows this structure:

BEGIN
BEGIN
MOVE STOCK OUT OF LOCATION
CREATE AUDIT OF MOVEMENT OUT
COMMIT

BEGIN
MOVE STOCK INTO NEW LOCATION
CREATE AUDIT OF MOVEMENT IN
COMMIT
COMMIT

If the vb.net software crashes part way through outer transaction, the whole lot should be rolled back (it is surrounded with a try/catch with a rollback) BUT, it seems that sometimes the first nested transactions is left as committed thus bypassing the rollback on the whole thing.

Any thoughts would be appreciated, I'm supporting somebody else's code here and short of rewriting big chunks of it to make it work how I would have done it I'm at a loss as to how this is happening!

Many thanks

 

[jmcilhinney]

Committing an inner transaction only changes the database for the purposes of the outer transaction. If the outer transaction is not committed then noone else will ever see any changes made within the outer transaction.

 

[crooksey]

Thanks for the clarification, it MUST be the clumsyness of the VB code then. Rewrite time :nevreness:

 

[cjard]

Possibly more like clumsiness of the vb.net coder

The concept of scope is present everwhere in programming:

Dim s as String
If test Then
  Dim t as String
End If

If othertest Then
  Dim u as String
EndIf

S is available in both IFs, T only in the first and U only in the second..

Similarly, transactions have scopes.. Denoted by a BEGIN that starts one and COMMIT or ROLLBACK that ends it. Everything that happened inside the active current scope is committed at that time

BEGIN
  BEGIN
    BEGIN
      UPDATE 1
      UPDATE 2
    COMMIT
    UPDATE 3
    BEGIN
      UPDATE 4
    ROLLBACK
  COMMIT
  UPDATE 5
ROLLBACK

Updates 1 2 and 3 are comitted, 4 and 5 rolled back

 

[crooksey]

Possibly more like clumsiness of the vb.net coder

The concept of scope is present everwhere in programming:

Dim s as String
If test Then
  Dim t as String
End If

If othertest Then
  Dim u as String
EndIf

S is available in both IFs, T only in the first and U only in the second..

Similarly, transactions have scopes.. Denoted by a BEGIN that starts one and COMMIT or ROLLBACK that ends it. Everything that happened inside the active current scope is committed at that time

BEGIN
  BEGIN
    BEGIN
      UPDATE 1
      UPDATE 2
    COMMIT

* CRASH HERE

    UPDATE 3
    BEGIN
      UPDATE 4
    ROLLBACK
  COMMIT
  UPDATE 5
ROLLBACK

Updates 1 2 and 3 are comitted, 4 and 5 rolled back

Yeah I suspected this was happening, so in your opinion if the crash happened where indicated above - 1 & 2 would already be committed and not rolled back by any such statements in a try/catch containing the rollback, even if ex is thrown from the inner commands?

I can see why it's been done the way it has, the stock movements are 2 seperate functions within the core of the software which both include a number of sql transactions surrounded by Begin/Commit with rollbacks in place. These are called from within an outer begin/commit with a try/catch

So:

     TRY
          BEGIN
               CallStockOut()
               CallStockIn()
          COMMIT
     CATCH
          ROLLBACK 
     END TRY


CallStockOut()
     TRY
          BEGIN
               UPDATE 1
               UPDATE 2
          COMMIT
     CATCH
          THROW
          ROLLBACK
     END TRY

CallStockIn()
     TRY
                    BEGIN
               UPDATE 3
               UPDATE 4
          COMMIT
     CATCH
          THROW
          ROLLBACK
     END TRY

 

[cjard]

Essentially the same as:

          BEGIN
               UPDATE 1
               UPDATE 2
          COMMIT
          BEGIN
               UPDATE 3
               UPDATE 4
          COMMIT

Really, if you want the whole thing to succeed or fail, CallStockXX methods shouldnt do anything with transactions