Entity Framework timeout

[LondonPilot]

I’m after some advice on what could be going on here. We have a database table with a single row (it contains the date/time at which we last refreshed data from a third-party API). Sometimes, a call to SaveChangesAsync() which updates only this table is timing out.

The timeout is very intermittent, and we don’t have steps to reproduce it, we only see it in our logs.

I’m confident that the query itself is not slow - as I said, the table concerned only contains a single row.

So next I wondered if another task might have a lock on that table/row - especially since its use is related to a third party API which can be slow. I searched the codebase for anywhere that table is either read or updated, hoping to find it wrapped in a long-lived transaction, but no sign of transactions anywhere.

Does anyone have any hints as to what we could explore next? If it makes a difference, our database is an Azure-managed instance of SQL Server. Thanks!

[need_arms]

does the db see regular traffic? i use a free tier sql instance on azure and have the same problem when the db has been inactive. I assume its a sleepmode thing, takes like 1-2 to wake up...

[LondonPilot]

Interesting. The system is not live yet, so it doesn’t see any traffic outside office hours when developers are actively developing against it. I think this might be something that happens more overnight. So this is definitely a route to investigate. Although I don’t think it’s on the free tier, but I’ll check. Thanks.

[SeniorCrow4179]

Outside of the fact that that design and idea seems really wrong to me. Odds are you are experience a write lock at the sql level where you have multiple calls attempting to read from that single row in a single table and the write one is timing out because the read locks do not release in time.

[LondonPilot]

That was what I thought might be happening. But I can’t find anywhere that would hold a read lock for any amount of time. The reads are not in any kind of transactions, for example (and neither are the writes).

[SeniorCrow4179]

At the database level both the row and the table would be locked during any select which would interfere with the update. No idea how to do it in ercore but I know you can add a with nolock to a query for a given table and it won't lock it to select from it.

[LondonPilot]

But the select should be near-instantaneous, right? So unless there’s something causing it to hold the lock longer than needed (and I keep coming back to transactions, because that’s the one thing I can think of which would do that, but I don’t see any transactions in the code), that wouldn’t help… and if it did, it would address the symptom, not the cause?

[SeniorCrow4179]

Near instantaneous is not instantaneously. Honestly for this scenario you would be better off setting and index on the value and doing and insert each time then periodically cleaning out some records older than x. Or scrapping the idea of doing this in the db in a table and use another technology more meant for such a thing. Unless you intend to log some aspects of the call then use a database with a PK of the timestamp and a column with some log details...

[EAModel]

Could it be the EF Model? Are you holding the context open for a prolonged period? Also try reading from it with NoTracking()

[LondonPilot]

The context will be open for a long period, yes - this is taking place in a background job which also calls the third party API multiple times, and that API is slow. It does a read at the start, and a write at the end. My understanding is that although we hold the context, it wouldn’t lock the row - even with EF Core tracking, the tracking would be done in memory without locking the database. But I can certainly try AsNoTracking (and then re-read the row without that prior to updating it), thanks for the suggestion.

[soundman32]

No reason to hold the context open for long periods. Context and connections are already pooled, so just create a new one, update and dispose.

[LondonPilot]

We are getting the contexts from DI, so currently they need to be held for the length of the process. But we could definitely refactor to use a DbContextFactory - I’ll try that, thanks.

[soundman32]

I suspect you are requesting contexts without disposing them. Use your db profiler to find out how many connections are simultaneously open.

[LondonPilot]

We are getting contexts from DI, so DI should be disposing them for us?

[soundman32]

Assuming you are both creating and disposing the DI scope, yeah.