In my C# .NET 3.5 application I am using CastleProject ActiveRecord over NHibernate. This is desktop application using MS SQL Server 2008. I have set ADO command timeout to 0 to prevent timeout exception during bulk operations:
<activerecord>
<config>
...
<add key="hibernate.command_timeout" value="0" />
</config>
</activerecord>
<hibernate-configuration xmlns="urn:nhibernate-configuration-2.2">
<session-factory>
...
<property name="command_timeout">0</property>
</session-factory>
</hibernate-configuration>
However, I am still receiving timeout exception! The NHibernate log shows something like this:
Somewhere at the beginning:
2010-10-02 06:29:47,746 INFO NHibernate.Driver.DriverBase - setting ADO.NET command timeout to 0 seconds
Somewhere at the end:
2010-10-02 07:36:03,020 DEBUG NHibernate.AdoNet.AbstractBatcher - Closed IDbCommand, open IDbCommand s: 0 2010-10-02 07:36:03,382 ERROR NHibernate.Event.Default.AbstractFlushingEventListener - Could not syn chronize database state with session NHibernate.HibernateException: An exception occurred when executing batch queries ---> System.Data.S qlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the opera tion or the server is not responding. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
How come? How to fix this?
It's correct that a value of 0 indicates no timeout (as defined in the MSDN docs), however while NHibernate's driver passes the config value to the db command when it's >= 0, the batcher's condition checks that the value is > 0.
Therefore, when you set batching on, with a timeout value of 0, the value isn't carried over to the db command so it remains as default.
It's entirely possible that this is by design, and that NHibernate developers intentionally disabled disabling timeouts for batch scenarios. Disabling timeout is a bad idea anyway, if you have troubles with timeout errors I would raise the value, but not disable it.
Please confirm this with NHibernate devs.