ASP.NET NHibernate transaction duration

Question

Currently in our ASP.NET app we have 1 session per Request, and create one transaction every time we load or update and object. See below:

    public static T FindById<T>(object id)
    {
        ISession session = NHibernateHelper.GetCurrentSession();
        ITransaction tx = session.BeginTransaction();
        try
        {
            obj = session.Get<T>(id);
            tx.Commit();
        }
        catch
        {
            session.Close();
            throw;
        }
        finally
        {
            tx.Dispose();
        }
        return obj;
    }

    public virtual void Save()
    {
        ISession session = NHibernateHelper.GetCurrentSession();
        ITransaction transaction = session.BeginTransaction();
        try
        {
            if (!IsPersisted)
            {
                session.Save(this);
            }
            else
            {
                session.SaveOrUpdateCopy(this);
            }
            transaction.Commit();

        }
        catch (HibernateException)
        {
            if (transaction != null)
            {
                transaction.Rollback();
            }
            if (session.IsOpen)
            {
                session.Close();
            }
            throw;
        }
        finally
        {
            transaction.Dispose();
        }

    }

Obviously this isn't ideal as it means you create a new connection to the database every time you load or save an object, which incurs performance overhead.

Questions:

• If an entity is already loaded in the 1st level cache will the GetTransaction() call open a database connection? I suspect it will...
• Is there a better way to handle our transaction management so there are less transactions and therefore less database connections?

Unfortunately the app code is probably too mature to structure everything like so (with the get and update all in the same transaction):

using(var session = sessionFactory.OpenSession()) 
using(var tx = session.BeginTransaction()) 
{
    var post = session.Get<Post>(1);
    // do something with post
    tx.Commit();
} 

Would it be a terrible idea to create one transaction per Request and commit it at the end of the request? I guess the downside is that it ties up one database connection while non-database operations take place.

Answer 1

One transaction Per Request is concidered as best practice with NHibernate. This pattern is implemented in Sharp Architecture.

But in Nhibernate method BeginTransaction() doest open connection to DB. Connection is opened at first real sql request and closed just after query is executed. So Nhibernate holds open connection for some seconds to perform query. You can verify it by SQL Profiler.

Additionally NHiberante always try to use Sql Servers connection pool and that why opening your connection may be not so expensive.

Answer 2

Would it be a terrible idea to create one transaction per Request and commit it at the end of the request

It wouldn't be terrible but I think it's a poor practice. If there is an error and the transaction is rolled back, I would much rather handle it on the page then at the end of the request. I prefer to use one session per request with as many transactions as I need during the request (typically one).

NHibernate is very conscientious about managing its database connections, you don't need to worry about it in most cases.

I don't like your transaction logic, especially since you kill the session if the transaction fails. And I'm not sure why you're calling SaveOrUpdateCopy. NHibernate will detect if the object needs to be persisted so the IsPersisted check is probably not needed. I use this pattern:

using (var txn = session.BeginTransaction())
{
    try
    {
        session.SaveOrUpdate(this);
        txn.Commit();
    }
    catch (Exception ex)
    {
        txn.Rollback();
        // log
        // handle, wrap, or throw
     }
 }