SqlConnection OpenAsync blocks UI when SQL Server is shutdown

Asked 3/8, 2019 at 9:48 Answered 3/8, 2019 at 11:35

I have simple WPF client application (.NET 4.6.2) with one button. SqlConnection.OpenAsync method blocks the UI thread when following steps are done:

SQL Server is started and reachable from client
Start client application
Click on button => UI is not blocked (as expected) => message box show 'ok'
Shutdown SQL Server
Click on button => UI is BLOCKED - OpenAsync block UI => message box 'show' error
Click again on button => UI is not blocked again => message box show 'error'

Only if it goes from click->success to click->error OpenAsync block UI thread. I am missing something?

private async void Button_Click(object sender, RoutedEventArgs e)
{
    try
    {
        using (var conn = new SqlConnection("...."))
        {
            await conn.OpenAsync();
        }
    }
    catch (System.Exception)
    {
        MessageBox.Show("err");
        return;
    }
    MessageBox.Show("ok");
}

Edit:

Call stack when I pause debugger during the hang:

System.Data.dll!SNINativeMethodWrapper.SNIOpenSyncEx(SNINativeMethodWrapper.ConsumerInfo consumerInfo, string constring, ref System.IntPtr pConn, byte[] spnBuffer, byte[] instanceName, bool fOverrideCache, bool fSync, int timeout, bool fParallel, int transparentNetworkResolutionStateNo, int totalTimeout, bool isAzureSqlServerEndpoint)    Unknown
    System.Data.dll!System.Data.SqlClient.SNIHandle.SNIHandle(SNINativeMethodWrapper.ConsumerInfo myInfo, string serverName, byte[] spnBuffer, bool ignoreSniOpenTimeout, int timeout, out byte[] instanceName, bool flushCache, bool fSync, bool fParallel, System.Data.SqlClient.TransparentNetworkResolutionState transparentNetworkResolutionState, int totalTimeout)   Unknown
    System.Data.dll!System.Data.SqlClient.TdsParserStateObject.CreatePhysicalSNIHandle(string serverName, bool ignoreSniOpenTimeout, long timerExpire, out byte[] instanceName, byte[] spnBuffer, bool flushCache, bool async, bool fParallel, System.Data.SqlClient.TransparentNetworkResolutionState transparentNetworkResolutionState, int totalTimeout) Unknown
    System.Data.dll!System.Data.SqlClient.TdsParser.Connect(System.Data.SqlClient.ServerInfo serverInfo, System.Data.SqlClient.SqlInternalConnectionTds connHandler, bool ignoreSniOpenTimeout, long timerExpire, bool encrypt, bool trustServerCert, bool integratedSecurity, bool withFailover, bool isFirstTransparentAttempt, System.Data.SqlClient.SqlAuthenticationMethod authType, bool disableTnir, System.Data.SqlClient.SqlAuthenticationProviderManager sqlAuthProviderManager)  Unknown
    System.Data.dll!System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(System.Data.SqlClient.ServerInfo serverInfo, string newPassword, System.Security.SecureString newSecurePassword, bool ignoreSniOpenTimeout, System.Data.ProviderBase.TimeoutTimer timeout, bool withFailover, bool isFirstTransparentAttempt, bool disableTnir)  Unknown
    System.Data.dll!System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(System.Data.SqlClient.ServerInfo serverInfo, string newPassword, System.Security.SecureString newSecurePassword, bool redirectedUserInstance, System.Data.SqlClient.SqlConnectionString connectionOptions, System.Data.SqlClient.SqlCredential credential, System.Data.ProviderBase.TimeoutTimer timeout)    Unknown
    System.Data.dll!System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(System.Data.ProviderBase.TimeoutTimer timeout, System.Data.SqlClient.SqlConnectionString connectionOptions, System.Data.SqlClient.SqlCredential credential, string newPassword, System.Security.SecureString newSecurePassword, bool redirectedUserInstance) Unknown
    System.Data.dll!System.Data.SqlClient.SqlInternalConnectionTds.SqlInternalConnectionTds(System.Data.ProviderBase.DbConnectionPoolIdentity identity, System.Data.SqlClient.SqlConnectionString connectionOptions, System.Data.SqlClient.SqlCredential credential, object providerInfo, string newPassword, System.Security.SecureString newSecurePassword, bool redirectedUserInstance, System.Data.SqlClient.SqlConnectionString userConnectionOptions, System.Data.SqlClient.SessionData reconnectSessionData, System.Data.ProviderBase.DbConnectionPool pool, string accessToken, bool applyTransientFaultHandling, System.Data.SqlClient.SqlAuthenticationProviderManager sqlAuthProviderManager)    Unknown
    System.Data.dll!System.Data.SqlClient.SqlConnectionFactory.CreateConnection(System.Data.Common.DbConnectionOptions options, System.Data.Common.DbConnectionPoolKey poolKey, object poolGroupProviderInfo, System.Data.ProviderBase.DbConnectionPool pool, System.Data.Common.DbConnection owningConnection, System.Data.Common.DbConnectionOptions userOptions) Unknown
    System.Data.dll!System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(System.Data.ProviderBase.DbConnectionPool pool, System.Data.Common.DbConnection owningObject, System.Data.Common.DbConnectionOptions options, System.Data.Common.DbConnectionPoolKey poolKey, System.Data.Common.DbConnectionOptions userOptions)   Unknown
    System.Data.dll!System.Data.ProviderBase.DbConnectionPool.CreateObject(System.Data.Common.DbConnection owningObject, System.Data.Common.DbConnectionOptions userOptions, System.Data.ProviderBase.DbConnectionInternal oldConnection)   Unknown
    System.Data.dll!System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(System.Data.Common.DbConnection owningObject, System.Data.Common.DbConnectionOptions userOptions, System.Data.ProviderBase.DbConnectionInternal oldConnection)  Unknown
    System.Data.dll!System.Data.ProviderBase.DbConnectionPool.TryGetConnection(System.Data.Common.DbConnection owningObject, uint waitForMultipleObjectsTimeout, bool allowCreate, bool onlyOneCheckConnection, System.Data.Common.DbConnectionOptions userOptions, out System.Data.ProviderBase.DbConnectionInternal connection)   Unknown
    System.Data.dll!System.Data.ProviderBase.DbConnectionPool.TryGetConnection(System.Data.Common.DbConnection owningObject, System.Threading.Tasks.TaskCompletionSource<System.Data.ProviderBase.DbConnectionInternal> retry, System.Data.Common.DbConnectionOptions userOptions, out System.Data.ProviderBase.DbConnectionInternal connection)    Unknown
    System.Data.dll!System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(System.Data.Common.DbConnection owningConnection, System.Threading.Tasks.TaskCompletionSource<System.Data.ProviderBase.DbConnectionInternal> retry, System.Data.Common.DbConnectionOptions userOptions, System.Data.ProviderBase.DbConnectionInternal oldConnection, out System.Data.ProviderBase.DbConnectionInternal connection)    Unknown
    System.Data.dll!System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(System.Data.Common.DbConnection outerConnection, System.Data.ProviderBase.DbConnectionFactory connectionFactory, System.Threading.Tasks.TaskCompletionSource<System.Data.ProviderBase.DbConnectionInternal> retry, System.Data.Common.DbConnectionOptions userOptions)  Unknown
    System.Data.dll!System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(System.Data.Common.DbConnection outerConnection, System.Data.ProviderBase.DbConnectionFactory connectionFactory, System.Threading.Tasks.TaskCompletionSource<System.Data.ProviderBase.DbConnectionInternal> retry, System.Data.Common.DbConnectionOptions userOptions)    Unknown
    System.Data.dll!System.Data.SqlClient.SqlConnection.TryOpenInner(System.Threading.Tasks.TaskCompletionSource<System.Data.ProviderBase.DbConnectionInternal> retry)  Unknown
    System.Data.dll!System.Data.SqlClient.SqlConnection.TryOpen(System.Threading.Tasks.TaskCompletionSource<System.Data.ProviderBase.DbConnectionInternal> retry)   Unknown
    System.Data.dll!System.Data.SqlClient.SqlConnection.OpenAsync(System.Threading.CancellationToken cancellationToken) Unknown
    System.Data.dll!System.Data.Common.DbConnection.OpenAsync() Unknown
