Can the Oracle Managed Driver Use Async/Await Properly

Can the Oracle managed driver use async/await properly?

No. The managed driver does not support async / await.

You can call those methods, since they must be implemented to comply with the interface definition, but the code is actually synchronous. You can use Task.Run if you want to, but you can't have two calls at the same time (Oracle will threat them synchronous).

Asynchronous calls to Oracle DB using ADO.NET

async and await are for asynchronous code. Normally, if you had a scalable database, you could make your db calls asynchronous and thus scale your server. Note that the primary benefit of async on ASP.NET is scalability, not response time.

However, as others have noted, Oracle doesn't support asynchronous code.

But that is immaterial, since the code you posted isn't actually asynchronous to begin with! It's what I call "fake asynchronous", because it's just pushing synchronous work off to a background thread using Task.Run instead of using naturally-asynchronous APIs. (But as already noted, in this case (that is, Oracle), you don't have any naturally-asynchronous APIs to work with).

So, what you end up with is parallelism, not asynchrony. In particular, the code is spreading itself over 5 thread pool threads to do its work.

Now, the first thing you need to do is ask yourself if you really want parallelism on the server. Your requests will take 5 threads instead of 1 (or 0). This can greatly impact the scalability of your web server (in a bad way). Also, take into consideration the capabilities of your backend. If it's a single server and these queries are all hitting a single database on a single hard drive, will parallelizing 5 of them actually produce any benefit or will it actually be just as bad if not worse due to disk contention? (You should be able to whip up a quick console app to test how your db responds with serial vs parallel requests, when idle and when under load).

I find that the vast majority of the time, the answer is "no, I do not want to bring my entire db server to its knees for this one request" - in other words, avoid parallelism on the server.

But if you have weighed the options and decided that yes, yours is one of the rare cases where parallelism is appropriate on ASP.NET, then you should ask the question that you've posted here: why are these running sequentially and not concurrently? (side note: it's sequential vs concurrent here, not synchronous vs asynchronous)

Answer: I don't know.

But I have a guess: if the database connection (cn in your code snippet) is shared, then it's likely that the db connection itself is limited to one query at a time. Other database connection systems have similar restrictions. The first thing I'd try is giving each query its own connection.

That is, if you want to parallelize your web server. Which is a big "if".

Async/Await snippet deadlocks when targeting MS-SQL-Server (as expected) but doesn't deadlock when targeting Oracle (unexpected)

https://community.oracle.com/thread/4092961

Well crap. Oracle is pulling our feet. They don't have any respect for async/await. They internally resort to blocking calls as if async/await was not even being used anywhere. 'Great'.

Dapper QueryAsync blocks UI for the first time querying (against Oracle server)?

With async await you can free and use UI thread only during execution of a truly asynchronous operation (for example, asynchronous IO or a task delegated to a thread pool thread). In your case, methods that utilze Oracle driver (ODP.NET) are not truly asynchronous. See Can the Oracle Managed Driver use async/wait properly? discussion on Stack Overflow.

If you want to offload work from a UI thread to increase responsiveness, simply use Task.Run():

var items = await Task.Run(() => LoadItems());

Use of any other mechanism such as Task.ConfigureAwait(false), or synchronization context replacement combined with Task.Yield() will result in use of an additional thread pool thread too, but it will free UI thread later.

For more information check:

• Should I expose asynchronous wrappers for synchronous methods? article by Stephen Toub
• Async/Await FAQ article by Stephen Toub
• Task.Run Etiquette Examples: Even in the Complex Case, Don't Use Task.Run in the Implementation article by Stephen Cleary

Fill GridViews with Entity Framework Asynchronously

According to this answer, Oracle does not support asynchronous queries in its .NET library. So, the way you're doing asynchronous LINQ is correct; it's just that Oracle doesn't support it. This is why you're seeing the queries run synchronously rather than asynchronously.

To get the code off the UI thread, you can use Task.Run. I recommend also adding a comment to the code, since it looks wrong:

// Task.Run is necessary because Oracle does not support async: https://stackoverflow.com/questions/29016698/can-the-oracle-managed-driver-use-async-wait-properly/29034291#29034291
radGridViewLast7Days.DataSource = await Task.Run(() => MonitoringToolCore.SwiftQueries.ReturnLast7DaysAsync());

On a side note, EF does support multiple simultaneous queries if they each use their own db context. Since your methods do use their own db context, you can load all these queries simultaneously:

private async void SwiftCheck_Load(object sender, EventArgs e)
{
  // Task.Run is necessary because Oracle does not support async: https://stackoverflow.com/questions/29016698/can-the-oracle-managed-driver-use-async-

  var last7DaysTask = Task.Run(() => MonitoringToolCore.SwiftQueries.ReturnLast7DaysAsync());
  var compareAmountsTask = Task.Run(() => MonitoringToolCore.SwiftQueries.CompareAmountsAsync());
  var compareAmountsByTypeTask = Task.Run(() => MonitoringToolCore.SwiftQueries.CompareAmountsByTypeAsync());
  var compareAmountsBySenderTask = Task.Run(() => MonitoringToolCore.SwiftQueries.CompareAmountsBySenderAsync());
  await Task.WhenAll(last7DaysTask, compareAmountsTask, compareAmountsByTypeTask, compareAmountsBySenderTask);
  radGridViewLast7Days.DataSource = await last7DaysTask;
  radGridViewAmount.DataSource = await compareAmountsTask;
  radGridViewAmountBySwift.DataSource = await compareAmountsByTypeTask;
  radGridViewAmountBySender.DataSource = await compareAmountsBySenderTask;

  //Code to adapt the gridviews layout after data has been bound
  this.radGridViewAmountBySender.BestFitColumns();
}

EF Core - Async functions are blocking UI

Unfortunately, the actual implementation of these methods seem to leave something to be desired:

Why would an EF query with ToListAsync hang in a WPF application?

As a workaround, to keep your UI responsive, you could execute the synchronous version on a background thread:

private async void Button_Click(object sender, RoutedEventArgs e)
{
    await Task.Run(() =>
    {
        using var ctx = new eWMSContext()
        {
            var a = ctx.TJobLines.ToList();
        }
    });
}

Then you don't have to rely on the implementation of ToListAsync being non-blocking.

---

Related Topics