1

I'm maintaining an ASP.NET website on .NET 4.7.1 that displays some fairly extensive information using Entity Framework 6.0. Right now, all these DB queries are being performed in serial, so I'm attempting to improve performance by implementing async/await.

The problem I'm having is that running multiple simultaneous queries against the same database seems to be somewhat delicate, and I'm having trouble searching up any best practices for this type of scenario.

The site's initial implementation created a context for each of these queries inside an ambient transaction, and disposed the context after use. Upon converting the whole site to use async (and noting TransactionScopeAsyncFlowOption.Enabled), the page load began throwing exceptions claiming Distributed Transaction Coordinator needed to be configured.

System.Transactions.TransactionManagerCommunicationException: Network access for Distributed Transaction Manager (MSDTC) has been disabled.
Please enable DTC for network access in the security configuration for MSDTC using the Component Services Administrative tool.

Some searching at that point led me to believe that this could be remedied in code without perturbing configuration, so I next redesigned data layer to manage connections in a way that would allow them to share the same context. However, when testing that approach, new exceptions are thrown claiming that the connection is too busy.

System.Data.SqlClient.SqlException: Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. The request failed to run because the batch is aborted, this can be caused by abort signal sent from client, or another request is running in the same session, which makes the session busy.

Normally this page's load time is slow (several seconds) but nowhere near the default timeout threshold.

Is async/await best suited only when the queries to run in parallel connect to different databases? If not, is MSDTC the only way to enable this behavior? Or is it perhaps just not a wise thing to blast a single database with so many simultaneous queries?

Improve this question
9
  • I don't think you can share the context at all if you're accessing any overlapping data. Don't know about the MSDTC Commented Mar 27, 2018 at 1:57
  • Just out of curiosity, are you awaiting calls to stuff like ToListAsync or are you by any chance just wrapping your code inside of Task.Run. I have to ask because many people think the latter is the correct way to do async programming. Also this is why having at least some code is helpful. Commented Mar 27, 2018 at 2:02
  • Also if you convert it correctly it shouldn't make it any faster or cause queries to run in parallel that were not running in parallel before. Async is really about freeing up threads while IO occurs. This is a bigger help when scaling than in raw performance. Specifically you should have async/await code from the top (webservice endpoints, event handlers) all the way down to the code that makes IO calls. Commented Mar 27, 2018 at 2:07
  • 3
    Hard to tell anything without code. Chances are that your implementation is just wrong. Databases are designed to be blasted by ton of simultaneous queries. DTC is not needed - you are working with one database. Sharing EF context in any way is major no no, never share it. Commented Mar 27, 2018 at 3:38
  • 1
    @bwerks Because before the change you'd have something like run query1 then query2 then query3 and the thread is blocked the whole time. With async it should be await query1 then await query2 then await query3 the thread is not blocked while each query runs but they don't run at the same time (unless you don't await them and instead capture the tasks and await a Task.WhenAll) But I have no idea what you are doing without seeing code. Commented Mar 27, 2018 at 11:56

1 Answer 1

Reset to default
2

I am not able to understand exactly what changes you have done to the application. I am also not sure that the application was correctly written in the first place and it was following reasonable practices. But here are a few data points that I hope can help:

  • Async support in EF is designed to be used to yield threads back to the pool while waiting for I/O so that the application can process a higher number of requests using less threads and resources. It is not meant to enable parallel execution using the same DbContext. Like the majority of the types in .NET, the DbContext is not thread safe (in any version of EF) so you cannot safely execute multiple queries (async or not) in parallel on the same context instance.

  • Using separate DbContext instances that don't share state or connection objects should be fine, however it is recommended that in ASP.NET you still use a single thread at any point in time to process a request (when you make an async call that yields, processing may continue on a different thread, but that is not a concern) rather than trying to parallelize work within the same request.

  • Also, regarding the exception from System.Transaction, it may very well be that something you changed is now causing multiple connections to auto-enlist in the same System.Transactions.Transaction, which may require escalating the transaction to a distributed transaction.

  • I won't try to come up with a complete explanation for the timeouts, because as I said, I am not sure I understand what changes you made to the application. But it is perfectly possible that if you create too many threads, some of them will end up starving and timing out. It also extremely hard to anticipate everything that could go wrong if you start using types are not thread safe (e.g. database connections, DbContext) from multiple threads.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Just want to know more about this sentence in particular - "the DbContext is not thread safe (in any version of EF) so you cannot safely execute multiple queries (async or not) in parallel on the same context instance", Is this something only related to writing to context, or does it affect in the read only scenarios as well. Correct me if I have misunderstood it, but multiple threads READING from the same context shouldn't be a problem, or can it be..
Reading can be a problem as well. In the first place, the data structures used by the DbContext for change tracking are not thread-safe, but even if the query is a non-tracking query, other things can fail. For example, the DbConnection EF is using to talk to the server, execute queries and read data is not thread-safe.
You can read learn.microsoft.com/ef/core/miscellaneous/… for more information.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.