SqlConnection.OpenAsync() hangs when there are no active connections

Question

When I run this:

using(SqlConnection connection = new SqlConnection(connectionString)){
    await connection.OpenAsync();
}

It hangs on the connection.OpenAsync() line.

If I look in Sql Server Management Studio how many connections are active for the database, there's only one: probably the one that this code uses. So, I'm not sure it is that I'm running out of connections from the app pool.

What am I doing wrong?

1) Does it work if you do a normal Open? 2) Does it lock up on a `await Task.Delay(1000);` if you put it right before your `using` statement? If yes to both questions your problem has nothing to do with SQL and your real problem is explained in http://stackoverflow.com/questions/14485115/synchronously-waiting-for-an-async-operation-and-why-does-wait-freeze-the-pro — Scott Chamberlain, Oct 17 '14 at 21:25
@ScottChamberlain Ah, that was it. The async/await feature is really good, but so easy to shoot yourself in the foot with. — AxiomaticNexus, Oct 17 '14 at 22:20
It also doesn't acknowledge the cancellation token. I set the cancellation token, but it still sets there and times out if it cannot make a connection, instead of returning immediately when the cancellation token is set. Absolutely useless. Furthermore, the only reason I'm calling OpenAsync is to take advantage of the cancellation token -- only to find out it doesn't work at all -- because there's no async version of Database.BeginTransaction. What a bunch of half-assed work. — Triynko, Apr 02 '19 at 20:40

score 21 · Accepted Answer · edited Oct 11 '21 at 17:24

21

The problem was not the connection at all. The problem was that I shot myself in the foot with a deadlock on my threads. I was trying to make a synchronous call to the method containing the connection.OpenAsync(), like this:

Task task = MyAsyncMethod();
task.Wait();

By calling task.Wait() I was blocking the thread. When await connection.OpenAsync() returns, the rest of the method wants to run on the same thread I just blocked, so the task never finishes and task.Wait() never returns.

The solution:
Because in my async method I had nothing requiring me to stick to the same thread that called it, I simply used await connection.OpenAsync().ConfigureAwait(false), to make it run the remainder of the method in a different thread other than the one I block with task.Wait().

edited Oct 11 '21 at 17:24

OfirD

9,442
5
47
90

answered Oct 20 '14 at 20:38

AxiomaticNexus

6,190
3
41
61

4

I managed to trigger this same situation by calling `.Result;` on the end of the encompassing SQL client call. – dckuehn May 31 '18 at 22:47
Related: [how blocking in asynchronous code causes deadlock](https://blog.stephencleary.com/2012/07/dont-block-on-async-code.html). – OfirD Oct 11 '21 at 18:43

score 4 · Answer 2 · answered Apr 02 '19 at 20:46

4

Another reason it could hang is because the implementation is bad. OpenAsync(CancellationToken) doesn't even use the cancellation token for the Open operation, so you can't actually cancel it. You have to wait for it to timeout. All it does is return a cancelled task if the cancellationToken was already set when you called the method, something you could check yourself without any special implementation. So this "async" overload is actually useless.

answered Apr 02 '19 at 20:46

Triynko

18,766
21
107
173

That implementation doesn't even look like it's asynchronous at all. The `open()` operation is done synchronously and by the time the task is returned, its result has already been set. – AxiomaticNexus Apr 03 '19 at 13:53
@AxiomaticNexus `Open` calls `private bool TryOpen(TaskCompletionSource retry)` - [reference source](https://referencesource.microsoft.com/#System.Data/System/Data/SqlClient/SqlConnection.cs,c53043fbbc15e882) – IvanH Apr 08 '19 at 14:57
The `OpenAsync` pictured here is a virtual method from the `DbConnection` class. That method would only ever be called if a data provider class didn't include an override for `OpenAsync`. And the `SqlConnection` class referenced in this question has such an override. See source: [now](https://github.com/dotnet/SqlClient/blob/21475c8ed5d935e4a010d0a3d83cfd265cb7b01d/src/Microsoft.Data.SqlClient/netcore/src/Microsoft/Data/SqlClient/SqlConnection.cs#L1544) and in [.NET Framework 4](https://referencesource.microsoft.com/#System.Data/System/Data/SqlClient/SqlConnection.cs,1383). – Jake Braun Jun 03 '21 at 14:24
1

Mmm. No. I was right. Even in the source you referenced, the cancellationToken passed to OpenAsync is NOT passed to TryOpen. A task completion source (with no reference to the received cancellationToken) is passed to TryOpen. It looks like TryOpen may return immediately with an open connection. However, if it doesn't complete, the cancellation is simply on the wait for the operation to complete (or to cancel future retry attempts), not the 'Open' operation itself. Again, the cancellationToken is NOT passed to TryOpen at all, so it cannot possibly cancel the actual connection attempt. – Triynko Dec 22 '21 at 17:48

SqlConnection.OpenAsync() hangs when there are no active connections

2 Answers2