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.

Answer 1

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.

Answer 2

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

Answer 3

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