Wednesday, October 04, 2006

SQL Server pseudo nested transactions

SQL Server 2000 does not support nested transactions. So, if we have the following two stored procedures:

SP1:

create procedure sp1
as

begin tran
exec sp2

if (@@error <> 0)
goto on_error

commit tran

on_error:
rollback tran



SP1:
create procedure sp2
as

begin tran
insert table values ('sp2')

if (@@error <> 0)
goto on_error

commit tran

on_error:
rollback tran


The rules for nested begin transactions is:
Basically, I think of nested transactions as really just one main transaction that could contain multiple time points where the state of the transaction (committed, rollback) can be modified.

When using the ADO.NET SqlTransaction object you need to be aware of how transactions are committed and rolled back. When the SqlTransaction.BeginTransaction method is executed, it implicitly executes the BEGIN TRAN SQL statement. Any SQL statements executed will then execute on this main transaction. If the SQL statements that are executed perform a commit or rollback, the main transaction is affected based on the rules above. There may be certain cases where a servere SQL error will cause the main transaction to rollback, and if the main transaction is created by the SqlTransaction.BeginTransaction method, when the exception is caught, you will need to check if the transacion was actually already rolled back before executing the SqlTransaction.Rollback method:


try
{
try
{
SqlTransaction txn = new SqlTransaction();

txn.BeginTransaction();

Data.CallSproc1();
Data.CallSproc2(); // throws an exception and rolls back the transaction
txn.CommitTransaction();
}
catch(SqlException ex)
{
txn.RollbackTransaction(); // If the transaction is already rolled back, this will throw an exception
}
}
catch(SqlException ex)
{
// Transaction was already rolled back, do something
}
}

Comments: Post a Comment



<< Home

This page is powered by Blogger. Isn't yours?