none
LINQ to SQL read-write locking RRS feed

  • Question

  • Hi,

    I have the following LINQ to SQL query.

    How can I create a read-write lock so that other db users cannot access customers table during the time interval between *1 and *2?

    1Using dc As New MyData.MyDataContext 
    2  ' a couple of LINQ to SQL queries 
    3  ' *1 
    4  dim myCustomer as MyData.customer = _ 
    5    (from customers from dc.customers _ 
    6      select customers).First 
    7  myCustomer.balance += 101 
    8  ' *2 
    9  ' another couple of LINQ to SQL queries 
    10  dc.SubmitChanges 
    11End Using 


    Thx.
    Friday, March 6, 2009 9:38 AM

Answers

All replies

  • I am quite sure that if you submit changes when the source object has changed that LINQ will throw an exception and the transaction will rollback. Please post again if this is not the case.
    Friday, March 6, 2009 2:36 PM
  • That is only true if UpdateCheck on the field that has changed is set to Always or WhenChanged.

    There is no pessimistic concurrent/locking mechanism available - it follows the optimistic model described at http://msdn.microsoft.com/en-us/library/bb399373.aspx

    [)amien
    Friday, March 6, 2009 4:26 PM
    Moderator

  • vmware said:

    How can I create a read-write lock so that other db users cannot access customers table during the time interval between *1 and *2?



    If you really want to lock the entire table (!), you can manually lock it by wrapping everything in a transactioncontext and issuing a "select xyz from table with (tablockx)" using ExecuteQuery.

    If you just want to make sure noone else can update the record you're working with, just wrapping with a transactioncontext set to serializable should do it.

    See http://social.msdn.microsoft.com/forums/en-US/linqprojectgeneral/thread/2d6fdb2e-e17e-4a4c-8da0-6968e60ef855/
    Kristofer - Huagati Systems Co., Ltd. - web: www.huagati.com - blog: blog.huagati.com - twitter: twitter.com/KristoferA
    • Proposed as answer by KristoferAEditor Monday, March 9, 2009 2:13 AM
    • Marked as answer by vmware Thursday, April 16, 2009 8:15 AM
    Monday, March 9, 2009 2:13 AM
    Answerer