Peta poco - ExecuteReader requires an open and available Connection

Question

In rare situations I experience some issues with petapoco.

Sometimes I get the following exception:

System.InvalidOperationException: ExecuteReader requires an open and available Connection. The connection's current state is connecting.
   at System.Data.SqlClient.SqlConnection.GetOpenConnection(String method)
   at System.Data.SqlClient.SqlConnection.ValidateConnectionForExecute(String method, SqlCommand command)
   at System.Data.SqlClient.SqlCommand.ValidateCommand(String method, Boolean async)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
   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()
   at PetaPoco.Database.<Query>d__44`1.MoveNext()

I really dont know what to do about it. Has anyone seen this before? Any suggestions on why it happens and what to do about it?

I cannot reproduce it since it happens rarely(maybe 2-3% of the db calls)

I am using version 5.0.1 of petapoco.

Thanks! :)

EDIT:

I am using the following constructor to instantiate the Database:

public Database(string connectionString, string providerName)
{
    _connectionString = connectionString;
    _providerName = providerName;
    CommonConstruct();
}

I am instantiating it as a singleton using dependency injection like below:

Container.Register(Component.For<IDatabase>().ImplementedBy<Database>().UsingFactoryMethod(() => new Database(configuration.ConnectionString, configuration.DbFactoryProvider)).LifestyleSingleton());

Answer 1

When I first read the post, I thought ah a threading bug. However, I didn't simply want to write it off, as such, without an explanation. FYI, any bug that is inconsistent like this in a web app, is most likely a threading bug.

Ok so why is it a threading bug. You're using PetaPoco as a singleton instance, which means one instance for the whole application. It works, mostly, because PetaPoco has internal smarts to know when to open/close a shared connection, and just by luck itself, the usages of PetaPoco aren't colliding, except 2-3% of the time.

In addition, there's an internal counter (smarts), which when zero will either create a new connection or close the existing one depending on the operation. Now given PetaPoco is not thread-safe, this internal counter could suffer form a threading related issue too, if two or more threads increment/de-increment the counter at the same time etc.

TDLR; In dotnet the general rule is to assume/make static methods thread-safe and everything else not thread-safe, excepted where clearly marked and where it makes sense. Your usage of PetaPoco would work if PetaPoco was thread-safe, but it is not. To fix this issue, create a new instance of PetaPoco for each request; a fairly cheap operation (more so with fluent configuration).

I see you're using PetaPoco 5.0.1, I suggest looking at the latest release as there's a new FluentConfiguration feature. And it just so happens that the documentation page for this feature, at the bottom of the page, has a sample container setup. I would suggest you check this out. Documentation

Happy PetaPoco'er'ing