Asp.Net MVC UNitOfWork and MySQL and Sleeping Connections

Question

I have a MVC web app that is based on the following architecture

Asp.Net MVC2, Ninject, Fluent NHibernate, MySQL which uses a unit of work pattern.

Every connection to MySQL generates a sleep connection that can be seen as an entry in the SHOW PROCESSLIST query results.

Eventually this will spawn enough connections to exeed the app pool limit and crash the web app.

I suspect that the connections are not being disposed correctly.

If this is the case where and how should this happen?

Here is a snapshot of the code that I am using:

public class UnitOfWork : IUnitOfWork
{
    private readonly ISessionFactory _sessionFactory;
    private readonly ITransaction _transaction;
    public ISession Session { get; private set; }

    public UnitOfWork(ISessionFactory sessionFactory)
    {
        _sessionFactory = sessionFactory;
        Session = _sessionFactory.OpenSession();
        Session.FlushMode = FlushMode.Auto;
        _transaction = Session.BeginTransaction(IsolationLevel.ReadCommitted);
    }

    public void Dispose()
    {
        if (Session != null)
        {
            if (Session.IsOpen)
            {
                Session.Close();
                Session = null;
            }
        }
    }

    public void Commit()
    {
        if (!_transaction.IsActive)
        {
            throw new InvalidOperationException("No active transation");
        }
        _transaction.Commit();
        Dispose();
    }

    public void Rollback()
    {
        if (_transaction.IsActive)
        {
            _transaction.Rollback();
        }
    }
}




public interface IUnitOfWork : IDisposable
{
    void Commit();
    void Rollback();
}




public class DataService
{
    int WebsiteId = Convert.ToInt32(ConfigurationManager.AppSettings["Id"]);

    private readonly IKeyedRepository<int, Page> pageRepository;
    private readonly IUnitOfWork unitOfWork;

    public PageService Pages { get; private set; }


    public DataService(IKeyedRepository<int, Page> pageRepository,
        IUnitOfWork unitOfWork)
    {
        this.pageRepository = pageRepository;
        this.unitOfWork = unitOfWork;

        Pages = new PageService(pageRepository);

    }

    public void Commit()
    {
        unitOfWork.Commit();
    }

}


public class PageService
{
    private readonly IKeyedRepository<int, Page> _pageRepository;
    private readonly PageValidator _pageValidation;

    public PageService(IKeyedRepository<int, Page> pageRepository)
    {
        _pageRepository = pageRepository;
        _pageValidation = new PageValidator(pageRepository);
    }

    public IList<Page> All()
    {
        return _pageRepository.All().ToList();
    }

    public Page FindBy(int id)
    {
        return _pageRepository.FindBy(id);
    }
}

Answer 1

Your post does not give any information in which scope UoW's are created.

If it is transient. It won't be disposed at all and this is up to you.

In case of InRequestScope it will be disposed after the GC has collected the HttpContext. But as I told Bob recently in the Ninject Mailing List it is possible to release all objects in the end request event handler of the HttpApplication. I'll add support for this in the next release of Ninject.

Answer 2

I did some investigation into the root cause of this problem. Here is a bit more information and possible solutions:

http://blog.bobcravens.com/2010/11/using-ninject-to-manage-critical-resources/

Enjoy.