The connection does not support MultipleActiveResultSets

[malhotrar]

We are hitting an issue sporadically with connections not supporting MARS. Our connection strings do not have MARS set, and the functionality has worked fine.

The sequence that causes this behavior, is first a "The sempahor timeout period has expired"

Exception Type: System.ComponentModel.Win32Exception
Error message: The semaphore timeout period has expired 
No Stack Trace Available
Exception Type: Microsoft.Data.SqlClient.SqlException
Error message: A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The semaphore timeout period has expired.) 
at Microsoft.Data.SqlClient.SqlCommand.EndExecuteNonQueryAsync(IAsyncResult asyncResult) in SqlCommand.cs:line 1723
at System.Threading.Tasks.TaskFactory`1.FromAsyncCoreLogic(IAsyncResult iar, Func`2 endFunction, Action`1 endAction, Task`1 promise, Boolean requiresSynchronization)
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Medallion.Threading.Internal.Data.DatabaseCommand.<ExecuteAsync>d__12`1.MoveNext() in DatabaseCommand.cs:line 88
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Medallion.Threading.SqlServer.SqlApplicationLock.<ExecuteAcquireCommandAsync>d__15.MoveNext() in SqlApplicationLock.cs:line 64
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Medallion.Threading.SqlServer.SqlApplicationLock.<Medallion-Threading-Internal-Data-IDbSynchronizationStrategy<System-Object>-TryAcquireAsync>d__13.MoveNext() in SqlApplicationLock.cs:line 47
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Threading.Tasks.ValueTask`1.get_Result()
at Medallion.Threading.Internal.Data.MultiplexedConnectionLock.<TryAcquireAsync>d__4`1.MoveNext() in MultiplexedConnectionLock.cs:line 61
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at Medallion.Threading.Internal.Data.MultiplexedConnectionLock.<TryAcquireAsync>d__4`1.MoveNext() in MultiplexedConnectionLock.cs:line 76
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Medallion.Threading.Internal.Data.MultiplexedConnectionLockPool.<TryAcquireAsync>d__8`1.MoveNext() in MultiplexedConnectionLockPool.cs:line 60
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at Medallion.Threading.Internal.Data.MultiplexedConnectionLockPool.<TryAcquireAsync>d__8`1.MoveNext() in MultiplexedConnectionLockPool.cs:line 82
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Medallion.Threading.Internal.DistributedLockHelpers.<Wrap>d__1`1.MoveNext() in DistributedLockHelpers.cs:line 41
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Threading.Tasks.ValueTask`1.get_Result()
at Medallion.Threading.Internal.DistributedLockHelpers.<ThrowTimeoutIfNull>d__18`1.MoveNext() in DistributedLockHelpers.cs:line 142
--- End of stack trace from previous location where exception was thrown ---

And then subsequent failures when trying to acquire the same lock:
Exception Type: System.InvalidOperationException Error message: The connection does not support MultipleActiveResultSets. at Microsoft.Data.SqlClient.SqlCommand.CachedAsyncState.SetActiveConnectionAndResult(TaskCompletionSource1 completion, String endMethod, SqlConnection activeConnection) in SqlCommand.cs:line 243 at Microsoft.Data.SqlClient.SqlCommand.BeginExecuteNonQueryInternalReadStage(TaskCompletionSource1 completion) in SqlCommand.cs:line 1608 at Microsoft.Data.SqlClient.SqlCommand.BeginExecuteNonQueryInternal(CommandBehavior behavior, AsyncCallback callback, Object stateObject, Int32 timeout, Boolean inRetry, Boolean asyncWrite) in SqlCommand.cs:line 1530 at Microsoft.Data.SqlClient.SqlCommand.BeginExecuteNonQueryAsync(AsyncCallback callback, Object stateObject) in SqlCommand.cs:line 1480 at System.Threading.Tasks.TaskFactory1.FromAsyncImpl(Func3 beginMethod, Func2 endFunction, Action1 endAction, Object state, TaskCreationOptions creationOptions) at System.Threading.Tasks.TaskFactory1.FromAsync(Func3 beginMethod, Func2 endMethod, Object state) at Microsoft.Data.SqlClient.SqlCommand.ExecuteNonQueryAsync(CancellationToken cancellationToken) in SqlCommand.cs:line 2911 --- End of stack trace from previous location where exception was thrown --- at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at Medallion.Threading.Internal.Data.DatabaseCommand.d__121.MoveNext() in DatabaseCommand.cs:line 88 --- End of stack trace from previous location where exception was thrown --- at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at Medallion.Threading.SqlServer.SqlApplicationLock.<ExecuteAcquireCommandAsync>d__15.MoveNext() in SqlApplicationLock.cs:line 64 --- End of stack trace from previous location where exception was thrown --- at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at Medallion.Threading.SqlServer.SqlApplicationLock.<Medallion-Threading-Internal-Data-IDbSynchronizationStrategy<System-Object>-TryAcquireAsync>d__13.MoveNext() in SqlApplicationLock.cs:line 47 --- End of stack trace from previous location where exception was thrown --- at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at System.Threading.Tasks.ValueTask1.get_Result() at Medallion.Threading.Internal.Data.MultiplexedConnectionLock.d__41.MoveNext() in MultiplexedConnectionLock.cs:line 61 --- End of stack trace from previous location where exception was thrown --- at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at Medallion.Threading.Internal.Data.MultiplexedConnectionLock.<TryAcquireAsync>d__41.MoveNext() in MultiplexedConnectionLock.cs:line 76 --- End of stack trace from previous location where exception was thrown --- at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at Medallion.Threading.Internal.Data.MultiplexedConnectionLockPool.d__81.MoveNext() in MultiplexedConnectionLockPool.cs:line 60 --- End of stack trace from previous location where exception was thrown --- at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at Medallion.Threading.Internal.Data.MultiplexedConnectionLockPool.<TryAcquireAsync>d__81.MoveNext() in MultiplexedConnectionLockPool.cs:line 82 --- End of stack trace from previous location where exception was thrown --- at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at Medallion.Threading.Internal.DistributedLockHelpers.d__11.MoveNext() in DistributedLockHelpers.cs:line 41 --- End of stack trace from previous location where exception was thrown --- at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at System.Threading.Tasks.ValueTask1.get_Result() at Medallion.Threading.Internal.DistributedLockHelpers.d__181.MoveNext() in DistributedLockHelpers.cs:line 142 --- End of stack trace from previous location where exception was thrown --- at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at

Our method of acquiring a lock looks as follow:

public ValueTask<SqlDistributedLockHandle> AcquireLockAsync(string lockName, CancellationToken cancellationToken = default(CancellationToken), TimeSpan? timeOut = null)
		{
			Throw.IfNull(lockName, nameof(lockName));
			var distributedLock = new SqlDistributedLock(name: lockName, connectionString: this._connectionString);
			return distributedLock.AcquireAsync(cancellationToken: cancellationToken, timeout: timeOut);
		}


using (var @lock = await lockProvider.AcquireLockAsync(GetJobLockName(primaryClientId.Value), timeOut: LockDuration))
				{
					await...
				}

There is only one instance of this lock in sql (i.e., there is a scheduler upstream that guarantees only 1 instance of this code is running, but we use a lock regardless for additional safety). The code being invoke is the only place where this lock is asked for (i.e., no other shared code uses this lock name).

Let me know if you need additional detail.

[malhotrar]

We are using version 2.0.1 and SQL Server Enterprise 2017

[madelson]

I'm unable to repro the exact error (The semaphore timeout period has expired), but I can confirm that we don't have ideal handling right now for the case where a multiplexed connection gets itself into a broken state:

        [Test]
        public async Task TestBrokenConnectionIsRemovedFromPool()
        {
            var applicationName = this._lockProvider.Strategy.SetUniqueApplicationName();

            var lock1 = this._lockProvider.CreateLock("1");
            await using var handle1 = await lock1.AcquireAsync();

            // kill the session
            await this._lockProvider.Strategy.Db.KillSessionsAsync(applicationName);

            var lock2 = this._lockProvider.CreateLock("2");
            Assert.DoesNotThrowAsync(async () => await (await lock2.AcquireAsync()).DisposeAsync());
        }

That said, I believe that the specific issue here is a MSFT one (see linked issue).