Answered by:
LINQ to SQL read-write locking

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?1 Using 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 11 End Using
Thx.Friday, March 6, 2009 9:38 AM
Answers
-
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 AMAnswerer
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[)amienFriday, March 6, 2009 4:26 PM
-
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 AMAnswerer