>   TestWpf.exe!TestWpf.UndoManagerTest.UndoManagerTestView.Button_Click(object sender, System.Windows.RoutedEventArgs e) Line 35   C#
    PresentationCore.dll!System.Windows.RoutedEventHandlerInfo.InvokeHandler(object target, System.Windows.RoutedEventArgs routedEventArgs) Unknown
    PresentationCore.dll!System.Windows.EventRoute.InvokeHandlersImpl(object source, System.Windows.RoutedEventArgs args, bool reRaised)    Unknown
    PresentationCore.dll!System.Windows.UIElement.RaiseEventImpl(System.Windows.DependencyObject sender, System.Windows.RoutedEventArgs args)   Unknown
    PresentationCore.dll!System.Windows.UIElement.RaiseEvent(System.Windows.RoutedEventArgs e)  Unknown
    PresentationFramework.dll!System.Windows.Controls.Primitives.ButtonBase.OnClick()   Unknown
    PresentationFramework.dll!System.Windows.Controls.Button.OnClick()  Unknown
    PresentationFramework.dll!System.Windows.Controls.Primitives.ButtonBase.OnMouseLeftButtonUp(System.Windows.Input.MouseButtonEventArgs e)    Unknown
    PresentationCore.dll!System.Windows.UIElement.OnMouseLeftButtonUpThunk(object sender, System.Windows.Input.MouseButtonEventArgs e)  Unknown
    PresentationCore.dll!System.Windows.Input.MouseButtonEventArgs.InvokeEventHandler(System.Delegate genericHandler, object genericTarget) Unknown
    PresentationCore.dll!System.Windows.RoutedEventArgs.InvokeHandler(System.Delegate handler, object target)   Unknown
    PresentationCore.dll!System.Windows.RoutedEventHandlerInfo.InvokeHandler(object target, System.Windows.RoutedEventArgs routedEventArgs) Unknown
    PresentationCore.dll!System.Windows.EventRoute.InvokeHandlersImpl(object source, System.Windows.RoutedEventArgs args, bool reRaised)    Unknown
    PresentationCore.dll!System.Windows.UIElement.ReRaiseEventAs(System.Windows.DependencyObject sender, System.Windows.RoutedEventArgs args, System.Windows.RoutedEvent newEvent)  Unknown
    PresentationCore.dll!System.Windows.UIElement.OnMouseUpThunk(object sender, System.Windows.Input.MouseButtonEventArgs e)    Unknown
    PresentationCore.dll!System.Windows.Input.MouseButtonEventArgs.InvokeEventHandler(System.Delegate genericHandler, object genericTarget) Unknown
    PresentationCore.dll!System.Windows.RoutedEventArgs.InvokeHandler(System.Delegate handler, object target)   Unknown
    PresentationCore.dll!System.Windows.RoutedEventHandlerInfo.InvokeHandler(object target, System.Windows.RoutedEventArgs routedEventArgs) Unknown
    PresentationCore.dll!System.Windows.EventRoute.InvokeHandlersImpl(object source, System.Windows.RoutedEventArgs args, bool reRaised)    Unknown
    PresentationCore.dll!System.Windows.UIElement.RaiseEventImpl(System.Windows.DependencyObject sender, System.Windows.RoutedEventArgs args)   Unknown
    PresentationCore.dll!System.Windows.UIElement.RaiseTrustedEvent(System.Windows.RoutedEventArgs args)    Unknown
    PresentationCore.dll!System.Windows.UIElement.RaiseEvent(System.Windows.RoutedEventArgs args, bool trusted) Unknown
    PresentationCore.dll!System.Windows.Input.InputManager.ProcessStagingArea() Unknown
    PresentationCore.dll!System.Windows.Input.InputManager.ProcessInput(System.Windows.Input.InputEventArgs input)  Unknown
    PresentationCore.dll!System.Windows.Input.InputProviderSite.ReportInput(System.Windows.Input.InputReport inputReport)   Unknown
    PresentationCore.dll!System.Windows.Interop.HwndMouseInputProvider.ReportInput(System.IntPtr hwnd, System.Windows.Input.InputMode mode, int timestamp, System.Windows.Input.RawMouseActions actions, int x, int y, int wheel)   Unknown
    PresentationCore.dll!System.Windows.Interop.HwndMouseInputProvider.FilterMessage(System.IntPtr hwnd, MS.Internal.Interop.WindowMessage msg, System.IntPtr wParam, System.IntPtr lParam, ref bool handled)   Unknown
    PresentationCore.dll!System.Windows.Interop.HwndSource.InputFilterMessage(System.IntPtr hwnd, int msg, System.IntPtr wParam, System.IntPtr lParam, ref bool handled)    Unknown
    WindowsBase.dll!MS.Win32.HwndWrapper.WndProc(System.IntPtr hwnd, int msg, System.IntPtr wParam, System.IntPtr lParam, ref bool handled) Unknown
    WindowsBase.dll!MS.Win32.HwndSubclass.DispatcherCallbackOperation(object o) Unknown
    WindowsBase.dll!System.Windows.Threading.ExceptionWrapper.InternalRealCall(System.Delegate callback, object args, int numArgs)  Unknown
    WindowsBase.dll!System.Windows.Threading.ExceptionWrapper.TryCatchWhen(object source, System.Delegate callback, object args, int numArgs, System.Delegate catchHandler) Unknown
    WindowsBase.dll!System.Windows.Threading.Dispatcher.LegacyInvokeImpl(System.Windows.Threading.DispatcherPriority priority, System.TimeSpan timeout, System.Delegate method, object args, int numArgs)   Unknown
    WindowsBase.dll!MS.Win32.HwndSubclass.SubclassWndProc(System.IntPtr hwnd, int msg, System.IntPtr wParam, System.IntPtr lParam)  Unknown
    [Native to Managed Transition]  
    [Managed to Native Transition]  
    WindowsBase.dll!System.Windows.Threading.Dispatcher.PushFrameImpl(System.Windows.Threading.DispatcherFrame frame)   Unknown
    WindowsBase.dll!System.Windows.Threading.Dispatcher.PushFrame(System.Windows.Threading.DispatcherFrame frame)   Unknown
    PresentationFramework.dll!System.Windows.Application.RunDispatcher(object ignore)   Unknown
    PresentationFramework.dll!System.Windows.Application.RunInternal(System.Windows.Window window)  Unknown
    PresentationFramework.dll!System.Windows.Application.Run(System.Windows.Window window)  Unknown
    PresentationFramework.dll!System.Windows.Application.Run()  Unknown
    TestWpf.exe!TestWpf.App.Main() Line 53  C#

