DbContext.BeginTransactionAsync sometimes takes multiple seconds to complete

Question

Using EF Core 3.1, we're having issues with calls to BeginTransactionAsync().

The code is implemented like this:

With BeginTransaction implemented like this:

Most of the time it takes 1-4 milliseconds, but every now and then it takes a lot longer:

The above example shows just above 1 second, but I've seen numbers as high as 15 seconds.

Our database is Azure Elasic pool in the premium tier. Max CPU usage is not above 60% for the entire pool nor is eDTU usage, so clearly this is not a CPU issue. What could be causing this?

It does not appear to be happening in any specific interval and the issue appears in all databases in the elastic pool. (It's a multi tenant app with a database per tenant). The issue appears to happen when multiple transactions are created at the same instant (or very close to) as seen at 05:55:18.745, but other times it's working just fine as seen at 05:55:15.704

Does BeginTransactionAsync() cause any form of locking in the database?

Our app is running in Azure App Services using linux containers. The database is in the same azure region as our containers.

Probably task queue is full and scheduler cannot find free thread to execute task. — Svyatoslav Danyliv, Sep 08 '21 at 18:08
A quick way to test this theory would be to make the call synchronous, right? — Søren Lorentzen, Sep 08 '21 at 18:20
probably better, to break process and check [Tasks window](https://learn.microsoft.com/en-us/visualstudio/debugger/using-the-tasks-window?view=vs-2019) — Svyatoslav Danyliv, Sep 08 '21 at 18:25
I'm not able to do that as it cannot be reproduced and I'm not able to attach the debugger to our production environment — Søren Lorentzen, Sep 09 '21 at 06:00

score 0 · Answer 1 · answered Sep 20 '21 at 10:04

Because the threadpool is called, there appears to be some lag, however it's roughly 3 times quicker than the.NET Framework implementation.

The original answer's results using the most recent versions of EF (6.4.0) and the.NET Framework (4.7.2).

When a binary(max) column is present in a table and an async call is executed over it. The Ado.Net side has a problem with producing too many Tasks, which slows down the process. The issue with EF is that it does not make proper use of Ado.Net.

Why not use EntityFramework and.NET Transactions together?

Multiple database operations can be executed in an atomic way using transactions. All of the operations are successfully applied to the database if the transaction is committed. None of the operations are applied to the database if the transaction is rolled back.

This is a Original post from GitHub which can be helful: Significant Query Slowdown When Using Multiple Joins Due To Changes In 3.0

DbContext.BeginTransactionAsync sometimes takes multiple seconds to complete

1 Answers1