FirstOrDefault returning null, while row exists

.net-core c# entity-framework-core

Question

I have a bit of a race condition going on, where a row in DB might be created by two threads at the same time. To get around this, I've implemented retries, like so:

int retries = 0;
while (true)
{                
    try
    {
        var saved = context.Table.FirstOrDefault(x => x.field1 == val1 && x.field2 == val2);

        if (saved != null)
        {
            //edits saved
        }
        else
        {
            context.Table.Add(new Table
            {
                field1 = val1,
                field2 = val2
            });
        }
        await context.SaveChangesAsync();
        return Json(true);
    }
    catch (Exception e)
    {
        if (retries >= 5)
            throw (e);
        retries++;
    }
}

Somehow this fails 5 times in a row with the same error:

Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while updating the entries. See the inner exception for details. ---> System.Data.SqlClient.SqlException: Cannot insert duplicate key row in object 'dbo.Table' with unique index 'IX_Table_field1_field2'. The duplicate key value is (val1, val2).

Why is FirstOrDefault returning null, even though the row clearly exists in the database? I'm using Microsoft.AspNetCore.All v.2.1.4

EDIT: For clarification. The context is not being shared between threads. The race occurs when multiple HTTP requests arrive at the same time. The context is injected into the controller (where this code is). It was registered with an AddDbContext call with default settings, making its ServiceLifetime scoped.

SOLUTION: Fenixil's comment gave me the necessary hint. The added, but unsaved row remains in context and keeps trying to get inserted. I kept a reference to the new row and added this to the catch block:

context.Entry(NewRow).State = EntityState.Detached;
1
0
9/30/2019 7:39:39 AM

Popular Answer

Did you shared DbContext? DbContext is not thread safe.

Try wrapping your insert operation in a using block of DbContext, instead of a retry:

using(var context = new DbContext)
{
  // Insert operation here
}

The conflict is easy to understand, but first you need to know that when you await a call, the thread immediately returns to the caller.

Image this scenario, you have two threads, running your code. Here is the execute order:

  1. Thread 1: FirstOrDefault returns null.
  2. Thread 2: FirstOrDefault returns null.
  3. Thread 1: Add runs. SQL generated and queued at database server.
  4. Thread 1: await context.SaveChangesAsync(). The call completes immediately.
  5. Database: Completed call from Thread 1.
  6. Thread 2: Add runs. SQL generated and queued at database server.
  7. Thread 2: await context.SaveChangesAsync(). The call completes immediately.
  8. Database: Tried call from Thread 2, but cannot complete it because a row with the same key value was inserted before.
1
9/27/2019 5:25:56 PM


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