Clodhopping answered 3/8, 2019 at 9:48 Comment(3)

How much time it remains Blocked? – Fabozzi 3/8, 2019 at 10:6

Pause the debugger during the hang. Post the stack trace of the UI thread including external code. – Cranage 3/8, 2019 at 10:49

It remains blocked until connection timeout. – Clodhopping 3/8, 2019 at 10:50

Short version:

Connection code seems to be in a synchronous part of the OpenAsync.

In such a case there seems to be a single option - treat OpenAsync as a basically synchronous method and execute it with Task.Run so that the delay occurs on a thread pool thread

Task.Run(async () => ... await connection.OpenAsync() ...);

TLDR:

Most probably the issue stems from the current implementation of OpenAsync - https://github.com/microsoft/referencesource/blob/master/System.Data/System/Data/SqlClient/SqlConnection.cs#L1383

Can't be sure at first glance but so far it seems that there are far too few ConfigureAwait(false) in the OpenAsync and other methods it calls.

It means that probably the code that searches for DB host and retries connection to the shut down service just executes on the current (GUI) thread not really allowing any "async voidness" that one may expect.

While when you call it the second time the caching works in a way that returns nearly momentarily.

So, I recommend you to try adding ConfigureAwait(false) in the event handler.

using (var conn = new SqlConnection("...."))   
{
    await conn.OpenAsync().ConfigureAwait(false);
}

