ConnectionManager, TransactionScope and Transactions

NemisisJedi posted on Monday, October 21, 2013

I have some code that i need to run within a transaction. The code is within a DataPortal_Execute function with a transactionType set to Manual.

e.g
<Transactional(TransactionalTypes.Manual)> _ 

Within this DataPortal_Execute method i have some code that i want to run, that is not within the transaction and some that is.  I thought i could do this by wrapping the code within a TransactionScope using statement like this.

<Transactional(TransactionalTypes.Manual)> _ 
Protected Overrides Sub DataPortal_Execute()

   Using lTransactionScope As New TransactionScope(TransactionScopeOption.Suppress)

      ' Do some code here, which may call other DataPortal_Execute methods

   End Using 

   Using lTransactionScope As New TransactionScope(TransactionScopeOption.RequiresNew)

      ' This code should be included within a transaction

   End Using
End Sub 

Now the reason i have the Suppress using statement, is because another object can call this dataportal method, which has its own transactionScope, so i need this code to not be included within it. Something like this.

' PARENT

<Transactional(TransactionalTypes.Manual)> _ 
Protected Overrides Sub DataPortal_Execute()
   Using lTransactionScope As New TransactionScope(TransactionScopeOption.RequiresNew)

      ' Call child methods here

   End Using
End Sub 

' CHILD

<Transactional(TransactionalTypes.Manual)> _ 
Protected Overrides Sub DataPortal_Execute()

   Using lTransactionScope As New TransactionScope(TransactionScopeOption.Suppress)

      ' This code should not be within the PARENT transaction

   End Using 

   Using lTransactionScope As New TransactionScope(TransactionScopeOption.RequiresNew)

      ' This code should be included within the PARENT transaction

   End Using
End Sub 

For some reason though, my code within the transactionScope suppress, is still included within the transaction and eventually my code freezes because all the tables are locked.  Within the Suppress using statement, i have code that gets data from tables in Sql.

sergeyb replied on Monday, October 21, 2013

If you want to have child code run withing parent transaction, you should not have RequiresNew there, you should not have any code at all there that deals with transactions.  The fact that you query some data within a transaction, but using suppress, may still result in an issue because you are querying the data that has not been committed.  If you want this, you must use the same connection, which I am not sure you are using.

In general you can certainly roll out your own transactions with CSLA, although there is usually no reason to do so.