SQL Server `sp_who2` procedure shows long locks by a sleeping connection

Question

I'm running an Azure SQL Server and have some issues with the WebApi application which is using it. At some point under heavy load, the app starts to create lots of connections and sp_who2 shows that I have several long locks. After following the chain of locks I can see that the culprit is a sleeping connection which executes nothing at the moment (status = 'sleeping' and BlkBy = '.').

When I do a memory dump of the WebApi process, I can see that most of them have the following call stack:

[Managed to Native Transition]  
    >   mscorlib.dll!System.Threading.SemaphoreSlim.WaitUntilCountOrTimeout(int millisecondsTimeout, uint startTime, System.Threading.CancellationToken cancellationToken)  Unknown
        mscorlib.dll!System.Threading.SemaphoreSlim.Wait(int millisecondsTimeout, System.Threading.CancellationToken cancellationToken) Unknown
        mscorlib.dll!System.Threading.SemaphoreSlim.Wait()  Unknown
        System.Data.dll!System.Data.SqlClient.SqlInternalConnectionTds.SyncAsyncLock.Wait(bool canReleaseFromAnyThread) Unknown
        System.Data.dll!System.Data.SqlClient.SqlInternalConnectionTds.ObtainAdditionalLocksForClose()  Unknown
        System.Data.dll!System.Data.ProviderBase.DbConnectionInternal.CloseConnection(System.Data.Common.DbConnection owningObject, System.Data.ProviderBase.DbConnectionFactory connectionFactory) Unknown
        System.Data.dll!System.Data.SqlClient.SqlConnection.Close() Unknown
        EntityFramework.dll!System.Data.Entity.Infrastructure.Interception.InternalDispatcher<System.Data.Entity.Infrastructure.Interception.IDbConnectionInterceptor>.Dispatch<System.Data.Common.DbConnection, System.Data.Entity.Infrastructure.Interception.DbConnectionInterceptionContext>(System.Data.Common.DbConnection target, System.Action<System.Data.Common.DbConnection, System.Data.Entity.Infrastructure.Interception.DbConnectionInterceptionContext> operation, System.Data.Entity.Infrastructure.Interception.DbConnectionInterceptionContext interceptionContext, System.Action<System.Data.Entity.Infrastructure.Interception.IDbConnectionInterceptor, System.Data.Common.DbConnection, System.Data.Entity.Infrastructure.Interception.DbConnectionInterceptionContext> executing, System.Action<System.Data.Entity.Infrastructure.Interception.IDbConnectionInterceptor, System.Data.Common.DbConnection, System.Data.Entity.Infrastructure.Interception.DbConnectionInterceptionContext> executed) Unknown
        EntityFramework.dll!System.Data.Entity.Infrastructure.Interception.DbConnectionDispatcher.Close(System.Data.Common.DbConnection connection, System.Data.Entity.Infrastructure.Interception.DbInterceptionContext interceptionContext)   Unknown
        EntityFramework.dll!System.Data.Entity.Core.EntityClient.EntityConnection.StoreCloseHelper()    Unknown
        EntityFramework.dll!System.Data.Entity.Core.Objects.ObjectContext.ReleaseConnection()   Unknown
        EntityFramework.dll!System.Data.Entity.Core.Common.Internal.Materialization.Shaper<My.EF6.Entity>.Finally() Unknown
        EntityFramework.dll!System.Data.Entity.Core.Common.Internal.Materialization.Shaper<System.__Canon>.ObjectQueryNestedEnumerator.Dispose()    Unknown
        EntityFramework.dll!System.Data.Entity.Internal.LazyAsyncEnumerator<System.__Canon>.Dispose()   Unknown
        EntityFramework.dll!System.Data.Entity.Infrastructure.IDbAsyncEnumerableExtensions.FirstOrDefaultAsync<My.EF6.Entity>(System.Data.Entity.Infrastructure.IDbAsyncEnumerable<My.EF6.Entity> source, System.Threading.CancellationToken cancellationToken) Unknown
        mscorlib.dll!System.Threading.ExecutionContext.RunInternal(System.Threading.ExecutionContext executionContext, System.Threading.ContextCallback callback, object state, bool preserveSyncCtx)   Unknown
        mscorlib.dll!System.Threading.ExecutionContext.Run(System.Threading.ExecutionContext executionContext, System.Threading.ContextCallback callback, object state, bool preserveSyncCtx)   Unknown
        mscorlib.dll!System.Runtime.CompilerServices.AsyncMethodBuilderCore.MoveNextRunner.Run()    Unknown
        EntityFramework.dll!System.Data.Entity.Utilities.TaskExtensions.CultureAwaiter<bool>.UnsafeOnCompleted.AnonymousMethod__0() Unknown
        mscorlib.dll!System.Threading.Tasks.AwaitTaskContinuation.RunOrScheduleAction(System.Action action, bool allowInlining, ref System.Threading.Tasks.Task currentTask)    Unknown
        mscorlib.dll!System.Threading.Tasks.Task.FinishContinuations()  Unknown
        mscorlib.dll!System.Threading.Tasks.Task<bool>.TrySetResult(bool result)    Unknown
        mscorlib.dll!System.Runtime.CompilerServices.AsyncTaskMethodBuilder<bool>.SetResult(bool result)    Unknown
        EntityFramework.dll!System.Data.Entity.Internal.LazyAsyncEnumerator<My.EF6.Entity>.FirstMoveNextAsync(System.Threading.CancellationToken cancellationToken) Unknown
        mscorlib.dll!System.Threading.ExecutionContext.RunInternal(System.Threading.ExecutionContext executionContext, System.Threading.ContextCallback callback, object state, bool preserveSyncCtx)   Unknown
        mscorlib.dll!System.Threading.ExecutionContext.Run(System.Threading.ExecutionContext executionContext, System.Threading.ContextCallback callback, object state, bool preserveSyncCtx)   Unknown
        mscorlib.dll!System.Runtime.CompilerServices.AsyncMethodBuilderCore.MoveNextRunner.Run()    Unknown
        EntityFramework.dll!System.Data.Entity.Utilities.TaskExtensions.CultureAwaiter<System.__Canon>.UnsafeOnCompleted.AnonymousMethod__0()   Unknown
        mscorlib.dll!System.Threading.Tasks.AwaitTaskContinuation.RunOrScheduleAction(System.Action action, bool allowInlining, ref System.Threading.Tasks.Task currentTask)    Unknown
        mscorlib.dll!System.Threading.Tasks.Task.FinishContinuations()  Unknown
        mscorlib.dll!System.Threading.Tasks.Task<System.__Canon>.TrySetResult(System.__Canon result)    Unknown
        mscorlib.dll!System.Runtime.CompilerServices.AsyncTaskMethodBuilder<System.Data.Entity.Core.Objects.ObjectResult<My.EF6.Entity>>.SetResult(System.Data.Entity.Core.Objects.ObjectResult<My.EF6.Entity> result)  Unknown
        EntityFramework.dll!System.Data.Entity.Core.Objects.ObjectQuery<My.EF6.Entity>.GetResultsAsync(System.Data.Entity.Core.Objects.MergeOption? forMergeOption, System.Data.Entity.Infrastructure.IDbExecutionStrategy executionStrategy, System.Threading.CancellationToken cancellationToken) Unknown
        mscorlib.dll!System.Threading.ExecutionContext.RunInternal(System.Threading.ExecutionContext executionContext, System.Threading.ContextCallback callback, object state, bool preserveSyncCtx)   Unknown
        mscorlib.dll!System.Threading.ExecutionContext.Run(System.Threading.ExecutionContext executionContext, System.Threading.ContextCallback callback, object state, bool preserveSyncCtx)   Unknown
        mscorlib.dll!System.Runtime.CompilerServices.AsyncMethodBuilderCore.MoveNextRunner.Run()    Unknown
        EntityFramework.dll!System.Data.Entity.Utilities.TaskExtensions.CultureAwaiter<System.__Canon>.UnsafeOnCompleted.AnonymousMethod__0()   Unknown
        mscorlib.dll!System.Threading.Tasks.AwaitTaskContinuation.RunOrScheduleAction(System.Action action, bool allowInlining, ref System.Threading.Tasks.Task currentTask)    Unknown
        mscorlib.dll!System.Threading.Tasks.Task.FinishContinuations()  Unknown
        mscorlib.dll!System.Threading.Tasks.Task<System.__Canon>.TrySetResult(System.__Canon result)    Unknown
        mscorlib.dll!System.Runtime.CompilerServices.AsyncTaskMethodBuilder<System.Data.Entity.Core.Objects.ObjectResult<My.EF6.Entity>>.SetResult(System.Data.Entity.Core.Objects.ObjectResult<My.EF6.Entity> result)  Unknown
        EntityFramework.SqlServer.dll!System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.ExecuteAsyncImplementation<System.Data.Entity.Core.Objects.ObjectResult<My.EF6.Entity>>(System.Func<System.Threading.Tasks.Task<System.Data.Entity.Core.Objects.ObjectResult<My.EF6.Entity>>> func)  Unknown
        mscorlib.dll!System.Threading.ExecutionContext.RunInternal(System.Threading.ExecutionContext executionContext, System.Threading.ContextCallback callback, object state, bool preserveSyncCtx)   Unknown
        mscorlib.dll!System.Threading.ExecutionContext.Run(System.Threading.ExecutionContext executionContext, System.Threading.ContextCallback callback, object state, bool preserveSyncCtx)   Unknown
        mscorlib.dll!System.Runtime.CompilerServices.AsyncMethodBuilderCore.MoveNextRunner.Run()    Unknown
        mscorlib.dll!System.Threading.Tasks.AwaitTaskContinuation.RunOrScheduleAction(System.Action action, bool allowInlining, ref System.Threading.Tasks.Task currentTask)    Unknown
        mscorlib.dll!System.Threading.Tasks.Task.FinishContinuations()  Unknown
        mscorlib.dll!System.Threading.Tasks.Task<System.__Canon>.TrySetResult(System.__Canon result)    Unknown
        mscorlib.dll!System.Runtime.CompilerServices.AsyncTaskMethodBuilder<System.Data.Entity.Core.Objects.ObjectResult<My.EF6.Entity>>.SetResult(System.Data.Entity.Core.Objects.ObjectResult<My.EF6.Entity> result)  Unknown
        EntityFramework.dll!System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransactionAsync<System.Data.Entity.Core.Objects.ObjectResult<My.EF6.Entity>>(System.Func<System.Threading.Tasks.Task<System.Data.Entity.Core.Objects.ObjectResult<My.EF6.Entity>>> func, System.Data.Entity.Infrastructure.IDbExecutionStrategy executionStrategy, bool startLocalTransaction, bool releaseConnectionOnSuccess, System.Threading.CancellationToken cancellationToken)   Unknown
        mscorlib.dll!System.Threading.ExecutionContext.RunInternal(System.Threading.ExecutionContext executionContext, System.Threading.ContextCallback callback, object state, bool preserveSyncCtx)   Unknown
        mscorlib.dll!System.Threading.ExecutionContext.Run(System.Threading.ExecutionContext executionContext, System.Threading.ContextCallback callback, object state, bool preserveSyncCtx)   Unknown
        mscorlib.dll!System.Runtime.CompilerServices.AsyncMethodBuilderCore.MoveNextRunner.Run()    Unknown
        EntityFramework.dll!System.Data.Entity.Utilities.TaskExtensions.CultureAwaiter<System.__Canon>.UnsafeOnCompleted.AnonymousMethod__0()   Unknown
        mscorlib.dll!System.Threading.Tasks.AwaitTaskContinuation.RunOrScheduleAction(System.Action action, bool allowInlining, ref System.Threading.Tasks.Task currentTask)    Unknown
        mscorlib.dll!System.Threading.Tasks.Task.FinishContinuations()  Unknown
        mscorlib.dll!System.Threading.Tasks.Task<System.__Canon>.TrySetResult(System.__Canon result)    Unknown
        mscorlib.dll!System.Runtime.CompilerServices.AsyncTaskMethodBuilder<System.Data.Entity.Core.Objects.ObjectResult<My.EF6.Entity>>.SetResult(System.Data.Entity.Core.Objects.ObjectResult<My.EF6.Entity> result)  Unknown
        EntityFramework.dll!System.Data.Entity.Core.Objects.Internal.ObjectQueryExecutionPlan.ExecuteAsync<My.EF6.Entity>(System.Data.Entity.Core.Objects.ObjectContext context, System.Data.Entity.Core.Objects.ObjectParameterCollection parameterValues, System.Threading.CancellationToken cancellationToken)   Unknown
        mscorlib.dll!System.Threading.ExecutionContext.RunInternal(System.Threading.ExecutionContext executionContext, System.Threading.ContextCallback callback, object state, bool preserveSyncCtx)   Unknown
        mscorlib.dll!System.Threading.ExecutionContext.Run(System.Threading.ExecutionContext executionContext, System.Threading.ContextCallback callback, object state, bool preserveSyncCtx)   Unknown
        mscorlib.dll!System.Runtime.CompilerServices.AsyncMethodBuilderCore.MoveNextRunner.Run()    Unknown
        EntityFramework.dll!System.Data.Entity.Utilities.TaskExtensions.CultureAwaiter<System.__Canon>.UnsafeOnCompleted.AnonymousMethod__0()   Unknown
        mscorlib.dll!System.Threading.Tasks.AwaitTaskContinuation.RunOrScheduleAction(System.Action action, bool allowInlining, ref System.Threading.Tasks.Task currentTask)    Unknown
        mscorlib.dll!System.Threading.Tasks.Task.FinishContinuations()  Unknown
        mscorlib.dll!System.Threading.Tasks.Task<System.__Canon>.TrySetResult(System.__Canon result)    Unknown
        mscorlib.dll!System.Runtime.CompilerServices.AsyncTaskMethodBuilder<System.Data.Common.DbDataReader>.SetResult(System.Data.Common.DbDataReader result)  Unknown
        EntityFramework.dll!System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.ExecuteStoreCommandsAsync(System.Data.Entity.Core.EntityClient.EntityCommand entityCommand, System.Data.CommandBehavior behavior, System.Threading.CancellationToken cancellationToken)   Unknown
        mscorlib.dll!System.Threading.ExecutionContext.RunInternal(System.Threading.ExecutionContext executionContext, System.Threading.ContextCallback callback, object state, bool preserveSyncCtx)   Unknown
        mscorlib.dll!System.Threading.ExecutionContext.Run(System.Threading.ExecutionContext executionContext, System.Threading.ContextCallback callback, object state, bool preserveSyncCtx)   Unknown
        mscorlib.dll!System.Runtime.CompilerServices.AsyncMethodBuilderCore.MoveNextRunner.Run()    Unknown
        EntityFramework.dll!System.Data.Entity.Utilities.TaskExtensions.CultureAwaiter<System.__Canon>.UnsafeOnCompleted.AnonymousMethod__0()   Unknown
        mscorlib.dll!System.Threading.Tasks.AwaitTaskContinuation.RunOrScheduleAction(System.Action action, bool allowInlining, ref System.Threading.Tasks.Task currentTask)    Unknown
        mscorlib.dll!System.Threading.Tasks.Task.FinishContinuations()  Unknown
        mscorlib.dll!System.Threading.Tasks.Task.Finish(bool bUserDelegateExecuted) Unknown
        mscorlib.dll!System.Threading.Tasks.Task.ExecuteWithThreadLocal(ref System.Threading.Tasks.Task currentTaskSlot)    Unknown
        mscorlib.dll!System.Threading.Tasks.Task.ExecuteEntry(bool bPreventDoubleExecution) Unknown
        mscorlib.dll!System.Threading.Tasks.ThreadPoolTaskScheduler.TryExecuteTaskInline(System.Threading.Tasks.Task task, bool taskWasPreviouslyQueued)    Unknown
        mscorlib.dll!System.Threading.Tasks.TaskScheduler.TryRunInline(System.Threading.Tasks.Task task, bool taskWasPreviouslyQueued)  Unknown
        mscorlib.dll!System.Threading.Tasks.TaskContinuation.InlineIfPossibleOrElseQueue(System.Threading.Tasks.Task task, bool needsProtection)    Unknown
        mscorlib.dll!System.Threading.Tasks.Task.FinishContinuations()  Unknown
        mscorlib.dll!System.Threading.Tasks.Task<System.__Canon>.TrySetResult(System.__Canon result)    Unknown
        mscorlib.dll!System.Threading.Tasks.UnwrapPromise<System.Data.SqlClient.SqlDataReader>.TrySetFromTask(System.Threading.Tasks.Task task, bool lookForOce)    Unknown
        mscorlib.dll!System.Threading.Tasks.UnwrapPromise<System.__Canon>.ProcessInnerTask(System.Threading.Tasks.Task task)    Unknown
        mscorlib.dll!System.Threading.Tasks.UnwrapPromise<System.__Canon>.Invoke(System.Threading.Tasks.Task completingTask)    Unknown
        mscorlib.dll!System.Threading.Tasks.Task.FinishContinuations()  Unknown
        mscorlib.dll!System.Threading.Tasks.Task.Finish(bool bUserDelegateExecuted) Unknown
        mscorlib.dll!System.Threading.Tasks.Task.ExecuteWithThreadLocal(ref System.Threading.Tasks.Task currentTaskSlot)    Unknown
        mscorlib.dll!System.Threading.Tasks.Task.ExecuteEntry(bool bPreventDoubleExecution) Unknown
        mscorlib.dll!System.Threading.ThreadPoolWorkQueue.Dispatch()    Unknown
        [Native to Managed Transition]  

All the blocked/blocking sessions are regular updates which are really fast and coudln't not delay a database call from the client side. I'm using EF6 and using(var db = new DB()) {...} for every db call.

What could be possibly wrong with the sleeping connection which blocks others? How can I debug the issue?

Answer 1

Ran into this issue just today! Ours is a web application, same as your situation, and the web app was incorrectly leaving a transaction open after finishing a request. Then it would just sit there waiting for the next request while it had the database locked.

Two things to check in the SQL code which might cause this problem are:

(1) Make sure that all of your BEGIN TRANSACTION statements have a corresponding COMMIT TRANSACTION at the end. Otherwise your app might incorrectly hold a transaction open while waiting for the next user request.

(2) Check all uses of THROW to see whether any THROW statements are inside of a transaction. If you want to throw an error during a transaction, you first have to ROLLBACK TRANSACTION before the THROW, or else SQL Server will leave the transaction open and the database locked.