Protecting critical sections based on a condition in C#

Question

Hello,

I'm dealing with a courious scenario.

I'm using EntityFramework to save (insert/update) into a SQL database in a multithreaded environment. The problem is i need to access database to see whether a register with a particular key has been already created in order to set a field value (executing) or it's new to set a different value (pending). Those registers are identified by a unique guid.

I've solved this problem by setting a lock since i do know entity will not be present in any other process, in other words, i will not have same guid in different processes and it seems to be working fine. It looks something like that:

static readonly object LockableObject = new object();
static void SaveElement(Entity e)
{
       lock(LockableObject)
       {
           Entity e2 = Repository.FindByKey(e);
           if (e2 != null)
           {
               Repository.Insert(e2);
           }
           else
           {
               Repository.Update(e2);
           }
       }
}

But this implies when i have a huge ammount of requests to be saved, they will be queued.

I wonder if there is something like that (please, take it just as an idea):

static void SaveElement(Entity e)
{
       (using ThisWouldBeAClassToProtectBasedOnACondition protector = new ThisWouldBeAClassToProtectBasedOnACondition(e => e.UniqueId)
       {
           Entity e2 = Repository.FindByKey(e);
           if (e2 != null)
           {
               Repository.Insert(e2);
           }
           else
           {
               Repository.Update(e2);
           }
       }
}

The idea would be having a kind of protection that protected based on a condition so each entity e would have its own lock based on e.UniqueId property.

Any idea?

Answer 1

This might work for you, you can just lock on the instance of e:

   lock(e)
   {
       Entity e2 = Repository.FindByKey(e);
       if (e2 != null)
       {
           Repository.Insert(e2);
       }
       else
       {
           Repository.Update(e2);
       }
   }

Answer 2

Don't use application-locks where database transactions or constraints are needed.

The use of a lock to prevent duplicate entries in a database is not a good idea. It limits the scalability of your application be forcing only a single instance to ever exist that can add or update such records. Or worse, someone will eventually try to scale the application to multiple processes or servers and it will cause data corruption (since locks are local to a single process).

What you should consider instead is using a combination of unique constraints in the database and transactions to ensure that no two attempts to add the same entry can both succeed. One will succeed - the other will be forced to rollback.