Database deadlock on simultaneous API requests that insert to 2 tables

1

So i have endpoint in my api that accepts file with data. For each file i extract data and save it to Products table and at the same time i save filename and date added to diffrent SavedFiles table. Each product has fileId so that we know which file does it come from.

Essentialy inside endpoint in controller there is:

await Handler.SaveFromFile(fileStream, fileName)

Handler is registered in Startup.cs as services.AddScoped<IProductHandler, ProductHandler>(); and injected into controller.

the method is build like so:

public async Task SaveFromFile(Stream fileStream, string fileName)
{
     List<Products> products = Extract(fileStream);
     var file = new SavedFile 
     {
          FileName = fileName,
          DateAdded = DateTime.UtcNow
     };

     foreach (var product in products)
     {
          product.SavedFile = file;
     }

     DbContext.AddRange(products);
     await DbContext.SaveChangesAsync();
}

If file is sent to this endpoint at the same time from two or more diffrent clients i get exception:

System.InvalidOperationException: An exception has been raised that is likely due to a transient failure. Consider enabling transient error resiliency by adding 'EnableRetryOnFailure()' to the 'UseSqlServer' call.
---> Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while updating the entries. See the inner exception for details.
---> Microsoft.Data.SqlClient.SqlException (0x80131904): Transaction (Process ID 55) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

I tried setting builder.EnableRetryOnFailure(5, TimeSpan.FromSeconds(10), null); but then i got another exception:

Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while updating the entries. See the inner exception for details.
 ---> Microsoft.Data.SqlClient.SqlException (0x80131904): The MERGE statement conflicted with the FOREIGN KEY constraint "FK_Products_SavedFiles_SavedFileId". The conflict occurred in database "TestDB", table "dbo.SavedFiles", column 'Id'.

How to avoid conflicts like this? I actually have diffrent endpoints for files with diffrent data too. They save data to diffrent tables but info about file is saved to the same table as other endpoints and if there are requests to two diffrent endpoints i get the same exceptions (propably because all of them use SavedFiles table) so i need solution that works across all endpoints.

@EDIT:

@Guru stron asked for whole controller action and retry config.

Controller action:

[HttpPost("Upload")]
public async Task<ActionResult> Upload([FromForm]IFormFile file)
{
    if (file == null || file.Length == 0)
    {
        return BadRequest();
    }

    using var fileStream = file.OpenReadStream();

    await Handler.SaveFromFile(fileStream, file.FileName);

    return Ok();
}

My attempt to set retry in Startup.cs:

    services.AddDbContext<TestDbContext>(options => options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection"), builder => 
    {
        builder.EnableRetryOnFailure(5, TimeSpan.FromSeconds(10), null);
    }));

@Dan Guzman asked for deadlock info

Deadlock info from system_health trace:

<deadlock>
 <victim-list>
  <victimProcess id="process2026c423088" />
 </victim-list>
 <process-list>
  <process id="process2026c423088" taskpriority="0" logused="94536" waitresource="KEY: 7:72057594043236352 (5d937923d5a3)" waittime="3199" ownerId="6294993" transactionname="user_transaction" lasttranstarted="2020-08-26T15:23:12.940" XDES="0x2026ed30490" lockMode="S" schedulerid="3" kpid="21092" status="suspended" spid="66" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2020-08-26T15:23:14.487" lastbatchcompleted="2020-08-26T15:23:14.227" lastattention="1900-01-01T00:00:00.227" clientapp="Core Microsoft SqlClient Data Provider" hostname="DESKTOP" hostpid="18656" loginname="kamil" isolationlevel="read committed (2)" xactid="6294993" currentdb="7" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
   <executionStack>
    <frame procname="adhoc" line="3" stmtstart="73724" stmtend="110198" sqlhandle="0x02000000b605be0cf972a469745510a34ff7a63c7c992a5c0000000000000000000000000000000000000000">
unknown    </frame>
    <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown    </frame>
   </executionStack>
   <inputbuf>
(@p6 decimal(1,0),@p7 bigint,@p8 nvarchar(4000),@p9 decimal(2,2),@p10 nvarchar(4000),@p11 decimal(3,3),@p12 int,@p13 tinyint,@p14 decimal(1,0),@p15 bigint,@p16 nvarchar(4000),@p17 decimal(4,4),@p18 nvarchar(4000),@p19 decimal(5,5),@p20 int,@p21 tinyint,@p22 decimal(1,0),@p23 bigint,@p24 nvarchar(4000),@p25 decimal(5,4),@p26 nvarchar(4000),@p27 decimal(17,15),@p28 int,@p29 tinyint,@p30 decimal(1,0),@p31 bigint,@p32 nvarchar(4000),@p33 decimal(5,4),@p34 nvarchar(4000),@p35 decimal(17,15),@p36 int,@p37 tinyint,@p38 decimal(1,0),@p39 bigint,@p40 nvarchar(4000),@p41 decimal(5,4),@p42 nvarchar(4000),@p43 decimal(17,15),@p44 int,@p45 tinyint,@p46 decimal(1,0),@p47 bigint,@p48 nvarchar(4000),@p49 decimal(5,4),@p50 nvarchar(4000),@p51 decimal(17,15),@p52 int,@p53 tinyint,@p54 decimal(1,0),@p55 bigint,@p56 nvarchar(4000),@p57 decimal(5,4),@p58 nvarchar(4000),@p59 decimal(17,15),@p60 int,@p61 tinyint,@p62 decimal(1,0),@p63 bigint,@p64 nvarchar(4000),@p65 decimal(5,4),@p66 nvarchar(4000),@p67 decimal(17,15),@p68 int,@p6   </inputbuf>
  </process>
  <process id="process202715a5848" taskpriority="0" logused="855980" waitresource="KEY: 7:72057594043236352 (39c9529c1a80)" waittime="3841" ownerId="6294746" transactionname="user_transaction" lasttranstarted="2020-08-26T15:23:08.467" XDES="0x20269474490" lockMode="S" schedulerid="2" kpid="29744" status="suspended" spid="67" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2020-08-26T15:23:13.770" lastbatchcompleted="2020-08-26T15:23:13.757" lastattention="1900-01-01T00:00:00.757" clientapp="Core Microsoft SqlClient Data Provider" hostname="DESKTOP" hostpid="18656" loginname="kamil" isolationlevel="read committed (2)" xactid="6294746" currentdb="7" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
   <executionStack>
    <frame procname="adhoc" line="3" stmtstart="73728" stmtend="110166" sqlhandle="0x0200000045e14010c29c7110c8ba274a0810d01d7814e9f90000000000000000000000000000000000000000">
unknown    </frame>
    <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown    </frame>
   </executionStack>
   <inputbuf>
(@p0 decimal(1,0),@p1 bigint,@p2 nvarchar(4000),@p3 decimal(2,2),@p4 nvarchar(4000),@p5 decimal(3,3),@p6 int,@p7 tinyint,@p8 decimal(1,0),@p9 bigint,@p10 nvarchar(4000),@p11 decimal(3,2),@p12 nvarchar(4000),@p13 decimal(5,3),@p14 int,@p15 tinyint,@p16 decimal(1,0),@p17 bigint,@p18 nvarchar(4000),@p19 decimal(2,2),@p20 nvarchar(4000),@p21 decimal(4,3),@p22 int,@p23 tinyint,@p24 decimal(1,0),@p25 bigint,@p26 nvarchar(4000),@p27 decimal(3,2),@p28 nvarchar(4000),@p29 decimal(4,3),@p30 int,@p31 tinyint,@p32 decimal(1,0),@p33 bigint,@p34 nvarchar(4000),@p35 decimal(2,2),@p36 nvarchar(4000),@p37 decimal(17,17),@p38 int,@p39 tinyint,@p40 decimal(1,0),@p41 bigint,@p42 nvarchar(4000),@p43 decimal(2,2),@p44 nvarchar(4000),@p45 decimal(16,16),@p46 int,@p47 tinyint,@p48 decimal(1,0),@p49 bigint,@p50 nvarchar(4000),@p51 decimal(3,2),@p52 nvarchar(4000),@p53 decimal(17,15),@p54 int,@p55 tinyint,@p56 decimal(1,0),@p57 bigint,@p58 nvarchar(4000),@p59 decimal(3,2),@p60 nvarchar(4000),@p61 decimal(16,15),@p62 int,@p63 tinyint,   </inputbuf>
  </process>
 </process-list>
 <resource-list>
  <keylock hobtid="72057594043236352" dbid="7" objectname="TestDB.dbo.SavedFiles" indexname="PK_SavedFiles" id="lock20238cb3d80" mode="X" associatedObjectId="72057594043236352">
   <owner-list>
    <owner id="process202715a5848" mode="X" />
   </owner-list>
   <waiter-list>
    <waiter id="process2026c423088" mode="S" requestType="wait" />
   </waiter-list>
  </keylock>
  <keylock hobtid="72057594043236352" dbid="7" objectname="TestDB.dbo.SavedFiles" indexname="PK_SavedFiles" id="lock20279061100" mode="X" associatedObjectId="72057594043236352">
   <owner-list>
    <owner id="process2026c423088" mode="X" />
   </owner-list>
   <waiter-list>
    <waiter id="process202715a5848" mode="S" requestType="wait" />
   </waiter-list>
  </keylock>
 </resource-list>
</deadlock>

c#
sql-server
asp.net-core
.net-core
entity-framework-core
asked on Stack Overflow Aug 26, 2020 by kkamil4sz • edited Aug 26, 2020 by kkamil4sz

1 Answer

-1

You can use the lock statement: https://docs.microsoft.com/en-us/dotnet/csharp/language-reference/keywords/lock-statement

Simple Example:

private static object lockPad = new object(); 

[HttpPost("Upload")]

public async Task Upload([FromForm]IFormFile file) 
{ 
  if (file == null || file.Length == 0) 
  { 
    return BadRequest(); 
  }

  using var fileStream = file.OpenReadStream();

  lock (lockPad)
  {
    await Handler.SaveFromFile(fileStream, file.FileName);
  }

  return Ok();
}
answered on Stack Overflow Aug 26, 2020 by Mosaab N. • edited Aug 26, 2020 by Mosaab N.

User contributions licensed under CC BY-SA 3.0