Unstable transaction exception with Oracle and NHibernate

[ThermIt]

I saw this exception in Spring 1.0.2+NHibernate 1.2 and it's still exist in Spring 1.2+NHibernate 2.0.

When using IIS, Spring (WebServiceExporter), NHibernate and Oracle sometimes I catch this Exception in my project: (with stack trace of inner exceptions)

CannotCreateTransactionException: Could not open Hibernate Session for transaction



   at Spring.Data.NHibernate.HibernateTransactionManager.DoBegin(Object transaction, ITransactionDefinition definition)

   at Spring.Transaction.Support.AbstractPlatformTransactionManager.GetTransaction(ITransactionDefinition definition)

   at Spring.Transaction.Interceptor.TransactionAspectSupport.CreateTransactionIfNecessary(ITransactionAttribute sourceAttr, String joinpointIdentification)

   at Spring.Transaction.Interceptor.TransactionAspectSupport.CreateTransactionIfNecessary(MethodInfo method, Type targetType)

   at Spring.Transaction.Interceptor.TransactionInterceptor.Invoke(IMethodInvocation invocation)

   at Spring.Aop.Framework.AbstractMethodInvocation.Proceed()

   at Usp.Mls.Services.Facade.Aop.WebServiceMethodInterceptor.Invoke(IMethodInvocation invocation)

   at Spring.Aop.Framework.AbstractMethodInvocation.Proceed()

   at Spring.Aop.Framework.Adapter.ThrowsAdviceInterceptor.Invoke(IMethodInvocation invocation)



TransactionException: Begin failed with SQL exception



   at NHibernate.Transaction.AdoTransaction.Begin(IsolationLevel isolationLevel)

   at NHibernate.Impl.SessionImpl.BeginTransaction(IsolationLevel isolationLevel)

   at Spring.Data.NHibernate.HibernateTransactionManager.DoBegin(Object transaction, ITransactionDefinition definition)



OracleException: ORA-01453: SET TRANSACTION must be first statement of transaction.



   at System.Data.OracleClient.OracleConnection.CheckError(OciErrorHandle errorHandle, Int32 rc)

   at System.Data.OracleClient.OracleCommand.Execute(OciStatementHandle statementHandle, CommandBehavior behavior, Boolean needRowid, OciRowidDescriptor& rowidDescriptor, ArrayList& resultParameterOrdinals)

   at System.Data.OracleClient.OracleCommand.ExecuteNonQueryInternal(Boolean needRowid, OciRowidDescriptor& rowidDescriptor)

   at System.Data.OracleClient.OracleCommand.ExecuteNonQuery()

   at System.Data.OracleClient.OracleTransaction..ctor(OracleConnection connection, IsolationLevel isolationLevel)

   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)





Server stack trace:

   at Usp.Mls.Services.Facade.Aop.ExceptionHandlerAdvice.AfterThrowing(MethodInfo method, Object[] args, Object target, Exception e)

   at Spring.Aop.Framework.Adapter.ThrowsAdviceInterceptor.InvokeHandlerMethod(IMethodInvocation invocation, Exception triggeringException, MethodInfo handlerMethod)

   at Spring.Aop.Framework.Adapter.ThrowsAdviceInterceptor.LookupAndInvokeAnyHandler(Exception ex, IMethodInvocation invocation)

   at Spring.Aop.Framework.Adapter.ThrowsAdviceInterceptor.Invoke(IMethodInvocation invocation)

   at Spring.Aop.Framework.AbstractMethodInvocation.Proceed()

   at Spring.Aop.Framework.DynamicProxy.AdvisedProxy.Invoke(Object proxy, Object target, Type targetType, MethodInfo targetMethod, MethodInfo proxyMethod, Object[] args, IList interceptors)

   at CompositionAopProxy_e6d74c44d2194451924342247a8a0ca1.ReadPlaceLicences(Int64 id, RemoteCallContextDTO callContext)

   at SaoRemoteObjectProxy_60e527da1dfc45688e5991b35952ebf1.ReadPlaceLicences(Int64 id, RemoteCallContextDTO callContext)

   at System.Runtime.Remoting.Messaging.StackBuilderSink._PrivateProcessMessage(IntPtr md, Object[] args, Object server, Int32 methodPtr, Boolean fExecuteInContext, Object[]& outArgs)

   at System.Runtime.Remoting.Messaging.StackBuilderSink.PrivateProcessMessage(RuntimeMethodHandle md, Object[] args, Object server, Int32 methodPtr, Boolean fExecuteInContext, Object[]& outArgs)

   at System.Runtime.Remoting.Messaging.StackBuilderSink.SyncProcessMessage(IMessage msg, Int32 methodPtr, Boolean fExecuteInContext)



