.net – How does Subsonic handle connections

netsubsonic

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>

Best Answer

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.

Related Solutions

C# – How to enumerate an enum

foreach (Suit suit in (Suit[]) Enum.GetValues(typeof(Suit)))
{
}

Note: The cast to (Suit[]) is not strictly necessary, but it does make the code 0.5 ns faster.

.net – How to solve a connection pool problem between ASP.NET and SQL Server

In most cases connection pooling problems are related to connection leaks. Your application probably doesn't close its database connections correctly and consistently. When you leave connections open, they remain blocked until the .NET garbage collector closes them for you by calling their Finalize() method.

You want to make sure that you are really closing the connection. For example the following code will cause a connection leak, if the code between .Open and Close throws an exception:

var connection = new SqlConnection(connectionString);

connection.Open();
// some code
connection.Close();

The correct way would be this:

var connection = new SqlConnection(ConnectionString);

try
{
     connection.Open();
     someCall (connection);
}
finally
{
     connection.Close();                
}

using (SqlConnection connection = new SqlConnection(connectionString))
{
     connection.Open();
     someCall(connection);
}

When your function returns a connection from a class method make sure you cache it locally and call its Close method. You'll leak a connection using this code for example:

var command = new OleDbCommand(someUpdateQuery, getConnection());

result = command.ExecuteNonQuery();
connection().Close();

The connection returned from the first call to getConnection() is not being closed. Instead of closing your connection, this line creates a new one and tries to close it.

If you use SqlDataReader or a OleDbDataReader, close them. Even though closing the connection itself seems to do the trick, put in the extra effort to close your data reader objects explicitly when you use them.

This article "Why Does a Connection Pool Overflow?" from MSDN/SQL Magazine explains a lot of details and suggests some debugging strategies:

Run sp_who or sp_who2. These system stored procedures return information from the sysprocesses system table that shows the status of and information about all working processes. Generally, you'll see one server process ID (SPID) per connection. If you named your connection by using the Application Name argument in the connection string, your working connections will be easy to find.
Use SQL Server Profiler with the SQLProfiler TSQL_Replay template to trace open connections. If you're familiar with Profiler, this method is easier than polling by using sp_who.
Use the Performance Monitor to monitor the pools and connections. I discuss this method in a moment.
Monitor performance counters in code. You can monitor the health of your connection pool and the number of established connections by using routines to extract the counters or by using the new .NET PerformanceCounter controls.

Best Answer

Related Solutions

C# – How to enumerate an enum

.net – How to solve a connection pool problem between ASP.NET and SQL Server

Related Topic