nhibernate-issues

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

[NHibernate J. <nh...@gm...>]

Database connection is not closed after rollback in TransactionScope
--------------------------------------------------------------------

                 Key: NH-2107
                 URL: http://nhjira.koah.net/browse/NH-2107
             Project:  NHibernate
          Issue Type: Bug
          Components: Core
    Affects Versions: 2.1.2.GA
            Reporter: William Lai
            Priority: Critical


.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://nhjira.koah.net/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira

        

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

[NHibernate J. <nh...@gm...>]

    [ http://nhjira.koah.net/browse/NH-2107?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=19120#action_19120 ] 

Fabio Maulo commented on NH-2107:
---------------------------------

what is  NHibernateHelper.OpenSession() ?
are you using NH's transaction or not ?

> Database connection is not closed after rollback in TransactionScope
> --------------------------------------------------------------------
>
>                 Key: NH-2107
>                 URL: http://nhjira.koah.net/browse/NH-2107
>             Project:  NHibernate
>          Issue Type: Bug
>          Components: Core
>    Affects Versions: 2.1.2.GA
>            Reporter: William Lai
>            Priority: Critical
>
> .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://nhjira.koah.net/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira

        

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

[Nate S. (JIRA) <nh...@gm...>]

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

Nate Spillson commented on NH-2107:
-----------------------------------

We tried using the AdoNetTransactionFactory as suggested by Yves but we're still able to reproduce the issue on oracle 11g.

> 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

        

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

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

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

Sam Ritchie commented on NH-2107:
---------------------------------

This is an issue for us also on 2.1.2. AdoNetTransactionFactory appears to operate as a workaround.

IMO a connection leak is not a trivial 'cosmetic' problem - in our environment, this caused a production service to stop working.

> 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

        

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

[Julian M. (JIRA) <nh...@gm...>]

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

Julian Maughan commented on NH-2107:
------------------------------------

Observations:
1) In your above example, you are not disposing the NHibernate Session before Complete() is called. So try the 'using' syntax for the ISession to ensure that it is.
2) In the attached example you are calling TransactionScope.Complete() before disposing the Session. Instead try:

using (var txScope = new TransactionScope()) 
{
	using (var session = factory.OpenSession()) 
	{
		session.Save(new Entity());
		session.Flush();
		// Not here --> txScope.Complete() 
	} // ISession.Dispose()

	txScope.Complete();
}

> 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

        

[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

        

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

[Fabio M. (JIRA) <nh...@gm...>]

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

Fabio Maulo commented on NH-2107:
---------------------------------

Ritesh,
and what happen if you use the NH's as we are recommending ?

> 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

        

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

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

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

Sam Ritchie commented on NH-2107:
---------------------------------

I'm a bit concerned the 'solution' that appears to be implicitly touted here is "change your design so that you dispose the ISession before calling transaction.Complete()". That's not a solution - it's a workaround. Most of us are no doubt discovering this bug by managing the session lifecycle external to the transactional code, eg via dependency injection. NServiceBus in particular is causing us issues as the transaction lifecycle is also external to user code.

To illustrate the conceptual issue I have with this design approach, allow me to inflict terrible ASCII art on you all. This is a sequence diagram showing how you use NHibernate transactions:

ISession  ITransaction
     |                |
    | |              |
    | |---->| |
    | |<----| |
    | |              |
    | |---->| |
    | |<----| |
    | |              |
     |                |
 
And this is how it appears you're 'meant' to use a TransactionScope

ISession  TransactionScope
     |                |
     |               | |
    | |             | |
    | |             | |
     |               | |
     |                |

I'm not sure I understand why the different transaction technology requires a complete reversal of the respective lifecycles - this is going to be extraordinarily non-obvious to pretty much everyone who stumbles across it.
 

> 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

        

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

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

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

Sam Ritchie commented on NH-2107:
---------------------------------

Sorry - let's try the non-ASCII version:

http://www.websequencediagrams.com/cgi-bin/cdraw?lz=VXNlci0-SVNlc3Npb246IENyZWF0ZSAACQcKYWN0aXZhdGUgABoICgAjCC0-SVRyYW5zYWN0AC0MAAkLADILAAoMACsMAHAKIDogQ29tcGxlADUPZGUALxcAchUgAA17VXNlcjogQ2xvc2UvRGlzcG9zAII9CgCBOgwAgmQH&s=default

http://www.websequencediagrams.com/cgi-bin/cdraw?lz=VXNlci0-VHJhbnNhY3Rpb25TY29wZTogQ3JlYXRlIAAOCwphY3RpdgAGDwApBQoAPwZJU2Vzc2lvbgA0CQAJBwAvCgAaCAoAIwgtPlVzZXI6IENsb3NlL0Rpc3BvcwAuCmRlACgSAIEXGW9tbWl0AIEjDQA0CwCBUBA&s=default

> 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

        

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

[Fabio M. (JIRA) <nh...@gm...>]

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

Fabio Maulo commented on NH-2107:
---------------------------------

What happen if you use even NH's transaction ?
Which is the reason because you are no using even the NH's transaction instead only the session ?

> 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

        

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

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

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

Sam Ritchie commented on NH-2107:
---------------------------------

Sorry Fabio, are you saying that explicitly using an NH transaction within a TransactionScope should work properly? This is not normal usage of a TransactionScope - most developers would expect the resource managers to participate in the ambient transaction without further intervention.

eg I regard this as idiomatic usage of a TransactionScope:

using (var transaction = new TransactionScope())
{
    SomeObj obj = session.Get<SomeObj>(id); // I expect this line to start a local transaction on SQL Server 2005
    messageQueue.Send(obj, MessageQueueTransactionType.Automatic); // I expect this line to escalate the transaction to MSDTC
    transaction.Complete(); // commit the transaction on SQL & MSMQ
} // exception will roll back the transaction on SQL & MSMQ

If I was using a straight SqlConnection/SqlCommand instead of NH I would not create an SqlTransaction at all, the TransactionScope would handle it for me. I hope this explains where I'm coming from.

> 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

        

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

[William L. (JIRA) <nh...@gm...>]

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

William Lai commented on NH-2107:
---------------------------------

Our final solution is to close the connection explicitly in our code, i.e. set "connection.release_mode" to "on_close". However, we need to refactor many of our codes in order to do this. We need to add code to close connection in all places where there is db access, even we only want to retrieve but not saving any data. 

We start using nhibernate from version 1.2.1 and we find that it is easy to implement. But now we need to be more carefully about the design pattern. This solution make our code less clean and more bug prone.

> 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

        

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

[Fabio M. (JIRA) <nh...@gm...>]

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

Fabio Maulo commented on NH-2107:
---------------------------------

Nobody answered my questions.

What happen if you use even NH's transaction ?
Which is the reason because you are no using even the NH's transaction instead only the session ?

Note: NH's transaction <= the transaction of NH.

> 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

        

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

[William L. (JIRA) <nh...@gm...>]

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

William Lai commented on NH-2107:
---------------------------------

I can't tell whether it is ok or not in 2.1.2GA but we use NH's transaction in 1.2.1GA and there is no problem. However, in our new project, we need to use TransactionScope to support MSDTC so we migrate to the new version and find out the problem.

> 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

        

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

[Fabio M. (JIRA) <nh...@gm...>]

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

Fabio Maulo commented on NH-2107:
---------------------------------

You can use TransactionScope and you should continue using NH's transaction too.
Where you have read that the Usage of TransactionScope mean the avoid of usage of NH's transaction ?

In app. where I'm working I'm using NH's and NH's transaction and the code where I'm beginning a NH's transaction does not have to know about DTC and for that reason it works. I can add a DTC where I need it without touch existing code, I don't have to remove the usage the NH's transaction.

> 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

        

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

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

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

Sam Ritchie commented on NH-2107:
---------------------------------

>From MSDN (http://msdn.microsoft.com/en-us/library/ms172152%28VS.90%29.aspx):

"The TransactionScope class provides a simple way to mark a block of code as participating in a transaction, without requiring you to interact with the transaction itself. A transaction scope can select and manage the ambient transaction automatically. Due to its ease of use and efficiency, it is recommended that you use the TransactionScope class when developing a transaction application.

In addition, you do not need to enlist resources explicitly with the transaction. Any System.Transactions resource manager (such as SQL Server 2005) can detect the existence of an ambient transaction created by the scope and automatically enlist."

If NHibernate supports TransactionScope, this is how I would expect it to operate - I should never need to use NH transactions directly.

> 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

        

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

[Jason Y. (JIRA) <nh...@gm...>]

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

Jason Young commented on NH-2107:
---------------------------------

I agree with Sam Ritchie's comments, you should NOT be required to interact with the nhibernate session. Can anyone explain why this is NOT and issue with SQL2008. The issue only occurs on SQL2005 for me?

> 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

        

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

[Ricardo P. (JIRA) <nh...@gm...>]

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

Ricardo Peres commented on NH-2107:
-----------------------------------

Davy Brion described the problem one year ago:

http://davybrion.com/blog/2010/05/avoiding-leaking-connections-with-nhibernate-and-transactionscope/

I don't know if NHibernate can be changed to use only ambient transactions without the need for its own transactions, and keep things from leaking, but it is a known fact that, since ever, we need its own transactions.

> 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