Linq version of SELECT FOR UPDATE

Question

I'm getting a ChangeConflictException in my web application when the code updates a certain row within a certain table. The best I can tell it seems as though two users are completing the transaction at the same exact time and optimistic concurrency only affect the SubmitChanges() method instead of doing the lock when the row is selected.

So in other words I have a transaction like this:

Dim query = From row in Table _
            Where row.ID = <blah> _
            Select row
Dim result = query.Single()
result.COLUMN = 2
dataContext.SubmitChanges()

The built-in optimistic concurrency locks the record when SubmitChanges() is called but if the record is changed after Single() and before SubmitChanges() then an error is thrown.

...at least that's my theory...

Does anyone know of a way to start the lock at the Single() call instead of just at SubmitChanges()?

Answer 1

No, you have to wrap your transaction in a try/catch block, and deal with the exception that is thrown. Linq to SQL provides plenty of information to allow you to deal with the exception in an intelligent manner. See http://elsharpo.blogspot.com/2008/04/handling-concurrency-conflicts-in-linq.html

Answer 2

There are ways to do transactions with Linq to SQL - but I haven't got there yet.

I agree it may also be better to deal with the problem of resolving the concurrency issue (prompt the user) than to use a transaction however, a couple of links that may help:

LINQ to SQLTransactions (LINQ to SQL) (Microsoft)

and a short video:

http://www.microsoft.com/uk/msdn/nuggets/nugget/206/Transactions-in-LINQ-to-SQL.aspx

Answer 3

The simplest thing that could possibly work is to use DataContext.ExecuteCommand and send your own update statement.