[nhibernate-issues] [NHJIRA] Commented: (NH-2107) Database connection is not closed after rollback in TransactionScope

[Ritesh R. (JIRA) <nh...@gm...>]

    [ http://216.121.112.228/browse/NH-2107?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=19613#action_19613 ] 

Ritesh Rao commented on NH-2107:
--------------------------------

Here's a proper test case to replicate this issue:

var factory = Fluently.Configure()
                .Database(MsSqlConfiguration.MsSql2005
                              .ConnectionString(x => x.Is(connString))
                              .ProxyFactoryFactory<ProxyFactoryFactory>())
                .Mappings(x => x.FluentMappings.AddFromAssemblyOf<Program>())
                .BuildSessionFactory();

using (var scope = new TransactionScope(TransactionScopeOption.Required))
using (var session = factory.OpenSession())
{
                var customer = session.CreateCriteria<Customer>()
                    .Add(Restrictions.Eq("LastName", "Test"))
                    .SetMaxResults(100)
                    .List<Customer>();
                throw new ApplicationException("Mimiking an application exception");
                scope.Complete(); //Will never be called
}

Open up perfmon, profile the .Net Data Provider for SqlServer performance counters for the app and you'll see the NumberOfStasisConnections counter go up. As previous commenters have pointed out, a workaround is to is to use the AdoNetTransactionFactory instead of AdoNetWithDistributedTransactionFactory, or set the connection's release mode via connection.release_mode to on_close. 

Now here's the kicker... this issue cannot be replicated on SQL Server 2008 and up. It will always occur on SQL Server 2005 (I've tested via SP2 and up) but not for SQL Server 2008.

For what I can tell, when the Rollback method is called on the DistributedTransactionContext, it calls AfterTransactionCompletion method on the SessionImpl, which internally causes the ConnectionManager to perform an AggressiveRelease of the open conenction. The moment, the ConnectionManager calls a CloseConnection (causing the ConenctionProvider to issue a Close on the IDbConnection), the no of stasis connections jump. BUT if the connection's release mode is set to on_close, the aggressive release happens on the callback thread of System.Transaction.TransactionCompleted event (Line 37, AdoNetWithDistributedTransactionFactory.cs). That code path does the exact same thing as the Rollback method in DistributedTransactionContext, but the only difference being the underlying System.Transaction has signalled a complete. 

I'm not sure why it doesn't happen for SQL Server 2008 connections though.   

Hope this helps track down this issue.

Thanks.

> Database connection is not closed after rollback in TransactionScope
> --------------------------------------------------------------------
>
>                 Key: NH-2107
>                 URL: http://216.121.112.228/browse/NH-2107
>             Project:  NHibernate
>          Issue Type: Bug
>          Components: Core
>    Affects Versions: 2.1.2.GA
>            Reporter: William Lai
>         Attachments: ProgramToReproduce.zip
>
>
> .Net Framework Version: 3.5
> Database: SQL Server 2005
> We have just migrated from version 1.2.1 to 2.1.2GA since we need to integrate multiple layer of components using the TransactionScope. After some testing, we find that the database connection increases continuously after every transaction rollback. At the end, all the connections in the connection pool are used up. The problem does not occur in commit case. 
> Here is the testing code:
>             ISession session = null;
>             Test test = null; // Database object
>             using (TransactionScope scope = new TransactionScope())
>             {
>                 session = NHibernateHelper.OpenSession();
>                 Test test2 = new Test();
>                 test2.Name = DateTime.Now.ToString();
>                 session.SaveOrUpdate(test2);
>                 //scope.Complete();
>             }
> After running the above case, a new connection will be used in SQL Server. The connection will not free up in SQL Server until we close our application.
> Any advise for the problem? Thanks in advance.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://216.121.112.228/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira