1

I seeing a very bizarre timeout exception on what seems to be a very lightweight query, and I'm not sure why.

The query looks something like this

var locationId = 9;

var quantities = db.OrderDetails
                   .Where(d => d.ANullableDate == null
                               && d.Order.LocationId == locationId 
                               && d.AnotherNullabelDate == null
                               && d.ABooleanValue)
                   .Select(d => new { d.SkuId, d.Quantity })
                   .GroupBy(d => d.SkuId)
                   .ToList()
                   .ToDictionary(grp => grp.Key, grp => grp.Sum(x => x.Quantity));

A couple of notes about this:

  • This works fine when locationId is not 9
  • It worked fine yesterday.
  • This works okay on my machine.
  • It works okay on my machine when I configure it to use the DB in azure.
  • If I pull the raw SQL related to this query from the Azure portal and run it directly against the Azure DB, it works fine.
  • This query is significantly simpler, and uses significantly less data than other queries that are not failing.
  • I searched for problem events via the query that can be found "https://learn.microsoft.com/en-us/azure/sql-database/sql-database-connectivity-issues" and nothing came up for around the time that the issue was occurring.
  • In case you're curious about the Select call and the ToList call, they are things I tried after hearing of this issue(they didn't work).

I am totally at a loss as to what can possibly be the problem with this. What should I be looking for?

Update:

It's working now. The last thing I did before It started working again was to downgrade the DB from S4 to S3 (I did this because S4 configuration is in public preview).

Scaling it back up to S4 didn't cause the problem to start happening again, so I can't really assume that the scaling is what fixed it. I still have no idea what was wrong.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Sam I am says Reinstate Monica
  • 30,851
  • 12
  • 72
  • 100
  • 2
    i love that "works on my machine" and "works yesterday", thats our day by day :). Anyway, have you examined the sql query generated before the ToList()? Maybe you can find somethig there, also, you can split the query and evaluate each part to see what works and what do not. – animalito maquina Mar 02 '18 at 18:20

1 Answers1

0

The record was probably blocked by a transaction left open. When you scale up/down a tier open transactions are rolled back. Now the record does not have a lock for data modification.

Alberto Morillo
  • 13,893
  • 2
  • 24
  • 30
  • In that case why did other queries work, or even the same query when queried for a different location? – Sam I am says Reinstate Monica Mar 02 '18 at 19:27
  • It depends of the type of locks involved and how compatible they are between them. Just an example https://stackoverflow.com/questions/49057593/why-is-my-azure-sql-database-table-permanently-locked/49060766?noredirect=1#comment85129989_49060766 – Alberto Morillo Mar 02 '18 at 22:54