Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2004
    Posts
    3

    Unanswered: Nested transactions

    I am writing a program using VC++ 6.0 and SQL 2000 and I am trying to use nested transactions. I have 1 outer transaction and the 2 inner transactions are in sepetrate function calls inside the outer transaction. I have something like this:

    BEGIN TRANSACTION;

    if (!functioncall1()) // commit if function suceeds, otherwise rollback
    {
    Rollback Transaction;
    return;
    }

    if (!functioncall2()) // commit if function suceeds, otherwise rollback
    {
    Rollback Transaction;
    return;
    }

    COMMIT TRANSACTION ;

    Both functions contain a complete transaction inside the function call. If either function fails however, I want to do a rollback of the entire transaction. This is not happening though. If functioncall1 suceeds and the transaction in that function commits, then if I do a rollback during functioncall2, the transaction in functioncall1 is not rolled back. This seems to be directly opposite of the SQL help for transaction. Am I missing something obvious here?

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This gets rather complicated to explain, but I'll give it a shot.

    SQL transactions don't nest in the strict (relational algebra) sense of the word. When you "nest" SQL transactions, they form something more like a procedure call stack where the COMMIT behaves like a return and the ROLLBACK behaves something like throwing an execption. The first rollback to come along basically wipes you back to step 1, before the first BEGIN TRANSACTION occured.

    There were reasons for this behavior, once upon a time. There is still a reasonable logical arguments for maintaining the behavior, even though it goes so badly against the mental model used by third generation programming tools (like VB, VC, C#, etc).

    The simplest solution I see to your problem is to avoid nesting, and use a try/throw/catch model to allow the application side logic to match the database side.

    -PatP

  3. #3
    Join Date
    Mar 2004
    Posts
    3
    I have tried using the nested transactions and I can get them to work in the SQL query analyzer, but when I try to do the same thing in the application, the rollback will undo the work done in the inner most transaction, but not to the outer transaction.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Ah... If that is the case, please post the Transact-SQL for what you want to do. It will be much easier to help you translate the Transact-SQL to C than to guess how it is different from what you've posted. What you posted won't behave the way you want it to, because SQL transactions don't work that way.

    -PatP

  5. #5
    Join Date
    Mar 2004
    Posts
    3
    When I call function 1, the function completes and the transaction is completed. Function 2 doesn't finish and a rollback occurs inside the function, but the changes that were done in function 1 never get undone.

    The SQL that I can get to work in the query analyzer is:

    BEGIN Transaction Test1
    Begin Transaction Test2
    Insert into TABLE2
    Commit Transaction Test2

    BEGIN Transaction Test3
    Insert into TABLE3
    Commit Transaction
    Rollback Transaction

    Here is the C++ code that I can't get to work:

    void main ( )
    {
    ExecuteSQL(_T("BEGIN TRANSACTION "));
    if (!Function1( ) ) //If this function fails, then rollback
    {
    ExecuteSQL(_T("ROLLBACK TRANSACTION "));
    return 0;
    }

    ExecuteSQL(_T("BEGIN TRANSACTION "));
    if (!Function2( ) ) //If this function fails, then rollback
    {
    ExecuteSQL(_T("ROLLBACK TRANSACTION "));
    return 0;
    }

    // commit transaction and return success
    ExecuteSQL(_T("COMMIT TRANSACTION "));
    return 1;

    ////////////////////////////////////////////////////////////////////////////////////
    int Function1( )
    {

    sqlStatement.Format(_T("INSERT INTO table1( VARIABLE LIST) FROM TABLE1");

    try
    {
    ExecuteSQL(_T("BEGIN TRANSACTION "));
    nRowCount = ExecuteSQL(sqlStatement);
    }
    catch (CException *e)
    {
    ExecuteSQL(_T("ROLLBACK TRANSACTION "));
    return 0;
    }

    if (nRowCount != 1)
    {
    ExecuteSQL(_T("ROLLBACK TRANSACTION "));
    return 0;
    }

    sqlStatement.Format(_T("DELETE FROM TABLE2 ");

    try
    {
    nRowCount = ExecuteSQL(sqlStatement);
    }
    catch (CException *e)
    {
    ExecuteSQL(_T("ROLLBACK TRANSACTION "));
    e->Delete();
    return 0;
    }


    // commit transaction and return success
    ExecuteSQL(_T("COMMIT TRANSACTION "));
    return 1;
    }

    ////////////////////////////////////////////////////////////////////////////////////
    int Function2( )
    {
    // Clear any previous errors
    ResetError();

    // Start a transaction
    try
    {
    ExecuteSQL(_T("BEGIN TRANSACTION "));
    }
    catch (CException *e)
    {
    return 0;
    }
    CString sqlStatement;
    sqlStatement.Format( _T("DELETE FROM TABLE2 ");

    try
    {
    ExecuteSQL(sqlStatement);
    }
    catch (CException *e)
    {
    e->Delete();
    ExecuteSQL(_T("ROLLBACK TRANSACTION"));
    return 0;
    }
    }
    sqlStatement.Format( _T( "INSERT INTO TABLE3( )"),
    TRY
    {
    ExecuteSQL(sqlStatement);
    }
    CATCH( CException *e)
    {
    ExecuteSQL(_T("ROLLBACK TRANSACTION"));
    return 0;
    }
    END_CATCH

    // commit the changes
    TRY
    {
    ExecuteSQL( _T("COMMIT TRANSACTION"));
    }
    CATCH( CException, e)
    {
    return 0;
    }
    END_CATCH

    return 1;
    }
    Last edited by MOlive; 03-15-04 at 10:48.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •