Avoiding the if-exists-update-else-insert race problem in Entity Framework

.net entity-framework race-condition transactions

Question

I've been reading previous inquiries on how to implement if-exists-insert-else-update semantics in EF, but either the answers don't make sense to me or they don't really deal with the problem. A typical technique is to enclose the task in a transaction scope (for example, Using Entity Framework to implement if-not-exists-insert without race conditions):

using (var scope = new TransactionScope()) // default isolation level is serializable
using(var context = new MyEntities())
{
    var user = context.Users.SingleOrDefault(u => u.Id == userId); // *
    if (user != null)
    {
        // update the user
        user.property = newProperty;
        context.SaveChanges();
    }
    else
    {
        user = new User
        {
             // etc
        };
        context.Users.AddObject(user);
        context.SaveChanges();
    }
}

However, I am unable to understand how this resolves anything since in order for this to function, the line I have highlighted above should block if a second thread attempts to access the same user ID, unblocking only after the first thread has completed its task. However, using a transaction will result in this, and an UpdateException will be generated as a result of the key violation that happens when the second thread tries to create the same user twice.

Catching the race condition-related exception would be preferable to preventing the race condition from occurring in the first place. One approach to do this would be for the highlighted line to place an exclusive lock on the database record that meets its requirement, making it such that only one thread at a time may interact with a user inside the context of this block.

I'm trying to find a clear, all-purpose answer since it seems like this must be a widespread issue for EF users.

If at all possible, I'd really prefer to avoid creating my user via a stored procedure.

Any thoughts?

Despite removing serializable transactions, I tried running the aforementioned code simultaneously on two distinct threads with the same user ID, and both were successful in concurrently entering the vital portion (*). When the second thread tried to input the identical user ID that the first had just inserted, an UpdateException was generated as a result of this. This is due to the fact that a serializable transaction only acquires exclusive locks after it has started altering data, not reading, as mentioned by Ladislav below.

1
31
5/23/2017 12:34:25 PM

Accepted Answer

SQL Server problems shared locks on read records and tables when utilizing serializable transactions. Shared locks prevent other transactions from changing locked data (transactions will block), but they do permit other transactions to read data prior to the transaction that issued the locks beginning to alter data. Concurrent reads are permitted with shared locks up until the first transaction begins changing data, which is why the example fails.

If you desire isolation, a select command that locks the whole table only for one client will do. If the whole table is not locked, concurrency for adding "the same" record will not be resolved. Although utilizing hints allows for granular control for locking data or tables via select commands, using them requires writing straight SQL queries since EF does not enable that. I proposed a method for locking the table here exclusively, however this method creates sequential access to the database and has an impact on all clients using this table.

Place the code in the important part (.NET synchronization, for example, with the assumption that this action only occurs in your single procedure and that no other apps are utilizing your database).lock ) and ensure on the .NET side that only single thread can access critical section. That is not so reliable solution but any playing with locks and transaction levels has a big impact on the database performance and throughput. This strategy may be used with optimistic concurrency (unique constraints, timestamps, etc).

14
5/23/2017 12:10:05 PM

Popular Answer

Just to add my two cents, it doesn't really address the inconvenience of exceptions being raised and transactions not fully working as a scalable solution, but it does prevent race situations from creating issues in distributed systems, where lock type solutions aren't feasible (or easily controlled).

I attempt the insert first and very simply utilize the exception. I'll provide an example using a change of your original code:

using(var context = new MyEntities())
{
    EntityEntry entityUser = null;
    try 
    {
        user = new User
        {
             // etc
        };
        entityUser = context.Users.Add(user);
        context.SaveChanges(); // Will throw if the entity already exists
    } 
    catch (DbUpdateException x)
    when (x.InnerException != null && x.InnerException.Message.StartsWith("Cannot insert duplicate key row in object"))
    {
        if (entityUser != null)
        {
            // Detach the entity to stop it hanging around on the context
            entityUser.State = EntityState.Detached;
        }
        var user = context.Users.Find(userId);
        if (user != null) // just in case someone deleted it in the mean time
        {
            // update the user
            user.property = newProperty;
            context.SaveChanges();
        }
    }
}

Although it isn't attractive, it functions and could be useful to someone.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow