title: Web API using EF Core and SQL Server database deadlocks
body: <p>I have 3 tables in my database. One table with simple data and the other 2 tables are configured in one to many relationships. If I <code>AddRange()</code> to first table and web API gets many requests like that at the same time everything is OK. </p>
  
  <p>The problem is when the API gets many requests for adding to second table where <code>AddRange()</code> receives list of objects which have child objects. If I send just one request with list of objects its OK, but when I sent 2 or more at the same time I got deadlock exception:</p>
  
  <blockquote>
    <p>Transaction (Process ID ...) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.</p>
  </blockquote>
  
  <p>How to fix this? Can I disable locking somehow? Every request has its own set of data, so entities are not related between separate requests. Its just insert. Or can I at least make it wait for unlock, so requests will be handled in sequence? </p>
  
  <p>For concurency I just found this solution:
  <a href="https://docs.microsoft.com/en-us/ef/core/saving/concurrency" rel="nofollow noreferrer">https://docs.microsoft.com/en-us/ef/core/saving/concurrency</a>
  but manually catching exception seems not like a good solution. There are a lot of databases that receive hundreds of inserts even to the same table and they can have massive amounts of data in each insert, but my database can't handle 2 simple requests with not much data in them.</p>
  
  <p>I suppose I just need to change some configuration? I'm surprised that SQL Server / EF Core doesn't handle this out of the box.</p>
  
  <p><strong>Deadlock graph:</strong>
  <a href="https://i.stack.imgur.com/sQqS8.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/sQqS8.png" alt="Deadlock graph"></a></p>
  
  <p><strong>Deadlock XML:</strong></p>
  
  <pre><code>&lt;deadlock-list&gt;
   &lt;deadlock victim="process2419c047468"&gt;
    &lt;process-list&gt;
     &lt;process id="process2419c047468" taskpriority="0" logused="253644" waitresource="KEY: 6:72057594043498496 (75bf0aa3f90e)" waittime="756" ownerId="1694917" transactionname="user_transaction" lasttranstarted="2020-04-29T11:34:58.637" XDES="0x2418f384490" lockMode="S" schedulerid="6" kpid="16744" status="suspended" spid="51" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2020-04-29T11:34:59.160" lastbatchcompleted="2020-04-29T11:34:59.027" lastattention="1900-01-01T00:00:00.027" clientapp="Core Microsoft SqlClient Data Provider" hostname="DESKTOP-ABCDEF3" hostpid="13564" loginname="DESKTOP-ABCDEF3\Kamil" isolationlevel="read committed (2)" xactid="1694917" currentdb="6" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056"&gt;
      &lt;executionStack&gt;
       &lt;frame procname="adhoc" line="3" stmtstart="64102" stmtend="107398" sqlhandle="0x02000000a861c91ecec3072b8d69ff21a13cd3417ee0845b0000000000000000000000000000000000000000"&gt;
  unknown     &lt;/frame&gt;
       &lt;frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000"&gt;
  unknown     &lt;/frame&gt;
      &lt;/executionStack&gt;
      &lt;inputbuf&gt;
  (@p120 bigint,@p121 nvarchar(4000),@p122 int,@p123 bigint,@p124 nvarchar(4000),@p125 int,@p126 bigint,@p127 nvarchar(4000),@p128 int,@p129 bigint,@p130 nvarchar(4000),@p131 int,@p132 bigint,@p133 nvarchar(4000),@p134 int,@p135 bigint,@p136 nvarchar(4000),@p137 int,@p138 bigint,@p139 nvarchar(4000),@p140 int,@p141 bigint,@p142 nvarchar(4000),@p143 int,@p144 bigint,@p145 nvarchar(4000),@p146 int,@p147 bigint,@p148 nvarchar(4000),@p149 int,@p150 bigint,@p151 nvarchar(4000),@p152 int,@p153 bigint,@p154 nvarchar(4000),@p155 int,@p156 bigint,@p157 nvarchar(4000),@p158 int,@p159 bigint,@p160 nvarchar(4000),@p161 int,@p162 bigint,@p163 nvarchar(4000),@p164 int,@p165 bigint,@p166 nvarchar(4000),@p167 int,@p168 bigint,@p169 nvarchar(4000),@p170 int,@p171 bigint,@p172 nvarchar(4000),@p173 int,@p174 bigint,@p175 nvarchar(4000),@p176 int,@p177 bigint,@p178 nvarchar(4000),@p179 int,@p180 bigint,@p181 nvarchar(4000),@p182 int,@p183 bigint,@p184 nvarchar(4000),@p185 int,@p186 bigint,@p187 nvarchar(4000),@p188 int,@p189 bigi    &lt;/inputbuf&gt;
     &lt;/process&gt;
     &lt;process id="process2419c047088" taskpriority="0" logused="291736" waitresource="KEY: 6:72057594043498496 (11e5211c362d)" waittime="1356" ownerId="1694886" transactionname="user_transaction" lasttranstarted="2020-04-29T11:34:58.240" XDES="0x2418f38c490" lockMode="S" schedulerid="6" kpid="14740" status="suspended" spid="53" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2020-04-29T11:34:58.967" lastbatchcompleted="2020-04-29T11:34:58.937" lastattention="1900-01-01T00:00:00.937" clientapp="Core Microsoft SqlClient Data Provider" hostname="DESKTOP-ABCDEF3" hostpid="13564" loginname="DESKTOP-ABCDEF3\Kamil" isolationlevel="read committed (2)" xactid="1694886" currentdb="6" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056"&gt;
      &lt;executionStack&gt;
       &lt;frame procname="adhoc" line="3" stmtstart="13224" stmtend="22082" sqlhandle="0x0200000051e34c004f1431af05c0540d2834ca932b4d01af0000000000000000000000000000000000000000"&gt;
  unknown     &lt;/frame&gt;
       &lt;frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000"&gt;
  unknown     &lt;/frame&gt;
      &lt;/executionStack&gt;
      &lt;inputbuf&gt;
  (@p0 bigint,@p1 nvarchar(4000),@p2 int,@p3 bigint,@p4 nvarchar(4000),@p5 int,@p6 bigint,@p7 nvarchar(4000),@p8 int,@p9 bigint,@p10 nvarchar(4000),@p11 int,@p12 bigint,@p13 nvarchar(4000),@p14 int,@p15 bigint,@p16 nvarchar(4000),@p17 int,@p18 bigint,@p19 nvarchar(4000),@p20 int,@p21 bigint,@p22 nvarchar(4000),@p23 int,@p24 bigint,@p25 nvarchar(4000),@p26 int,@p27 bigint,@p28 nvarchar(4000),@p29 int,@p30 bigint,@p31 nvarchar(4000),@p32 int,@p33 bigint,@p34 nvarchar(4000),@p35 int,@p36 bigint,@p37 nvarchar(4000),@p38 int,@p39 bigint,@p40 nvarchar(4000),@p41 int,@p42 bigint,@p43 nvarchar(4000),@p44 int,@p45 bigint,@p46 nvarchar(4000),@p47 int,@p48 bigint,@p49 nvarchar(4000),@p50 int,@p51 bigint,@p52 nvarchar(4000),@p53 int,@p54 bigint,@p55 nvarchar(4000),@p56 int,@p57 bigint,@p58 nvarchar(4000),@p59 int,@p60 bigint,@p61 nvarchar(4000),@p62 int,@p63 bigint,@p64 nvarchar(4000),@p65 int,@p66 bigint,@p67 nvarchar(4000),@p68 int,@p69 bigint,@p70 nvarchar(4000),@p71 int,@p72 bigint,@p73 nvarchar(4000),@p74 int,@p75 bi    &lt;/inputbuf&gt;
     &lt;/process&gt;
    &lt;/process-list&gt;
    &lt;resource-list&gt;
     &lt;keylock hobtid="72057594043498496" dbid="6" objectname="Database.dbo.TabExample" indexname="PK_TabExample" id="lock2419256aa80" mode="X" associatedObjectId="72057594043498496"&gt;
      &lt;owner-list&gt;
       &lt;owner id="process2419c047088" mode="X"/&gt;
      &lt;/owner-list&gt;
      &lt;waiter-list&gt;
       &lt;waiter id="process2419c047468" mode="S" requestType="wait"/&gt;
      &lt;/waiter-list&gt;
     &lt;/keylock&gt;
     &lt;keylock hobtid="72057594043498496" dbid="6" objectname="Database.dbo.TabExample" indexname="PK_TabExample" id="lock24191a1b580" mode="X" associatedObjectId="72057594043498496"&gt;
      &lt;owner-list&gt;
       &lt;owner id="process2419c047468" mode="X"/&gt;
      &lt;/owner-list&gt;
      &lt;waiter-list&gt;
       &lt;waiter id="process2419c047088" mode="S" requestType="wait"/&gt;
      &lt;/waiter-list&gt;
     &lt;/keylock&gt;
    &lt;/resource-list&gt;
   &lt;/deadlock&gt;
  &lt;/deadlock-list&gt;
  </code></pre>
  
  <p><strong>Simplified code that produces this error:</strong></p>
  
  <pre><code>[HttpPost]
  public async Task&lt;IActionResult&gt; Upload([FromForm]IFormFile file)
  {
      // fileinfo is actually a data model and it is added to database in separate class, but i tried to make it as simple as i can.
      var fileinfo = new { filename = file.FileName, dateAdded = DateTime.UtcNow }; // it also has id property
      DbContext.Add(fileinfo);
      await DbContext.SaveChangesAsync();
  
      var dataExtractedFromFile = ExtractDataFromFile(file);
      foreach (var entity in dataExtractedFromFile)
          {
              entity.fileDataId = filedata.id;
          }
      DbContext.AddRange(dataExtractedFromFile);
      return Ok();
  }
  </code></pre>
  
  <p>dataExtractedFromFile is a collection that consists of entities where every entity has its own collection.</p>
  
  <p>The deadlock happens when this controller endpoint is called 2 or more time at the same time. On frontend you can prepare many files and when you click one button all of them are sent at the same time. It works fine if you send files one by one.</p>