Exception rethrown at [0]:

   at Usp.Mls.Presentation.Aop.WebServiceMethodInterceptor.Invoke(IMethodInvocation invocation)

   at Spring.Aop.Framework.AbstractMethodInvocation.Proceed()

   at Spring.Aop.Framework.DynamicProxy.AdvisedProxy.Invoke(Object proxy, Object target, Type targetType, MethodInfo targetMethod, MethodInfo proxyMethod, Object[] args, IList interceptors)

   at CompositionAopProxy_b190003f8e3440c7811f723a10a80401.ReadPlaceLicences(Int64 id, RemoteCallContextDTO callContext)

   at Usp.Mls.Presentation.MainModule.Controls.AdministrativeObjectViews.PlaceView.PlaceLicenceViewControl.PlaceLicenceViewControl.UpdateControl(Int64 placeId)

   at ...some client code

Part of server config. It uses .Net Remoting, but it doesn't matter, Exception is still here:

  <object id="TxProxyTemplate" type="Spring.Transaction.Interceptor.TransactionProxyFactoryObject, Spring.Data" abstract="true">
    <property name="PlatformTransactionManager">
      <ref object="TransactionManager" />
    </property>
    <property name="TransactionAttributes">
      <name-values>
        <add key="*" value="PROPAGATION_REQUIRED" />
      </name-values>
    </property>
    <property name="PreInterceptors">
      <list>
        <ref local="ExceptionHandlerAdvice"/> <!-- no db/service usage here -->
        <ref local="WebServiceMethodInterceptor"/> <!-- no db/service usage here -->
      </list>
    </property>
  </object>

  <object id="PlaceServiceExporter" lazy-init="false" type="Spring.Remoting.SaoExporter, Spring.Services">
    <property name="TargetName" value="PlaceServiceProxy"/>
    <property name="ServiceName" value="PlaceService.rem" />
    <property name="Infinite" value="true" />
  </object>

  <object id="PlaceServiceProxy" parent="TxProxyTemplate">
    <property name="Target">
      <object type="Usp.Mls.Services.Facade.PlaceService, Usp.Mls.Services">
        <property name="PlaceDescriptionDao" ref="PlaceDescriptionDao" />
...
      </object>
    </property>
  </object>

	<object singleton="true" id="SessionFactory" type="Usp.Mls.DataAccess.DelegatingLocalSessionFactoryObject, Usp.Mls.DataAccess"> <!-- same problem with LocalSessionFactoryObject -->

		<property name="MappingAssemblies">
			<list>
				<value>Usp.Mls.DataAccess</value>
			</list>
		</property>
		<property name="DbProvider" ref="MainDbProvider" />
		<property name="HibernateProperties">
			<dictionary>
				<entry key="connection.provider" value="NHibernate.Connection.DriverConnectionProvider" />
				<entry key="dialect" value="NHibernate.Dialect.Oracle9Dialect" />
				<entry key="connection.driver_class" value="NHibernate.Driver.OracleClientDriver" />
				<entry key="connection.isolation" value="ReadCommitted" />
				<entry key="hibernate.show_sql" value="true" />
			</dictionary>
		</property>
	</object>

	<object id="MainDbProvider" type="Usp.Mls.Services.DBProviders.CustomDBProvider, Usp.Mls.Services">
		<constructor-arg value="System.Data.OracleClient"/>
		<constructor-arg value="Data Source=DSource;Persist Security Info=True;User ID=TEST_USER;Password=TEST_PASSWORD"/>
		<constructor-arg value="DB Name"/>
	</object>

I can't reproduce this exception, but I see it very often, especially when debugging a lot. Where is my problem? Am I doing something wrong, or it's Spring.Net/NHibernate error?
Sometimes only restart of web service(ie:Web.Config change) helps to temporary fix the problem when it appear too often. It looks like there is a problem with connections pool.

[ThermIt]

Problem was solved by replacing

	<object id="TransactionManager" singleton="true" type="Spring.Data.NHibernate.HibernateTransactionManager, Spring.Data.NHibernate20">
		<property name="SessionFactory">
			<ref object="SessionFactory" />
		</property>
	</object>

with

	<object id="TransactionManager" singleton="true" type="Spring.Data.NHibernate.HibernateTransactionManager, Spring.Data.NHibernate20">
		<property name="DbProvider" ref="DbProvider"/>
		<property name="SessionFactory">
			<ref object="SessionFactory" />
		</property>
	</object>

[tcrossland]

I recently suffered the dreaded ORA-01453 using the following setup:

• .NET Framework 2.0
• Spring.NET 1.1.2
• NHibernate 1.2
• Oracle 8i (don't even ask!)
• System.Data.OracleClient (Can't use Oracle.DataAccess.Client with 8i, unfortunately)

After spending a good few hours debugging NHibernate and Spring, I tracked the following code in NHibernate.Transaction.AdoTransaction:

public void Begin(IsolationLevel isolationLevel)
{
    ...
    try
    {
        if (isolationLevel == IsolationLevel.Unspecified)
        {
            isolationLevel = session.Factory.Isolation;
        }

        if (isolationLevel == IsolationLevel.Unspecified)
        {
            trans = session.Connection.BeginTransaction();
        }
        else
        {
            trans = session.Connection.BeginTransaction(isolationLevel);
        }
    }
...

It turns out that Spring's TransactionAttribute has as default IsolationLevel.ReadCommitted. The obvious attempt to explicitly mark the Transaction IsolationLevel as IsolationLevel.Unspecified resolved the problem. Here's my configuration:

<object id="SessionFactory" type="Bluetab.Commons.Dao.AttributeDrivenLocalSessionFactoryObject, Bluetab.Commons">
  <property name="DbProvider" ref="DbProvider"/>
  <property name="AnnotatedMappingAssemblies">
    <list>
      <value>Bt.DataModel</value>
    </list>
  </property>
  <property name="HibernateProperties">
    <object type="System.Collections.Hashtable">
      <constructor-arg>
        <dictionary>
          <entry key="hibernate.connection.provider" value="NHibernate.Connection.DriverConnectionProvider"/>
          <entry key="hibernate.dialect" value="NHibernate.Dialect.OracleDialect"/>
          <entry key="hibernate.connection.driver_class" value="NHibernate.Driver.OracleClientDriver"/>
          <entry key="hibernate.connection.isolation" value="Unspecified"/>
        </dictionary>
      </constructor-arg>
    </object>
  </property>
</object>

<object id="TransactionInterceptor" type="Spring.Transaction.Interceptor.TransactionInterceptor, Spring.Data">
  <property name="TransactionManager" ref="TransactionManager"/>
  <property name="TransactionAttributeSource" ref="AttributeTransactionAttributeSource"/>
</object>

<object id="AttributeTransactionAttributeSource" type="Spring.Transaction.Interceptor.AttributesTransactionAttributeSource, Spring.Data" />

<object id="TransactionManager" type="Spring.Data.NHibernate.HibernateTransactionManager, Spring.Data.NHibernate12">
  <property name="DbProvider" ref="DbProvider"/>
  <property name="SessionFactory" ref="SessionFactory"/>
</object>

<tx:attribute-driven transaction-manager="TransactionManager"/>

[Mark Pollack]

Hi,
Do you know if this is an 8i issue or one with System.Data.OracleClient. ReadCommitted is a default used elsewhere in the base class libraries from msoft..

Mark

[tcrossland]

I've run into this again using Spring.NET 1.3.1 (Nightly Build) and NHibernate 3.1, the above configuration no longer solves the problem. I'm looking into it, but it is also occurring in my Oracle 10g (but using Oracle8iDialect due to the fact that our customer is using Oracle 8i!). Will post further details once I've figured out what's going on...

[tcrossland]

Seems like Spring.Transaction.Support.DefaultTransactionDefin ition now has Isolation.ReadCommitted hard-wired as a default value. When no TransactionAttribute is present, this isolation level will be sent to NHibernate, overriding it's "connection.isolation" property.

I might create an issue in JIRA for this, as I think it would be useful to override this value by configuration instead of having to patch DefaultTransactionDefinition.

[tcrossland]

An alternative solution could be to override HibernateTransactionManager.DoBegin(), but this is complicated by the fact that the inner class HibernateTransactionObject is private.

[tcrossland]

I've created issue https://jira.springsource.org/browse/SPRNET-1513 with a patch to allow HibernateTransactionManager to be subclassed.