timeout when deleting user inside of transactionScope

Question

• Background

We are trying to archive old user data to keep our most common tables smaller.

• Issue

  Normal EF code for removing records works for our custom tables. The AspNetUsers table is a different story. It appears that the way to do it is using _userManager.Delete or _userManager.DeleteAsync. These work without trying to do multiple db calls in one transaction. When I wrap this in a transactionScope, it times out. Here is an example:

  public bool DeleteByMultipleIds(List<string> idsToRemove)
  {
      try
      {
          using (var scope = new TransactionScope())
          {
              foreach (var id in idsToRemove)
              {
                  var user = _userManager.FindById(id);
                  //copy user data to archive table
                  _userManager.Delete(user);//causes timeout
              }
              scope.Complete();
          }
          return true;
      }
      catch (TransactionAbortedException e)
      {
          Logger.Publish(e);
          return false;
      }
      catch (Exception e)
      {
          Logger.Publish(e);
          return false;
      }
  }
  

Note that while the code is running and I call straight to the DB like:

    DELETE
    FROM ASPNETUSERS
    WHERE Id = 'X'

It will also time out. This SQL works before the the C# code is executed. Therefore, it appears that more than 1 db hit seems to lock the table. How can I find the user(db hit #1) and delete the user (db hit #2) in one transaction?

Answer 1

Advice from microsoft is to use a different API when doing transactions with EF. This is due to the interactions between EF and the TransactionScope class. Implicitly transaction scope is forcing things up to serializable, which causes a deadlock.

Good description of an EF internal API is here: MSDN Link

For reference you may need to look into user manager if it exposes the datacontext and replace your Transaction scope with using(var dbContextTransaction = context.Database.BeginTransaction()) { //code }

Alternatively, looking at your scenario, you are actually quite safe in finding the user ID, then trying to delete it and then just catching an error if the user has been deleted in the fraction of a second between finding it and deleting it.