Connection leak when ambient transaction aborts

344 views
Skip to first unread message

Iain

unread,
Apr 15, 2010, 12:48:27 AM4/15/10
to nhusers
Hi All,

I am having a connection leak when we using the ambient transaction,
and the transaction is aborted. That is, I get the exception:
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.

I am guessing something is holding onto a reference to the connection,
or an internal part of the connection. As adding a watch to following
and debug breaking within the transaction also causes this issue.
((System.Data.SqlClient.SqlInternalConnectionTds)
((System.Data.SqlClient.SqlConnection)
(session.Connection))._innerConnection)._connectionPool.Count

Is this a known issue or unsupported? Currently our workaround is to
promote the transaction to a distributed transaction which seems to
work ok, as per this article: http://davybrion.com/blog/2010/03/msdtc-woes-with-nservicebus-and-nhibernate/

Here is the unit test that re-produces the issue:

[Test]
public void CanUseSystemTransactionsToAbort()
{
for (var i = 0; i < 200; i++)
{
using (ISession session = sessions.OpenSession())
using (TransactionScope tx = new
TransactionScope(TransactionScopeOption.RequiresNew))
{
W s = new W();
session.Save(s);
session.Flush();
}
}
}

Cheers,

Iain

Iain

unread,
Apr 15, 2010, 8:13:07 AM4/15/10
to nhusers
UPDATE: I've since found that the solution of promoting to a
distributed transaction brings up another issue. It appears that the
distributed transaction sets up a call on another thread, and it is
from this code that the nHibernate SessionImpl.AfterTransaction is
called. This then causes an exception that a collection is modified
whilst being iterated over. Still this is my best solution unless any
one has a better idea??

On Apr 15, 2:48 pm, Iain <iain.robe...@gmail.com> wrote:
> Hi All,
>
> I am having a connection leak when we using the ambient transaction,
> and the transaction is aborted. That is, I get the exception:
> 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.
>
> I am guessing something is holding onto a reference to the connection,
> or an internal part of the connection. As adding a watch to following
> and debug breaking within the transaction also causes this issue.
>                 ((System.Data.SqlClient.SqlInternalConnectionTds)
> ((System.Data.SqlClient.SqlConnection)
> (session.Connection))._innerConnection)._connectionPool.Count
>
> Is this a known issue or unsupported? Currently our workaround is to
> promote the transaction to a distributed transaction which seems to

> work ok, as per this article:http://davybrion.com/blog/2010/03/msdtc-woes-with-nservicebus-and-nhi...

Diego Mijelshon

unread,
Apr 15, 2010, 9:59:39 AM4/15/10
to nhu...@googlegroups.com
I'm not big on ambient transactions... but your I think there's a problem with your code.
IMO, it should look like this:

       using (TransactionScope tx = new TransactionScope(TransactionScopeOption.RequiresNew))
       using (ISession session = sessions.OpenSession())
       using (ITransaction nhtx = session.BeginTransaction())

       {
           W s = new W();
           session.Save(s);
           session.Flush();
           nhtx.Commit();
       }
 
Try it and see if there's any difference.

   Diego



--
You received this message because you are subscribed to the Google Groups "nhusers" group.
To post to this group, send email to nhu...@googlegroups.com.
To unsubscribe from this group, send email to nhusers+u...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/nhusers?hl=en.


Iain

unread,
Apr 15, 2010, 10:55:57 AM4/15/10
to nhusers
I haven't really looked much into the session.BeginTransaction(). I
need use nHibernate with MSMQ and WCF and my feeling is that
session.BeginTransaction will not hock into the WCF transaction. Am I
wrong? I can't find much on it.

> >http://davybrion.com/blog/2010/03/msdtc-woes-with-nservicebus-and-nhi...


>
> > Here is the unit test that re-produces the issue:
>
> > [Test]
> > public void CanUseSystemTransactionsToAbort()
> > {
> >    for (var i = 0; i < 200; i++)
> >    {
> >        using (ISession session = sessions.OpenSession())
> >        using (TransactionScope tx = new
> > TransactionScope(TransactionScopeOption.RequiresNew))
> >        {
> >            W s = new W();
> >            session.Save(s);
> >            session.Flush();
> >        }
> >    }
> > }
>
> > Cheers,
>
> > Iain
>
> > --
> > You received this message because you are subscribed to the Google Groups
> > "nhusers" group.
> > To post to this group, send email to nhu...@googlegroups.com.
> > To unsubscribe from this group, send email to

> > nhusers+u...@googlegroups.com<nhusers%2Bunsu...@googlegroups.com >

Diego Mijelshon

unread,
Apr 15, 2010, 11:05:09 AM4/15/10
to nhu...@googlegroups.com
NHibernate transactions will enlist in ambient transactions. And you *must* use transactions for NH data access.
Try that and tell us if it works.

   Diego


To unsubscribe from this group, send email to nhusers+u...@googlegroups.com.

Iain

unread,
Apr 15, 2010, 7:47:25 PM4/15/10
to nhusers
Kudos to you Diego, that fixed it!


On Apr 16, 1:05 am, Diego Mijelshon <di...@mijelshon.com.ar> wrote:
> NHibernate transactions will enlist in ambient transactions. And you **must**
> > > > nhusers+u...@googlegroups.com<nhusers%2Bunsu...@googlegroups.com­>
> > <nhusers%2Bunsu...@googlegroups.com<nhusers%252Bunsubscribe@googlegroup­s.com>>
> > > > .
> > > > For more options, visit this group at
> > > >http://groups.google.com/group/nhusers?hl=en.
>
> > --
> > You received this message because you are subscribed to the Google Groups
> > "nhusers" group.
> > To post to this group, send email to nhu...@googlegroups.com.
> > To unsubscribe from this group, send email to
> > nhusers+u...@googlegroups.com<nhusers%2Bunsu...@googlegroups.com­>
> > .
> > For more options, visit this group at
> >http://groups.google.com/group/nhusers?hl=en.- Hide quoted text -
>
> - Show quoted text -

Iain

unread,
Apr 16, 2010, 12:13:20 AM4/16/10
to nhusers
Here is my test code:
using (var session = _sessionFactory.OpenSession())
{
using (var scope = new
TransactionScope(TransactionScopeOption.RequiresNew))
{
using (var tx = _target.BeginTransaction())
{
dog = GenerateDog();
session.Save(dog);
session.Flush();
}
scope.Dispose();
}
}
I have found that if you use the ambient transaction and begin
transaction, you cannot use nHibernate Transaction Rollback. If you
do, you get an exception when you try and Complete or Dispose the
outer scope. Instead just let the nHibernate transaction dispose.

And if you do not Commit the nHibernate transaction, you MUST dispose
the ambient transaction. If you call Complete you get an exception.

Does this sound ok?
> > >http://groups.google.com/group/nhusers?hl=en.-Hide quoted text -
>
> > - Show quoted text -
>
> --
> You received this message because you are subscribed to the Google Groups "nhusers" group.
> To post to this group, send email to nhu...@googlegroups.com.
> To unsubscribe from this group, send email to nhusers+u...@googlegroups.com.
> For more options, visit this group athttp://groups.google.com/group/nhusers?hl=en.- Hide quoted text -

Diego Mijelshon

unread,
Apr 16, 2010, 7:36:10 AM4/16/10
to nhu...@googlegroups.com
It sounds OK enough to me... remember that the most common usage (in production code, not unit tests) is doing commit/complete in your code and only having exceptions rollback.

   Diego

Norman

unread,
Apr 21, 2010, 8:57:12 AM4/21/10
to nhusers
Diego - can you please explain why both "new TransactionScope()" and
"session.BeginTransaction()" are required? My understanding is that
when using TransactionScope there is no need to create a transaction
with the session as one is created automatically.

Thanks.

Diego Mijelshon

unread,
Apr 21, 2010, 9:38:02 AM4/21/10
to nhu...@googlegroups.com
Norman,

It's simple: your assumption is not correct.
NHibernate transactions automatically enlist in the scope transactions, but they are still needed (i.e. having a TransactionScope does NOT create a NH transaction)

   Diego

Carlos cubas

unread,
Apr 21, 2010, 9:53:36 AM4/21/10
to nhu...@googlegroups.com
It is a feature that would be nice to add to NHibernate though. The auto creation of NHibernate transaction if an ambient transaction is detected.  Fabio asked me to put in a Jira a while back but Jira was down then and I forgot to do it.  I'll submit one today.

-Carlos
 
Practice makes perfect, but if no one is perfect, why practice?




Date: Wed, 21 Apr 2010 10:38:02 -0300
Subject: Re: [nhusers] Re: Connection leak when ambient transaction aborts
From: di...@mijelshon.com.ar
To: nhu...@googlegroups.com

Norman

unread,
Apr 22, 2010, 3:45:13 AM4/22/10
to nhusers
Thanks for your quick responses on this.

However I still don't understand why an NHibernate transaction is
required in addition to TransactionScope. I've written a test (below)
and TransactionScope works without the need to create an NHibernate
transaction.

The result is that Richard and Simon are saved to the database, but
Norman is rolled back.

What additional benefit do you get from also calling
"session.BeginTransaction()"?


public void TransactionScopeTest()
{
Person norman = new Person() { Name = "Norman" };
Person richard = new Person() { Name = "Richard" };
Person simon = new Person() { Name = "Simon" };

using (ISession s1 = OpenSession())
{
using (TransactionScope scope = new
TransactionScope())
{
s1.Save(norman);

// do not complete so automatically rollback
}
}

using (ISession s2 = OpenSession())
{
using (TransactionScope scope = new
TransactionScope())
{
s2.Save(richard);

scope.Complete();
}
}

using (ISession s3 = OpenSession())
{
s3.Save(simon);
}
}



On Apr 21, 2:38 pm, Diego Mijelshon <di...@mijelshon.com.ar> wrote:
> Norman,
>
> It's simple: your assumption is not correct.
> NHibernate transactions automatically enlist in the scope transactions, but
> they are still needed (i.e. having a TransactionScope does NOT create a NH
> transaction)
>
>    Diego
>
>
>
>
>
> On Wed, Apr 21, 2010 at 09:57, Norman <d...@cheerful.com> wrote:
> > Diego - can you please explain why both "new TransactionScope()" and
> > "session.BeginTransaction()" are required? My understanding is that
> > when using TransactionScope there is no need to create a transaction
> > with the session as one is created automatically.
>
> > Thanks.
>
> > --
> > You received this message because you are subscribed to the Google Groups
> > "nhusers" group.
> > To post to this group, send email to nhu...@googlegroups.com.
> > To unsubscribe from this group, send email to
> > nhusers+u...@googlegroups.com<nhusers%2Bunsu...@googlegroups.com­>
> > .
> > For more options, visit this group at
> >http://groups.google.com/group/nhusers?hl=en.
>
> --
> You received this message because you are subscribed to the Google Groups "nhusers" group.
> To post to this group, send email to nhu...@googlegroups.com.
> To unsubscribe from this group, send email to nhusers+u...@googlegroups.com.
> For more options, visit this group athttp://groups.google.com/group/nhusers?hl=en.- Hide quoted text -
>
> - Show quoted text -

John Davidson

unread,
Apr 22, 2010, 6:35:41 AM4/22/10
to nhu...@googlegroups.com
The test is not valid. That it works the way you expect is coincidence, rather than by design. Try adding a few seconds delay after session 2 and before opening session 3. If you are using an automatic flush then it will probably occur before before the session 3 open and that save will fail.

John Davidson

Diego Mijelshon

unread,
Apr 22, 2010, 8:18:57 AM4/22/10
to nhu...@googlegroups.com
Let me guess: you're using identity.

   Diego

Carlos cubas

unread,
Apr 22, 2010, 10:14:05 AM4/22/10
to nhu...@googlegroups.com


-Carlos
 
Practice makes perfect, but if no one is perfect, why practice?




Date: Thu, 22 Apr 2010 09:18:57 -0300

Subject: Re: [nhusers] Re: Connection leak when ambient transaction aborts
From: di...@mijelshon.com.ar
To: nhu...@googlegroups.com

Norman

unread,
May 17, 2010, 7:19:14 AM5/17/10
to nhusers
I’ve finally had chance to attempt to fix my original issue (see link)
with the solution described by Diego to use both TransactionScope and
BeginTransaction().

Unfortunately, with Oracle the following two test cases fail with
Oracle exceptions as shown. (Both pass with SQL Server.)

Any comments would be appreciated.

Thanks.

Link to original problem: http://groups.google.com/group/nhusers/browse_thread/thread/160bad2228c15fc6?fwc=1


------------------------


[Test]
public void
CanBeginTransactionWithinTransactionScopeWhenSessionIsCreatedBeforeTransactionScope()
{
using (ISession s = OpenSession())
{
using (TransactionScope scope = new
TransactionScope())
{
using (ITransaction tx = s.BeginTransaction()) //
=> Exception here
{
tx.Commit();
}

scope.Complete();
}
}
}


*****
NHibernate.Test.NHSpecificTest.NH2176.Fixture.CanBeginTransactionWithinTransactionScopeWhenSessionIsCreatedBeforeTransactionScope
12:06:03,742 ERROR AdoTransaction:144 - Begin transaction failed
System.InvalidOperationException: OracleConnection does not support
parallel transactions.
at
System.Data.OracleClient.OracleInternalConnection.BeginOracleTransaction(IsolationLevel
il)
at
System.Data.OracleClient.OracleInternalConnection.BeginTransaction(IsolationLevel
il)
at
System.Data.OracleClient.OracleConnection.BeginDbTransaction(IsolationLevel
isolationLevel)
at
System.Data.Common.DbConnection.System.Data.IDbConnection.BeginTransaction(IsolationLevel
isolationLevel)
at NHibernate.Transaction.AdoTransaction.Begin(IsolationLevel
isolationLevel) in \NHibernate-2.1.2.GA-src\src\NHibernate\Transaction
\AdoTransaction.cs:line 134




[Test]
public void
CanBeginTransactionWithinTransactionScopeWhenSessionIsCreatedAfterTransactionScope()
{
using (TransactionScope scope = new TransactionScope())
{
using (ISession s = OpenSession())
{
using (ITransaction tx = s.BeginTransaction()) //
=> Exception here
{
tx.Commit();
}
}

scope.Complete();
}
}


*****
NHibernate.Test.NHSpecificTest.NH2176.Fixture.CanBeginTransactionWithinTransactionScopeWhenSessionIsCreatedAfterTransactionScope
12:05:31,820 ERROR AdoTransaction:144 - Begin transaction failed
System.InvalidOperationException: OracleConnection does not support
parallel transactions.
at
System.Data.OracleClient.OracleInternalConnection.BeginOracleTransaction(IsolationLevel
il)
at
System.Data.OracleClient.OracleInternalConnection.BeginTransaction(IsolationLevel
il)
at
System.Data.OracleClient.OracleConnection.BeginDbTransaction(IsolationLevel
isolationLevel)
at
System.Data.Common.DbConnection.System.Data.IDbConnection.BeginTransaction(IsolationLevel
isolationLevel)
at NHibernate.Transaction.AdoTransaction.Begin(IsolationLevel
isolationLevel) in \NHibernate-2.1.2.GA-src\src\NHibernate\Transaction
\AdoTransaction.cs:line 134

Juan

unread,
May 18, 2010, 12:15:54 AM5/18/10
to nhusers
Norman, if you are using oracle, why are you using microsoft crippled
implementation? I recommend you try the latest ODP.NET from oracle for
better compatibility.

Cubas, I thought distributed transactions (transactionscope) didn't
need an explicit NHibernate/ADO transaction created, this should be
handled by the DTC and the resource manager, in this case OraMTS. I'm
not sure what is the behavior with other DBs, but an implicit
transaction is created in Oracle.

On May 17, 7:19 am, Norman <d...@cheerful.com> wrote:
> I’ve finally had chance to attempt to fix my original issue (see link)
> with the solution described by Diego to use both TransactionScope and
> BeginTransaction().
>
> Unfortunately, with Oracle the following two test cases fail with
> Oracle exceptions as shown. (Both pass with SQL Server.)
>
> Any comments would be appreciated.
>
> Thanks.
>
> Link to original problem:http://groups.google.com/group/nhusers/browse_thread/thread/160bad222...
>
> ------------------------
>
>         [Test]
>         public void
> CanBeginTransactionWithinTransactionScopeWhenSessionIsCreatedBeforeTransact­ionScope()
>         {
>             using (ISession s = OpenSession())
>             {
>                 using (TransactionScope scope = new
> TransactionScope())
>                 {
>                     using (ITransaction tx = s.BeginTransaction()) //
> => Exception here
>                     {
>                         tx.Commit();
>                     }
>
>                     scope.Complete();
>                 }
>             }
>         }
>
> *****
> NHibernate.Test.NHSpecificTest.NH2176.Fixture.CanBeginTransactionWithinTran­sactionScopeWhenSessionIsCreatedBeforeTransactionScope
> 12:06:03,742 ERROR AdoTransaction:144 - Begin transaction failed
> System.InvalidOperationException: OracleConnection does not support
> parallel transactions.
>    at
> System.Data.OracleClient.OracleInternalConnection.BeginOracleTransaction(Is­olationLevel
> il)
>    at
> System.Data.OracleClient.OracleInternalConnection.BeginTransaction(Isolatio­nLevel
> il)
>    at
> System.Data.OracleClient.OracleConnection.BeginDbTransaction(IsolationLevel
> isolationLevel)
>    at
> System.Data.Common.DbConnection.System.Data.IDbConnection.BeginTransaction(­IsolationLevel
> isolationLevel)
>    at NHibernate.Transaction.AdoTransaction.Begin(IsolationLevel
> isolationLevel) in \NHibernate-2.1.2.GA-src\src\NHibernate\Transaction
> \AdoTransaction.cs:line 134
>
>         [Test]
>         public void
> CanBeginTransactionWithinTransactionScopeWhenSessionIsCreatedAfterTransacti­onScope()
>         {
>             using (TransactionScope scope = new TransactionScope())
>             {
>                 using (ISession s = OpenSession())
>                 {
>                     using (ITransaction tx = s.BeginTransaction()) //
> => Exception here
>                     {
>                         tx.Commit();
>                     }
>                 }
>
>                 scope.Complete();
>             }
>         }
>
> *****
> NHibernate.Test.NHSpecificTest.NH2176.Fixture.CanBeginTransactionWithinTran­sactionScopeWhenSessionIsCreatedAfterTransactionScope
> 12:05:31,820 ERROR AdoTransaction:144 - Begin transaction failed
> System.InvalidOperationException: OracleConnection does not support
> parallel transactions.
>    at
> System.Data.OracleClient.OracleInternalConnection.BeginOracleTransaction(Is­olationLevel
> il)
>    at
> System.Data.OracleClient.OracleInternalConnection.BeginTransaction(Isolatio­nLevel
> il)
>    at
> System.Data.OracleClient.OracleConnection.BeginDbTransaction(IsolationLevel
> isolationLevel)
>    at
> System.Data.Common.DbConnection.System.Data.IDbConnection.BeginTransaction(­IsolationLevel

Juan

unread,
May 18, 2010, 12:21:00 AM5/18/10
to nhusers
Iain, connection leaks occur because the session is not properly
disposed/closed. The current pattern I have been using with no
problems is

ISession session = OpenSessio();
try
{
using(var ts = new TransactionScope())
{
...
ts.Complete();
}
}
finally { session.Dispose(); }
On Apr 15, 12:48 am, Iain <iain.robe...@gmail.com> wrote:
> Hi All,
>
> I am having a connection leak when we using the ambient transaction,
> and the transaction is aborted. That is, I get the exception:
> 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.
>
> I am guessing something is holding onto a reference to the connection,
> or an internal part of the connection. As adding a watch to following
> and debug breaking within the transaction also causes this issue.
>                 ((System.Data.SqlClient.SqlInternalConnectionTds)
> ((System.Data.SqlClient.SqlConnection)
> (session.Connection))._innerConnection)._connectionPool.Count
>
> Is this a known issue or unsupported? Currently our workaround is to
> promote the transaction to a distributed transaction which seems to
> work ok, as per this article:http://davybrion.com/blog/2010/03/msdtc-woes-with-nservicebus-and-nhi...
>
> Here is the unit test that re-produces the issue:
>
> [Test]
> public void CanUseSystemTransactionsToAbort()
> {
>     for (var i = 0; i < 200; i++)
>     {
>         using (ISession session = sessions.OpenSession())
>         using (TransactionScope tx = new
> TransactionScope(TransactionScopeOption.RequiresNew))
>         {
>             W s = new W();
>             session.Save(s);
>             session.Flush();
>         }
>     }
>
> }
>
> Cheers,
>
> Iain

Iain

unread,
Jun 1, 2010, 10:19:20 PM6/1/10
to nhusers
Juan, I have tested that scenario and it does leak, unless you promote
the transaction to a distributed transaction. The solution I have
settled on is this one:
using (var scope = new
TransactionScope(TransactionScopeOption.Suppress))
{
using (ISession session = sessionFactory.OpenSession())
{
using (var scope = new
TransactionScope(TransactionScopeOption.RequiresNew))
{
// This will hit the database
session.CreateSQLQuery("SELECT
GETDATE()").ExecuteUpdate();
Transaction.Current.PromoteToDistributed();
}
}
}
Notes:
1. When you call session.Dispose, you cannot be in another
transaction. Otherwise it leaks
2. When you do your work, you must ensure it is a distributed
transaction. Otherwise it leaks
3. You cannot re-use a session, because the transaction completes on
another thread your get a race condition

After my last reply I tested a few other scenarios using the
nHibernate BeginTransaction and they failed. But there is hope, a
colleague pointed out the following blog, he is testing it and I'll
post back if it works.
http://davybrion.com/blog/2010/05/avoiding-leaking-connections-with-nhibernate-and-transactionscope/



On May 18, 2:21 pm, Juan <juan.gree...@gmail.com> wrote:
> Iain,connectionleaks occur because the session is not properly
> disposed/closed. The current pattern I have been using with no
> problems is
>
> ISession session = OpenSessio();
> try
> {
>   using(var ts = new TransactionScope())
>   {
>     ...
>     ts.Complete();
>   }}
>
> finally { session.Dispose(); }
> On Apr 15, 12:48 am, Iain <iain.robe...@gmail.com> wrote:
>
>
>
>
>
> > Hi All,
>
> > I am having aconnectionleakwhen we using the ambient transaction,
> > and the transaction is aborted. That is, I get the exception:
> > System.InvalidOperationException: Timeout expired.  The timeout period
> > elapsed prior to obtaining aconnectionfrom the pool.  This may have
> > occurred because all pooled connections were in use and max pool size
> > was reached.
>
> > I am guessing something is holding onto a reference to theconnection,
> > or an internal part of theconnection. As adding a watch to following
> > and debug breaking within the transaction also causes this issue.
> >                 ((System.Data.SqlClient.SqlInternalConnectionTds)
> > ((System.Data.SqlClient.SqlConnection)
> > (session.Connection))._innerConnection)._connectionPool.Count
>
> > Is this a known issue or unsupported? Currently our workaround is to
> > promote the transaction to a distributed transaction which seems to
> > work ok, as per this article:http://davybrion.com/blog/2010/03/msdtc-woes-with-nservicebus-and-nhi...
>
> > Here is the unit test that re-produces the issue:
>
> > [Test]
> > public void CanUseSystemTransactionsToAbort()
> > {
> >     for (var i = 0; i < 200; i++)
> >     {
> >         using (ISession session = sessions.OpenSession())
> >         using (TransactionScope tx = new
> > TransactionScope(TransactionScopeOption.RequiresNew))
> >         {
> >             W s = new W();
> >             session.Save(s);
> >             session.Flush();
> >         }
> >     }
>
> > }
>
> > Cheers,
>
> > Iain
>
> --
> You received this message because you are subscribed to the Google Groups "nhusers" group.
> To post to this group, send email to nhu...@googlegroups.com.
> To unsubscribe from this group, send email to nhusers+u...@googlegroups.com.

Iain

unread,
Jun 1, 2010, 10:42:18 PM6/1/10
to nhusers
Juan, I should add it leaks if you rollback the transaction, perhaps
due to an exception. If you commit, its fine.

On Jun 2, 12:19 pm, Iain <iain.robe...@gmail.com> wrote:
> Juan, I have tested that scenario and it doesleak, unless you promote
> the transaction to a distributed transaction. The solution I have
> settled on is this one:
> using (var scope = new
> TransactionScope(TransactionScopeOption.Suppress))
> {
>     using (ISession session = sessionFactory.OpenSession())
>     {
>         using (var scope = new
> TransactionScope(TransactionScopeOption.RequiresNew))
>         {
>             // This will hit the database
>             session.CreateSQLQuery("SELECT
> GETDATE()").ExecuteUpdate();
>             Transaction.Current.PromoteToDistributed();
>         }
>     }}
>
> Notes:
> 1. When you call session.Dispose, you cannot be in another
> transaction. Otherwise it leaks
> 2. When you do your work, you must ensure it is a distributed
> transaction. Otherwise it leaks
> 3. You cannot re-use a session, because the transaction completes on
> another thread your get a race condition
>
> After my last reply I tested a few other scenarios using the
> nHibernate BeginTransaction and they failed. But there is hope, a
> colleague pointed out the following blog, he is testing it and I'll
> post back if it works.http://davybrion.com/blog/2010/05/avoiding-leaking-connections-with-n...
> > For more options, visit this group athttp://groups.google.com/group/nhusers?hl=en.-Hide quoted text -
>
> > - Show quoted text -- Hide quoted text -

Graham Bunce

unread,
Jun 3, 2010, 5:49:41 AM6/3/10
to nhusers
>Let me guess: you're using identity.
>
> Diego

Can I ask if this makes a difference to the pattern? I'm NOT using NH
txn's as, like most people, I ran the tests, it worked and so, far
have not had any problems. I'm using GUID.COMB and FlushMode.Never
though. IMO this whole issue needs clearing up and adding to the wiki
as I would think a lot of people would assume TransactionScope would
work correctly without NH needing anything special. Especially if the
solution only works with SQL Server and not Oracle ??(as other posts
seem to suggest above)

Diego Mijelshon

unread,
Jun 3, 2010, 12:43:03 PM6/3/10
to nhu...@googlegroups.com
When you use identity, inserts are immediate because that's the only way to get the persistent Id.
That's why some things seem to work, even when not following the recommended practices.

   Diego 
Reply all
Reply to author
Forward
0 new messages