title: EF Core deadlock with multi threads + BeginTransaction + Commit
body: <p>I have some questions regarding how <code>SaveChangesAsync()</code> and <code>BeginTransaction()</code> + <code>transaction.Commit()</code> work.  </p>
  
  <p>My team has a .NET Core worker that receives events from Microsoft EventHub and saves data into SQL server via EF Core 3.<br>
  One of event types has a lot of data, so we created a few tables, separate data and then save it into these tables. The child tables reference the parent table's <code>id</code> column (FK_Key).<br>
  Some data in the DB has to be deleted before new data is saved under certain conditions, so we delete -> upsert data.  </p>
  
  <p>To save data into the DB, we call <code>dbContext.Database.BeginTransaction()</code> and <code>transaction.Commit()</code>. When we run the worker, we get deadlock exception like <code>Transaction (Process ID 71) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.</code>  </p>
  
  <p>I found that one of  <code>.BatchDeleteAsync()</code> in <code>PurgeDataInChildTables()</code> or one of <code>BulkInsertOrUpdateAsync()</code> in <code>Upsert()</code> throws a deadlock exception (it changes every time I run the worker).  </p>
  
  <p>Here is the code:</p>
  
  <pre><code>public async Task DeleteAndUpsert(List&lt;MyEntity&gt; entitiesToDelete, List&lt;MyEntity&gt; entitiesToUpsert)
  {
      if (entitiesToDelete.Any())
          await myRepository.Delete(entitiesToDelete);
  
      if (entitiesToUpsert.Any())
          await myRepository.Upsert(entitiesToUpsert);
  }
  
  
  public override async Task Upsert(IList&lt;MyEntity&gt; entities)
  {
      using (var dbContext = new MyDbContext(DbContextOptions, DbOptions))
      {
          using (var transaction = dbContext.Database.BeginTransaction())
          {
              await PurgeDataInChildTables(entities, dbContext);
              await dbContext.BulkInsertOrUpdateAsync(entities);
              // tables that depends on the parent table (FK_Key)
              await dbContext.BulkInsertOrUpdateAsync(entities.SelectMany&lt;Child1&gt;(x =&gt; x.Id).ToList());
              await dbContext.BulkInsertOrUpdateAsync(entities.SelectMany&lt;Child2&gt;(x =&gt; x.Id).ToList());
              await dbContext.BulkInsertOrUpdateAsync(entities.SelectMany&lt;Child3&gt;(x =&gt; x.Id).ToList());
              transaction.Commit();
          }
      }
  }
  
  public override async Task Delete(IList&lt;MyEntity&gt; entities)
  {
      using (var dbContext = new MyDbContext(DbContextOptions, DbOptions))
      {
          using (var transaction = dbContext.Database.BeginTransaction())
          {
              await PurgeDataInChildTables(entities, dbContext);
              await dbContext.BulkDeleteAsync(entities);
              transaction.Commit();
          }
      }
  }
  
  private async Task PurgeDataInChildTables(IList&lt;MyEntity&gt; entities, MyDbContext dbContext)
  {
      var ids = entities.Select(x =&gt; x.Id).ToList();
  
      await dbContext.Child1.Where(x =&gt; ids.Contains(x.Id)).BatchDeleteAsync();
      await dbContext.Child2.Where(x =&gt; ids.Contains(x.Id)).BatchDeleteAsync();
      await dbContext.Child3.Where(x =&gt; ids.Contains(x.Id)).BatchDeleteAsync();
  }
  </code></pre>
  
  <p>When the worker starts up, it creates four threads and they all upsert to the same table (and delete too). So, I assume that deadlock occurs when one thread starts a transaction and another starts another transaction (or something similar..) and then try to upsert to (or delete from) child tables.<br>
  I tried some stuff to solve the issue and noticed that deadlock seems to be resolved when I remove <code>BeginTransaction()</code> and use <code>SaveChangesAsync()</code> instead.  </p>
  
  <p>Here is the modified code:</p>
  
  <pre><code>public override async Task Upsert(IList&lt;MyEntity&gt; entities)
  {
      using (var dbContext = new MyDbContext(DbContextOptions, DbOptions))
      {
          await PurgeDataInChildTables(entities, dbContext);
          await dbContext.BulkInsertOrUpdateAsync(entities);
          // tables that depends on the parent table (FK_Key)
          await dbContext.BulkInsertOrUpdateAsync(entities.SelectMany(x =&gt; x.Child1).ToList());
          await dbContext.BulkInsertOrUpdateAsync(entities.SelectMany(x =&gt; x.Child2).ToList());
          await dbContext.BulkInsertOrUpdateAsync(entities.SelectMany(x =&gt; x.Child3).ToList());
          await dbContext.SaveChangesAsync();
      }
  }
  
  public override async Task Delete(IList&lt;MyEntity&gt; entities)
  {
      using (var dbContext = new MyDbContext(DbContextOptions, DbOptions))
      {
          await PurgeDataInChildTables(entities, dbContext);
          await dbContext.BulkDeleteAsync(entities);
          await dbContext.SaveChangesAsync();
      }
  }
  </code></pre>
  
  <p>Deadlock was occuring ~ 30 seconds after the worker starts up but it didn't occur for 2 ~ 3 mins when I modified the code, so I think the issue is resolved, thought it might still occur if I run the worker longer.  </p>
  
  <p>Finally, here are my questions:  </p>
  
  <ul>
  <li>When I use <code>BeginTransaction()</code> + <code>.Commit()</code> deadlock occurs but it won't happen when I use <code>SaveChangesAsync()</code>. Why is it?</li>
  <li>What is the difference between the two methods in terms of transaction?</li>
  <li>If the modified code still could cause deadlock or not a good solution, how do I solve it?</li>
  </ul>