NHibernate Session connection close. Connection timeout error, connection pooling issue

Hibernate in .Net.


Connection/Session may get disposed after method gets called (in finally block). if error occurs you have to make transaction rollback for the attached session else it will leave more connection open and will reach the default connection pool size.

Good practice is to commit the transaction and rollback the transaction on error.

if session is close, this will commit the transaction. but if rollback to be done then there happens session leak.

so best practice is to commit transaction/rollback it. and if you want to have session open for other tranansaction . when submit change.. commit sessoin.transaction and open a new transaction for that session. this will have your session active and you can do other operation.

so If Session throws exception in operation.. you must rollback the transaction.

System.InvalidOperationException: 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

[TestFixture]

    public class ConnectionLeakTest

    {

        private static ISessionFactory sessionFactory;

        static ConnectionLeakTest()

        {

            sessionFactory = new Configuration()

                .Configure()

                .AddAssembly("MyAssembly")

                .BuildSessionFactory();

        }

        [Test]

        public void LeaksConnection()

        {

            var counter = 11;

            for (int i = 0; i < counter; i++)

            {

                using (var scope = new TransactionScope(TransactionScopeOption.Required))

                {

                    using (var session = sessionFactory.OpenSession())

                    {

                        var blah = session.CreateCriteria<User>().List<User>();

                    }

                }

            }

        }

    }

 Your transaction is properly committed if you call the Complete method of the TransactionScope and it is indeed rolled back if you do not call the Complete method. The only problem (and it’s a major one obviously) is that you’re connection will leak when the transaction is rolled back.

public void LeaksConnection()

        {

            var counter = 11;

            for (int i = 0; i < counter; i++)

            {

                using (var scope = new TransactionScope(TransactionScopeOption.Required))

                {

                    using (var session = sessionFactory.OpenSession())

                    using (var transaction = session.BeginTransaction())

                    {

                        var blah = session.CreateCriteria<User>().List<User>();

                        transaction.Rollback();

                    }

                }

            }

        }

works fine now.Then the problem goes away. Transactions are correctly rolled back, and none of the connections leak anymore. It’s too bad that we still need to use NHibernate’s transactions only for the sake of being able to rollback in case of failure without leaking the connection

using (var scope = new TransactionScope(TransactionScopeOption.Required))

            {

                using (var session = sessionFactory.OpenSession())

                using (var transaction = session.BeginTransaction())

                {

                    // do what you need to do with the session

                    transaction.Commit();

                }

                scope.Complete();

            }

Read:

After committing a transaction, the NHibernate session replaces it with a new transaction. This means you should keep a reference to the transaction you're committing if you think you'll need it afterward. This is necessary if you need to call transaction.WasCommited. session.Transaction.WasCommitted always returns false.

Reference: http://thatextramile.be/blog/2010/05/avoiding-leaking-connections-with-nhibernate-and-transactionscope/