I have a system with a DB that gets inputs asynchronously from multiple sources for multiple users. New data ends up updating multiple rows in a common table. I'm using linq2sql and the basic approach is to read those rows, update them, and then call SaveChanges(). I know I need to write my own mutual exclusion, but I'd rather not "lock" the entire table, just the rows that belong to a specific user.
I recently discovered the super-simple Lock(someObject), but would like to have a self-generating collection of user-specific lock objects, something like this:
using System.Collections.Generic;
namespace Playground
{
public static class UserSpecificLockTester
{
private static readonly ConcurrentDictionary<int, object> LockObjects = new ConcurrentDictionary<int, object>();
private static object GetLockObject(int lockId)
{
return LockObjects.GetOrAdd(lockId, new object());
}
public static void DoSomeUpdate(int userId, InputXType inputX)
{
lock (GetLockObject(userId))
{
using (var theDb = new MyContext())
{
var someRecord = theDb.theTable.FirstOrDefault(x => x.id == userId);
// I'm concerned that another thread could modify the same record at this point.
someRecord.someProperty = inputX;
theDb.SaveChanges();
}
}
}
}
}
I'm concerned that the lock would be on the object reference. Any idea how I could test this? In Java I'd just test to see if the object ID stayed the same. Is there a way to tell if the lock is truly on the object contained in the Dictionary?
EDIT: Edited to use ConcurrentDictionary as recommended below.
EDIT 2: Expanded the section under lock to show my feared point of contention.
Edit 3: I've learned a few things from the Great Google and colleagues, and although my question still stands I wanted to help those who come after. So TIL:
The DB-esque name of this problem is "concurrency". The locking approach I've outlined above is a way of managing concurrency pessimistically. While it doesn't actually do row level locking, it does solve the problem by preventing it from occurring and at least avoids locking the entire table.
In pure sql my problem still exists. Even if you begin a transaction, select a row, perform mods, update that same row, then commit, there's still a non-zero chance that another bit of code could read the old value and race you to the commit, loser's data lost.
MySql has SELECT FOR UPDATE that does row-based locking (I think), and would be effective within a transaction. Sadly this isn't supported by Linq2Sql.
A primary issue (aka design defect) in my case is that one of the columns in the table I'm protecting is derived from other columns and serves only to simplify queries. I probably should have set the derived field in a before-update trigger. Full stack is hard. :(