Read lock in Linq2SQL

Question

I have a classic balance update concurrency situation:

using (var context = GenerateContext())
{
   var account = context.Accounts.First(x => x.id == accountId);
   var balanceBefore = account.balance;
   // ... do some other stuff ...
   account.balance = balanceBefore + depositAmount;
   context.SubmitChanges();
}

I would like to lock the whole table for reads/writes during this update.

Can it be done with Linq2SQL?

Edit:

So I tried the following, which does lock the table during the transaction, but it doesn't preform the update - the balance never changes.

using (var context = GenerateContext())
{
   context.Connection.Open();
   context.Transaction = context.Connection.BeginTransaction(IsolationLevel.Serializable);
   var account = context.ExecuteQuery<Account>("SELECT TOP 1 * FROM [Account] WHERE [Id] = 10 WITH (TABLOCKX)").First();
   var balanceBefore = account.balance;
   // ... do some other stuff ...
   account.balance = balanceBefore + depositAmount;
   context.SubmitChanges();
}

What am I doing wrong?

No, don't do that! SQL Server will lock **those rows** that are being updated - and that's quite enough! No point in locking the entire table just to update a single row! — marc_s, Jan 28 '14 at 16:34
possible duplicate of [SQL Server - How to lock a table until a stored procedure finishes](http://stackoverflow.com/questions/3662766/sql-server-how-to-lock-a-table-until-a-stored-procedure-finishes) — SQLMason, Jan 28 '14 at 16:35
I agree with @marc_s locking your data table is a bit extreme. However you could lock a control table (lock info table) while you update it which your application monitors. — SQLMason, Jan 28 '14 at 16:38
I don't see how this is a duplicate of the above, since they talk about a different tier. @marc_s: how do I make sure another process doesn't read the balance during the "dangerous" period between the time I check the balance and the time I update it? — the-lights, Jan 28 '14 at 16:44
@the-lights it's the same answer, that's how you lock the table. — SQLMason, Jan 28 '14 at 21:36

score 1 · Answer 1 · answered Jan 29 '14 at 09:51

To start with, balance updates are best handled on the back end:

UPDATE Account
  OUTPUT inserted.*
  SET balance += @deposit
  WHERE ID = @id;

But lets consider you can only code logic on the client. You should always use optimistic concurrency instead. The benefits of optimistic concurrency under load are too big to dismiss. While pessimistic concurrency is easier to code (as there is no update failure due to concurrency to handle), pessimistic concurrency performs poorly under load.

Linq2SQL supports optimistic concurrency, see Optimistic Concurrency: Overview. The key is the Updatecheck attribute. However, handling concurrency violations in the app is more tricky than just detecting them, but the topic is entirely dependent on the actual app logic.

For the extremely rare case when Optimistic concurrency is not appropriate, the first solution to try is ... still optimistic concurrency, but augmented with application locks.

Even if, say, the world would come to end if you used optimistic concurrency and you decide that you absolutely must take the pessimistic route, use (UPDLOCK, ROWLOCK) and have an appropriate index on ID.

Thank you for the answer. Since I'm doing other things with the balance in the application before actually changing it, I need this update logic to be there. I want to make sure that another process won't read the balance after my process read the balance and before my process finished updating it. That's the only way I can make sure the balance will stay correct. As far as I can see, (UPDLOCK, ROWLOCK) won't help me. — the-lights, Jan 29 '14 at 11:34

the-lights · Answer 2 · 2014-01-30T07:52:40.423

Okay, I managed to make it work.

I had to encapsulate everything with a TransactionScope and apply TransactionOptions with isolation level to the transaction scope.

It now looks like this:

using (var context = GenerateContext())
{
   var txnOptions = new TransactionOptions();
   txnOptions.IsolationLevel = System.Transactions.IsolationLevel.Serializable;
   using (var txnScope = new TransactionScope(TransactionScopeOption.Required, txnOptions))
   {  
       var account = context.ExecuteQuery<Account>("SELECT TOP 1 * FROM [Account] WITH (TABLOCKX)  WHERE [Id] = 10").First();
       var balanceBefore = account.balance;
       // ... do some other stuff ...
       account.balance = balanceBefore + depositAmount;
       context.SubmitChanges();
       txnScope.Complete();
   }
}

`TABLOCKX` on every balance update? You do understand that you had just flushed down the drain any performance claims. Your table is **completely** locked by any balance update. — Remus Rusanu, Jan 29 '14 at 09:39

Read lock in Linq2SQL

2 Answers2