none
Several Transactions for one connection or how to handle nested transaction scopes RRS feed

  • Question

  • Hello everybody,

    Following problem:

    I have an insert with identity within an Transaction scope:

    private TDTO InsertWithIdentity(TDTO dtObject) 
            { 
                using (TransactionScope scope = new TransactionScope(TransactionScopeOption.RequiresNew)) 
                { 
                    try 
                    { 
                        insert identity.... 
                        SubmitChanges(); causes InvalidOperationException
                        insert details with the created identity... 
                        scope.Complete(); 
                        return result; 
                    } 
                    catch (Exception ex) 
                    { 
                        //return false; 
                        logger.LogError(ex); 
                        throw new Exception(ex.Message); 
                         
                    } 
                } 
     
            } 

    Within another method with transaction scope i am calling insert:

    public override void CopyToCANBus(List<CANModulSWDTO> copyList, int oldCANBus) 
            { 
                using (TransactionScope scope = new TransactionScope(TransactionScopeOption.RequiresNew)) 
                { 
                    try 
                    { 
                        calling a function which again calls insert... 
                             
                        } 
                        scope.Complete(); 
                    } 
                    catch (Exception ex) 
                    { 
                        //throw new Exception(ex.Message); 
                        //ThrowHelper.ThrowBackupException("Finalizing occurs an error. The transcation will be rollback."); 
                        //return false; 
                        logger.LogError(ex); 
                    } 
                } 
            } 

    With the two nested transaction scopes i get a System.InvalidOperationException: Für die Verbindung ist gegenwärtig eine Transaktion eingetragen. Beenden Sie die aktuelle Transaktion, und versuchen Sie es erneut. (sorry guess one of my testing databse engines is still in german.)

    The exception basically means: For the connection there is currently a transaction enlisted. Please terminate the transaction and try again.

    Obviously this is caused by bed desiign althoguh i need the two transaction scopes cause i could call the two methods autonomously.

    Big thx for any advices!
    Wednesday, February 11, 2009 5:00 AM

Answers

  • Not sure if this is the correct answer but SQL only permits a single transaction per connection - you cannot nest them.

    I think that the transaction scope is trying to create two nested SQL transactions - hence it is failing.

    Howard
    • Marked as answer by wuz Wednesday, February 11, 2009 1:14 PM
    Wednesday, February 11, 2009 12:50 PM
    Answerer

All replies

  • Not sure if this is the correct answer but SQL only permits a single transaction per connection - you cannot nest them.

    I think that the transaction scope is trying to create two nested SQL transactions - hence it is failing.

    Howard
    • Marked as answer by wuz Wednesday, February 11, 2009 1:14 PM
    Wednesday, February 11, 2009 12:50 PM
    Answerer
  • Thx, completely right. So I changed

     using (TransactionScope scope = new TransactionScope(TransactionScopeOption.RequiresNew))             {

    to

    using (TransactionScope scope = new TransactionScope())             {

    More Information can be found here: http://msdn.microsoft.com/en-us/library/ms973865.aspx (Introducing System.Transactions in the .NET Framework 2.0)


    Wednesday, February 11, 2009 1:16 PM
  • No problem.. had to code my way around something similar last year!
    Howard
    Wednesday, February 11, 2009 2:14 PM
    Answerer