System.Transactions.TransactionScope and IDbTransaction

I've enjoyed reading about the new System.Transactions namespace and the easy to use TransactionScope object.  But there was always something that I wasn't sure about, and from questions I've received its clear I wasn't the only one.  The question is basically what happens if you have a traditional ADO.NET IDbTransaction and you enclose it in a TransactionScope?  I finally got around to testing out this scenario and I'm pleased to report that everything works exactly as you would hope.  In other words, even though you may have an IDbTransaction that you have committed, it will still get rolled back if its enclosed in a TransactionScope that does not Complete.  This means that you can continue to use all your existing code and libraries that work with IDbTransactions -- and still use the new TransactionScope when you need something more.  It also means that I don't need to do anything special for my ORMapper -- I can continue to use IDbTransaction and my users can use the new TransactionScope also.  By the way, I knew Access didn't support distributed transactions, but I was pleasantly surprised that I got an exception when I tried out of curiosity.  Finally, for all my readers that think this behavior with IDbTransactions should be obvious, I agree but I've also learned to test things instead of assuming.

10 Comments

  • ... only on sqlserver 2005, as the db has to promote the transaction to a DTC controlled one, it won't work on Oracle, IMHO.

  • I tested this with Ms Sql 2000 -- so I know that its false to say you must have Ms Sql 2005. And my understanding, but untested, is that Oracle does support the DTC -- else how would it work with Enterprise Services. What I keep hearing, and its also in Julie's link, is that you will automatically get even a single transaction in the DTC unless you are using Ms Sql 2005. Thus you want to continue using IDbTransactions unless you really need more, but then it should work seamlessly -- or at least that was true on Ms Sql 2000 in my tests.

  • I thought to get the fully promotable transaction, you had to have a specific type of connection, which Sql2k5 as the first database to support this connection.

  • There's a lot of things we've all read and heard, and that's the problem. Some people seem to have heard that you need Ms Sql 2005, but my tests show that is simply not true since it worked with Ms Sql 2000. Of course my assumption that things will also work with Oracle, since I don't see it as any different than Ms Sql 2000, also needs to be tested since otherwise its no better advice than the other hearsay. The one thing that seems to be consistent in all the hearsay is that only Ms Sql 2005 is smart enough to not enlist in the DTC if its not necessary -- but that's not the same thing as saying you need Ms Sql 2005.

  • Paul,



    Please correct me if I am wrong on this, however, I think we are looking at two slightly different questions.

    #1 is your original question with regards to the transactionscope integration. I believe that you are talking about the ability to integrate.

    #2 is the ability to have promotable transactions. I believe that this is a inadvertent threadjacking.



    Wally

  • I'm not sure I see the distinction. If you mean that only Ms Sql 2005 doesn't automatically enlist the DTC then I agree, but if you mean that Ms Sql 2000 (and probably Oracle) won't get promoted to the DTC then I think I disagree.

  • Ok, I misread your posting. I thought the transaction wasn't initially in an enterprise services managed component (or what's its successor called in .NET 2.0). So if it's not in a COM+ managed component, the DTC won't know about it, and it will never get promoted. Though if it's already in a COM+ managed transaction, then yes, you don't need to do anything. But that's not really that new, in .NET 1.x this is also already the case.

  • No you read the initial post correctly. I have intentionally not introduced anything related to Enterprise Services or COM+ -- other than the of course using the new TransactionScope. All I did was create two regular IDbTransactions against a Ms Sql 2000 database and execute two commands, the first of which I committed, but the second one failed and rolled back (due to duplicate record in my particular case). The block of code without be wrapped in a TransactionScope ended up with the first command having been committed, but the second not -- as I expected. The exact same block of code wrapped in a TransactionScope had the result that both commands were rolled back. Nowhere did I do anything else that involved any setup on my part to prepare things for the DTC by using EnterpriseServices or COM+.

  • Sure, we are in agreement that using the TransactionScope is not the way to do a single transaction (unless maybe you know you are using SQL 2005). I don't think anyone has disagreed with that, although maybe that wasn't clear so thank you for clarifying that. My point was that you can take your existing code that uses the IDbTransaction and seamlessly enlist it into a TransactionScope when you need to do so.

  • We maybe in agreement, the point is it is a real shame... I would have really liked to replace my SqlTransaction code, with TransactionScope calls instead, it would have made my code so much more readable.



    But now I have a decision to make is readability or speed more important?

Comments have been disabled for this content.