We are using hibernate, spring and mssql. While running queries in a transaction, we see that the implicit transaction is on in MSSQL profiler. We want to set the implicit transaction off.

-- network protocol: TCP/IP
set quoted_identifier on
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions on
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read uncommitted

We would like to turn that off. We tried to set the hibernate.connection.autocommit=false in the hibernate.properties file, but that does not seem to have any effect. Here is how the session factory is created.

<bean id="fcSessionFactory" class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
<description>Hibernate session factory</description>
<property name="configLocation" value="/hibernate.cfg.xml" />
<property name="hibernateProperties" value="classpath:hibernate.properties" />
<property name="dataSource" ref="fcDataSource" />
<property name="jtaTransactionManager" ref="transactionManagerJBossJTA" />
<property name="lobHandler" ref="$fc{fleetcycle.fleetcycleSessionFactory.lobHandler}"/>
<property name="eventListeners">
<map>
<entry key="post-insert" value-ref="postInsertAuditListener" />
<entry key="pre-update" value-ref="preUpdateAuditListener" />
<entry key="post-update" value-ref="postUpdateAuditListener" />
<entry key="post-delete" value-ref="postDeleteAuditListener" />
</map>
</property>
</bean>

hibernate.properties file is used when trying to create the session factory.

We also tried using this porperty as a JVM parameter -Dhibernate.connection.autocommit=false during server start up, that also did not help us getting the implicit transactions turned off. Does any one know how I can get this turned off? Any help is apprecriated.

As far as I know, the implicit_transactions Flag of MS-SQL is set automatically if on the JDBC-connection the autoCommit-Flag has been set to false. (standard-value would be true).

Hibernate sets a JDBC-connection automatically to autoCommit=false, to start it's own transactions. (see also https://www.hibernate.org/403.html for further information).

The only way which I know to turn IMPLICIT_TRANSACTIONS OFF, is to use transact-SQL-statements - but I wouldn't appreciate this way if using Hibernate/JDBC.

If the above is true and you need to issue custom sql (don't know myself because I was a good person in my previous life and thus don't have to work on MSSQL), you can provide the connections to Hibernate yourself via a custom connection provider that wraps your connection pool.

But I bet if you read the doco that comes with your DB and JDBC driver, you can get the behavior you want without such foolish shennanigans. (now if you were running Ingres, then you would have to write lame-o connection code)