As ConfigureAwait seems not to help, then it probably means that original connection code is in a synchronous part of the OpenAsync.

In such a case there seems to be a single option - treat OpenAsync as a basically synchronous method and execute it with Task.Run so that the delay occurs on a thread pool thread:

private async void Button_Click(object sender, RoutedEventArgs e)
{
    await Task.Run(async () =>
        {
            try
            {
                using (var conn = new SqlConnection("...."))
                {
                    await conn.OpenAsync();
                }
            }
            catch (System.Exception)
            {
                MessageBox.Show("err");
                return;
            }
            MessageBox.Show("ok");
        }
    );
}

Mithridatism answered 3/8, 2019 at 10:32 Comment(3)

Thank for reply. Unfortunately ConfigureAwait(false) does not work. – Clodhopping 3/8, 2019 at 11:11

Use Task.Run() seems to be ony option... But it is just workaround, I thing this is a bug in the OpenAsync implementation and wonder why it has not been discovered and fixed yet. – Clodhopping 3/8, 2019 at 11:29

@DavidSkula Sems to be so. You may wish to create an issue on github.com/dotnet/corefx for this problem - github.com/dotnet/corefx/… . – Mithridatism 3/8, 2019 at 11:35

The stack trace demonstrates that ADO.NET is performing synchronous IO although you have requested async IO. This is a bug in ADO.NET which likely happens in some fairly infrequent code path so that it was not noticed.

ADO.NET was upgraded fairly recently and incrementally to support asynchronous IO better. Async database access was uncommon in the early days of .NET.

You can try upgrading to a newer .NET version.

Report this bug in the corefx repository on GitHub.

You can work around this by running the blocking operation on a non-UI thread:

await Task.Run(async () => await conn.OpenAsync());

Cranage answered 3/8, 2019 at 11:35 Comment(0)