Strategy for thread-safe database access

Question

Say I have the following code that inserts users into MS Dynamics in a MVC Application:

public bool CreateContact(string email)
{
    if (crm.contacts.Count(x => x.Email == email) > 0)
         return false; //Email already exist in the Crm. Skip

    var contact = new contact {Email = email};
    crm.AddTocontacts(contact);
    crm.SaveChanges();

    return true;
}

It works great for stopping users from registering with the same Email address, until recently when we are having MAJOR performance issues with Dynamics.

Apparently, users are getting a huge delay and often triple clicks the button that fires this piece of code.

Problem is, .Count() is firing at the same time in different Http Requests before .SaveChanges() finished in the first Request. As a result, we are seeing contacts with the same Email address.

While I already added a fix from the client-side, I would like to see if this can be done on the server side as well.

What would be a good strategy to make this thread-safe?

---

Edit:

While adding a constraint on the CRM is the best solution as many suggested here, I cannot implement that solution at the moment because duplicates already exist in the CRM long before this issue is discovered. Apparently there are more than one application that talks to the CRM.

With very little experience with locking and threading, I ended up doing the following:

internal static class ContactLock
{
    internal static readonly object Locker = new object();
}

public bool CreateContact(string email)
{
    lock(ContactLock.Locker)
    {
        if (crm.contacts.Any(x => x.Email == email))
            return false; //Email already exist in the Crm. Skip

        var contact = new contact {Email = email};
        crm.AddTocontacts(contact);
        crm.SaveChanges();

        return true;
    }
}

It passes my unit test and seems to be working without any issues.

Answer 1

That kind of validation should typically be backed by a unique constraint in the underlying data store. If it is possible to create constraints in the CRM database, that's where the fix should go.

Your code snippet shows a typical place where some kind of locking is needed. The check (with Count()) and the SaveChanges() should be protected by a lock. I would suggest that you start with locking on an object that's static - which means it will be a global lock preventing simultanous registrations. If that proves to be a problem, you could revise the locking strategy.

Regarding the call taking a lot of time - that's something you should address. Adding a unique constraint to the email column will force it to be indexed, which will probably improve performance a lot. If possible (again, I don't know CRM) you should use linq's Any() operator instead of Count() to check for existence. The former can break on the first hit, while the latter will have to continue scanning.