How to deal with concurrency on EF Core

Question

I have a method that searches for an entity on database and attempts to create it if it doesn't exist. As an example:

public async Country FindOrCreate(string name)
{
    var country = _context.Countries.FirstOrDefault(p => p.Name == name);

    if (country != null)
        return country;

    country = new Country
    {
        Name = name
    };

    _context.Countries.Add(country);
    _context.SaveChanges();

    return country;
}

The problem is: there are several tasks in background calling FindOrCreate concurrently. I have prevented duplicates from being inserted by creating an unique index, but the following happens, causing unwanted exceptions:

Task A tries to find Country and fails
Task B tries to find Country and fails
Task A tries to create Country and succeeds
Task B tries to create Country and fails

What would be the appropriate way of handling these scenarios of concurrency? Should I work with locks using C# code? Or should I set a transaction? TIA

You should probably *not* use an ORM at all. What you want to do can easily be handled by a single `INSERT ... WHERE` — Panagiotis Kanavos, Jan 30 '19 at 14:15
Country is your aggregate. As DDD states you shouln't be bothering with keeping invariants between aggregates. They should be eventually consistent. Otherwise you will get a non scalable, monolithic system full of deadlocks. Another option is to create another aggregate, which groups countries, i.e Europe, Asia, and enforce inforce invariants inside it. There you can easily apply pessimistic or optimistic concurrency. https://stackoverflow.com/questions/47180508/best-way-to-enforce-invariants-between-aggregates — DmitriBodiu, Jan 30 '19 at 14:59

Alex Buyny · Accepted Answer · 2019-01-30T14:09:00.190

5

Handling concurrency at the DB level using unique index is the right approach here. Unique index will guarantee uniqueness of countries no matter what.

Don't bother locking the C# code, it will not work as soon as you've got more than 1 server running your app (these days it is likely). Transactions in this case are tricky business so I wouldn't bother.

How to handle those exceptions:

In case your task failed when creating country, catch the exception and retry. If you failed to get the country second time for some reason, log the exception and fail.

edited Jan 30 '19 at 14:09

answered Jan 30 '19 at 13:46

Alex Buyny

3,047
19
25

The index ensures bad data isn't stored. This particular problem though has nothing to do with transactions. ORMs aren't meant for such queries. An `INSERT WHERE NOT EXISTS` would solve the problem without requiring any transactions and *without* any concurrency issues – Panagiotis Kanavos Jan 30 '19 at 14:11
there can be 2 concurrent queries `INSERT WHERE NOT EXISTS` where the `NOT EXISTS` part will succeed and one `INSERT` will fail – Alex Buyny Jan 30 '19 at 14:15
No it won't. It won't insert but that doesn't mean it will fail – Panagiotis Kanavos Jan 30 '19 at 14:16
How is that? I would've thought it will result in the unique index violation? – Alex Buyny Jan 30 '19 at 14:20
Why? The `WHERE` clause would prevent any insertions *unless* there was no matching row – Panagiotis Kanavos Jan 30 '19 at 14:22
`INSERT WHERE NOT EXISTS` is not atomic. let have a [chat](https://chat.stackoverflow.com/rooms/187579/room-for-alex-buyny-and-panagiotis-kanavos)? – Alex Buyny Jan 30 '19 at 14:27
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/187582/discussion-between-alex-buyny-and-panagiotis-kanavos). – Alex Buyny Jan 30 '19 at 14:38
Hey, @AlexBuyny. Thanks for the answer. Your idea kinda "worked", but now my code has a very odd behavior. I am super lost atm and investigating. For some reason, it is throwing duplicate index exception in a upper method, after the right entity has been found and returned. – Vitor Durante Jan 30 '19 at 14:55

How to deal with concurrency on EF Core

1 Answers1