NHibernate Transaction Timeout for long transaction process

bavarian26 · **Joined:** Mon May 11, 2009 7:28 am **Posts:** 8

Hi Everyone,

i have encountered the following problem in my project while i am using Nhibernate (version 1.2.0.4000):

I have a windows service (local service) that when it is started, it calls a web service in my solution that triggers a transaction to be executed. The transaction contains a very long procedure of validations, saving, inserting, and many other processes in it which takes expectedly 15-20 minutes to finish. But after the transaction begins and works normally for about 8-9 minutes, the transaction stops and the following crash is registered in my log:

2009-05-07 17:23:14,637 [14] ERROR | NHibernate.Util.ADOExceptionReporter - cannot open connection
2009-05-07 17:23:14,637 [14] WARN | NHibernate.Util.ADOExceptionReporter - System.Transactions.TransactionException: The operation is not valid for the state of the transaction. ---> System.TimeoutException: Transaction Timeout
--- End of inner exception stack trace ---
at System.Transactions.TransactionState.EnlistPromotableSinglePhase(InternalTransaction tx, IPromotableSinglePhaseNotification promotableSinglePhaseNotification, Transaction atomicTransaction)
at System.Transactions.Transaction.EnlistPromotableSinglePhase(IPromotableSinglePhaseNotification promotableSinglePhaseNotification)
at System.Data.SqlClient.SqlInternalConnection.EnlistNonNull(Transaction tx)
at System.Data.SqlClient.SqlInternalConnection.Enlist(Transaction tx)
at System.Data.SqlClient.SqlInternalConnectionTds.Activate(Transaction transaction)
at System.Data.ProviderBase.DbConnectionInternal.ActivateConnection(Transaction transaction)
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at NHibernate.Connection.DriverConnectionProvider.GetConnection()
at NHibernate.Impl.SessionFactoryImpl.OpenConnection()
2009-05-07 17:23:14,637 [14] ERROR | NHibernate.Util.ADOExceptionReporter - The operation is not valid for the state of the transaction.
2009-05-07 17:23:14,637 [14] WARN | NHibernate.Util.ADOExceptionReporter - System.TimeoutException: Transaction Timeout
2009-05-07 17:23:14,637 [14] ERROR | NHibernate.Util.ADOExceptionReporter - Transaction Timeout
2009-05-07 17:23:14,668 [14] ERROR | KFSC.Hrm.Services - Exception caught at: OnScheduledEvent()
2009-05-07 17:23:14,918 [14] ERROR | My Logger - Unhandled exception
NHibernate.ADOException: cannot open connection ---> System.Transactions.TransactionException: The operation is not valid for the state of the transaction. ---> System.TimeoutException: Transaction Timeout
--- End of inner exception stack trace ---
at System.Transactions.TransactionState.EnlistPromotableSinglePhase(InternalTransaction tx, IPromotableSinglePhaseNotification promotableSinglePhaseNotification, Transaction atomicTransaction)
at System.Transactions.Transaction.EnlistPromotableSinglePhase(IPromotableSinglePhaseNotification promotableSinglePhaseNotification)
at System.Data.SqlClient.SqlInternalConnection.EnlistNonNull(Transaction tx)
at System.Data.SqlClient.SqlInternalConnection.Enlist(Transaction tx)
at System.Data.SqlClient.SqlInternalConnectionTds.Activate(Transaction transaction)
at System.Data.ProviderBase.DbConnectionInternal.ActivateConnection(Transaction transaction)
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at NHibernate.Connection.DriverConnectionProvider.GetConnection()
at NHibernate.Impl.SessionFactoryImpl.OpenConnection()
--- End of inner exception stack trace ---
at NHibernate.Impl.SessionFactoryImpl.OpenConnection()
at NHibernate.Impl.ConnectionManager.GetConnection()
at NHibernate.Impl.BatcherImpl.Prepare(IDbCommand cmd)
at NHibernate.Impl.BatcherImpl.ExecuteReader(IDbCommand cmd)
at NHibernate.Loader.Loader.GetResultSet(IDbCommand st, 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)
at NHibernate.Loader.Loader.ListIgnoreQueryCache(ISessionImplementor session, QueryParameters queryParameters)
at NHibernate.Loader.Loader.List(ISessionImplementor session, QueryParameters queryParameters, ISet querySpaces, IType[] resultTypes)
at NHibernate.Loader.Criteria.CriteriaLoader.List(ISessionImplementor session)
at NHibernate.Impl.SessionImpl.Find(CriteriaImpl criteria, IList results)
at NHibernate.Impl.SessionImpl.Find(CriteriaImpl criteria)
at NHibernate.Impl.CriteriaImpl.List()
at NHibernate.Impl.CriteriaImpl.UniqueResult()
at NHibernate.Impl.CriteriaImpl.UniqueResult[T]()
at KFSC.Hrm.Services.AnnualRaiseService.UpdateEmployeeDegree(EmployeeInfo e, DateTime date, IEmployeeRepository empRep) in D:\Projects\KFSC\3.Developing\Sourcecode\KFSC.Hrm\KFSC.HRM.Services\AnnualRaise\AnnualRaiseService.cs:line 237
at KFSC.Hrm.Services.AnnualRaiseService.<>c__DisplayClass9.<ApplyAnnualRaiseToEmployee>b__8() in D:\Projects\KFSC\3.Developing\Sourcecode\KFSC.Hrm\KFSC.HRM.Services\AnnualRaise\AnnualRaiseService.cs:line 221
at EsFramework.With.Transaction(IsolationLevel level, Proc transactional) in D:\Projects\KFSC\3.Developing\Sourcecode\EsFramework\With\With.Transaction.cs:line 45
at EsFramework.With.Transaction(Proc transactional) in D:\Projects\KFSC\3.Developing\Sourcecode\EsFramework\With\With.Transaction.cs:line 68
at KFSC.Hrm.Services.AnnualRaiseService.ApplyAnnualRaiseToEmployee(Nullable`1 raiseId, EmployeeInfo e, DateTime date, Nullable`1 decisionId, Int32 applicantId, Int32 transactionType, IEmployeeRepository empRep, Boolean isBatch, Boolean skipValidation, Guid batchKey, Boolean raiseError) in D:\Projects\KFSC\3.Developing\Sourcecode\KFSC.Hrm\KFSC.HRM.Services\AnnualRaise\AnnualRaiseService.cs:line 218
at KFSC.Hrm.Services.AnnualRaiseService.SaveAnnualRaiseForEmployees(Nullable`1 raiseId, ICollection`1 employees, DateTime date, Nullable`1 decisionId, Int32 applicantId, Int32 transactionType, Boolean isBatch, Guid batchKey, Boolean raiseErrors) in D:\Projects\KFSC\3.Developing\Sourcecode\KFSC.Hrm\KFSC.HRM.Services\AnnualRaise\AnnualRaiseService.cs:line 183
at KFSC.Hrm.Services.AnnualRaiseService.OnScheduledEvent(DateTime date, KeyValueXML keyValueXml) in D:\Projects\KFSC\3.Developing\Sourcecode\KFSC.Hrm\KFSC.HRM.Services\AnnualRaise\AnnualRaiseService.cs:line 328

I have made some steps to solve it -but it failed totally-, as followed:
1- I tried to increase the transaction timeout for the NHibernate connection in my config:

Code:

   <property name="connection.connection_string">
      Data Source=Es-sys403; Initial Catalog=GRP;User ID=sa; password=sa2005; Connect Timeout=3600
   </property>

and it still not working and the same error occurs !!!

2- I tried to change transaction timeout property in the Component Services -> COM+ Applications from 60 seconds to 3600 seconds and still the same !!!!

Can anyone please help

Thanks

bavarian26 · **Joined:** Mon May 11, 2009 7:28 am **Posts:** 8

Waiting for your replies ...........

Is there any configuration to be done on the NHibernate config other than in the NHibernate connection string??

wolli · **Posted:** Mon May 11, 2009 10:07 am

You need the CommandTimeout, not the ConnectionTimeout ! Put this line in your hibernate configuration:

hibernate.command_timeout="xxxx"

0 = forever

bavarian26 · **Joined:** Mon May 11, 2009 7:28 am **Posts:** 8

wolli wrote:

You need the CommandTimeout, not the ConnectionTimeout ! Put this line in your hibernate configuration:

hibernate.command_timeout="xxxx"

0 = forever

_____________________________________

Thanks for your reply, but i dont get it where I set this property in my code exactly?

wolli · **Posted:** Mon May 11, 2009 10:52 am

Not in your code, in your configuration file (app.config, hibernate.cfg.xml). E.g. in app.config:

<property name="hibernate.command_timeout">0</property>

bavarian26 · **Joined:** Mon May 11, 2009 7:28 am **Posts:** 8

I tried it and it is still the same !!!!

wolli · **Posted:** Mon May 11, 2009 11:25 am

Hmmm .... you use "connect timeout" in your DSN. Try Connection Timeout. Don't know if it's recognised both ways ?!

Data Source=Es-sys403; Initial Catalog=GRP;User ID=sa; password=sa2005; Connection Timeout=3600

bavarian26 · **Joined:** Mon May 11, 2009 7:28 am **Posts:** 8

it did not work still the same

i forget to tell you that i am using a scope transaction .... is it a different case??!!

Also, i mentioned above i am using a windows service that is calling a Web Service assyncrounisally ...

bavarian26 · **Joined:** Mon May 11, 2009 7:28 am **Posts:** 8

This is my Nhibernate config file:

Code:

<?xml version="1.0"?>
<hibernate-configuration xmlns="urn:nhibernate-configuration-2.2">
   <session-factory name="KFSC.Project.Domain">
      <property name="connection.provider">
         NHibernate.Connection.DriverConnectionProvider
      </property>
      <property name="dialect">
         NHibernate.Dialect.MsSql2005Dialect
      </property>
      <property name="connection.driver_class">
         NHibernate.Driver.SqlClientDriver
      </property>
      <property name="connection.connection_string">
         Data Source=Es-sys403; Initial Catalog=GRP;User ID=sa; password=sa2005; Connection Timeout=3600
      </property>
      <property name="command_timeout">0</property>
      <mapping assembly="KFSC.Shared.Data" />

   </session-factory>
</hibernate-configuration>

I do what what you told me but still i have the same problem

wolli · **Posted:** Tue May 12, 2009 4:13 am

You're still with 1.2, right ? I suppose you need to prefix the properties with "hibernate.":

Code:

<property namehibernate.connection.provider">
         NHibernate.Connection.DriverConnectionProvider
      </property>
<property name="hibernate.dialect">
         NHibernate.Dialect.MsSql2005Dialect
</property>
<property name="hibernate.command_timeout">0</property>
...

wolli · **Posted:** Tue May 12, 2009 4:21 am

Sorry, wrong statement. That's only true for app.config, but not for hibernate.cfg.xml. I'm running out of ideas.

bavarian26 · **Joined:** Mon May 11, 2009 7:28 am **Posts:** 8

gadjio · **Joined:** Wed Jun 03, 2009 3:23 pm **Posts:** 1

Did you try to change the value for the transaction timeout in the machine.config

<system.transactions>

<defaultSettings distributedTransactionManagerName="" timeout="00:05:00" />

<machineSettings maxTimeout="00:30:00" />
</system.transactions>

bavarian26 · **Joined:** Mon May 11, 2009 7:28 am **Posts:** 8

Thanks for your advice

but i have changed the code of mine so that the timeout want by dividing my transaction to a smaller shorter transactions