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?

Why bother with the transaction during reading? You're not changing data so there's be nothing to roll back. — Richard Barker, Jun 20 '16 at 20:47
agreed with comment above, infact you can use remove range,which is more optimized way to delete. — Anshul Nigam, Jun 21 '16 at 10:29
@RichardBarker. Thank you and good point. I have tried reading outside of the transaction and passing in the users to be deleted. The code then throws _"The object cannot be deleted because it was not found in the ObjectStateManager."_ because (I think) the context for reading is different than the context for deleting. Maybe I am missing what you are suggesting. — bjscharf, Jun 21 '16 at 15:05
@AnshulNigam Thank you. Yes, I can use _RemoveRange()_ for more than one entity object. I do that for the other entity tables. But in using MS asp.net-identity, it seems that I cannot (or should not) do this for the _AspNetusers_ table. For example, [MS link](http://www.asp.net/mvc/overview/getting-started/introduction/examining-the-details-and-delete-methods) and Horizon_Net's reply [Here](http://stackoverflow.com/questions/23977036). I might be misunderstanding, but do you know of code to perform a deleteRange on the users table using MS Identity? — bjscharf, Jun 21 '16 at 15:15
I'd follow the answer on this. Change your transaction to use what you've got in that comment. If it works it works and remember the KISS method. — Richard Barker, Jun 21 '16 at 15:20
@bjscharf: sorry i had not used identity framework till now, but check this https://msdn.microsoft.com/en-us/magazine/dn818488.aspx if it helps, also did you try increasing timeout time? — Anshul Nigam, Jun 22 '16 at 04:28

score 5 · Answer 1 · answered Aug 04 '17 at 04:20

For me, the problem involved the use of multiple separate DbContexts within the same transaction. The BeginTransaction() approach did not work.

Internally, UserManager.Delete() is calling an async method in a RunSync() wrapper. Therefore, using the TransactionScopeAsyncFlowOption.Enabled parameter for my TransactionScope did work:

using (var scope = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled))
{
    _myContext1.Delete(organisation);
    _myContext2.Delete(orders);
    _userManager.Delete(user);
    scope.Complete();
}

Thanks, solved my issue brilliantly. This should be the accepted answer — bgx, Feb 17 '21 at 14:39

score 3 · Accepted Answer · answered Jun 21 '16 at 02:22

3

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.

answered Jun 21 '16 at 02:22

Spence

28,526
15
68
103

Thank you! When I use the _context.Database.BeginTransaction()_ method instead of _TransactionScope()_ method it works without error. The link was very helpful! I still want to keep using transactions so that we can also prune data from tables associated with those users all in one transaction. Now it works! – bjscharf Jun 21 '16 at 14:59
You are new here I take it :). You need to click the up button and then the tick to say that this answer fixed your problem. That's how stackoverflow works. – Spence Jun 22 '16 at 00:51

timeout when deleting user inside of transactionScope

2 Answers2