none
SqlTransaction.BeginTransaction() within TransactionScope bad?

    Question

  • Hi all, 

    I have some existing code that updates the database by creating a 
    SqlCommand and SqlTransaction: 

    public class GroupMapper 
        public static void Update(Group g) 
        { 
            // Gets SqlConnection, creates SqlCommand and calls 
    SqlConnection.BeginTransaction(). 
        } 


    That works fine what I'm only updating one object at a time.  But now 
    I need to update a bunch of objects (of different types) in a single 
    transaction, so I wrap the Update() calls in a TransactionScope 
    (within the TransactionScope, there might be calls to read the 
    database that doesn't require a transaction): 

    public void foo() 
        using (TransactionScope scope = new TransactionScope()) 
        { 
            GroupMapper.Update(group); 
            FooMapper.Update(foo); 
            .... 
            scope.Complete(); 
        } 


    But I read in this article (http://msdn2.microsoft.com/en-us/library/ 
    ms973865(d=printer).aspx) that: 

    "When using System.Transactions, applications should not directly 
    utilize transactional programming interfaces on resource managers--for 
    example the T-SQL BEGIN TRANSACTION or COMMIT TRANSACTION verbs, or 
    the MessageQueueTransaction() object in System.Messaging namespace, 
    when dealing with MSMQ. Those mechanisms would bypass the distributed 
    transaction management handled by System.Transactions, and combining 
    the use of System.Transactions with these resource manager "internal" 
    transactions will lead to inconsistent results. As a rule, use 
    System.Transactions in the general case, and use resource manager 
    internal transactions only in specific cases where you are certain the 
    transaction will not span multiple resources, and will not be composed 
    into a larger transaction. Never mix the two." 

    My transaction will not span multiple resources, so I should be ok? 

    The reason I'm asking this is that when running my unit tests, 
    occasionally, I see the following exception message: 

    "ExecuteReader requires the command to have a transaction when the 
    connection assigned to the command is in a pending local transaction. 
    The Transaction property of the command has not been initialized." 

    Which makes me wonder if I should delete all the code that creates 
    SqlTransaction and wrap each database update method in its own 
    TransactionScope. 

    Any ideas will be greatly appreciated! 

    Thanks, 
    Harold
    Thursday, November 22, 2007 7:07 PM

All replies

  • Hi,

     

    First of all unit tests should not invoke the database but use mocks to isolate the code under test.  The TransactionScope's purpose is to be used at the business layer level. (ie a fund transfer transaction from account A to B would be like this:

     

    void TransferFund(decimal amount, Account source, Account Destination)

    {

       using (TransactionScope scope = new TransactionScope()) {

         source.Funds -= amount;

         destination.Funds += amount;

         Account.Update(source);

         Account.Update(destination);

         scope.Complete();

       }

    }

     

    Here the Account.Update is a DAL method that is completely unaware of any pending transaction.

     

    Hope this helps,

    Charles

    Saturday, December 01, 2007 1:10 AM
  •  

    Hi cverdon,

     

    Thanks for the reply.

     

    I agree I should have used mocks.  But then, because I didn't, I actually see the ExecuteReader error that I will not able able to see had I use mocks.  My TransactionScope is created at the business layer, and the Update in the DAL layer creates it's own transaction using SqlTransaction.  Yes, the Update is not aware of any pending transaction, but that's when I get the ExecuteReader error.

     

    My Update() contains code that updates several tables, so it is wrapped within SqlTransaction.  I pretty much have the same code you have shown.  But I cannot figure out why I get the ExecuteReader error once in a while?

     

    Harold

    Saturday, December 01, 2007 12:04 PM
  • Ah, so you need a recursive transaction scope. In my last job we implemented that with our own implementation of the transaction scope that accessed a thread static object that had an int variable that increaments for each level of sub transaction.  It only did the real commit when the last level of transaction called the commit and failed if any level of the transaction failed. ie:

     

    using (RecursiveTransactionScope scope = new RecursiveTransactionScope())

    {

       //do some work

       //...

       //invoke other function

       myService.DoSomeAdditionalWork();

       scope.Complete();

    }

     

    public void DoSomeAdditionalWork()

    {

       using (RecursiveTransactionScope scope = new RecursiveTransactionScope())

               //A transaction is not created if there is already one started

               //If this function is called from somewhere else the transaction will be created.

       {

          //do some work

          //...

          scope.Complete(); //does not really commit, just decrements the level of the transaction scope

       }

    }

     

    Hope this helps,

    Charles

    Saturday, December 01, 2007 4:46 PM
  • Hi Charles,

     

    Thanks for the suggestion.  My understanding is that I can create nested TransactionScope() with System.Transactions.  I didn't do it because all the Update() methods I have are "legacy" code that uses SqlTransaction.  When I wrap the multiple Update() calls in a TransactionScope() and get the ExecuteReader error, I started wondering if creating SqlTransaction within a TransactionScope() is a bad idea.

     

    BTW, the ExecuteReader error comes from a Get() method that checks if the database contains the record, before I do the Update().  The Get() method doesn't create it's own SqlTransaction.  I even tried to modify that particular Get() method to use TransactionScope(TransactionScopeOption.Suppress), but I still get that ExecuteReader error once in a while. 

     

    Anyway, thanks again for the suggestions.

     

    Harold

     

    Saturday, December 01, 2007 11:05 PM
  • You need to check the record just to throw an exception/log? if so you can issue just the update and check the number of rows affected...

     

    Charles

    Saturday, December 01, 2007 11:28 PM