none
Using transactions without MSDTC RRS feed

  • Question

  • We have a number of legacy VB6 apps that manage to intereact with a remote database without the need to use the MSDTC (I'll come back to this in a second.) Now we're moving along with a .NET application, and it appears that in order to use Pessimistic Concurrency, I'll need to use some sort of transaction. Just getting a DataContext, doing some DB operations and calling SubmitChanges uses Optimistic Concurrency and this is not an option.

    So I'm exploring the different .NET LINQ TO SQL Concurrency options for transactions. Wrapping everything in a using block for a new TransactionScope will be difficult to impliment because of the asyncronous aspects of the application that are already in place. And from everything I've read about using the CommittableTransaction class, it appears I'll need to go through the MSDTC. Is there someway to use Commit and Rollback transactions in .NET without having to go through the MSDTC? The old legacy VB6 applications didn't use this.

    I've tried using the CommittableTransaction class and going through the MSDTC, but it's all too complicated. Like if I want to connect to the remote DB with my development enviroment over a VPN, then I can't because my machine isn't in the same domain, or something like that.

    What is the EASIEST way to impliment some form of Pessimistic Concurrency with LINQ TO SQL?

    Thanks,


    Bill McCormick -- MSDN Subscriber
    Wednesday, April 28, 2010 8:08 PM

Answers

All replies

  • I'm glad I happened upon this post.  I would love to know the answer to this too.

    Come forth ye SQL experts and avail us of your wisdom.

     

     

    Wednesday, April 28, 2010 8:52 PM
  • Hi Bill,

     

    From this MSDN document, LINQ to SQL supports three distinct transaction models.   Can you use the first model – Explicit Local Transaction by setting the DataContext.Transaction property to a single SqlTransaction? 

     

    The single SqlTransaction seems to use LTM instead of MSDTC until the second SqlConnection connected to a different database, http://dotnetslackers.com/SQL/re-3463_SqlTransaction_vs_System_Transactions.aspx.  

     

    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Thursday, April 29, 2010 5:46 AM
    Moderator
  • Thanks Lin. I don't see how I can use the Explicit Local Transaction as it is described. Even before I call SubmitChanges, I need to have at least one table locked and a number of records in other tables locked. So, if I understand correctly, I need to make most of the DB calls prior to my SubmitChanges, and subsequently the SubmitChanges itself, within the scope of a transaction.

    It sort of sounds like my best course of action will be to re-code portions of my application so I can wrap (or bracket) all of my DB actions inside a TransactionScope.

    I'd like another alternative, so chime in if you have one.

    Thanks,


    Bill McCormick -- MSDN Subscriber
    Thursday, April 29, 2010 2:31 PM
  • >>Even before I call SubmitChanges, I need to have at least one table locked and a number of records in other tables locked.

    Bill I don't understand how this is going to solve your pessimistic locking problem.  Please clarify what you mean by pessimistic. 

    Do you want to do something like the following?:

    Lock some rows in some tables

    Read some data from the locked rows

    Present the data to the user for edting

    Write the edited data back to disk

    Unlock the rows

     

    As far as I know SQL server does not support the idea of pessimistic locking in the sense described above.  I'm no expert. If I'm wrong someone please correct me.

    As for SubmitChanges, you need to create one and only one instance of your datacontext, make ALL of your db calls against said context, then call SubmitChanges.  If, within a transaction, you make a call against a second instance of your context you are going to get the dreaded MSDTC error.

     

     

     

    Thursday, April 29, 2010 6:01 PM
  • Yes, that's what I mean by pessimistic locking. Except I'm not sure if SQL Server can do row level locking or if int can only do table level locking. All multiuser databases must support some form of locking, yes? Otherwise thay wouldn't be of much use to multiple users that happen to want to edit the same data at the same time. So maybe you mean to say that SQL Server locks the whole table, and cannot do row level locking? OK.

    I have this book from Apress titled Pro LINQ, "Rattz". In chapter 17 he talks about Concurrency Conflicts and there's one section called Pessimistic Concurrency. It says on pg. 568, "With the pessimistic concurrency approach [wrapping the read and update in a TransactionScope], there are no actual conflicts to resolve [compared to the Optimistic Concurrency approach,] because the database is locked by your transaction, so no one else can be modifying it behind your back."  That's what I want, sort of. I don't neseccarily want to lock the whoile database; and I'd rather not lock complete tables - if there's some way to manage this.

    Locking issues asside, my design issue right now is how to work with the TansactionScope in a multithreaded enviroment. The framework won't allow you to call SubmitChanges on a DataContaxt from a different thread from the one that the DataContext was created on, right? I'm not a very experienced programmer when it comes to designing mulitthreaded apps, so my head hurts trying to figure out a clean solution.

    Thanks,


    Bill McCormick -- MSDN Subscriber
    Thursday, April 29, 2010 7:49 PM
  • >>>"With the pessimistic concurrency approach [wrapping the read and update in a TransactionScope], there are no actual conflicts to resolve [compared to the Optimistic Concurrency approach,] because the database is locked by your transaction, so no one else can be modifying it behind your back."

     

    Well that is an interesting approach. I am interested to know if anyone has successfully  implemented it and what the tradeoffs  are.  Bill, if you will forgive me for asking questions in your thread, I'd like to know if such an approach can be used in a web application.

     

    Thursday, April 29, 2010 8:28 PM
  • No problem Sam, I don't mind at all. Unfortunateley I don't do ASP.NET web apps, so I would not know. But I believe this book I referenceed make mention of web apps.


    Bill McCormick -- MSDN Subscriber
    Thursday, April 29, 2010 9:50 PM
  • So I've recoded portions of my application so that all DB calls are bracketed inside of a TransactionScope.  From my devel PC connected to the SQL Server via a VPN I don't seem to have any issues now. However from the customers PC which is connected to the the server (and the domain) via a dedicated T1, I now get this error:

    A request to establish a connection with the transaction manager was denied

    I'm stumped. Please help MS dudes.

    Thanks,


    Bill McCormick -- MSDN Subscriber
    Friday, April 30, 2010 7:00 PM
  • Hi Bill,

    Thank you for following up.   I think now the problem is not quite related to LINQ to SQL.   For the new transaction error, I recommend you read this SQL newsgroup thread, http://www.sqlnewsgroups.net/group/microsoft.public.sqlserver.server/topic16040.aspx for some troubleshooting skills and possible cause of the issue.  

    Besides, you may get better support in the SQL Server forums about this question, http://social.technet.microsoft.com/Forums/en/category/sqlserver/

     

    Have a nice weekend!

     

    Best Regards,
    Lingzhi Sun
    MSDN Subscriber Support
    in Forum
    If you have any feedback on our support, please contact
    msdnmg@microsoft.com.  


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Saturday, May 1, 2010 11:57 AM
    Moderator
  • This turned out to be a problem on the Vista cleint PC. I justed needed to enable network transactions. The Vista MSDTC configuration and the way to get to it is slightly different than it was under XP.

    Thanks,


    Bill McCormick -- MSDN Subscriber
    Saturday, May 8, 2010 3:22 PM