Multiple calls to CLR SP results in "Context Connection Is Busy"

[Chris N]

Hi all,

We have some VS 2008 .Net code which is performing database related queries
which has been marked for use as a CLR Stored Procedure and running in SQL
2005 which runs fine for one user. But we are having problems when calling
the same stored procedure via multiple clients at the same time.

Here is a very simplified version of what we are doing in which we open a
dataReader and just iterate through the records doing "stuff" (Note: The
connection has been already established with "Context Connection" = True" at
construction of the class)

[Microsoft.SqlServer.Server.SqlProcedure]
public static void DoSomethingDatabaseRelated()
{
SqlCommand cmd = new SqlCommand( "Select * from People", mConnection );
using ( SqlDataReader dataReader = cmd.ExecuteReader() )
{
while ( dataReader.Read() )
{
//Do "stuff" here
}
dataReader.Close();
}
}

Now I assume that it's obviously sharing the copy of the .Net assembly under
the bonnet in SQL and therefore the connection object which is static, and
that you can't open two dataReaders at once unless you have MARS turned on -
which you can't seem to do on a Context Connection. We can't have 2
connections open at the same time either as it errors whenever we try because
only one Context Connection is allowed at once.

I also tried using thread locking - justing wrapping "lock(this)" around the
processing of the dataReader so that it would block the other one until the
current call had finished but this doesn't seem to be allowed in a CLR SP -
comes back with an illegal operation exception. Maybe there is some locking
that we can use that is allowed?

Or is there a way of forcing each client calling into the Stored Procedure
to have their own copy of the .Net assembly in memory - although that doesn't
sound like a good idea really!

Is there something really simple that I'm missing because not being able to
call the stored procedure simultaneously from multiple clients just seems
such an obvious flaw, that it must be something we are doing wrong?

Here is the error and callstack:

************** Exception Text **************
System.Data.SqlClient.SqlException: A .NET Framework error occurred during
execution of user-defined routine or aggregate "_FS_RunSimpleQuery":
System.InvalidOperationException: Context connection is busy.
System.InvalidOperationException:
at
System.Data.SqlServer.Internal.ClrLevelContext.StartCallbackSection(Boolean*
pfResetBusy)
at
System.Data.SqlServer.Internal.ClrLevelContext.AllocateNativeRequest(SmiEventSink
sink, UrtCacheOptions ucoOption, Char* pwchSql, Int32 cwchSql, UrtCommandType
uctType, UInt64 cbMaxSize, UInt32 cFields, UrtNativeRequest**
ppNativeRequest, SqlTaskMem& stmMemory)
at System.Data.SqlServer.Internal.RequestExecutor..ctor(SmiEventSink
sink, ClrLevelContext owningContext, String strCommand, CommandType
eCommandType, SmiParameterMetaData[] smdParams)
at
System.Data.SqlServer.Internal.ClrLevelContext.CreateRequestExecutor(String
commandText, CommandType commandType, SmiParameterMetaData[]
parameterMetaData, SmiEventSink eventSink)
at
System.Data.SqlClient.SqlCommand.SetUpSmiRequest(SqlInternalConnectionSmi
innerConnection)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderSmi(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method,
DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior
behavior)
at
System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset,
DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable,
IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 ...
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception,
Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
exception, Boolean breakConnection)
at
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject
stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior,
SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet
bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds,
RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method,
DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader()

[Erland Sommarskog]

Chris N (Chr...@discussions.microsoft.com) writes:
> We have some VS 2008 .Net code which is performing database related
> queries which has been marked for use as a CLR Stored Procedure and
> running in SQL 2005 which runs fine for one user. But we are having
> problems when calling the same stored procedure via multiple clients at
> the same time.

>...

> Now I assume that it's obviously sharing the copy of the .Net assembly
> under the bonnet in SQL and therefore the connection object which is
> static, and that you can't open two dataReaders at once unless you have
> MARS turned on - which you can't seem to do on a Context Connection. We
> can't have 2 connections open at the same time either as it errors
> whenever we try because only one Context Connection is allowed at once.

It seems unbelievable to me that two processes cannot use their
respective context connection simultaneously. That would be a major
roadblock.

I looked through your stack dump, and it seems that SqlClient.TdsParser.Run
throws an exception which is caught by SqlConnection.OnError which in
its turn invokes System.Data.Common.DbDataAdapter.Fill and then it all
goes downhill from there.

That is, the real error is occluded by an exception handler in
the connection object that tries to run a query. Now, what query that
might be, I don't know, or even less how anyone could get the idea.

What you could try is to use Profiler and look the the Exception and
UserMessage events, in case the underlying error is an SQL error.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Adam Machanic]

I'm not sure how your code is structured but it sounds like the problem is
that static connection. I don't see any way to make that work; are you
initializing it in a static constructor, or what? If that's the case then
the first connection that comes in will work, and then never again--it's not
like context connections can be shared by more than one connection; they're
bound to the connection that created them. Static connection objects make
little or no sense in the SQLCLR world, especially when we're talking about
context connections.

--
Adam Machanic
Want to become a SQLCLR expert? Take my full-day seminar, November 2, 2009,
at SQL PASS!
http://summit2009.sqlpass.org/Agenda/PrePostConferenceSessions/MachanicQA.aspx

"Chris N" <Chr...@discussions.microsoft.com> wrote in message
news:ED7C5082-47B3-483A...@microsoft.com...