"Connection is closed" error when using consecutive TransactionScopes within same NHibernate session

[Norman]

Hi,

We're currently having an issue that seems to be caused because the
Transaction.TransactionCompleted event is fired on a different thread
when using TransactionScope.

When NHibernate handles this event it closes the database connection.
However by this time another transaction and database call has already
started on the main thread. NHibernate has opened the connection for
the second call and expects it to remain open.

Detailed information is below.

Any comments would be appreciated.

Thanks,

Norman

Timeline:

MAIN THREAD: SessionFactory.OpenSession() // without passing a
connection

MAIN THREAD: using (new TransactionScope())
MAIN THREAD: SomeDatabaseWork();
MAIN THREAD: // NHibernate opens connection
MAIN THREAD: // NHibernate does database work
MAIN THREAD: // NHibernate closes connection
MAIN THREAD: // SomeDatabaseWork(); returns
MAIN THREAD: TransactionScope.Complete();
MAIN THREAD: TransactionScope.Dispose(); // from using

MAIN THREAD: using (new TransactionScope())
MAIN THREAD: MoreDatabaseWork();
MAIN THREAD: // NHibernate opens connection
ANOTHER THREAD: Transaction.TrasactionCompleted event fires
ANOTHER THREAD: Nhibernate event handler closes connection
MAIN THREAD: // NHibernate does database work --> EXCEPTION
HERE

Exception:

NHibernate.ADOException was unhandled by user code
Message="could not execute query\r\n[ select * from blah]"
Source="NHibernate"
SqlString="select * from blah"
StackTrace:
at NHibernate.Loader.Loader.DoList(ISessionImplementor session,
QueryParameters queryParameters)
at
NHibernate.Loader.Loader.ListIgnoreQueryCache(ISessionImplementor
session, QueryParameters queryParameters)
at NHibernate.Loader.Loader.List(ISessionImplementor session,
QueryParameters queryParameters, ISet`1 querySpaces, IType[]
resultTypes)
at
NHibernate.Loader.Criteria.CriteriaLoader.List(ISessionImplementor
session)
at NHibernate.Impl.SessionImpl.List(CriteriaImpl criteria,
IList results)
at NHibernate.Impl.CriteriaImpl.List(IList results)
at NHibernate.Impl.CriteriaImpl.List[T]()
at
NHibernate.Linq.Visitors.ImmediateResultsVisitor`1.GetElementList(MethodCallExpression
call, Int32 count)
at
NHibernate.Linq.Visitors.ImmediateResultsVisitor`1.HandleFirstOrDefaultCall(MethodCallExpression
call)
at
NHibernate.Linq.Visitors.ImmediateResultsVisitor`1.VisitMethodCall(MethodCallExpression
call)
at NHibernate.Linq.Visitors.ExpressionVisitor.Visit(Expression
exp)
at
NHibernate.Linq.Visitors.NHibernateExpressionVisitor.Visit(Expression
exp)
at
NHibernate.Linq.Visitors.ImmediateResultsVisitor`1.GetResults(MethodCallExpression
expr)
at
NHibernate.Linq.Visitors.RootVisitor.HandleImmediateResultsCall(MethodCallExpression
call)
at
NHibernate.Linq.Visitors.RootVisitor.VisitMethodCall(MethodCallExpression
expr)
at NHibernate.Linq.Visitors.ExpressionVisitor.Visit(Expression
exp)
at
NHibernate.Linq.Visitors.NHibernateExpressionVisitor.Visit(Expression
exp)
at
NHibernate.Linq.Visitors.NHibernateQueryTranslator.TranslateInternal(Expression
expression)
at
NHibernate.Linq.Visitors.NHibernateQueryTranslator.Translate(Expression
expression, QueryOptions queryOptions)
at
NHibernate.Linq.NHibernateQueryProvider.TranslateExpression(Expression
expression)
at NHibernate.Linq.NHibernateQueryProvider.Execute(Expression
expression)
at
NHibernate.Linq.QueryProvider.System.Linq.IQueryProvider.Execute[T]
(Expression expression)
at System.Linq.Queryable.FirstOrDefault[TSource](IQueryable`1
source)
at our code
at SyncInvokeGetGroupWellStates(Object , Object[] , Object[] )
at
System.ServiceModel.Dispatcher.SyncMethodInvoker.Invoke(Object
instance, Object[] inputs, Object[]& outputs)
at
System.ServiceModel.Dispatcher.DispatchOperationRuntime.InvokeBegin(MessageRpc&
rpc)
InnerException: System.InvalidOperationException
Message="Invalid operation. The connection is closed."
Source="System.Data.OracleClient"
StackTrace:
at
System.Data.OracleClient.OracleConnection.GetOpenInternalConnection()
at
System.Data.OracleClient.OracleConnection.get_ErrorHandle()
at
System.Data.OracleClient.OracleDataReader.FillColumnInfo()
at
System.Data.OracleClient.OracleDataReader..ctor(OracleCommand command,
OciStatementHandle statementHandle, String statementText,
CommandBehavior commandBehavior)
at
System.Data.OracleClient.OracleCommand.ExecuteReader(CommandBehavior
behavior)
at
System.Data.OracleClient.OracleCommand.ExecuteDbDataReader(CommandBehavior
behavior)
at
System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
at
NHibernate.AdoNet.AbstractBatcher.ExecuteReader(IDbCommand cmd)
at NHibernate.Loader.Loader.GetResultSet(IDbCommand st,
Boolean autoDiscoverTypes, Boolean callable, RowSelection selection,
ISessionImplementor session)
at NHibernate.Loader.Loader.DoQuery(ISessionImplementor
session, QueryParameters queryParameters, Boolean returnProxies)
at
NHibernate.Loader.Loader.DoQueryAndInitializeNonLazyCollections(ISessionImplementor
session, QueryParameters queryParameters, Boolean returnProxies)
at NHibernate.Loader.Loader.DoList(ISessionImplementor
session, QueryParameters queryParameters)
InnerException:

Possible Workarounds:

1. Provide NHibernate with a connection when opening a session; or
2. Create a TransactionScope for duration of session (not ideal)

[Norman]

This seems to be an NHibernate bug that is specific to Oracle.

I've raised a Jira (NH-2176) for this and provided a failing unit
test:

http://216.121.112.228/browse/NH-2176

[Iain]

I think I am having the same issue. I have noticed that I sometimes
get an exception on another thread. The other thread seems to be setup
by the distributed transaction.

Perhaps try the code I pasted into my issue and see what happens ...
http://groups.google.com/group/nhusers/browse_thread/thread/de1e4fc825b41a03

Option 2, do you mean create a new session for each transaction? That
is a good idea to me. I have been unable to use the same session, as
randomly the session decides it cannot disconnect as the transaction
has not been commited.

[Iain]

I have done a bit more testing and sometimes I get 3 threads hitting
the one session when I use sessions. As the session is not thread
safe, this often causes strange behavior, such as disconnecting a
connection that has just been open for the next job, or modifying a
list while the other thread is enumerating it.

I think the best solution is to always use a session per transaction.
That will avoid most of the issues. But I still find I get the
occasional exception from multiple threads hitting
StatefulPersistenceContext.AfterTransactionCompletion. I put a lock in
SessionImpl.AfterTransactionCompletion which fixed that. Although I
would prefer not to modify the source code .... any ideas?

On Apr 15, 10:20 pm, Iain <iain.robe...@gmail.com> wrote:
> I think I am having the same issue. I have noticed that I sometimes
> get an exception on another thread. The other thread seems to be setup
> by the distributed transaction.
>

> Perhaps try the code I pasted into my issue and see what happens ...http://groups.google.com/group/nhusers/browse_thread/thread/de1e4fc82...

[Juan]

I just experienced this same behavior. DTC is committing on a callback
thread. If the timing is right, the main thread won't see the
disconnect and assume the connection is still alive but it will puke
when it tries to use.

I run into this post because I wanted to porobably open a JIRA ticked,
but if it has already been done, I know I'm not the only one with the
issue

> 2. Create aTransactionScopefor duration of session (not ideal)

--
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.

[Juan]

I forgot to mention the easy workaround is to change the
connection.release_mode property to on_close. That way your connection
won't be closed after each commit

On Apr 9, 8:05 am, Norman <d...@cheerful.com> wrote: