0

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:

  1. 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.

  2. 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.

  3. 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.

  4. 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. :(

William T. Mallard
  • 1,562
  • 2
  • 25
  • 33
  • [ConcurrentDictionary](https://learn.microsoft.com/es-es/dotnet/api/system.collections.concurrent.concurrentdictionary-2?view=net-5.0) ? – rustyBucketBay Jun 03 '21 at 06:13
  • Use the `ConcurrentDictionary` class and for your `GetLockObject` method use its `GetOrAdd` method. – ckuri Jun 03 '21 at 06:13
  • Yeah @ckuri, I guess I need to protect the dictionary too to prevent initial collisions, question updated. – William T. Mallard Jun 03 '21 at 06:20
  • How many web servers are you running? – mjwills Jun 03 '21 at 07:18
  • @mjwills Just one (IIS), but it's running both a website and separate API, does that make a difference? – William T. Mallard Jun 03 '21 at 15:22
  • The reason I raise it is that if you ever have a web farm / garden, or your app ever stops, or it ever deploys etc etc then in-memory locks will be insufficient. – mjwills Jun 03 '21 at 22:19
  • @WilliamT.Mallard Since your database is accessed from multiple applications, no, you cannot have each application only block other requests from that same application. You need to use database level locking (which is almost always the case when it comes to the data integrity of a database). – Servy Jun 03 '21 at 23:24

0 Answers0