0

Having trouble with a SaveChanges call that is timing out after I have performed some basic add operations the last being adding a record to a bridging table. I am unsure why this is happening. Could anyone provide some tips and advice?

I am using ASP.NET Core with EF Core as an ORM for a MSSQL Server database.

Here is the code in question.

Edit: Made changes to code based on suggestions in comments.

Edit 2: Appologies I forgot to mention I am using .NET Core 2.2 and EF Core 2.2.6

public AddToQueueResponse AddToQueue(AddToQueueRequest request)
{
    AddToQueueResponse response = new AddToQueueResponse();

    response = (AddToQueueResponse)request.CheckValidation(response);

    if (response.Status == HttpStatusCode.BadRequest)
        return response;

    var nickname = new Nicknames()
    {
        Sid = request.SID,
        NickName = request.Nickname
    };

    var item = new Queueitem()
    {
        Description = request.Description,
        TimeAdded = DateTime.Now,
        TopicId = request.TopicID
    };

    if (request.CheckInID.HasValue)
    {
        var checkIn = _checkInDataLayer.GetCheckIn(request.CheckInID.Value);

        if (checkIn == null)
        {
            response.Status = HttpStatusCode.NotFound;
            response.StatusMessages.Add(new StatusMessage(HttpStatusCode.NotFound, "Check in not in database."));
            return response;
        }
    }

    using (IDbContextTransaction trans = _queueDataLayer.GetTransaction())
    {
        try
        {
            if (!request.StudentID.HasValue)
            {
                _studentDataLayer.AddStudentNickname(nickname);
                _studentDataLayer.Save();

                if (nickname.StudentId == 0)
                {
                    throw new Exception("Unable to add student nickname");
                }
                request.StudentID = nickname.StudentId;
            }

            item.StudentId = request.StudentID.Value;

            _queueDataLayer.AddToQueue(item);
            _queueDataLayer.Save();

            if (request.CheckInID.HasValue)
            {
                Checkinqueueitem checkinqueueitem = new Checkinqueueitem()
                {
                    CheckInId = request.CheckInID.Value,
                    QueueItemId = item.ItemId
                };

                _checkInDataLayer.AddCheckinQueueItem(checkinqueueitem);
                _checkInDataLayer.Save();
            }

            response.ItemId = item.ItemId;
            response.Status = HttpStatusCode.OK;

            trans.Commit();
        }
        catch (Exception ex)
        {
            trans.Rollback();
            s_logger.Error(ex, "Unable to add queue item");
            response.Status = HttpStatusCode.InternalServerError;
            response.StatusMessages.Add(new StatusMessage(HttpStatusCode.InternalServerError, "Unable to add queue item"));
        }
    }
    return response;
}
radulfr
  • 114
  • 3
  • 13
  • May be you need to do trans.Rollback(); before a first return response; too. – CrazyElf Dec 25 '19 at 13:04
  • 1
    And it's a bad practice to do a lot of logic inside a transaction statement. Do all the heavy logic before, if possible. Then open a transaction, save your data quickly and close transaction, if you don't want to be deadlocked by another parallel transaction or something like that. – CrazyElf Dec 25 '19 at 13:08
  • Which SQL statements are fired and what exactly times out, a command or the transaction? – Gert Arnold Dec 25 '19 at 19:43
  • @CrazyElf Not sure what you mean in your first comment. – radulfr Dec 26 '19 at 00:59
  • @GertArnold The various add methods in the datalayer are using EF Cores context.collection.add() method. The save calls are using EF Cores context.SaveChanges() method. _checkInDataLayer.Save(); is the call this is timing out. – radulfr Dec 26 '19 at 01:02
  • @CrazyElf made some changes based on your second comment. I am not worried about a second transaction locking at the moment as this is occurring in a single unit test call. – radulfr Dec 26 '19 at 01:10
  • Can we see an implementation of `_checkInDataLayer.AddCheckinQueueItem()` method? Also will the problem disappear if you remove transaction? – balbelias Dec 26 '19 at 02:48
  • @balbelias It's just one line which is context.Checkinqueueitem.Add(checkinqueueitem);. – radulfr Dec 26 '19 at 03:43
  • @balbelias yes removing the transaction does stop the timeout. I want to keep some sort of transaction though as all parts need to work or none of it works. – radulfr Dec 27 '19 at 01:50
  • 1
    I think the problem is that you have two layers (queue and checkin) which, I suppose, has their own dbcontext, constructed separately and thus having different scopes. Writes from first dbcontext locks data for second transaction (in second level). The whole point is you need to get both contexts work with same transaction. Whether use same context for both levels or share transaction between them (https://docs.microsoft.com/en-us/ef/core/saving/transactions#cross-context-transaction-relational-databases-only). Probably you can also try to change isolation level, but that will be worse imo. – balbelias Dec 27 '19 at 03:21
  • as @balbelias suggests, check whether you see any blocking calls when your unit test runs. Here's one way to do it: https://stackoverflow.com/questions/41078457/how-to-check-blocking-queries-in-sql-server/51279810 – timur Dec 27 '19 at 05:04

2 Answers2

0

I think the problem is that you have two layers (queue and checkin) which, I suppose, has their own dbcontext, constructed separately and thus having different scopes. Writes from first dbcontext lock data for transaction (in second layers). The whole point is that you need to get both contexts work with same transaction. Whether use same context for both levels or share transaction between them (https://learn.microsoft.com/en-us/ef/core/saving/transactions#cross-context-transaction-relational-databases-only). Probably you can also try to change isolation level, but that will be worse imo.

balbelias
  • 428
  • 5
  • 17
  • Not sure about this as there are three layers with their own instances of the same EF Model and three save changes (I was missing the first one) and the timeout is only happening with the third. – radulfr Dec 28 '19 at 02:24
  • That is possible. Having multiple contexts doesn’t always leads to deadlocks as they are occurring on database level and depends on actions performed. For example if you are making only reads most probable that there will be no locks. Also you said that removing transaction helps. You can use different techniques to simplify sharing your context among layers - Dependency Injection is most commonly used (https://hackernoon.com/asp-net-core-how-to-use-dependency-injection-in-entity-framework-core-4388fc5c148b) – balbelias Dec 29 '19 at 10:12
0

Based @bablelias's comment on their answer. I used the built in .NET Core dependency injection so that one instance was used throughout the program. This solved the issue not 100% sure why but I think it is because I know longer have multiple connections interacting with the database at once as there is now one context. Thanks everyone for your assistance.

radulfr
  • 114
  • 3
  • 13