How does Subsonic handle connections?

Question

In Nhibernate you start a session by creating it during a BeginRequest and close at EndRequest

public class Global: System.Web.HttpApplication
{
    public static ISessionFactory SessionFactory = CreateSessionFactory();

    protected static ISessionFactory CreateSessionFactory()
    {
     return new Configuration()
      .Configure(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "hibernate.cfg.xml"))
      .BuildSessionFactory();
    }

    public static ISession CurrentSession
    {
     get{ return (ISession)HttpContext.Current.Items["current.session"]; }
     set { HttpContext.Current.Items["current.session"] = value; }
    }

    protected void Global()
    {
     BeginRequest += delegate
     {
      CurrentSession = SessionFactory.OpenSession();
     };
     EndRequest += delegate
     {
      if(CurrentSession != null)
       CurrentSession.Dispose();
     };
    }
}

What’s the equivalent in Subsonic?

The way I understand, Nhibernate will close all the connections at endrequest.

Reason: While trouble shooting some legacy code in a Subsonic project I get a lot of MySQL timeouts,suggesting that the code is not closing the connections

MySql.Data.MySqlClient.MySqlException: error connecting: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached. Generated: Tue, 11 Aug 2009 05:26:05 GMT System.Web.HttpUnhandledException: Exception of type 'System.Web.HttpUnhandledException' was thrown. ---> MySql.Data.MySqlClient.MySqlException: error connecting: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached. at MySql.Data.MySqlClient.MySqlPool.GetConnection() at MySql.Data.MySqlClient.MySqlConnection.Open() at SubSonic.MySqlDataProvider.CreateConnection(String newConnectionString) at SubSonic.MySqlDataProvider.CreateConnection() at SubSonic.AutomaticConnectionScope..ctor(DataProvider provider) at SubSonic.MySqlDataProvider.GetReader(QueryCommand qry) at SubSonic.DataService.GetReader(QueryCommand cmd) at SubSonic.ReadOnlyRecord`1.LoadByParam(String columnName, Object paramValue)

My connection string is as follows

<connectionStrings>
    <add name="xx" connectionString="Data Source=xx.net; Port=3306; Database=db; UID=dbuid; PWD=xx;Pooling=true;Max Pool Size=12;Min Pool Size=2;Connection Lifetime=60" />
  </connectionStrings>

Answer 1

It's always a one-time shot unless you specifically wrap your stuff with a "SharedDbConnectionScope". I've seen this before - specifically while testing MySQL on windows - and the problem is that the MySQL driver is buggy and doesn't shut off connections.

I was able to repro this by creating a console app and a basic reader then looping over it - bam. ConnectionPool errors.

Not much of an answer, I know, but what can ya do.

Answer 2

I have run into the same style of error before with using SubSonic incorectly. We had a piece of Javascript that pinged the server every 2 or so minutes just to keep our session alive and when it pinged the server a call to our Membership was made that used a DataReader. Anyway long story short we weren't closing the reader and the little keep alive script would slowly eat up all of our 160 allowed connections even with only a handful of users. You may want to check to see if you are using SubSonic in that way(returning IDataReader from queries and then not disposing of them)

Answer 3

Sadly, I'm getting this with SQL 2005 as well.

I'm sloppily iterating through 4000 users, and probably an average of 5 items below.. something like

foreach (AspnetUsers user in <collection>)
{
  OrderCollection orders = <usersOrderCollection>;
  foreach(Order order in orders)
  {
    // do stuff
  }
}

I'm paraphraing, but you get the idea. And yes, it's sloppy, runs locally on 2005 Express just fine, but on SQL 2005 on my prod server, I get server not found or too busy or too many connections.

Running SubSonic 2.0.3, and although I love Rob Conery, well ... nah, I love Rob Conery.

I wonder how Thread.Sleep(100) will work. This is just a one time batch fix so when its done, it's done.