0

I have encountered this error in a singleton service when two instances of a same class tried to use the same method that uses a repository

Microsoft.EntityFrameworkCore.DbUpdateConcurrencyException: Database operation expected to affect 1 row(s) but actually affected 0 row(s). Data may have been modified or deleted since entities were loaded. See http://go.microsoft.com/fwlink/?LinkId=527962 for information on understanding and handling optimistic concurrency exceptions.
   at Npgsql.EntityFrameworkCore.PostgreSQL.Update.Internal.NpgsqlModificationCommandBatch.ConsumeAsync(RelationalDataReader reader, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(DbContext _, ValueTuple`2 parameters, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(IReadOnlyList`1 entriesToSave, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.DbContext.SaveChangesAsync(Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)

To fix this issue I have found a solution using Owned scope in Autofac

private readonly Func<Owned<IProductRepository>> productRepositoryFactory;
...
using (var repositoryOwned = productRepositoryFactory())
{
   await repositoryOwned.Value.DeleteRangeAsync(idsToRemove);
}

This resolved the issue, but I am afraid that the performance will be affected because of the spam of creation and disposal of DbContext (used DbContext is registered as per dependency). Is there a better solution to this problem that will not affect performance in such a way?

Panagiotis Kanavos
  • 120,703
  • 13
  • 188
  • 236
pope_yol
  • 1
  • There's no such thing as concurrent DbContext access. The class is *not* meant for concurrent acces – Panagiotis Kanavos Feb 05 '20 at 14:14
  • A DbContext isn't a database connection either. A connection is opened only when needed, and even then, connection pooling means there's no penalty for closing a connection immediately after use. On the contrary, keeping a connection open causes increasing delays as any locks acquired while the connection is open remain until it's closed. After a while, the connections start conflicting with each other. – Panagiotis Kanavos Feb 05 '20 at 14:16
  • Now I noticed that the actual error is `DbUpdateConcurrencyException: Database operation expected to affect 1 row(s) but actually affected 0 row(s).` That's not about multithreading or concurrent DbContext access at all. It's about optimistic concurrency. It means that the row the DbContext tried to store were modified in the meantime. This is a *business* issue, it can't be fixed by AutoFac or any kind of default solution. – Panagiotis Kanavos Feb 05 '20 at 14:19
  • This is similar to `git` conflicts. You have to decide *what* you want to do in this case - overwrite the modifications? In some cases, that's OK. Discard your own changes? Load the modified data and check to see which rows were modified? Modifications to auditing columns like `ModifiedBy` can be ignored safely. Or should you ask the end user what to do? – Panagiotis Kanavos Feb 05 '20 at 14:21
  • The EF Core docs explain how [Concurrency Conflicts](https://learn.microsoft.com/en-us/ef/core/saving/concurrency) work and how to resolve them. The question as-is doesn't contain enough information to say what should be done. The Autofac code isn't relevant. The exception doesn't even mention the entities. What does the `Product` class look like, how is it configured? How did two different connections end up modifying the *same* product? Is this a valid business scenario? Can *two* users open the same product page and change the description or name? – Panagiotis Kanavos Feb 05 '20 at 14:25
  • @PanagiotisKanavos I have 4 database relations: Product, Category, ProductCategory, ProductCategoryHistory. Even though Product can be assigned to max one Category at the time (constraint in the database), we still have a separate relation for this. We also keep the history of this assignment for statistics. To change Product Category u need to remove the assignment, add removal to history, add a new assignment, add addition to history. Problem occurs when one person is removing the category and second is changing the category. – pope_yol Feb 05 '20 at 18:58
  • I would like to make it so the change has priority over the removal, but it crashes on the removal step (when changing it takes 4 steps as described above) – pope_yol Feb 05 '20 at 19:00
  • It's not a matter of priority - the record is *already* deleted. It doesn't crash either - it tells you there's a concurrency conflict. You have to handle the conflict and either retry your changes, discard them, merge them or ask the user. `the change has priority over the removal` does that mean you want to *recreate* the deleted entity? That's.... strange to say the least. That DELETE probably deleted relater records too. In any case, the fix in this case is to *insert* the deleted records again, if possible – Panagiotis Kanavos Feb 06 '20 at 08:12
  • If you want to re-insert already deleted records, you have a bigger problem though - how is this justified in the business process? Typically, records are *not* deleted - their status changes to `Completed`, `inactive`, `cancelled` or something else that has business sense. When something is deleted in the database, it's meant to be gone without a trace. If you find yourself in a situation where you need to resurrect a deleted record, something is definitely wrong – Panagiotis Kanavos Feb 06 '20 at 08:15
  • What I've meant is that two different users try to remove the same record in database, not recreate it when changing, when change is applied it creates different record. From what I understood, I should just ignore the `actually affected 0 row(s)` error and retry the transaction without it, because I don't care that record that I wanted to remove is already deleted. – pope_yol Feb 06 '20 at 13:57
  • @PanagiotisKanavos Am I right or should I try to change the approach? – pope_yol Feb 06 '20 at 15:00
  • Or if I would want to change to pessimistic concurrency, how can I approach it in the case of removal? – pope_yol Feb 06 '20 at 16:02
  • Did you read the linked docs? That article explains what's going on and what you can do. The exception object already contains the old and new values, so you can check what happened. You *don't* need to switch to pessimistic concurrency. – Panagiotis Kanavos Feb 06 '20 at 16:04
  • 1
    Thx for the help. I think that I got the idea of how to fix this, but I need to test it within the context of a project and check the overall approach to the concurrency in the project. I cannot mark ur comment as an answer to thank u. Can u add the marker answer so I can credit u? – pope_yol Feb 10 '20 at 08:33

1 Answers1

0

Creating a scope is the only way if you plan to pass a context to async method. For example you can use DI for IServiceProvider serviceProvider in the constructor and than use it like this:

 using (var scope = _serviceProvider.CreateScope())
 {
     var service = scope.ServiceProvider.GetRequiredService<IYOURSERVICE>();
     ...
 }
Azzy Elvul
  • 1,403
  • 1
  • 12
  • 22