Handling concurrency with LINQ

Question

I'm currently writing a WCF web service that uses LINQ to SQL to fetch work with a SQL database.

I have a Users table that had an integer that stores how many requests to the service the user has done. During testing I'm getting some concurrency probs updating the integer if its being done at the same time.

What would be the best way to handle this? If LINQ detects a conflict, re-fetch the usage counter and retry until it changed?

Any ideas would help.

Answer 1

How real-time does the request count have to be?

If it doesn't have to be real-time, perhaps you can separate your fetch from your update. Retrieve the user information and drop a message in a MSMQ queue or a service broker and let some other process pull the messages out of the queue and update the request count that way.

Hope this helps.

Answer 2

Don't update the counter on the client (in the WCF process) but instead update it on the server:

UPDATE Users
SET Counter = Counter + 1
WHERE UserId = @userID;

This way you don't care about other concurent calls, because each call correctly increments the counter with 1. Problem is that LINQ cannot do that, but ultimately you have to use the right tool for the right job (in this case being a straight SqlCommand).