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?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|||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.|||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|||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;
}